PostgreSQL: inplace upgrade in Docker container

As I wrote in a older blog entry, we cannot just change the PostgreSQL image of a Docker container to a higher major version. Restarting the container like that will lead to a not running PostgreSQL container, as older data will not start using newer binaries.

But there's a great project that will help to do an inplace upgrade.

This is a PostgreSQL container running PostgreSQL 9.5:

  psql_mig:
    image: postgres:9.5
    hostname: psql_mig
    restart: always
    environment:
      POSTGRES_PASSWORD: XxXxXxXx
    ports:
      - 5442:5432

We will change the image to pgautoupgrade/pgautoupgrade:

  psql_mig:
    image: pgautoupgrade/pgautoupgrade
    hostname: psql_mig
    restart: always
    environment:
      POSTGRES_PASSWORD: XxXxXxXx
    ports:
      - 5442:5432

After that, we pull the latest image and restart the container using docker compose:

# docker compose pull && docker compose up -d

To see what happens, we execute

# docker compose logs -f

psql_mig-1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
psql_mig-1  |
psql_mig-1  | ************************************
psql_mig-1  | PostgreSQL data directory: /var/lib/postgresql/data
psql_mig-1  | ************************************
psql_mig-1  | Creating upgrade lock file at /var/lib/postgresql/data/upgrade_in_progress.lock
psql_mig-1  | *******************************************************************************************
psql_mig-1  | Performing PG upgrade on version 9.5 database files.  Upgrading to version 17
psql_mig-1  | *******************************************************************************************
psql_mig-1  | ----------------------------------------------------------------------
psql_mig-1  | Checking for left over artifacts from a failed previous autoupgrade...
psql_mig-1  | ----------------------------------------------------------------------
psql_mig-1  | -------------------------------------------------------------------------------
psql_mig-1  | No artifacts found from a failed previous autoupgrade.  Continuing the process.
psql_mig-1  | -------------------------------------------------------------------------------
psql_mig-1  | ---------------------------------------
psql_mig-1  | Creating OLD temporary directory /var/lib/postgresql/data/old
psql_mig-1  | ---------------------------------------
psql_mig-1  | --------------------------------------------
psql_mig-1  | Creating OLD temporary directory is complete
psql_mig-1  | --------------------------------------------
psql_mig-1  | -------------------------------------------------------
psql_mig-1  | Moving existing data files into OLD temporary directory
psql_mig-1  | -------------------------------------------------------
psql_mig-1  | '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'

[...]

psql_mig-1  | INFO:  "updates": scanned 1 of 1 pages, containing 83 live rows and 0 dead rows; 83 rows in sample, 83 estimated total rows
psql_mig-1  | VACUUM
psql_mig-1  | -------------------------------------
psql_mig-1  | Finished updating query planner stats
psql_mig-1  | -------------------------------------
psql_mig-1  | ------------------------
psql_mig-1  | Reindexing the databases
psql_mig-1  | ------------------------
psql_mig-1  | reindexdb: reindexing database "postgres"
psql_mig-1  | 2025-01-09 13:29:29.225 UTC [223] FATAL:  role "root" does not exist
psql_mig-1  | 2025-01-09 13:29:59.303 UTC [232] FATAL:  role "root" does not exist
psql_mig-1  | 2025-01-09 13:30:22.856 UTC [193] LOG:  checkpoint starting: wal
psql_mig-1  | 2025-01-09 13:30:29.365 UTC [244] FATAL:  role "root" does not exist
psql_mig-1  | 2025-01-09 13:30:59.424 UTC [254] FATAL:  role "root" does not exist
psql_mig-1  | 2025-01-09 13:31:06.765 UTC [193] LOG:  checkpoint complete: wrote 512 buffers (3.1%); 0 WAL file(s) added, 1 removed, 32 recycled; write=37.035 s, sync=6.680 s, total=44.273 s; sync files=174, longest=6.676 s, average=0.039 s; distance=544950 kB, estimate=544950 kB; lsn=0/C54439D8, redo lsn=0/A642D920
psql_mig-1  | 2025-01-09 13:31:07.295 UTC [193] LOG:  checkpoint starting: wal
psql_mig-1  | 2025-01-09 13:31:08.021 UTC [193] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 33 recycled; write=0.309 s, sync=0.011 s, total=0.727 s; sync files=4, longest=0.010 s, average=0.003 s; distance=540616 kB, estimate=544516 kB; lsn=0/C8464638, redo lsn=0/C741FCD8
psql_mig-1  | 2025-01-09 13:31:29.469 UTC [264] FATAL:  role "root" does not exist
psql_mig-1  | reindexdb: reindexing database "template1"
psql_mig-1  | -------------------------------
psql_mig-1  | End of reindexing the databases
psql_mig-1  | -------------------------------
psql_mig-1  | *************************************************************************************************
psql_mig-1  | Post upgrade tasks have finished successfully.  PostgreSQL should now be fully updated and online
psql_mig-1  | *************************************************************************************************

The PostgreSQL container is now upgraded to the latest version provided by pgautoupgrade/pgautoupgrade.

We can now revert the container image back to vanilla PostgreSQL:

psql_mig:
    image: postgres:17
    hostname: psql_mig
    restart: always
    environment:
      POSTGRES_PASSWORD: XxXxXxXx
    ports:
      - 5442:5432

Please keep in mind: You should always have a backup! Especially before upgrading, you should have a valid - and also tested! - backup you could use for a restore if something goes wrong.