Upgrading Between Major Versions of PostgreSQL on FreeBSD

Upgrading major versions of PostgreSQL on FreeBSD (e.g. from 12 to 13) can be tricky. The challenge is that pkg upgrade removes the old binaries before installing the new ones, but pg_upgrade requires both versions to be present to migrate your data. If you just delete the old package and install the new one, you're left without the old binaries needed to read your existing data format.

This guide explains the official way to upgrade PostgreSQL by manually preserving the old binaries in a temporary location before the upgrade. These instructions are taken directly from the UPDATING file in the ports tree.

The most critical step is creating a temporary backup of your current PostgreSQL binaries using pkg create and extracting them to a temporary directory. This allows us to point pg_upgrade to these old binaries even after the system package has been removed.

Preserve Old Binaries

We must save the binaries of the version you are currently running. We'll use pkg create to bundle the currently installed files into a package, and then extract them to /tmp/pg-upgrade.

For convenience, set your current/old version number and target/new version numbers as shell variables:

OLD_POSTGRES_VERSION=12
NEW_POSTGRES_VERSION=13

Create backup packages of the current installation:

pkg create postgresql${OLD_POSTGRES_VERSION}-server postgresql${OLD_POSTGRES_VERSION}-contrib

Create a temporary directory and extract the old binaries there

mkdir /tmp/pg-upgrade
tar xf $(pkg info -x postgresql${OLD_POSTGRES_VERSION}-server).pkg -C /tmp/pg-upgrade
tar xf $(pkg info -x postgresql${OLD_POSTGRES_VERSION}-contrib).pkg -C /tmp/pg-upgrade

Install New Version

Now that we have a copy of the old binaries safely stored in /tmp, we can remove the old packages from the system and install the new version.

First, stop the running PostgreSQL service:

service postgresql stop

Remove the old packages and install the new version:

# Remove the old packages
pkg delete -f databases/postgresql${OLD_POSTGRES_VERSION}-server databases/postgresql${OLD_POSTGRES_VERSION}-contrib databases/postgresql${OLD_POSTGRES_VERSION}-client

# Install the new version
pkg install databases/postgresql${NEW_POSTGRES_VERSION}-server databases/postgresql${NEW_POSTGRES_VERSION}-contrib

It's also a good idea to run a general upgrade to ensure all dependencies are consistent:

pkg upgrade -y

Migrate Data

Before migrating, we need to initialize a fresh data directory for the new version. This creates the directory structure expected by the new PostgreSQL server.

Initialize the new database cluster

su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data${NEW_POSTGRES_VERSION} -U postgres"

Now we run pg_upgrade. Point the -b flag (old bindir) to our temporary location /tmp/pg-upgrade/usr/local/bin/, while the -B flag (new bindir) points to the standard system path /usr/local/bin/ where we just installed the new version.

su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data${OLD_POSTGRES_VERSION}/ -B /usr/local/bin/ -D /var/db/postgres/data${NEW_POSTGRES_VERSION}/ -U postgres"

If the upgrade command reports success, you can start the new PostgreSQL service:

service postgresql start

Finally, run the analyze script to optimize statistics for the new cluster:

su -l postgres -c "/var/db/postgres/analyze_new_cluster.sh"

Final Cleanup

Once you have verified everything is working correctly, you can remove the old data directory and the temporary binaries using the script provided by the upgrade process:

su -l postgres -c "/var/db/postgres/delete_old_cluster.sh"
rm -rf /tmp/pg-upgrade

What if you already deleted the old package?

If you accidentally ran pkg upgrade or pkg delete before saving your old binaries, you have a few options to recover the necessary files.

Option 1: Check the Package Cache

FreeBSD keeps a cache of downloaded packages in /var/cache/pkg. Unless you've explicitly run pkg clean, the old package archive is likely still there.

ls -l /var/cache/pkg/postgresql${OLD_POSTGRES_VERSION}-server*

If you find it, you can extract the binaries directly from that cached file using tar as shown in the "Preserve Old Binaries" section.

Option 2: Rebuild from Ports (The Hard Way)

If the cache is empty, you must rebuild the old version from source. If that version is still in the ports tree, it's a simple make package.

However, if the port has been deleted (e.g. End of Life), you will need to go deeper. You'll need to identify the git commit that removed the port and revert it.

1. Clone the ports tree:

git clone https://git.FreeBSD.org/ports.git /usr/ports
cd /usr/ports

2. Find the commit that removed the port and revert it:

Find the deletion commit hash

git log -1 -- databases/postgresql${OLD_POSTGRES_VERSION}-server

Revert it (creates a new commit restoring the files)

git revert [COMMIT_HASH]

Repeat the same process for the contrib and client ports, as well as any plugins or extensions that were installed.

Tip: You may encounter merge conflicts when reverting, especially if the MOVED file or Makefile infrastructure has changed significantly since the deletion. You will need to manually resolve these conflicts (usually by editing the files to remove conflict markers) and then run git revert --continue.

3. Build the package. Using poudriere is strongly recommended here to build in a clean jail, ensuring you don't accidentally link against newer system libraries:

poudriere testport -j [JAILNAME] -p default databases/postgresql${OLD_POSTGRES_VERSION}-server

Once the build finishes, grab the .pkg file from your poudriere packages directory and extract the binaries you need.