Page MenuHomeSoftware Heritage

Upgrade PostgreSQLs to 12.x
Open, HighPublic


We have various PostgreSQL instances that would deserve an upgrade to PostgreSQL 12.

Considering the following issues:

  • replication between belvedere and somerset lagging by several TB
  • need to transfer all the data of the replica to migrate somerset to zfs
  • broken replication between somerset and dbreplica0
  • the indexer workers being stopped already because of the status of dbreplica0

now is probably as good a time as any to do these upgrades.

  • belvedere: main
  • belvedere: secondary
  • belvedere: indexer
  • somerset: replica (logical, from belvedere/main)
  • dbreplica0: replica (physical, from somerset/replica)
  • dbreplica1: replica (physical, from belvedere/indexer)

Event Timeline

olasd triaged this task as High priority.Sep 9 2020, 4:24 PM
olasd created this task.

I've done the first step of upgrading belvedere/main to 12, that is drop the logical replication subscription : on somerset, drop subscription softwareheritage_somerset

To test out the process for upgrading the main cluster, I've done the following:

  • snapshot the main cluster
zfs snapshot data/postgres-main@$(TZ=UTC date +%Y-%m-%dT%H:%M:%SZ)
  • clone the snapshot into a new volume
zfs clone data/postgres-main@2020-09-09T15:08:27Z data/postgres-main-12-test
  • mount the new volume
zfs set mountpoint=/srv/softwareheritage/postgres/12/main data/postgres-main-12-test
  • make "space" for pg_upgrade within the new volume, and prepare postgres for startup (to recover from the "unclean shutdown" caused by the live snapshot)
mkdir /srv/softwareheritage/postgres/12/main/pre-upgrade
mv /srv/softwareheritage/postgres/12/main/* /srv/softwareheritage/postgres/12/main/pre-upgrade/
rm /srv/softwareheritage/postgres/12/main/pre-upgrade/
rm /srv/softwareheritage/postgres/12/main/pre-upgrade/postgresql.conf
cp /etc/postgresql/11/main/postgresql.conf /srv/softwareheritage/postgres/12/main/pre-upgrade/
  • edit postgresql.conf to change ports, sockets, logfiles, etc

Once that's done, start the cloned postgres cluster

sudo -u postgres /usr/lib/postgresql/11/bin/pg_ctl start -D /srv/softwareheritage/postgres/12/main/pre-upgrade/

When that finishes recovering WALs, shut it down with

sudo -u postgres /usr/lib/postgresql/11/bin/pg_ctl stop -D /srv/softwareheritage/postgres/12/main/pre-upgrade/

Now time for the actual pg_upgrade test.

  • Create a new cluster
/usr/lib/postgresql/12/bin/initdb -D /srv/softwareheritage/postgres/12/main/post-upgrade --locale=C.UTF-8
  • Run pg_upgrade in check mode
/usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/12/bin -d /srv/softwareheritage/postgres/12/main/pre-upgrade -D /srv/softwareheritage/postgres/12/main/post-upgrade -j8 -k -c
  • then run pg_upgrade fully
/usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/12/bin -d /srv/softwareheritage/postgres/12/main/pre-upgrade -D /srv/softwareheritage/postgres/12/main/post-upgrade -j8 -k

Once pg_upgrade has run, copy the postgresql.conf over, and switch references from 11 to 12, then start the cluster.

sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl start -D /srv/softwareheritage/postgres/12/main/post-upgrade/

Once that's done, destroy the cloned volume and the snapshot:

zfs destroy data/postgres-main-12-test
zfs destroy data/postgres-main@2020-09-09T15:08:27Z

All the "unclean shutdown" process is only needed when doing a test on a snapshot of the live cluster. The actual upgrade will run on a properly stopped cluster.