Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

Next:From: Priya GDate: 2004-02-17 15:41:07
Subject: unsubscribe
Previous:From: Jeff BoesDate: 2004-02-17 15:26:20
Subject: pg_autovacuum bug with temp tables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group