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?
[EMAIL PROTECTED]:~$ 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
[EMAIL PROTECTED]:~$
[EMAIL PROTECTED]:~$ 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;
[EMAIL PROTECTED]:~$
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.
!#
[EMAIL PROTECTED]:~$ 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 [EMAIL PROTECTED])
