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: <7d85cf2a-db3b-b1dd-4646-396a8068a375@richardneill.org>
References: <7d85cf2a-db3b-b1dd-4646-396a8068a375@richardneill.org>
Comments: In-reply-to Richard Neill <postgresql@richardneill.org>
	message dated "Mon, 11 Sep 2017 22:52:24 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <6897.1505167752.1@sss.pgh.pa.us>
Date: Mon, 11 Sep 2017 18:09:12 -0400
Message-ID: <6898.1505167752@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Richard Neill <postgresql@richardneill.org> writes:
> Our virtual server (running 9.4) suffered a disk corruption outage due 
> to a flaky SAN. As a result, all the contents of /var/lib/postgresql 
> ended up (along with lots of other things) in lost+found with names like 
> #1835289.

> By looking for directories containing PG_VERSION (and checking the 
> version number), I found 9  directories named like: 
> ./#1835987/PG_VERSION. These look similar to other top-level dirs within
> main/base, and "strings" shows contents that does indeed look like I've 
> identified the right thing.

> So, I made a clean install of Postgres 9.4 on a new machine (Ubuntu 
> 17.04), and copied the directories into 
> /var/lib/postgresql/9.4/main/base. I now have:

> root@treacle:/var/lib/postgresql/9.4/main/base# ls
> 1  12172  12177  16384  1828647  1835009  1835289  1835317  1835987 
> 1843977  1844229  1844901  1958920

> where the first 3 directories are the system defaults, 16384 is a test 
> that I created, and 1828647 - 1958920 are the recovered top-level 
> database directories (but with the wrong names). However, Postgresql 
> doesn't recognise them.

This is unlikely to work unless you can also recover the pg_clog
contents, which unfortunately might be pretty difficult to identify.

(In any case, putting the cluster back into production is way too
scary.  If you can start it, with autovacuum off, and dump the data,
I'd recommend doing that and reloading.)

			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