Re: PITR Recovery and out-of-sync indexes

On 3-Oct-07, at 8:07 AM, Tom Lane wrote:
> 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.

PG tried to enforce the same LC_COLLATE and LC_CTYPE. On OS X, the
value of en_US.utf8 didn't exist, so I created a soft link to
en_US.UTF-8 in the /usr/share/locale/ directory. When I sort the
values of product_id_from_source on both systems using the locales in
this manner I get different orderings:

Linux box:

select product_id_from_source from fed_product order by
product_id_from_source desc limit 5;
product_id_from_source
------------------------
ZZring
ZZR0-70-720
zzdangle
ZZC0-68-320 -05
ZZ538264
(5 rows)

OS X box:

select product_id_from_source from fed_product order by
product_id_from_source desc limit 10;
product_id_from_source
------------------------
zzdangle
zz06
zz05
zz04
zz03
(5 rows)

and all of these rows exist on both databases. The data appears to be
okay. Is it possible the only issue is with indexes?

I can happily live with rebuilding indexes if this is the only
problem I can expect to encounter, and I would still prefer PITR over
replication. We tried PG Pool for replication and the performance was
poor compared to a single standalone server. Slony-I worked better
for us, but it is more difficult to maintain than PG's PITR and a
warm standby is sufficient for us. It would be nice to be able to use
the read-only warm stand-by PITR at some point as well, although with
the different locale orderings, I suppose this wouldn't be possible.

Brian Wipf
ClickSpace Interactive Inc.
<brian(at)clickspace(dot)com>

Heres the output from pg_controldata on both boxes:

Linux box:
pg_control version number: 822
Catalog version number: 200611241
Database system identifier: 5087840078460068765
Database cluster state: in production
pg_control last modified: Wed 03 Oct 2007 11:16:34 AM MDT
Current log file ID: 1126
Next log file segment: 99
Latest checkpoint location: 466/62000020
Prior checkpoint location: 466/61000020
Latest checkpoint's REDO location: 466/62000020
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/1720940695
Latest checkpoint's NextOID: 506360
Latest checkpoint's NextMultiXactId: 16963
Latest checkpoint's NextMultiOffset: 41383
Time of latest checkpoint: Wed 03 Oct 2007 11:16:34 AM MDT
Minimum recovery ending location: 0/0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: en_US.utf8
LC_CTYPE: en_US.utf8

OS X box:
pg_control version number: 822
Catalog version number: 200611241
Database system identifier: 5087840078460068765
Database cluster state: in production
pg_control last modified: Wed Oct 3 11:25:59 2007
Current log file ID: 1166
Next log file segment: 48
Latest checkpoint location: 48E/2A09A428
Prior checkpoint location: 48E/251024C8
Latest checkpoint's REDO location: 48E/2A086140
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/1750418938
Latest checkpoint's NextOID: 530936
Latest checkpoint's NextMultiXactId: 17655
Latest checkpoint's NextMultiOffset: 43050
Time of latest checkpoint: Wed Oct 3 11:23:31 2007
Minimum recovery ending location: 42B/701FDB0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: en_US.utf8
LC_CTYPE: en_US.utf8