From: Michael Adler <adler@pobox.com>
To: pgsql-admin@postgresql.org
Subject: how to recover corrupt data
Date: Tue, 17 Feb 2004 10:31:01 -0500
Message-ID: <20040217153101.GB25887@pobox.com> (raw)
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@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@myhost:~$
myuser@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@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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: adler@pobox.com
Subject: Re: how to recover corrupt data
In-Reply-To: <20040217153101.GB25887@pobox.com>
References: <20040217153101.GB25887@pobox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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