Page MenuHomeSoftware Heritage

Upgrade PostgreSQL clusters up to 11
Closed, ResolvedPublic

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 created this task.Mar 20 2019, 3:04 PM
olasd triaged this task as High priority.
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.

olasd added a comment.Mar 20 2019, 4:35 PM

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>
olasd updated the task description. (Show Details)Mar 20 2019, 4:36 PM
olasd updated the task description. (Show Details)Mar 20 2019, 6:47 PM
olasd updated the task description. (Show Details)Mar 21 2019, 3:51 PM
olasd updated the task description. (Show Details)Mar 21 2019, 4:14 PM
olasd updated the task description. (Show Details)Mar 22 2019, 8:25 AM
olasd added a comment.Mar 22 2019, 8:30 AM

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

olasd updated the task description. (Show Details)Mar 25 2019, 6:38 PM

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)Apr 2 2019, 11:57 AM
olasd closed this task as Resolved.

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