Fixing OID directory names after a fsck

From: Richard Neill <postgresql(at)richardneill(dot)org>
To: pgsql-admin(at)postgresql(dot)org
Subject: Fixing OID directory names after a fsck
Date: 2017-09-11 21:52:24
Message-ID: 7d85cf2a-db3b-b1dd-4646-396a8068a375@richardneill.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

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(at)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.

From what I've been able to understand, if I could find out what the
database OIDs used to be, I could just rename the 1828647 etc to the
correct names, and it would work. But I'm not sure how to do this... all
the documents are about how to find the file if you know the OID, rather
than how to set the OID to match the file. I tried the "obvious" step
of creating a new database called "rescue", getting 16384, stopping
postgresql, renaming one of my backups to 16384, and restarting - but
that doesn't help.

Can anyone here give me a pointer?

Thanks very much for your help,

Richard

P.S. I've just joined, and I think I'm posting this to the right place
but if not, please let me know.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2017-09-11 22:09:12 Re: Fixing OID directory names after a fsck
Previous Message Tom Lane 2017-09-11 20:44:14 Re: pg_dump making schema output consistent.