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.