X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4954CD1DD26 for ; Tue, 17 Feb 2004 15:31:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04323-01 for ; Tue, 17 Feb 2004 11:31:48 -0400 (AST) Received: from mail3.panix.com (mail3.panix.com [166.84.1.74]) by svr1.postgresql.org (Postfix) with ESMTP id 4E6F7D1DCD9 for ; Tue, 17 Feb 2004 11:31:44 -0400 (AST) Received: from panix1.panix.com (panix1.panix.com [166.84.1.1]) by mail3.panix.com (Postfix) with ESMTP id 05F9F98B0A for ; Tue, 17 Feb 2004 10:31:27 -0500 (EST) Received: (from adler@localhost) by panix1.panix.com (8.11.6p2-a/8.8.8/PanixN1.1) id i1HFV4l19530 for pgsql-admin@postgresql.org; Tue, 17 Feb 2004 10:31:04 -0500 (EST) Date: Tue, 17 Feb 2004 10:31:01 -0500 From: Michael Adler To: pgsql-admin@postgresql.org Subject: how to recover corrupt data Message-ID: <20040217153101.GB25887@pobox.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/253 X-Sequence-Number: 12427 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