Re: missing data/global - Mailing list pgsql-hackers

Tom I did the following:

(found out 7.2.3 does not have pg_database)

1. saved old data etc.

2. created new database, and the database. database oid was 16556;

3. moved data/global to the old data directory.

4. though, that postmaster would actually use the database oid to locate the 
directory, then load everything from there.. old database oid was 77573557, so 
I just linked this to 16556 in the data/base direcotry. (this might be the 
first possible error)

Now I can connect to the 'old' database, but get the error 

FATAL 1:  Index pg_operator_oid_index is not a btree

(if I run postmaster with -P I get not errors, but no tables as well).

By the way, I had to copy over the 'new' files from pg_clog and pg_xlog (this 
is the second possible error) to get the postmaster running. Perhaps better 
would be to use pg_resetxlog or similar?

Daniel

>>>Tom Lane said:> Daniel Kalchev <daniel@digsys.bg> writes:> > Is there ANY chance to recover data from a database
systemthat suffered d    isk> > crash, and is not missing the data/global directory?> > Version is 7.2.4. Database
filesseem to be intact as well as pg_clog and > > pg_xlog directories.> > The hard part I think would be reconstructing
pg_database,because you'd> need to get the database OIDs right.  I can't think of any way to do> that that doesn't
involvepoking at the file with a hex editor.> > Here's a sketch of how I'd proceed:> > 1. Make a tar backup of what you
have! That way you can start over> after you screw up ;-)> > 2. I assume you know the names and properties of your
databases,users,> and groups if any; also the SYSID numbers for the users and groups.> A recent pg_dumpall script would
bea good place to get this info.> > 3. You're also going to need to figure out the OIDs of your databases> (the OIDs
arethe same as the names of their subdirectories under> $PGDATA/base).  Possibly you can do this just from
directory/filesizes.> Note that template1 should be OID 1, and template0 will have the next> lowest number (probably
16555,in 7.2).> > 4. Initdb a scratch database in some other place (or move aside your> existing files, if that seems
safer). In this scratch DB, create> databases, users, and groups to match your old setup.  You should be> able to
duplicateeverything except the database OIDs using standard> SQL commands.> > 5. Shut down scratch postmaster, then
hex-editpg_database to insert the> correct OIDs.  Use pg_filedump or a similar tool to verify that you did> this
properly.>> 6. Restart scratch postmaster, and VACUUM FREEZE pg_database, pg_shadow,> and pg_group (from any database).
This will make the next step safe.> > 7. Stop scratch postmaster, and then copy over its $PGDATA/global> directory into
theold DB.> > 8. Cross your fingers and start postmaster ...> > This will probably *not* work if you had been doing
anythingto> pg_database, pg_shadow, or pg_group between your last checkpoint and the> crash, because the reconstructed
tablesare not going to be physically> identical to what they were before, so any actions replayed from WAL> against
thosetables will be wrong.  Hopefully you won't have that> problem.  If you do, it might work to shut down the
postmasterand again> copy the scratch $PGDATA/global directory into the old DB, thereby> overwriting what the WAL
replaydid.  This is getting into the realm of> speculation though.> >             regards, tom lane> >
---------------------------(endof broadcast)---------------------------> TIP 2: you can get off all lists at once with
theunregister command>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)