how to recover corrupt data

[Michael Adler]

The names of the participants have been changed to protect the innocent.

I'm trying to recover some lost data from this weekend. Apparently, we were able to INSERT into critical_table without error, but not able to SELECT, COPY, or VACUUM critical_table. I saved a copy of the PGDATA directory and restored from a backup.

Is there any way to extract this (possibly intact) data?


***@myhost:~$ vacuumdb -z -a
Vacuuming critical_db
WARNING: Rel critical_table: TID 11193/1: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/2: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/3: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/4: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/5: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/6: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/7: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/8: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/9: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/10: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/11: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/12: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/13: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/14: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/15: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/16: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/17: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/18: OID IS INVALID. TUPGONE 1.
WARNING: Rel critical_table: TID 11193/19: OID IS INVALID. TUPGONE 1.
PANIC: open of /var/lib/postgres/data/pg_clog/0455 failed: No such file or directory
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
vacuumdb: vacuum critical_db failed
***@myhost:~$


***@myhost:~$ pg_dump -Rx critical_db > backup
pg_dump: ERROR: MemoryContextAlloc: invalid request size 1396789829
pg_dump: lost synchronization with server, resetting connection
pg_dump: SQL command to dump the contents of table "critical_table" failed: PQendcopy() failed.
pg_dump: Error message from server: pg_dump: The command was: COPY public.critical_table ( ... column names here ... ) TO stdout;
***@myhost:~$



critical_db=# SELECT count(*) from critical_table;
count
--------
677939
(1 row)
critical_db=# create table ct_backup as select * from critical_table;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#


***@myhost:~$ postgres critical_db
LOG: database system was shut down at 2004-02-17 09:13:34 EST
LOG: checkpoint record is at 47/34F245FC
LOG: redo record is at 47/34F245FC; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 10038641; next oid: 39213744
LOG: database system is ready
POSTGRES backend interactive interface
$Revision: 1.307.2.1 $ $Date: 2003/01/01 21:57:18 $
backend> create table ct_backup as select * from critical_table;
Segmentation fault


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Tom Lane]

Post by Michael Adler
Is there any way to extract this (possibly intact) data?

It looks like you have one corrupted, perhaps partially zeroed, page
in your table. There may be more, but the trace output you show only
has corrupted tuples on one page (11193). You could try replacing that
page with zeroes, which will lose the rows on that page but allow you to
recover the rest of the data. See the archives for discussion of
corruption recovery procedures (searching for references to pg_filedump
will probably lead you to the right threads).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)