3

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

Improve this question

2 Answers 2

Reset to default
6

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

Improve this answer
1
  • Thank you very much for answer. I need more info about debian specific upgrade - not sure what simple package update does and what pg_updatecluster does (and etc.). I don't want to upgrade it right now - just want to be well prepared
    – Daimon
    Nov 21, 2011 at 19:36
1

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. 

[email protected]:~$ /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.

Improve this answer

You must log in to answer this question.

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