PostgreSQL - Forgotten database cluster upgrade…

After upgrading a system running Debian GNU/Linux from release Squeeze to Wheezy I almost lost my PostgreSQL database. The following PostgreSQL versions are shipped with recent Debian releases:

Debian Release PostgreSQL Version
Lenny 8.3
Squeeze 8.4
Wheezy 9.1

After upgrading from Lenny to Squeezy I missed to upgrade the databas cluster from PostgreSQL 8.3 to 8.4. I didn’t realize either that while upgrading from Squeeze to Wheezy the old postgresql-8.3 packages got removed due some libkrb5 dependency issues. After rebooting the system the old 8.3 database was lost, installing postgresql-8.3 from Lenny on Wheezy is not possible:

root@tarantoga:~# apt-get install postgresql-8.3
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 postgresql-8.3 : Depends: libkrb53 (>= 1.6.dfsg.2) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

Fortunately there is the PostgreSQL Global Development Group (PGDG) apt repository of PostgreSQL packages for Debian and Ubuntu. The repository supports to install different (old) PostgreSQL versions:

Currently, we support

- Debian 6.0 (squeeze), 7.0 (wheezy), and unstable (sid)
- Ubuntu 12.4 (precise), support for lucid is being worked on
- PostgreSQL 8.3, 8.4, 9.0, 9.1, 9.2
[..]

I was able to use Debian‘s pg_upgradecluster script to upgrade the database to PostgreSQL 9.1 from Wheezy after installing postgresql-8.3 from the PGDG apt repository:

root@tarantoga:~# apt-get update
OK   http://ftp.de.debian.org wheezy Release.gpg
[..]
Holen: 1 http://apt.postgresql.org squeeze-pgdg Release.gpg [836 B]            
Holen: 2 http://apt.postgresql.org squeeze-pgdg Release [32,8 kB]              
Ign http://apt.postgresql.org squeeze-pgdg Release                             
Holen: 3 http://apt.postgresql.org squeeze-pgdg/main i386 Packages [43,0 kB]
Ign http://apt.postgresql.org squeeze-pgdg/main Translation-de_DE              
Ign http://apt.postgresql.org squeeze-pgdg/main Translation-de
Ign http://apt.postgresql.org squeeze-pgdg/main Translation-en                 
Es wurden 76,7 kB in 4 s geholt (17,3 kB/s).                                   
Paketlisten werden gelesen... Fertig
W: GPG-Fehler: http://apt.postgresql.org squeeze-pgdg Release: Die folgenden Signaturen konnten nicht überprüft werden, weil ihr öffentlicher Schlüssel nicht verfügbar ist: NO_PUBKEY 7FCC7D46ACCC4CF8
root@tarantoga:~# apt-get install postgresql-8.3
Paketlisten werden gelesen... Fertig
Abhängigkeitsbaum wird aufgebaut.       
Statusinformationen werden eingelesen.... Fertig
Vorgeschlagene Pakete:
  oidentd ident-server locales-all
Die folgenden NEUEN Pakete werden installiert:
  postgresql-8.3
0 aktualisiert, 1 neu installiert, 0 zu entfernen und 17 nicht aktualisiert.
Es müssen 5.574 kB an Archiven heruntergeladen werden.
Nach dieser Operation werden 15,0 MB Plattenplatz zusätzlich benutzt.
WARNUNG: Die folgenden Pakete können nicht authentifiziert werden!
  postgresql-8.3
Diese Pakete ohne Überprüfung installieren [j/N]? j
Holen: 1 http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg/main postgresql-8.3 i386 8.3.23-1.pgdg60+1 [5.574 kB]
Es wurden 5.574 kB in 10 s geholt (543 kB/s).                                  
Vormals nicht ausgewähltes Paket postgresql-8.3 wird gewählt.
(Lese Datenbank ... 192284 Dateien und Verzeichnisse sind derzeit installiert.)
Entpacken von postgresql-8.3 (aus .../postgresql-8.3_8.3.23-1.pgdg60+1_i386.deb) ...
postgresql-8.3 (8.3.23-1.pgdg60+1) wird eingerichtet ...
[ ok ] Starting PostgreSQL 8.3 database server: main.

When the old PostgreSQL 8.3 database was running again I was able to migrate it to 9.1 using the Debian way:

root@tarantoga:~# pg_upgradecluster 8.3 main
Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster (configuration: /etc/postgresql/9.1/main, data: /var/lib/postgresql/9.1/main)...
Moving configuration file /var/lib/postgresql/9.1/main/postgresql.conf to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_hba.conf to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_ident.conf to /etc/postgresql/9.1/main...
Configuring postgresql.conf to use port 5432...
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database davical...
Analyzing database davical...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
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 (5432)...
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.3 main

I had to fix the listening port of the 9.1 database to PostgreSQL‘s default port 5432 by editing /etc/postgresql/9.1/main/postgresql.conf. Finally I dropped the old database and removed the postgresql-8.3 package again (don’t forget to drop the /etc/apt/sources.list entry, too):

root@tarantoga:~# pg_dropcluster 8.3 main
root@tarantoga:~# apt-get remove --purge postgresql-8.3

Comments !