As part of a server upgrade we are going from 32-bit Linux to 64-bit Linux (Gentoo if it makes a difference) and Postgresql 9.1 to 9.2. I'm having a heck of a time upgrading the database with pg_upgrade...
My first attempt was to stash away the old (32-bit 9.1) pgsql bin&lib directory, update the system, then on the updated (64-bit) system run:
pg_upgrade -b pgsql.old/bin -B /usr/lib64/postgresql-9.2/bin -d data.old -D data.new
This fails because pg_upgrade tries to run pgsql.old/bin/pg_ctl with the wrong libpq.so.5 (the 64-bit system version, not the 32-bit version in pgsql.old/lib). If I set LD_LIBRARY_PATH to point to pgsql.old/lib then I can manually run the old 32-bit pg_ctl just fine, but that doesn't seem to help for pg_upgrade.
So then I thought I'd just install the 64-bit Postgresql 9.1 along with 9.2. Now when I run:
pg_upgrade -b /usr/lib64/postgresql-9.1/bin -B /usr/lib64/postgresql-9.2/bin -d data.old -D data.new
The binaries run fine, but the upgrade fails early with:
old and new pg_controldata alignments are invalid or do not match
which I guess is due to 32-bit vs 64-bit alignment issues in the db?
I know that pg_dump/pg_restore will work just fine, but for speed reasons I'd like to use pg_upgrade if at all possible. This is not a one-shot deal - we have a couple hundred systems in the field that will need to be updated in an automated fashion (via bootable thumb drive with appropriate scripts).