Upgrading to Postgresql 9.0 on Debian Lenny

There is no backport for postgresql 9.0 for Debian Lenny. Installing from source is easy enough, but pg_upgrade won’t work to migrate any existing 8.3/8.4 database to 9.0, so you have to do a little manual work to complete the upgrade.

Before you do anything, make a dump of your existing postgres 8.x database:

$ su
# su - postgres
% /usr/bin/pg_dumpall > /tmp/postgres8dump

Download postgresql 9.0 source and build it:

$ ./configure
$ make
$ sudo checkinstall
$ sudo dpkg -i postgresql_9.0.1-1_i386.deb

See my earlier post for information on checkinstall.

This will install the 9.0 binaries in /usr/local/pgsql.

Now make the data directory for the 9.0 installation:

$ sudo mkdir -p /var/lib/postrgresql/9.0/main
$ sudo chown postgres:postgres /var/lib/postgresql/9.0/main
$ sudo chmod 700 /var/lib/postgresql/9.0/main

And use the new 9.0 binaries to initialize the data directory:

$ su
# su - postgres
% /usr/local/pgsql/bin/initdb /var/lib/postgresql/9.0/main

Now migrate the data from the old database to the new database:

$ su
# /etc/init.d/postgresql-8.3 stop
# su - postgres
% /usr/local/pgsql/bin/pg_ctl -D /var/lib/postgresql/9.0/main start
% /usr/local/pgsql/bin/psql -d postgres -f /tmp/postgres8dump 

That should be it. Check the data in your 9.0 installation to make sure the migration worked. You’ll also need to remove the init script in /etc/init.d/postgresql-8.3 and either write your own for postgresql 9.0 or use something else (I’m using daemontools for more robust process monitoring than init scripts).