how to recover corrupt data

From: Michael Adler <adler(at)pobox(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: how to recover corrupt data
Date: 2004-02-17 15:31:01
Message-ID: 20040217153101.GB25887@pobox.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

myuser(at)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
myuser(at)myhost:~$

myuser(at)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;
myuser(at)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.
!#

postgres(at)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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Priya G 2004-02-17 15:41:07 unsubscribe
Previous Message Jeff Boes 2004-02-17 15:26:20 pg_autovacuum bug with temp tables?