1

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

Improve this question
2
  • 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, 2013 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/[email protected]
    – fukawi2
    Sep 20, 2013 at 22:58

2 Answers 2

Reset to default
1

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

Improve this answer
2

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .