Re: PITR Recovery and out-of-sync indexes

Brian Wipf wrote:
> We are running a production server off of a new database that was
> synchronized using PITR recovery. We found that many of the btree
> indexes were out of sync with the underlying data after bringing the new
> server out of recovery mode, but the data itself appeared to be okay.
>
> Both servers have identical Intel processors and both are running 64-bit
> PostgreSQL 8.2.4. The original server is running 64-bit openSUSE 10.2
> (Linux 2.6.18.2-34-default #1 SMP Mon Jul 16 01:16:32 GMT 2007 x86_64
> x86_64 x86_64 GNU/Linux) and the new server is running Mac OS X Leopard
> Server.

This isn't necessarily safe. If your setup isn't *identical* then you
need to do a lot of checking to make sure this will work. In particular
you'd want to make sure that all your ./configure options are compatible
(e.g. --enable-integer-datetimes can change on-disk representations).

You also need to make sure there aren't any differences in behaviour in
any OS libraries used. That's not implausible since you're contrasting
Linux with a BSD-based system.

> In the Continuous Archiving Point-In-Time Recovery section of the docs,
> one of the caveats listed is:
> "Operations on hash indexes are not presently WAL-logged, so replay will
> not update these indexes. The recommended workaround is to manually
> REINDEX each such index after completing a recovery operation"
>
> Is it possible there are issues with btree indexes being maintained
> properly as well? Any other ideas?

If there is a problem then it's a bug. However, it's quite likely that
you're seeing underlying platform differences. Sounds like you want
replication rather than PITR.

--
   Richard Huxton
   Archonet Ltd