Page MenuHomeSoftware Heritage
Create Task
Maniphest T2581

Upgrade PostgreSQLs to 12.x / 13.x
Closed, MigratedEdits Locked
Actions

Description

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)

Related Objects
Search...

Event Timeline

olasd triaged this task as High priority.Sep 9 2020, 4:24 PM
olasd created this task.
olasd added a comment.Sep 9 2020, 5:06 PM

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

olasd added a comment.Sep 9 2020, 6:04 PM

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/postmaster.pid
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.

olasd changed the status of subtask T2582: Upgrade belvedere/main to PostgreSQL 12 from Open to Work in Progress.Sep 10 2020, 10:53 AM
olasd closed subtask T2583: Upgrade belvedere/secondary to PostgreSQL 12 as Resolved.Sep 10 2020, 3:55 PM
olasd mentioned this in T2583: Upgrade belvedere/secondary to PostgreSQL 12.
olasd updated the task description. (Show Details)
olasd closed subtask T2582: Upgrade belvedere/main to PostgreSQL 12 as Resolved.Sep 10 2020, 4:17 PM
ardumont renamed this task from Upgrade PostgreSQLs to 12.x to Upgrade PostgreSQLs to 12.x / 13.x.Sep 16 2021, 12:25 PM
vsellier mentioned this in T3579: Meta-task: upgrade infrastructure to Debian Bullseye.Sep 16 2021, 6:00 PM
ardumont mentioned this in T3801: Migrate production database servers to bullseye.Dec 14 2021, 5:10 PM
gitlab-migration closed this task as Migrated.Oct 19 2022, 5:58 PM
gitlab-migration claimed this task.
gitlab-migration added a subscriber: gitlab-migration.

This task has been migrated to GitLab.

gitlab-migration changed the status of subtask T2582: Upgrade belvedere/main to PostgreSQL 12 from Resolved to Migrated.Oct 19 2022, 5:58 PM
gitlab-migration changed the status of subtask T2583: Upgrade belvedere/secondary to PostgreSQL 12 from Resolved to Migrated.
About · Developer information · Legal