Upgrading from PostgreSQL 7.4 to 8.1 in Debian Etch

Debian allows both versions 7.4 and 8.1 of PostgreSQL to coexist on the same system which somewhat simplifies the upgrade procedure.

Assuming that the PostgreSQL 7.4 packages are currently installed with a fairly standard configuration, we start by installing PostgresSQL 8.1.

apt-get install postgresql-8.1 postgresql-client-8.1

The newly installed system will be started on a port 5433 whilst the old system continues to run on standard port 5432. For the time being it is best to leave both instances running on these ports which will allow you to migrate the data via a simple pipe.

Amend the configuration files for the new instance by hand, based on the existing configuration and restart the instance.

/etc/init.d/postgresql-8.1 restart

Populate the new instance with the existing databases:

sudo su - postgres
/usr/lib/postgresql/7.4/bin/pg_dumpall -p 5432 | /usr/lib/postgresql/8.1/bin/psql -p 5433

Stop the old database instance and use the new client.

/etc/init.d/postgresql-7.4 stop

Switch the new instance to using standard port 5432.

vi /etc/postgresql/8.1/main/postgresql.conf [set port = 5432]
/etc/init.d/postgresql-8.1 restart

Use the new psql client and your own applications to ensure that the new installation is operating correctly and that the data import was successful.

sudo -u postgres /usr/lib/postgresql/8.1/bin/psql -p 5432

If all is well then remove the old package, maybe backing up the old data.

tar cvzf ~/oldpgdata.tgz /var/lib/postgresql/7.4/data
apt-get purge postgresql-7.4 postgresql-client-7.4