Re: Postgresql-9.0.1 Recovery

On 30/08/2011 6:59 PM, Venkat Balaji wrote:
> Hello Everyone,
>
> I have a situation here -
>
> I am trying to restore the production online backup and recover the same.
>
> - I had initially rsynced (excluded pg_log) the data directory and the
> tarred and zipped the same

Did you do that after pg_start_backup() or on a stopped database server?

If you did it on a running database server without first running
pg_start_backup(), your backup is invalid.

Personally I like to take my base backups from an LVM snapshot of the
datadir just to be extra safe. That isn't necessary, though, and a
regular rsync or tar or whatever of a datadir after pg_start_backup() is
fine.

Remember to run pg_stop_backup() afterwards.

> - I got an error "unable to read <filename> from pg_clog location"
> (file size is around 160K)

... from PostgreSQL, when you tried to start it?

What emitted that error message?

> What i understood is that, rsync some how missed out on syncing the
> files in "pg_clog" so, i had manually coped the missing pg_clog file
> from production and tried recovery.

That won't work. You need a consistent snapshot of all the files in the
data dir. You cannot just mix and match copies taken at different times.

For efficiency reasons PostgreSQL will recycle used clog files. You
can't just copy a file over and hope that because it has the same name,
it still contains the data you want.

Your backup *failed* at the point where you got an incomplete copy of
the data directory.

> Do i need to get that particular wal archive which is before online
> backup time ?

No, you need to get the missing clog files. If you cannot do that, try
using pg_resetxlog, but be aware that that may lose transactions and can
potentially cause corruption of tables and indexes.

> By this experience what i understand is that Postgresql stores committed
> and uncommited transactions in pg_xlog / wal archive files and
> information (not the transaction data) about transaction commit status
> is stored in pg_clog. Am I correct ?

That sounds right to me, but I don't know as much about how Pg stores
things as I should.

> I am in the process of designing a disaster recovery planner for our
> productions systems.

Congratulations!

Be extremely glad this didn't happen in a real recovery scenario. This
is a marvellous example of why you should always test your backups - you
actually did, and found a problem that would've been a critical issue if
the backup were actually needed.

--
Craig Ringer