Re: Having trouble restoring our backups

On Fri, Jun 12, 2009 at 11:08 AM, Bryan Murphy <bmurphy1976(at)gmail(dot)com>wrote:

> I've read through the PITR documentation many times. I do not see anything
> that sheds light on what I'm doing wrong, and I've restored older backups
> successfully many times in the past few months using this technique. I have
> no explanation for why all of a sudden my last few backups are not restoring
> properly and we've not changed anything on our database setup recently.
>
> I'm currently creating a full backup of our primary database and will build
> a second warm spare with that, but the additional pressure this puts on our
> system is not acceptable as a long term backup solution.
>

FYI, for future reference for anybody else who runs into this problem, it
appears we somehow lost the pg_clog files during the last time we took a
full snapshot of our primary database. Our PITR spare was happily
recovering wal files, but when I tried to bring it up it was missing the
pg_clogs and it's literally been weeks since I last tried to do this (stupid
on my part).

We appear to have repaired our PITR based backup by copying the missing
pg_clog files from our production database which thankfully still had them.
I do not know how they got dropped from the last snapshot we took, but
we'll be looking into our hot-spare building process to see what we can do
to prevent this from happening again.

Thanks,
Bryan