Re: Postgresql 9.0.1 Corrupted

Please reply to the mailing list, not just to me. Use the "reply all"
button if your email program doesn't support reply-to-list.

> Yes I have the backup of the point where the db was corrupted. But no
> backup.

Ouch. I hope your data isn't too important to you.

If your data is vital to you, you should hire somebody who is
experienced in data recovery from damaged PostgreSQL instances. You can
find a list of professional PostgreSQL consultants here:

http://www.postgresql.org/support/professional_support

If it's that important, you should also stop the machine and take a full
disk image. It's probably too late now anyway, but a disk image just
after an fsck problem can capture data that would otherwise be
unrecoverable.

If it's not so important that you want to pay someone for full-service
data recovery, there may still be steps you can try.

First, before you do ANYTHING else, put a copy of the original damaged
database, the one you made before you tried to fix anything, on an
external hard drive or burn it to a DVD if it fits. Keep it somewhere
separate where you cannot accidentally modify it over overwrite it.

Now rename the data directory you've been messing with. I wouldn't
delete it, just to be safe. Where it was, put a copy of the "backup" you
took just after you discovered the damage, before you tried to fix it.

If you try to start the server, you should now get the error:

"DETAIL: The database cluster was initialized with PG_CONTROL_VERSION
0, but the server was compiled with PG_CONTROL_VERSION 903."

Just like you did before.

At this point you might be able to take careful steps to repair the
database. I don't know much about this, so others may be able to advise
you better.

What I'd try is:
- restore the PG_VERSION file from a newly inited database.
- Make sure the permissions are set so that user "postgres" has
read/write access to the entire database. Use something like
"chmod ug=rwX,o= -R /path/to/data/directory" .
- sudo -u postgres bash
- cd to the datadir
- rm pg_notify && mkdir pg_notify
- rm pg_xlog && mkdir -p pg_xlog/archive_status
- pg_resetxlog /path/to/datadir
- Start Pg in single-user mode. On my Ubuntu system that's a command
like this, but you'll have to adjust paths to suit your machine:

/usr/lib/postgresql/8.4/bin/postgres --single -D `pwd` \
-c config_file=/etc/postgresql/8.4/main/postgresql.conf \
-c hba_file=/etc/postgresql/8.4/main/pg_hba.conf \
postgres

... and see if it starts at all.

--
Craig Ringer