pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
could not locate a valid checkpoint record
3+ messages / 2 participants
[nested] [flat]

* could not locate a valid checkpoint record
@ 2005-09-09 14:17 Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>
  2005-09-09 15:07 ` Re: could not locate a valid checkpoint record Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Dilipan Sebastiampillai @ 2005-09-09 14:17 UTC (permalink / raw)
  To: pgsql-admin

Hi all,

We had a crash with our postgresql 7.4.5 and when we rebooted we have 
this message :

 [1-1] LOG:  could not create IPv6 socket: Address family not supported 
by protocol
 [2-1] LOG:  database system shutdown was interrupted at 2005-09-08 
21:03:00 BST
[3-1] LOG:  could not open file 
"/var/lib/pgsql/data/pg_xlog/0000000000000000" (log file 0, segment 0): 
No such file or directory
[4-1] LOG:  invalid primary checkpoint record
[5-1] LOG:  could not open file 
"/var/lib/pgsql/data/pg_xlog/0000000000000000" (log file 0, segment 0): 
No such file or directory
 [6-1] LOG:  invalid secondary checkpoint record
[7-1] PANIC:  could not locate a valid checkpoint record
[2-1] LOG:  startup process (PID 24513) was terminated by signal 6
[3-1] LOG:  aborting startup due to startup process failure
 postgresql: Starting postgresql service:  failed

I tried ( after copying thes PGDATA dir obviously ) :

pg_resetxlog /var/lib/pgsql/data/ -f

After that postgreSQL starts but I realised that pg_database is empty ( 
it contains only template0 and template1 ) .
If I type "psql myDB", with myDB being a database present before the 
crash, I have access to the psql shell but a "SELECT * from myDBtable" 
gives me the right table column name but empty !!

myDB=# select * from myDBtable;
 id | use | data
----+-----+------
(0 rows)

How can I get the data back? I still have all the PGDATA/base directory.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: could not locate a valid checkpoint record
  2005-09-09 14:17 could not locate a valid checkpoint record Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>
@ 2005-09-09 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-09-13 10:50   ` Re: could not locate a valid checkpoint record Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2005-09-09 15:07 UTC (permalink / raw)
  To: Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>; +Cc: pgsql-admin

Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com> writes:
> I tried ( after copying thes PGDATA dir obviously ) :
> pg_resetxlog /var/lib/pgsql/data/ -f

> After that postgreSQL starts but I realised that pg_database is empty ( 
> it contains only template0 and template1 ) .

It sounds like pg_resetxlog guessed at an XID counter that is too small
and so all your data rows seem to be "in the future".

You need to find out approximately where the XID counter had been.
You can probably tell this by looking at the pg_clog directory;
read the pg_resetxlog man page for details.  I would suggest paying
close attention to the other settable options for resetxlog, too
--- if it got XID wrong then it likely got the others wrong as well.

Once you get out of this I'd suggest a dump/initdb/restore to make
sure your data is consistent; you may well have more problems than
appear on the surface.

Oh, and updating to the latest release of the 7.4 branch would be
a good idea too ;-).  There are some pretty serious known bugs in
7.4.5.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: could not locate a valid checkpoint record
  2005-09-09 14:17 could not locate a valid checkpoint record Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>
  2005-09-09 15:07 ` Re: could not locate a valid checkpoint record Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-09-13 10:50   ` Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Dilipan Sebastiampillai @ 2005-09-13 10:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
thanks a lot Tom, it worked.<br>
<br>
Dilipan<br>
<br>
<br>
Tom Lane wrote:
<blockquote cite="mid10710.1126278473@sss.pgh.pa.us" type="cite">
  <pre wrap="">Dilipan Sebastiampillai <a class="moz-txt-link-rfc2396E" href="mailto:dilipan.sebastiampillai@framestore-cfc.com">&lt;dilipan.sebastiampillai@framestore-cfc.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">I tried ( after copying thes PGDATA dir obviously ) :
pg_resetxlog /var/lib/pgsql/data/ -f
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <pre wrap="">After that postgreSQL starts but I realised that pg_database is empty ( 
it contains only template0 and template1 ) .
    </pre>
  </blockquote>
  <pre wrap=""><!---->
It sounds like pg_resetxlog guessed at an XID counter that is too small
and so all your data rows seem to be "in the future".

You need to find out approximately where the XID counter had been.
You can probably tell this by looking at the pg_clog directory;
read the pg_resetxlog man page for details.  I would suggest paying
close attention to the other settable options for resetxlog, too
--- if it got XID wrong then it likely got the others wrong as well.

Once you get out of this I'd suggest a dump/initdb/restore to make
sure your data is consistent; you may well have more problems than
appear on the surface.

Oh, and updating to the latest release of the 7.4 branch would be
a good idea too ;-).  There are some pretty serious known bugs in
7.4.5.

			regards, tom lane
  </pre>
</blockquote>
<br>
<br>
<pre class="moz-signature" cols="72">-- 


Dilipan Sebastiampillai
Systems - DBA
Framestore CFC
9 Noel Street
London W1F 8GH
tel : +44(0)20 7106 2907
<a class="moz-txt-link-freetext" href="http://www.framestore-cfc.com/">http://www.framestore-cfc.com/</a;
</pre>
</body>
</html>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-09-13 10:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-09 14:17 could not locate a valid checkpoint record Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>
2005-09-09 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-13 10:50   ` Dilipan Sebastiampillai <dilipan.sebastiampillai@framestore-cfc.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox