Re: PITR Recovery and out-of-sync indexes

Richard Huxton <dev(at)archonet(dot)com> writes:
> Brian Wipf wrote:
>> 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.

PG 8.2 does store data in the pg_control file with which it can check
for the most common disk-format-incompatibility problems (to wit,
endiannness, maxalign, and --enable-integer-datetimes). If Brian has
stumbled on another such foot-gun, it'd be good to identify it so we
can think about adding more checking.

Noting that one of the columns in the corrupted index was varchar,
I am wondering if the culprit could have been a locale/encoding problem
of some sort. PG tries to enforce the same LC_COLLATE and LC_CTYPE
values (via pg_control entries) but when you are migrating across
widely different operating systems like this, identical spelling of
locale names proves damn near nothing.

What are the settings being used, anyway? (pg_controldata can tell
you.) Try using sort(1) to sort the values of product_id_from_source on
both systems, in that locale, and see if you get the same sort ordering.

regards, tom lane