From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Neill <postgresql@richardneill.org>
cc: pgsql-admin@postgresql.org
Subject: Re: Fixing OID directory names after a fsck
In-reply-to: <35287149-87d0-0763-6566-4aca468d0bad@richardneill.org>
References: <7d85cf2a-db3b-b1dd-4646-396a8068a375@richardneill.org>
 <6898.1505167752@sss.pgh.pa.us>
 <35287149-87d0-0763-6566-4aca468d0bad@richardneill.org>
Comments: In-reply-to Richard Neill <postgresql@richardneill.org>
	message dated "Mon, 11 Sep 2017 23:27:08 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <8124.1505169557.1@sss.pgh.pa.us>
Date: Mon, 11 Sep 2017 18:39:17 -0400
Message-ID: <8125.1505169557@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Richard Neill <postgresql@richardneill.org> writes:
>> This is unlikely to work unless you can also recover the pg_clog
>> contents, which unfortunately might be pretty difficult to identify.

> I can't find it. However, the failure was in the middle of the night, 
> and I think there's a pretty decent chance that postgres was idle at the 
> time - there was "probably" no transaction in progress, and the WAL 
> should have committed.

That's all fine, but it only means that the right transactions would
be marked committed in pg_clog.  There's no very good reason to suppose
that that marking has propagated back to the heap tuples in all cases.

Actually though, I suspect a big chunk of your problem is that even
tuples that are hinted committed will seem to be "in the future".
You'd need to also recover pg_control, or at least reset it to something
approximating the current XID counter, before stuff is likely to look
sane.

If you don't have pg_control, you might be able to dig around in the most
recently modified tables and guess at the current XID by looking for the
largest XIDs you can find.

But, TBH, this is getting way past what you're likely to successfully
pull off if you've never done it before.  There are folks who do PG
data recovery professionally [not me], and if your data is worth money
to you, you'd be best off hiring someone who's done this before.  If
it's not, chalk this up to a learning experience, and set up a better
backup system.

			regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin