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).

  • when you have "a couple hundred systems in the field " i really would suggest you'd contact credativ (website is in german, bu you'll find contact - ifnos at the bottom). they employ some postgres-core-devs who will help you, fast. i assume, time is money. disclaimer: i dont work for them or are somehow related; they just did excellent jobs for some clients of mine. – that guy from over there Sep 20 '13 at 19:55
  • Just to add to the answer below, according to Tom Lane (very knowledgable postgres team member) you definitely need to dump and restore when moving from 32 to 64: postgresql.org/message-id/10209.1262975951@sss.pgh.pa.us – fukawi2 Sep 20 '13 at 22:58

You can't use pg_upgrade to upgrade from 32-bit to 64-bit, or in general from any OS/CPU/platform to any other OS/CPU/platform. The data files are platform dependent, and pg_upgrade works by just copying (or linking) over the data files unchanged. So this is never going to work.

Your options at this point are dump/restore, or using a logical replication system to move your data (Slony, Londiste, Bucardo).


I would never do it the way you're going about it. Just for the record, I've been with PostgreSQL since, oh, 6.x days (aka many, many years).

I always, without question, do something along these lines:

  1. Dump the old version with pg_dump
  2. install the new version, perhaps along side your current version in your situation. I always sym-link the default location. If for example pgsql winds up in /usr/local/pgsql, I change this to /usr/local/pgsql-v9.1.2 and then I sym link that with pgsql so my RC scripts don't need a lot of adjusting nor does my LD_CONFIG
  3. Init the new DB environment with the new version just installed
  4. Restore your data with psql

I use custom RC script to stop, start, & restart the postmaster and within it I have settings that point to the current data & xlog directories.

Also, I understand you're using a particular distro's package manager. I avoid this and always build PostgreSQL from source.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.