How I upgraded PosgreSQL 12 -> 16

Historically, we had not updated PostgreSQL for a long time and were stuck on version 12. But the time came to update the project dependencies and it turned out that Django 5.1 no longer supports version 12 of PostgreSQL and this motivated me to upgrade to the latest version 16.

At the time of writing, everything is organized quite simply – all necessary components are launched in Docker containers via Docker Compose.

I had two options on how to migrate:

1. Make a full dump, upload a new image postgres:16 and roll a dump

2. Use the utility pg_upgradewhich allows you to migrate data between PostgreSQL versions

Since the first method takes more time to recover data, I decided to use the second option – pg_upgradePlus, this option gave the opportunity to try something new.

At first it was unclear how to apply pg_upgrade in docker, but after some googling I found a solution: https://github.com/tianon/docker-postgres-upgrade

As we learn how to work with docker-postgres-upgrade the following plan was drawn up:

1. stop services dependent on the DB

2. make a backup of the database

3. stop db

4. make a migration

5. run and test the database on a new version of PostgreSQL

6. start services

7. update docker-compose.yml

Note: although pg_upgrade and allows you to migrate hot, but I decided not to risk it and do it cold

Below I will briefly describe the migration process.

First, we need to understand what volume our PostgreSQL uses.

% cat docker-compose.yml

services:
...
	postgres:
		image: postgres:12
		container_name: postgres
		command: postgres -c "config_file=/etc/postgresql/postgresql.conf"
		environment:
			POSTGRES_PASSWORD: xxxxxx
		volumes:
			- ./postgresql/postgresql.conf:/etc/postgresql/postgresql.conf
			- postgres:/var/lib/postgresql/data
		ports:
			- "5432:5432"
...

volumes:
	postgres:

% docker volume ls
DRIVER    VOLUME NAME
local     1b7ffcbd614f280ab577370912c625135ef84fe8cf721953206ba0142bed8cad
local     6b82aa6ecc19fbea9eb91b8d68af0d6a18d04911717953935cff006a4220e123
local     7c2b74aa668a47d2fe15615b8f4a614ebcf3065ab101cdaf487da6b5e1c95ffd
local     16ea96ec68eeb0134f0eebd27ec983143e9bdd43d527d3c6e1d3f1cd2bacc516
local     180aeeeadba249e991c22bb77c53c9b2a50612a3d181598a8014b667d920762d
local     625ebc83103e8c223395fb7b5e7e31a714110400f6f70f499bee13f3d9b86bce
local     803c056ecb2c83b31e5d0dbb089689ae1b44996fb84aee1e12c8d224b583f8e7
local     a1ccdb17c4cc50333ec2c417cf24553e89e4f5aa1e402c1985a8fec2f52c00dd
local     a40c9d1b958169dda6db47e7493dacf7c05e6e0acd678954ea87f7ac3ffb8a16
local     backend_media
local     backend_postgres
local     backend_postgres-14
local     backend_postgres-16
local     backend_static
local     c73a013579341af8a05646a501f4b4a9595a3e2b1167543889d1c26a21e4fda3
local     cebd974e3e2f2a5bd0b2cf16a4ef293696e30d547ccfd59bad44d155541da16b
local     d393b6d74083d4ac5a679a7ed62cf364663a66fa41f113e1b60395ec369e26bb
local     dev_badger
local     dev_media
local     dev_postgres
local     dev_static
local     edb6be7221e82a9f9c4ba47ac1cae104765bc8d4d29020fef1397aa4d22e9472
local     minikube
local     minikube-m02
local     postgres
local     postgres16
local     qa-automation_badger
local     qa-automation_media
local     qa-automation_postgres
local     qa-automation_static

We need volume backend_postgresbecause Docker Compose by default names volumes in the format “project name + volume name”. Since the project is named backend (by default, the name of the directory where Docker Compose is launched, unless otherwise specified by the key -p), our target volume will be called backend_postgres. For the new version of PostgreSQL, the volume will be called backend_postgres16.

migration

% docker run --platform linux/amd64 --rm -v backend_postgres:/var/lib/postgresql/12/data -v backend_postgres16:/var/lib/postgresql/16/data tianon/postgres-upgrade:12-to-16

On MacOS with Apple Silicon architecture (M1/M2/M3) it became necessary to add a flag --platform linux/amd64since the image tianon/postgres-upgrade does not support ARM architecture by default. This allows you to force the use of x86_64 image for successful startup.

After completing the migration procedure, we do a test run:

% docker run -dit --name postgres16 -p 5432:5432 -e POSTGRESS_PASSWORD=postgres -v backend_postgres16:/var/lib/postgresql/data -v ./postgresql/postgresql.conf:/var/lib/postgresql/data/postgresql.conf -v ./postgresql/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf  postgres:16

I have custom postgresql.conf and pg_hba.conf, so we pass them inside the container.

And we check the logs that everything is fine.

% docker logs -f postgres16

You should check the logs for errors or warnings. Pay special attention to messages about version incompatibility or problems with accessing data.

If the test run was successful, you can commit the changes to the main file docker-compose.ymlreplacing the old version of PostgreSQL with the new one. It is important to ensure that all dependencies are updated and configured correctly before running in production.

New docker-compose.yml

services:
...
    postgres:
        image: postgres:16
        container_name: postgres
        environment:
            POSTGRES_PASSWORD: xxxxxx
        volumes:
            - ./postgresql/postgresql.conf:/var/lib/postgresql/data/postgresql.conf
            - ./postgresql/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf
            - postgres16:/var/lib/postgresql/data
        ports:
            - "5432:5432"
...

volumes:
    postgres16:

Conclusion

Thanks to docker-postgres-upgrade The migration process to the new version of PostgreSQL was quick and smooth. This is a great solution for those looking for a simple and reliable way to upgrade PostgreSQL in a Docker environment. I recommend it!