X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1A804D1B16A for ; Tue, 22 Jun 2004 16:36:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37759-08 for ; Tue, 22 Jun 2004 19:36:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D4874D1B1A5 for ; Tue, 22 Jun 2004 16:36:04 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5MJa3Xg021116; Tue, 22 Jun 2004 15:36:03 -0400 (EDT) To: "Shea,Dan [CIS]" Cc: pgsql-performance@postgresql.org Subject: Re: after using pg_resetxlog, db lost In-reply-to: <644D07D3D59D8F408CD01AC2F833D8C62B9209@cisxa.cmc.int.ec.gc.ca> References: <644D07D3D59D8F408CD01AC2F833D8C62B9209@cisxa.cmc.int.ec.gc.ca> Comments: In-reply-to "Shea,Dan [CIS]" message dated "Tue, 22 Jun 2004 14:42:56 -0400" Date: Tue, 22 Jun 2004 15:36:03 -0400 Message-ID: <21115.1087932963@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/236 X-Sequence-Number: 7294 "Shea,Dan [CIS]" writes: > The pg_resetxlog was run as root. It caused ownership problems of > pg_control and xlog files. > Now we have no access to the data now through psql. The data is still > there under /var/lib/pgsql/data/base/17347 (PWFPM_DEV DB name). But > there is no reference to 36 of our tables in pg_class. Also the 18 > other tables that are reported in this database have no data in them. > Is there anyway to have the database resync or make it aware of the data > under /var/lib/pgsql/data/base/17347? > How can this problem be resolved? What this sounds like is that you reset the transaction counter along with the xlog, so that those tables appear to have been created by transactions "in the future". This could be repaired by doing pg_resetxlog with a more appropriate initial transaction ID, but figuring out what that value should be is not easy :-( What I'd suggest is grabbing pg_filedump from http://sources.redhat.com/rhdb/ and using it to look through pg_class (which will be file $PGDATA/base/yourdbnumber/1259) to see the highest transaction ID mentioned in any row of pg_class. Then pg_resetxlog with a value a bit larger than that. Now you should be able to see all the rows in pg_class ... but this doesn't get you out of the woods yet, unless there are very-recently-created tables shown in pg_class. I'd suggest next looking through whichever tables you know to be recently modified to find the highest transaction ID mentioned in them, and finally doing another pg_resetxlog with a value a few million greater than that. Then you should be okay. The reason you need to do this in two steps is that you'll need to look at pg_class.relfilenode to get the file names of your recently-modified tables. Do NOT modify the database in any way while you are running with the intermediate transaction ID setting. > Jun 22 13:38:23 murphy postgres[1460]: [2-1] ERROR: xlog flush request > 210/E757F150 is not satisfied --- flushed only to 0/2074CA0 Looks like you also need a larger initial WAL offset in your pg_resetxlog command. Unlike the case with transaction IDs, there's no need to try to be somewhat accurate in the setting --- I'd just use a number WAY beyond what you had, maybe like 10000/0. Finally, the fact that all this happened suggests that you lost the contents of pg_control (else pg_resetxlog would have picked up the right values from it). Be very sure that you run pg_resetxlog under the same locale settings (LC_COLLATE,LC_CTYPE) that you initially initdb'd with. Otherwise you're likely to have nasty index-corruption problems later. Good luck. Next time, don't let amateurs fool with pg_resetxlog (and anyone who'd run it as root definitely doesn't know what they're doing). It is a wizard's tool. Get knowledgeable advice from the PG lists before you use it rather than after. regards, tom lane