PostgreSQL Kung-Fu: Migrating between major versions.

Has this happened to you?  You're called in to sandbox the newest version of xTuple for a customer.  "Piece of cake" you think to yourself, "I'll buzz in and buzz out, 30 minutes tops".

"ERROR: syntax error at or near "(" at character 578"

Arrrrgh!  We'll you try again, maybe even open up the update package and poke around looking for a syntax erorr.  "I know this worked on my laptop when I tried it at home..."  Deep breath, keep your cool... We all know that the 10 minute jobs are the ones that end up taking days and years off of our lives. 

"A gem is not polished without rubbing, nor a man perfected without trials."
Chinese Proverb

So, what happened here?  PostgreSQL 8.1 happened, and xTuple versions greater than 3.2.2 have some syntactical evolutions that make it incompatible with 8.1 syntax. It's time to upgrade the PostgreSQL version.  Upgrading from 8.1.1 to 8.1.18 isn't a big deal, but, going from 8.1.x to 8.2.x is.  This is known as a Major Version upgrade.  When the number after the first decimal changes, that's a major version - 8.1 to 8.3 are not compatible.  8.1.1 to 8.1.8 are compatible.  For more info see:  http://www.postgresql.org/docs/8.3/static/migration.html .

"To know the road ahead, ask those coming back."
Chinese Proverb

It's not that bad, really.  If on OSX/Linux, we'll start out by downloading the source for 8.3.x and building that in a directory other than where the OLD version of postgres lives (typically /usr/local/pgsql). You'll want to pass a config line to it like: 

./configure --prefix=/usr/local/pg83 --with-openssl

Hmm... I know I installed a new PostgreSQL somewhere, but where is it?  Well if you followed the example above, it should be in /usr/local/pg83/bin.  The `which` command is very useful to let you know that a particular utility is in it's search path. But, even I can get confused when confronted with multiple versions. Running the "which" command will only get you so far... 

[pclark@localhost ~]$ which pg_dumpall
/usr/bin/pg_dumpall

But, what version is it? Run the command with the -V flag to get the version. 

[pclark@localhost ~]$ /usr/bin/pg_dumpall -V
pg_dumpall (PostgreSQL) 8.3.8

"A little impatience will spoil great plans."
Chinese Proverb

Doh!  Not the version I wanted, and NOT THE SAME version I installed (suppose it's pg8.3.7)!  Where's the one I installed if it's not in my path! Let's find what I want a different way with a little helper. This can be executed on the commandline to find where the pg_dumpalls live, and will let you know on an RPM based system (centos, fedora, redhat) what pakage (if any) that version came from.  (Again, we already know from the config line that it's in /usr/local/pg83/bin) 

find /usr/local/p*/bin /usr/bin -type f -name "pg_dumpall" -exec echo " " \; -exec echo {} \; -exec {} -V \; -exec rpm -qf {} \;
 
/usr/local/pg8118/bin/pg_dumpall
pg_dumpall (PostgreSQL) 8.1.18
file /usr/local/pg8118/bin/pg_dumpall is not owned by any package
 
/usr/local/pg82/bin/pg_dumpall
pg_dumpall (PostgreSQL) 8.2.13
file /usr/local/pg82/bin/pg_dumpall is not owned by any package
 
/usr/local/pg83/bin/pg_dumpall
pg_dumpall (PostgreSQL) 8.3.7
file /usr/local/pg83/bin/pg_dumpall is not owned by any package
 
/usr/bin/pg_dumpall
pg_dumpall (PostgreSQL) 8.3.8
postgresql-8.3.8-1.fc11.x86_64

Quite a few eh... Well, for now, use the complete path to make sure you use the right one.  Notice that the one in /usr/bin is from the postgresql-8.3.8-1.fc11.x86_6 RPM package (which I ALWAYS say "Don't install the packaged PostgreSQL!").

Once you have found your proper version of pg_dumpall and the new server is installed and running on a different port (say 5433), let's make a dump with the pg8.3 version of pg_dumpall. 

/usr/local/pg83/bin/pg_dumpall -U postgres -h localhost -p 5432 > 83dumpall.sql

And load it up with: 

/usr/local/pg83/bin/psql -U postgres -h localhost -p 5433 < 83dumpall.sql 2>&1 | tee load.log

It is important that you create this dump with the NEW version against the OLD database.  The internal structures change and if you don't use the proper dump, it won't load correctly on the NEW server.  If you do your backups via PgAdmin, make sure you change the options for the PG Bin Path under File -> Options.  Point that to the NEW version of PostgreSQL's bin directory.  Check out the file that is created (load.log) for errors.  The errors displayed below are OK. 

grep "ERROR" load.log 
ERROR:  could not find function "pg_cipher_exists" in file "/usr/local/pgsql/lib/pgcrypto.so"
ERROR:  function public.cipher_exists(text) does not exist
ERROR:  could not find function "pg_digest_exists" in file "/usr/local/pgsql/lib/pgcrypto.so"
ERROR:  function public.digest_exists(text) does not exist
ERROR:  could not find function "pg_hmac_exists" in file "/usr/local/pgsql/lib/pgcrypto.so"
ERROR:  function public.hmac_exists(text) does not exist

These errors are not desireable: 

grep "ERROR" load.log
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2; 2615 171830 SCHEMA api mfgadmin
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "api" already exists
    Command was: 
CREATE SCHEMA api;
pg_restore: [archiver (db)] Error from TOC entry 2512; 2612 34425 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  language "plpgsql" already exists
    Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 2005; 1247 239383 TYPE achline mfgadmin
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "achline" already exists

Master Kan:  "See the Way of life as a stream. A man floats, and his way is smooth. The same man, turning to fight upstream, exhausts himself. To be One with the Universe, each must find his true path and follow it"
-- Episode No. 11

Here's a handy little tidbit to save you some time.  

/usr/local/pg83/bin/pg_dumpall -U postgres -h localhost -p 5432 | /usr/local/pg83/bin/psql -U postgres -h localhost -p 5433 2>&1 | tee load.log

This pipes the output from the pg_dumpall directly into the psql command to restore it, basically cutting your time spent in half.  When it's complete, you should be able to login to port 5433 and see all of your data.

Another alternate for your consideration which is useful if you want to move your database to a different machine. 

/usr/local/pg83/bin/pg_dumpall -U postgres -h localhost -p 5432 | /usr/local/pg83/bin/psql -U postgres -h 192.168.0.5 -p 5432 2>&1 | tee load.log

If you use the wrong versions, as below, you'll know it.  Do not use the -i option *EVER...* 

/usr/local/pg8118/bin/pg_dumpall -U postgres -h localhost -p 5432 | /usr/local/pg8118/bin/psql -U postgres -h localhost -p 5433 2>&1 | tee load.log
psql: FATAL:  database "postgres" does not exist
server version: 8.2.13; pg_dumpall version: 8.1.18
aborting because of version mismatch  (Use the -i option to proceed anyway.)

So, what have you learned?  Well, I hope you've learned that it's not really that difficult to migrate between major versions of Postgres.  Of course, it's impossible to know what every configuration of server you may encounter will be, or what caveats you may encounter, but I hopefully have imparted some Pg Kung-Fu Skills to you!  And remember, it is very important to test thouroughly all of the data and business processes after doing any sort of upgrade.

"Only You Can Prevent Forest Fires"
Smokey Bear

For further reading, please visit the PostgreSQL website at http://www.postgresql.org

The official documentation for this process can be found here:  http://www.postgresql.org/docs/8.3/static/migration.html