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;