Migrates an existing PostgreSQL cluster from a version packaged in one Ubuntu release to the version packaged in a newer Ubuntu release. Especially important when doing LTS upgrades. This is meant to be done after a do-release-upgrade.
Everybody running a PostgreSQL cluster on Ubuntu.
vim -d aka vimdiffInstall the newer version packaged for the new release. As an example, Ubuntu 20.04 will be used which ships PostgreSQL 12.
apt-get install postgresql-12 postgresql-client-12
Reinstall the postgresql-common package which contains Systemd unit files for PostgreSQL.
apt-get install --reinstall postgresql-common
Stop and disable the old cluster.
systemctl disable --now postgresql@10-main.service
Check for differences in the configs of both clusters and migrate them to the new config. The alternative to vimdiff would be diff -y, keeping note of the differences and applying them with an editor. While doing so, do not set the datadir of the new cluster to the old datadir as pg_upgrade will copy the old datadir to the new one.
vim -d /etc/postgresql/1?/main/pg_hba.conf
vim -d /etc/postgresql/1?/main/postgresql.conf
Switch to the postgres user and cd to a temporary empty directory somewhere. The error log and post-upgrade scripts will be written into it after the migration.
sudo -su postgres
mktemp -d
cd /tmp/tmp.*
Trigger the migration to the new cluster. As per the PostgreSQL documentation the upgrade must be triggered by pg_upgrade of the new version and requires the bin, data and config directories of both the old and new versions.
/usr/lib/postgresql/12/bin/pg_upgrade \
-b /usr/lib/postgresql/10/bin \
-B /usr/lib/postgresql/12/bin \
-d /var/lib/postgresql/10/main/ \
-D /var/lib/postgresql/12/main/ \
-o '-D /etc/postgresql/10/main/' \
-O '-D /etc/postgresql/12/main/'
Start the new cluster. If successful, enable it, too.
systemctl start postgresql@12-main.service
systemctl enable postgresql@12-main.service
Execute the scripts generated by pg_upgrade or type the commands in them: One regenerates statistics, the other removes the old cluster.
/usr/lib/postgresql/12/bin/vacuumdb --all --analyze-in-stages
rm -rf /var/lib/postgres/10/main/
Purge the old PostgreSQL version.
apt-get purge postgresql-10 postgresql-client-10