Upgrading from PostgreSQL 13 to 14 on FreeBSD

This article upgrades PostgreSQL from 13.4 to 14.0 on FreeBSD. pg_upgrade supports upgrades from 8.4.X and later to the current major release of PostgreSQL, including snapshot and beta releases.

Although it's hoped this article will be useful, it should be used with and does not replace the official documentation.

As with all upgrades, it's essential to ensure a working backup and to sufficiently test before performing upgrading the server as the data cannot be downgraded and would need to be restored from backup.

The server upgrade application pg_upgrade uses both old and new binaries so it's essential to ensure both 13 and 14 binaries are available.

On FreeBSD, PostgreSQL binaries are located in /usr/local/bin and as both versions conflict with each other the other version needs to be installed in a temporary location. Fortunately FreeBSD has excellent virtualisation including jails and bhyve so if you only have one box it's possible to run a PostgreSQL server in one jail, client apps in another, and replicas in yet another.

As PostgreSQL client libraries are backwards compatible, it's safe and essential to upgrade client applications before a server upgrade.

To keep things simple, this article will perform an upgrade on a single server instance.

File layout:

# Default client, contrib and server version 14 binaries
/usr/local/bin
# Version 13 client, contrib and server binaries:
~/pg13/usr/local/bin
# Version 13 database to upgrade
~/pg_data_13
# Upgraded version 14 database
~/pg_data_14
# pg_upgrade writes various log and script files so this is the location it will be run:
~/pg_up
psql --version
psql (PostgreSQL) 14.0
~/pg13/usr/local/bin/psql --version
psql (PostgreSQL) 13.4
$ pg13/usr/local/bin/pg_ctl status --pgdata=pg_data_13
pg_ctl: server is running (PID: 48351)

To upgrade PostgreSQL on FreeBSD you need the server, client and contrib binaries from both the current and previous version of PostgreSQL. This article has the latest version installed so the previous binaries will be extracted into ~/pg13 as follows:

mkdir pg13
cd pg13
tar xf postgresql13-client-13.4.pkg
tar xf postgresql13-contrib-13.4.pkg
tar xf postgresql13-server-13.4.pkg

First initialise the new database:

initdb --pgdata=../pg_data_14

Check upgrade can proceed:

pg_upgrade \
        --link \
        --check \
        --old-datadir "../pg_data_13" \
        --new-datadir "../pg_data_14" \
        --old-bindir "~/pg13/usr/local/bin" \
        --new-bindir "/usr/local/bin"
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

*Clusters are compatible*

Assuming all is well, stop the server and all replicas then run the upgrade, it writes various log and script files so change the working directory to ~/pg_up.

$ pg_upgrade \
        --link \
        --old-datadir "../pg_data_13" \
        --new-datadir "../pg_data_14" \
        --old-bindir "/usr/local/bin" \
        --new-bindir "../pg14/usr/local/bin"
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas                           ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster               ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from ../pg_data_13/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/local/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

There are a number of changes to the pg_hba.conf and postgresql.conf and the upgrade installs defaults so ensure you copy amendments you have made. diff -u is your friend.

After the upgrade process has completed you can now start the server, you may have to restart any services that don't automatically connect to the upgraded server.

After upgrading the primary database it's time to re-create streaming replication and log-shipping standby servers.

Although pg_upgrade mentions support upgrading standby servers from version 10, in practice the upgrade process touches almost every file so there are no advantages to using rsync in reducing network load and it's easier and faster to re-create the replica with a new pg_basebackup.

You may find the following bourne shell script to create/re-create new replicas useful, it also creates host specific replication slots ensuring wal files are preserved until needed.

#!/bin/sh

USER=username
REMOTE_HOST=primary.example.com
PGDATA=/var/db/pg_data
PGOWNER=postgres
PGGROUP=postgres
PG_PASS=/usr/home/postgres/.pgpass

do_check_root_dot_pgpass () {
    if [ ! -f "$PG_PASS" ]
    then
        echo "Please create a $PG_PASS file for the postgresql user"
    else
        PG_DOT_PASS_OWNER=$(stat -f "%Su" $PG_PASS)
        if [ "$PG_DOT_PASS_OWNER" = "${PGOWNER}" ]
        then
            echo "$PG_PASS is ok"
        else
          echo "$PG_PASS must be owned by ${PGOWNER} to enable replication"
            exit 1
        fi
    fi
}

do_pg_make_replica() {
    if [ "$(id -u )" != 0 ]
    then
      echo "This must be run as root"
        exit 1
    fi
    # Check .pgpass permissions
  do_check_root_dot_pgpass

    if service postgresql status 2> /dev/null
    then
        echo "PostgreSQL is running"
        echo "Stopping the postgresql service before continuing"
        service postgresql stop
    fi
    if service postgresql status 2> /dev/null
    then
        echo "PostgreSQL is still running"
        echo "Unable to continue"
        exit 1
    fi
    
    if [ -d "${PGDATA}" ]
    then
        echo "Deleting existing data"
        find "${PGDATA}" -delete
        mkdir "${PGDATA}"
        chmod 700 "${PGDATA}"
    fi
    echo Starting base backup
    # Checking slot name
    SLOT_HOST=$(hostname -s)
    SLOT_NAME="node_${SLOT_HOST}_slot"
    SLOT_EXISTS=$(psql --username="${USER}" --tuples-only -h ${REMOTE_HOST} -c "SELECT count(slot_name) as Slots FROM pg_replication_slots WHERE slot_name = '${SLOT_NAME}';")
    MAKE_SLOT=--write-recovery-conf
    if [ "$SLOT_EXISTS" -eq 0 ]
    then
        MAKE_SLOT=--create-slot
    fi
    pg_basebackup --pgdata="${PGDATA}" --host="${REMOTE_HOST}" --username="${USER}" --wal-method=stream --write-recovery-conf --slot="${SLOT_NAME}" "${MAKE_SLOT}" 
    if [ -d "${PGDATA}" ]
    then
        echo "Setting permissions"
        chmod 700 "${PGDATA}"
        chown -R "${PGOWNER}":"${PGGROUP}" "${PGDATA}"
    fi

    # Start the service
    service postgresql start
    if service postgresql status 2> /dev/null
    then
        echo "PostgreSQL successfully replicated and started"
    else
        echo "Something went wrong"
        exit 1
    fi
}

do_pg_make_replica

The status of replicas can be checked with the following:

SELECT slot_name, active, active_pid, restart_lsn
FROM pg_replication_slots;