While PostgreSQL is one of the oldest and most widely deployed databases, it is uncommon for instances to be upgraded frequently in production environments. When upgrades become necessary, the process can be challenging for some administrators, as automated scripts or migration procedures, as common for other databases, don't exist for PostgreSQL. Let's look at how upgrading between PostgreSQL versions works.
A sample setup
To illustrate the issues. we will start with the following sample setup:
docker-compose.yml
version: "3.8"
services:
pgdb:
image: postgres:13.8
container_name: my_pgdb
environment:
- POSTGRES_USER=my_user
- POSTGRES_DB=my_db
- POSTGRES_PASSWORD=my_pass
- PGDATA=/var/lib/postgresql/data/pgdata
volumes:
- "$PWD/pgdata:/var/lib/postgresql/data/pgdata"
Running docker compose up -d will initialize the database. Upgrading to a different minor release is trivial: simply change the version tag of the image, for example to postgres:13.12 and run docker compose up -d again. Upgrading between minor versions does not require additional steps, other than simply switching out the docker image. But if you change it to a different major version, for example postgres:14, you will get this error messages:
pgdb | 2023-10-21 02:50:44.346 UTC [1] FATAL: database files are incompatible with server
pgdb | 2023-10-21 02:50:44.346 UTC [1] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.9 (Debian 14.9-1.pgdg120+1).
Upgrading to a newer major version
The process of upgrading an existing Postgres database to a new major release looks like this:
- Export all data from the old database
- Start the new database version
- Import the previously exported data from the old database into the new one
Exporting the old data
The first step in upgrading is to export the data of the old database instance. The easiest way to do this is to run pg_dumpall, contained in the postgres docker image:
docker exec -it my_pgdb pg_dumpall -U my_user > dump.sql
Note that pg_dumpall writes to stdout, so we can redirect it into the file dump.sql on the host directly, without needing to copy anything between host and container.
Starting a database instance for the new version
Now that the data is exported, we need a new database instance, we simply add a second database to our docker-compose.yml. While we could simply delete the old database, it is common to add the new one first, restore the data on it and ensure everything worked as intended, before removing old versions.
docker-compose.yml
version: "3.8"
services:
pgdb:
image: postgres:13.8
container_name: my_pgdb
environment:
- POSTGRES_USER=my_user
- POSTGRES_DB=my_db
- POSTGRES_PASSWORD=my_pass
- PGDATA=/var/lib/postgresql/data/pgdata
volumes:
- "$PWD/pgdata:/var/lib/postgresql/data/pgdata"
new_pgdb:
image: postgres:15
container_name: my_new_pgdb
environment:
- POSTGRES_USER=my_user
- POSTGRES_DB=my_db
- POSTGRES_PASSWORD=my_pass
- PGDATA=/var/lib/postgresql/data/pgdata
volumes:
- "$PWD/pgdata_new:/var/lib/postgresql/data/pgdata"
Note that we adjusted the container name and storage volume path to avoid conflicts with the old instance.
Importing the data on the new instance
To import the previously exported data into our new PostgreSQL instance, we feed it to the psql command through stdin:
cat dump.sql | docker exec -i my_new_pgdb psql -U my_user my_db
Once this command completes, all your data is now readily available in the new database instance. You can now safely delete the old database instance and the file dump.sql.
More complex upgrades with pg_upgrade
For larger databases, the export/import approach may not be feasible, or the required downtime may be unacceptable for mission-critical databases. For such cases, the postgres toolkit offers the pg_upgrade utility, that can carry out upgrades in-place, even with near-zero downtime thanks to the -link option. It involves several manual steps and is typically ill-suited or a container environment, where containers are treated as ephemeral and only volumes are considered persistent. Refer to the PostgreSQL documentation for pg_upgrade for more information on using pg_upgrade to meet advanced upgrade requirements.
Upgrading a PostgreSQL instance to a new major release is always a balanced consideration between system stability and feature availability. While the process may seem daunting at first, it is safe and trivial once understood. In contrast, keeping minor versions up to date involves very little effort and should take some priority, to ensure the security and stability of the instance.