PostgreSQL Upgrade Tip

If you’re looking to move up a major PostgreSQL release (like 9.1 to 9.2), here’s my standard safe/quick way to do it. I’m using Windows in this example, but other than service management specifics it shouldn’t be too different on other platforms.

  • Install the latest PostgreSQL in a different directory with a different port (the Windows installer will automatically bump it up to 5433 if it detects another Postgres service).
  • Tweak out your postgresql.conf and pg_hba.conf files to your normal settings and install whatever extras you need (i.e. PostGIS) on the new server.
  • Run this command:
pg_dumpall -p 5432 -U postgres | psql -d postgres -p 5433 -U postgres
  • Translation: dump everything from the Postgres server on port 5432 into the one running on 5433. And it gets everything - databases, user roles, extensions, etc. You'll have an exact duplicate of your production server in no time flat.
  • Now for the stop-play dance. Hit stop on the new service, change the port in postgresql.conf to 5432, hit stop on the old service, and hit start on the new service. Bob's your uncle, updated server in production with 4 seconds of down time (depending on your mouse reflexes).
  • Set the old service to Disabled so it doesn't try to pop back on after the random it-keeps-things-running-smooth Windows reboot (insert generic Microsoft rant here).
That has been my PostgreSQL upgrade method for a while, and it has never let me down. The great thing is your old service is sitting there twiddling its thumbs - if you run into a problem stop the new service and crank the old one back up.

Edit: If you’re on a Windows box and you have your path variable set to your old PostgreSQL bin directory, don’t forget to point it to the new one.