Обсуждение: Trying to restore a PostgreSQL-9.6 database from an old complete dumpand/or a up-to-date just base directory and other rescued files
Hello. I'm trying to restore/rescue a database from some that I have:
* I have all the recent files in PGDATA/base
(/var/lib/postgresql/9.6/main/base/), but I have not the complete
/var/lib/postgresql/9.6/main/
* I have all files from an old backup (and not much different) dump
that I restored in a new installation of PostgreSQL-9.6.
* I have a lot of rescued files from the hard drive (from ddrescue)
and I got thousand of files without a name (having a "#" and then a
number instead and in lost+found directory), so, for instance:
* I have the pg_class file
* I have the pg_clog directory with 0000 file
I have not:
* I have not the original content in pg_xlog directory (I have an
old one, maybe, if it didn't changed, it still being useful)
The PGDATA/base directory I rescued from the hard drive (damaged)
contains directories 1, 12406, 12407 and 37972 with a lot of files
inside. I check with pg_filedump -fi that my updated data is stored on
files in directory 37972.
Same (but old) data is stored in files in directory PGDATA/base/16387 in
the restored dump.
I tried directly to copy the files from one to other mixing the updated
data over the old database but it doesn't work. After solved permission
errors I can go in to the "Frankenstein" database in that way:
postgres@host:~$ postgres --single -P -D /var/lib/postgresql/9.6/main/
dbname
And I tried to do something, like reindex, and I get this error:
PostgreSQL stand-alone backend 9.6.16
backend> reindex system dbname;
ERROR: could not access status of transaction 136889
DETAIL: Could not read from file "pg_subtrans/0002" at offset 16384:
Success.
CONTEXT: while checking uniqueness of tuple (1,7) in relation
"pg_toast_2619"
STATEMENT: reindex system dbname;
Certainly pg_subtrans/0002 file is part of the "Frankenstein" and not
the good one (because I didn't find it yet, not with that name), so I
tried: to copied another one that seems similar first and then, to
generate 8192 zeroes with dd to that file, in both cases I get the same
error (and in case that the file doesn't exist get the DETAIL: Could
not open file "pg_subtrans/0002": No such file or directory.). Anyway I
have not idea that what should be that file. Do you think could I get
that data from other file? Or could I find the missing file using some
tool? So pg_filedump show me empty for the other file in that directory
pg_subtrans/0000.
Extra note: I found this useful blog post that talk about restore from
just rescued files using pg_filedump, pg_class's file, reindex system
and other tools and but is so hard for me to understand how to adapt it
to my concrete and, I think, easier problem (I think that my problem is
easier because I have a dump):
https://www.commandprompt.com/blog/recovering_a_lost-and-found_database/
Thank you very much for some help.
al <al@blogmail.cc> writes:
> Hello. I'm trying to restore/rescue a database from some that I have:
> * I have all the recent files in PGDATA/base
> (/var/lib/postgresql/9.6/main/base/), but I have not the complete
> /var/lib/postgresql/9.6/main/
> * I have all files from an old backup (and not much different) dump
> that I restored in a new installation of PostgreSQL-9.6.
> * I have a lot of rescued files from the hard drive (from ddrescue)
> and I got thousand of files without a name (having a "#" and then a
> number instead and in lost+found directory), so, for instance:
> * I have the pg_class file
> * I have the pg_clog directory with 0000 file
> I have not:
> * I have not the original content in pg_xlog directory (I have an
> old one, maybe, if it didn't changed, it still being useful)
Unfortunately, not having an up-to-date copy of the xlog info is
practically fatal to your hopes of getting back consistent data.
There are some Postgres support companies who specialize in data recovery;
if this data is worth money to you, go hire a specialist. The chance
of hacking your way to a usable fix without knowing exactly what you're
doing is nil. (A specialist might not be successful either, of course.)
regards, tom lane
On 20/2/20 15:47, Tom Lane wrote: > > Unfortunately, not having an up-to-date copy of the xlog info is > practically fatal to your hopes of getting back consistent data. > [...] > Ok, so if the only way to restore the data is getting the content in pg_xlog directory I'm looking for it. I went to my restored pg_xlog directory and I found that it has an empty directory called arhive_status and 7 binary files with names: 00000001000000000000000A 00000001000000000000000B 00000001000000000000000C 00000001000000000000000D 00000001000000000000000E 00000001000000000000000F 000000010000000000000010 pg_filedump recognize them as a PostgreSQL files but doesn't show anything, I'm going to try with pg_xlogdump. Is my first time for me trying to use this program, so sintax is not familiar to me, so, anyway I saw that those files have each exactly the size of 16777216 bytes, so is easier to try to find some files with that size in my lost+found. I got 5 files with that size the names are: #288294 (date 2019-04-01) #288287 (date 2019-05-14) #288293 (date 2019-07-02) #261307 (date 2019-11-27) #270185 (date 2020-01-28) (the inode name where ddrescue found) Ok, they are not 7 but maybe I don't need 7 (I don't know), maybe I have 7 now because I was testing some restores from dump in addition at the first one. Also my old dump is from 2019-04-23, so the first one could be the same? So my next step is going to try to read those files with pg_xlogdump and/or trying to name them with those namefiles (beginning with 00000001000000000000000A by date). In the meantime I'm going to wait from other help from you, because is my first time studying PostgreSQL File Layout. It's fascinating but is huge documentation and I need some orientation to no lose the path to my particular issue. Thank you so much.