Upgrading postgresql databse server software on Debian Lenny (Old Post)

This is a systems administration and database technology oriented one:

Problem: Because we have several servers that was upgrade from Debian Etch and the to Debian Lenny via the “apt-get dist-upgrade” path , I need to upgrade older instance postgresql 8.1 to postgresql 8.3 as smoothly as possible.

The databases are fairly simple but I really don’t want to muck around with dump and restore procedures, if there is a better way to do it. I turns out there is a better way:

Solution:

First: backup your data using the pg_dump utility. This way you will have a fall-back and all of your data in case something does go wrong.

Second: install new postgresql packages: apt-get install postgresql-8.3 postgresql-client-8.3 . Now you have two database clusters in parallel.

Third: what we need to is migrate all the data from the old one to the new one. You will need to do this with sudo or root priviledges. To go ahead do the do the following:

1. Invoke pg_dropcluster –stop 8.3 main. This will stop the new postgresql cluster (but not the server) and make it dormant and ready for migration.

2. Invoke pg_upgradecluster 8.1 main to kick off the upgrade process. This may take some time. My output was something like this:

Creating new cluster (configuration: /etc/postgresql/8.3/main, data: /var/lib/postgresql/8.3/main)…
Moving configuration file /var/lib/postgresql/8.3/main/postgresql.conf to /etc/postgresql/8.3/main…
Moving configuration file /var/lib/postgresql/8.3/main/pg_hba.conf to /etc/postgresql/8.3/main…
Moving configuration file /var/lib/postgresql/8.3/main/pg_ident.conf to /etc/postgresql/8.3/main…
Configuring postgresql.conf to use port 5433…
Disabling connections to the old cluster during upgrade…
Disabling connections to the new cluster during upgrade…
Re-enabling connections to the old cluster…
Re-enabling connections to the new cluster…
Creating globals…
Fixing hardcoded library paths for stored procedures…
Upgrading database postfix…
Analyzing database postfix…
Fixing hardcoded library paths for stored procedures…
Upgrading database postgres…
Analyzing database postgres…
Fixing hardcoded library paths for stored procedures…
Upgrading database template1…
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1502; 1262 1 DATABASE template1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  database “template1” already exists
Command was:
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = ‘SQL_ASCII’;
WARNING: errors ignored on restore: 1
Analyzing database template1…
Copying old configuration files…
Copying old start.conf…
Stopping target cluster…
Stopping old cluster…
Disabling automatic startup of old cluster…
Configuring old cluster to use a different port (5433)…
Starting target cluster on the original port…
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

pg_dropcluster 8.1 main

3. Verify everytting and then when you’re happy that all data is as it should be, invoke: pg_dropcluster 8.1 main This command will remove old version of the database cluster, and clean up the postgresql instances

4. Now that all is working well you can clean up your server with : apt-get –purge remove postgresql-8.1 postgresql-client-8.1

This process was relatively painless and quick. Definitely a our preferred way to do this sort of work, once you have a good known backup of your data.