Complete solution to upgrade PostgreSQL on debian production server

Question

I'm using Debian 6 (Squeeze) in production for a couple of websites. I decided to use postgresql backports so that I could use PostgreSQL 9.0 features. I thought that it would remain 9.0 and receive updates to that major version.

Unfortunetly Squueze backports were updated to PostgreSQL 9.1 so probably I won't receive updates to 9.0.

I'm planning upgrading to 9.1 but I know that it's not done automatically.

I've read about official pg_upgrade and debian's pg_upgradecluster, but I would appreciate complete guide to upgrade.

1. What are steps to do (first apt-get install postgresql, then pg_upgradecluster, then remove old cluster)? List of steps would be nice.
2. What are possible failure scenarios?
3. How to prepare to failures and react on them?

I can stop database for a couple of hours only so I want to be prepared

Answer 1

It's pretty hard to give you a step-by-step upgrade process that's tailored to your situation/environment.
I've tried to hit some of the highlights below, but you really need to tailor the process to your environment.

---

Step Zero, decide if you need to upgrade. Postgres 9.1 brings Synchronous Replication and a bunch of other cool stuff which is useful if you need it, but not a reason to upgrade right away if you don't.

Step 1, expect that this will take a long time if your database is big.
Even using pg_upgrade the simple process of copying the data for a backup can take a while: our 16GB databases take an hour or morefor a pg_dump/pg_restore, or about 10-20 minutes for a pg_upgrade).

Step 2, I suggest reading through the 9.1 Release Notes. Make sure nothing you need is broken and nothing you use has changed.
There is nothing like a database upgrade that destroys your production systems to really ruin your month.

Step 3, The upgrading section of the Postgres manual is required reading.

Step 4, (insert any Debian-specific stuff here, but that's really not my bag :-)

Step 5, Create an upgrade plan.

Step 6, Test the upgrade plan (technically optional, but strongly recommended).

Step 7, Do the upgrade on your production systems.

---

Standard caveats apply:

• Upgrading could destroy your database. Make a backup.
  • Be sure you can restore it.
  • Be sure you can restore the binaries for the database system too.
    (Hang on to your old Debian packages if you can - I've seen people unable to back out of a failed upgrade because they don't have the old database binaries anymore, and it's a very sad sight indeed.)
    
    

• It will take longer than you expect. Plan for this.
  My company's DB upgrade policy is "Start at 6PM on Friday. If it's not working by 9AM on Sunday roll back."

• Mount a scratch monkey
  If your data is really important restore a copy of it elsewhere and test your upgrade path.
  Yeah, I do this with 16GB databases. I'll still do it with 160GB databases if I can find a way to make it work :-)

Answer 2

This is step 4 basically - plus the --check option to make sure your paths are right. My install is from the repos and has standard debian paths for the files.

postgres@example.org:~$ /usr/lib/postgresql/9.1/bin/pg_upgrade --old-datadir /var/lib/postgresql/9.0/main --new-datadir /var/lib/postgresql/9.1/main --old-bindir /usr/lib/postgresql/9.0/bin/ --new-bindir /usr/lib/postgresql/9.1/bin/ --check

This above failed - consistently said I was running both servers on the same port.

The following seemed to work:

pg_dumpall -p 5432 | psql -d postgres -p 6543

This is useful.