Re: Postgres Server crashed

Hi Scott/Kevin,

Everything got recovered with pg_resetxlog with appropriate -m, -x, -O and -l options set appropriately described in "http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html". We had lost 4 tables, which were insignificant (compared to losing everything), and did a sanity check on all the rest of the tables, seemed all fine. Please let me know if you see any pitfalls in the way this was recovered.

Now that things are normal, I would definitely look for a good backup, restoration process all set up. Stitch in time saves nine :-)

Thanks a ton for all the support.

Next destination postgres documentation. :-)

Many thanks and Regards,
Akash.

-----Original Message-----
From: Akash Kodibail
Sent: Tuesday, October 11, 2011 1:02 AM
To: 'Scott Marlowe'
Cc: Kevin Grittner; pgsql-admin(at)postgresql(dot)org
Subject: RE: [ADMIN] Postgres Server crashed

Hi Scott,

I believe pg_resetxlog has to have calculated and precise parameters:

$PGDATA/pg_xlog has file -- 000000010000034A000000C0 of 16Meg
$PGDATA/pg_multixact/members has file -- 0000 of 8k
$PGDATA/pg_multixact/offsets has file -- 0000 of 8k

Can you please help me out in the options that might have to be set for pg_resetxlog.

Backing up is still in progress, so will try my luck with pg_resetxlog.

Regards,
Akash.

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Tuesday, October 11, 2011 12:41 AM
To: Akash Kodibail
Cc: Kevin Grittner; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Postgres Server crashed

On Mon, Oct 10, 2011 at 1:13 PM, Akash Kodibail
<akash(dot)kodibail(at)onmobile(dot)com> wrote:
> I was hopeful of restoring the data from data files in $PGDATA path. I read this article about PITR using the recovery.conf, But I am not aware of the pre-requisites and not entirely sure about the concept beneath this.

With PITR you set up the system ahead of time to do this. It is not a
rescue / recovery procedure for a broken database. I like Kevin fear
that by starting it back up with an old pg_Controldata you may have
made matters much worse, depending on what autovacuum might have done.

Once you have a backup it's time to start experimenting, and I think
pg_resetxlog is the best place to start.

DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Further, this e-mail may contain viruses and all reasonable precaution to minimize the risk arising there from is taken by OnMobile. OnMobile is not liable for any damage sustained by you as a result of any virus in this e-mail. All applicable virus checks should be carried out by you before opening this e-mail or any attachment thereto.
Thank you - OnMobile Global Limited.