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
Everybody running a PostgreSQL cluster on Ubuntu.
Install 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 firstname.lastname@example.org
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
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 email@example.com systemctl enable firstname.lastname@example.org
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