marcello_dl's Journal: quick and dirty postgresql update- tutorial

Note, this procedure involves some downtime for the server.
First dump existing databases, e.g.

# su - postgres -c 'pg_dump -C my_database > /tmp/my_database.sql'

If you have user putting content into database this step could be done later, just before importing the sql in the new cluster. Remember to take down db clients if you care for db consistency.

Then install new version, e.g.

# aptitude install postgresql-8.3

when one installs a new version of postgres under debian, the old version stays installed and a new cluster is automatically created. The new version server is configured for an alternative port (5433). Watch for the messages/logs from the package manager.

Then import old users into the new cluster, take into account the listening port run the updated client as the bash command "psql" defaults to the old version.

# su - postgres
# createuser a_user --port 5433

Then copy or update the pg_hba.conf settings to the one in the new cluster in /etc/postgresql/8.3/main/pg_hba.conf

Do not forget to reload the settings.
# /etc/init.d/postgresql-8.3 reload

Then import the databases
# su - postgres
$ /usr/lib/postgresql/8.3/bin/psql --port 5433

Then configure the new server for the existing server port, e.g.
# /etc/init.d/postgresql-8.3 stop
edit /etc/postgresql/8.3/main/postgresql.conf:
    port=5432

Then stop the db clients (eg. it's downtime for my dynamic sites hosted with lighttpd), take down old db, take up new one, restart db clients /etc/init.d/lighttpd stop /etc/init.d/postgresql-8.2 stop /etc/init.d/postgresql-8.3 start
    netstat -nat /etc/init.d/lighttpd start

test the db clients (restart your browser for webapps to avoid using stale session data), if all is well we must remove old postgresql else at reboot it is restarted, conflicting with the newest server version.

Since the debian remove scripts assume the server is up we must take the old server back up- we need to configure the old server for an alternative port so both versions can be up at the same time, e.g.

edit /etc/postgresql/8.3/main/postgresql.conf:
    port=5434

# /etc/init.d/postgresql-8.2 start
# aptitude purge postgresql-8.2

This is all.
Note. It is better to upgrade incrementally than doing it say, from 8.1 to 8.3
There might be ways to reduce downtime, with WAL for example.