pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to recover corrupt data
2+ messages / 2 participants
[nested] [flat]
* how to recover corrupt data
@ 2004-02-17 15:31 Michael Adler <adler@pobox.com>
2004-02-17 23:45 ` Re: how to recover corrupt data Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Adler @ 2004-02-17 15:31 UTC (permalink / raw)
To: 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@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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: how to recover corrupt data
2004-02-17 15:31 how to recover corrupt data Michael Adler <adler@pobox.com>
@ 2004-02-17 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-02-17 23:45 UTC (permalink / raw)
To: Michael Adler <adler@pobox.com>; +Cc: pgsql-admin
Michael Adler <adler@pobox.com> writes:
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-02-17 23:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-17 15:31 how to recover corrupt data Michael Adler <adler@pobox.com>
2004-02-17 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
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