Journal 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 >
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
Do not forget to reload the settings.
#
Then import the databases
# su - postgres
$
Then configure the new server for the existing server port, e.g.
#
edit
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
netstat -nat
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
port=5434
#
# 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.