PostgreSQL Upgrades on Debian Linux

System administration
Comments

When doing a major version upgrade on Debian Linux with PostgreSQL server installed (using Debian-provided packages), this always catches me: After the upgrade there will be two different PostgreSQL instances running, it won’t do an in-place upgrade for the database automatically.

In order to get the existing database running on the newer PostgreSQL version, this is what I need to do:

  1. Backup the database (this is important if something goes wrong!)
  2. Upgrade the system normally (here I’ll use Debian 11 (Bullseye) to 12 (Bookworm) upgrade as en example)
  3. See that both postgresql@13-main.service and postgresql@15-main.service are running (both TCP ports 5432 and 5433 are shown listening with ss -ntl command)
    • PostgreSQL 13 is from Debian 11, PostgreSQL 15 is from Debian 12
  4. Run sudo pg_dropcluster --stop 15 main
  5. Run sudo pg_upgradecluster 13 main
  6. Check that everything works fine
  7. Check dpkg -l postgresql\* for any old version packages installed, then delete them with apt purge

Your mileage may vary, and all other usual caveats apply.

Leave a Reply