dt.iki.fi

Upgrade PostgreSQL Database (Cluster) from one Major Version to Another

Context

I'm in the process of finally upgrading my Debian install from oldstable (buster) to stable (bullseye). My DAViCal installation uses PostgreSQL for its database backend. Strangely, I have two versions of PostgreSQL (9.6 and 11) and I purged the older one (without deleting the databases, thankfully).
It turns out that the databases are not compatible between major versions and my beloved CalDAV server stopped working!

Re-install older PostgreSQL Version

Searching for the problem returns plenty of results, but all of them assume that I still have both versions installed; the newer version simply cannot read the older databases anymore.

Thankfully Debian still provides both packages and I can install them with dpkg:

#> dpkg -i postgresql-client-9.6_*.deb
#> dpkg -i postgresql-9.6_*.deb

If you installed them the wrong way around remember to run:

#> dpkg --configure postgresql-9.6

Migrate the Cluster(s)

  • Stop the postgresql service: systemctl stop postgresql
  • Open a session for the postgres user: sudo -iu postgres
  • See what you have with pg_lsclusters (both should be down)
  • Stop and delete the newer (11), empty, default cluster: pg_dropcluster --stop 11 main
  • See what you have with pg_lsclusters (should be only the older one now)
  • Migrate the 9.6 cluster to the current version: pg_upgradecluster 9.6 main (...lots of output...)
  • See what you have with pg_lsclusters (should be two again)
  • exit the postgres user session
  • systemctl daemon-reload, systemctl start postgresql, systemctl start postgresql@11-main

No, it's not working. No systemctl error messages, however I noticed that

  • the migrated database is running on a different port than the old one

  • PHP is still listening on the old port

  • systemctl stop postgresql, systemctl stop postgresql@11-main

  • edit /etc/postgresql/11/main/postgresql.conf to use the old port

  • systemctl start postgresql, systemctl start postgresql@11-main

If you see Can't open PID file /var/run/postgresql/11-main.pid (yet?) after start: No such file or directory in systemctl status, try manually restarting postgresql:

sudo -iu postgres
/usr/lib/postgresql/11/bin/pg_ctl restart -D /var/lib/postgresql/11/main

Final Cleanup

I purged postgresql-9.6, but I'm leaving the old database until the new one has proven itself in daily use.

[ #> su - postgres -c 'pg_dropcluster --stop 9.6 main' ]
#> apt purge postgresql-9.6
#> apt --purge autoremove

Next major Version Upgrade

This time from 11 to 13.

Again, I forgot to take care of things before uninstalling the old postgresql verison, and had to reinstall it manually via dpkg. After a long struggle, this command seems to have done it:

sudo -u postgres /usr/lib/postgresql/13/bin/pg_upgrade \
    -b /usr/lib/postgresql/11/bin/ -B /usr/lib/postgresql/13/bin/ \
    -d /etc/postgresql/11/main/ -D /etc/postgresql/13/main/

Also see here.

Trouble again

I finally migrated my Debian server from 32 to 64 bit. Crossgrading, it's called, and it is possible without a complete reinstall!

Everything worked, with only a little friction, but once again PostgreSQL failed. Bloody hell. This time it complained about this: FATAL: incorrect checksum in control file, and a quick web search revealed that the databases are binary incompatible between 32 and 64 bit.
But I learned a lot in the last 24h, and installed the 32bit versions of the postgresql binaries:

#> dpkg --add-architecture i386 # I had only just removed it
#> apt update
#> apt install postgresql-13:i386 postgresql-client-13:i386

Don't be surprised, it will install a ton of 32 bit dependencies, but should only uninstall the postgresql 64 bit binaries!
DaviCAL works again. That wasn't so bad.

To hopefully avoid future pitfalls, I have installed a weekly dump (to a plain text script) of all databases.

The script postgresql-13-dumpall:

sh
#!/bin/sh cd /var/lib/postgresql/dumpall || exit 1 file="$(date +%Y-%m-%d).db" printf '%s\n' "-- To reload database(s) from this file, you can use:" "-- $ psql -f $file postgres" > "$file" pg_dumpall >> "$file" tar -czvf "$file".tar.gz "$file" && rm "$file"

The service:

ini
[Unit] Description=Weekly PG Backup Documentation=man:pg_dumpall After=postgresql.service network.target network-online.target systemd-networkd.service NetworkManager.service connman.service [Service] Type=oneshot User=postgres Group=postgres ExecStart=/var/lib/postgresql/postgresql-13-dumpall KillMode=process TimeoutStopSec=900

The timer:

ini
[Unit] Description=Weekly PG Backup After=database-daily-backup.timer [Timer] # every monday 3am: OnCalendar=Mon *-*-* 03:00:00 RandomizedDelaySec=1h Persistent=true [Install] WantedBy=timers.target

Also see here and here.