dt.iki.fi
dt.iki.fi

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

Context-up-

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-up-

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)-up-

  • 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-up-

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-up-

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-up-

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.