Page MenuHomeSoftware Heritage

Upgrade PostgreSQL clusters up to 11
Closed, MigratedEdits Locked

Description

Our PostgreSQL clusters should be upgraded to PostgreSQL 11. This ticket tracks the progress of this operation.

Documentation for pg_upgrade : https://www.postgresql.org/docs/11/pgupgrade.html

Clusters to upgrade:

Primary

  • prado/main (softwareheritage)
  • prado/secondary (scheduler, etc.)
  • prado/hdd
  • somerset/indexer
  • somerset/testdedup

Physical replica (follow step 10 of the pgupgrade docs)

  • dbreplica1.euwest.azure/indexer (replica of somerset/indexer)

Ignored until upgrade of prado/main (T1534)

  • somerset/replica
  • dbreplica0.euwest.azure/replica

Event Timeline

olasd triaged this task as High priority.Mar 20 2019, 3:04 PM
olasd created this task.
olasd changed the task status from Open to Work in Progress.Mar 20 2019, 3:15 PM

Ah, it turns out that prado/hdd still contains the softwareheritage-archiver database, that's outdated but still relevant until we have anything better... I'll start with this one then.

Pre-upgrade steps:

  • install postgresql 11 and all needed extensions for it

Upgrade steps for primary databases:

# stop the old cluster
systemctl stop postgresql@10-<name>
# Disable starting of the old cluster: change auto to disabled in the following file
vim /etc/postgresql/10/<name>/start.conf
# initialize an empty cluster with the proper options (LC_ALL=C.UTF-8 and data directory)
LC_ALL=C.UTF-8 pg_createcluster -d <datadir> 11 <name>
# apply potential configuration changes from the old cluster to the new
vimdiff /etc/postgresql/{10,11}/<name>/postgresql.conf
# link the postgresql config in the datadir of the old/new cluster as `pg_upgrade` needs that
ln -s /etc/postgresql/11/<name>/postgresql.conf <datadir>/postgresql.conf
# move in a directory writable by the postgres user
mkdir /tmp/upgrade-<name>; chown postgres /tmp/upgrade-<name>; cd /tmp/upgrade-<name>
# run pg_upgrade in check mode
sudo -u postgres env LC_ALL=C.UTF-8 /usr/lib/postgresql/11/bin/pg_upgrade --old-datadir <olddatadir> --new-datadir <datadir> --old-bindir /usr/lib/postgresql/10/bin --new-bindir /usr/lib/postgresql/11/bin -k --check
# if that succeeded, run pg_upgrade in the "do it" mode by dropping the --check flag
sudo -u postgres env LC_ALL=C.UTF-8 /usr/lib/postgresql/11/bin/pg_upgrade --old-datadir <olddatadir> --new-datadir <datadir> --old-bindir /usr/lib/postgresql/10/bin --new-bindir /usr/lib/postgresql/11/bin -k
# Allow systemd to start the new cluster
systemctl daemon-reload
# Start the new cluster
systemctl start postgresql@11-<name>
# Cleanup after the upgrade
sudo -u postgres "/usr/lib/postgresql/11/bin/vacuumdb" --port <port> --analyze-in-stages --all
rm -r <olddatadir>
rm -r /etc/postgresql/10/<name>

On the indexer replica on dbreplica1, the rsync command suggested in step 10 of the pg_upgrade documentation failed by hanging. This somewhat looks like https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=906547.

I ended up recreating the replica from scratch by following https://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use steps 6-12

The replication of data from somerset/replica to dbreplica0/replica is ongoing (from scratch, as somerset/replica was recreated from scratch).

olasd updated the task description. (Show Details)

The replication of data from somerset to dbreplica0 completed, as well as the transaction backlog processing. All our PostgreSQL clusters are now running PostgreSQL 11