Upgrade pgsql after Ubuntu release upgrade


    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.

    Who is it for?

    Everybody running a PostgreSQL cluster on Ubuntu.


    • vim – To use vim -d aka vimdiff

    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 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