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
Date: Mon, 11 Sep 2017 18:39:17 -0400
Message-ID: <8125.1505169557@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, postgresql@richardneill.org
Subject: Re: Fixing OID directory names after a fsck
In-Reply-To: <8125.1505169557@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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