PostgreSQL: Upgrade to version 18 in-place

Recently, PostgreSQL released their latest Version - 18. As I always like to use the latest and greatest, I upgrade all my databases.

As I already mentioned in an earlier blog post, upgrading PostgreSQL in-place isn't possible. But we can use the great image made by PgAutoUpgrade. I just substitute the official PostgreSQL image.

My docker-compose.yml looks like this:

services:
  psql_upgrade:
    image: postgres:17
    hostname: psql_upgrade
    restart: always
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: XxXxXxXx

To upgrade it to version 18, I set it like this:

services:
  psql_upgrade:
    image: pgautoupgrade/pgautoupgrade
    hostname: psql_upgrade
    restart: always
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: XxXxXxXx

After that, pull the image and start the container:

# docker compose pull
# docker compose up -d

 ✔ psql_upgrade Pulled                                  1.0s
  ⠦ Container postgresql-psql_upgrade-1  Starting       0.7s

Let's have a look at the log files:

# docker compose logs -f

Error response from daemon: failed to create task for container: failed to create shim task: OCI runtime create failed: runc create failed: unable to start container process: error during container init: error mounting "/home/ubuntu/docker/postgresql/pgdata" to rootfs at "/var/lib/postgresql/data": change mount propagation through procfd: open o_path procfd: open /var/lib/docker/overlay2/d12dc39ab89c1813b05b39c54e97f194683637fe9842871c6fe5eb4e30f3ee90/merged/var/lib/postgresql/data: no such file or directory: unknown

Something seems to be wrong here...

After some research, I found out, that the data volume for PostgreSQL 18 in Docker has changed from /var/lib/postgresql/data to /var/lib/postgresql/18/docker.

So just let's change the yaml file and try again:

services:
  psql_upgrade:
    image: pgautoupgrade/pgautoupgrade
    hostname: psql_upgrade
    restart: always
    volumes:
      - ./pgdata:/var/lib/postgresql/18/docker
    environment:
      POSTGRES_PASSWORD: XxXxXxXx

# docker compose up -d
# docker compose logs -f

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

Ok, that was the start of the upgrade process.

The end will look like this:

psql_upgrade-1  | *******************************************
psql_upgrade-1  | Upgrade to PostgreSQL 18.0 complete.
psql_upgrade-1  | *******************************************
psql_upgrade-1  | Removing upgrade lock file at /var/lib/postgresql/18/docker/upgrade_in_progress.lock
psql_upgrade-1  | 2025-10-11 19:36:24.088 UTC [9] LOG:  starting PostgreSQL 18.0 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit
psql_upgrade-1  | 2025-10-11 19:36:24.088 UTC [9] LOG:  listening on IPv4 address "0.0.0.0", port 5432
psql_upgrade-1  | 2025-10-11 19:36:24.088 UTC [9] LOG:  listening on IPv6 address "::", port 5432
psql_upgrade-1  | 2025-10-11 19:36:24.092 UTC [9] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
psql_upgrade-1  | 2025-10-11 19:36:24.099 UTC [236] LOG:  database system was shut down at 2025-10-11 19:36:23 UTC
psql_upgrade-1  | 2025-10-11 19:36:24.106 UTC [9] LOG:  database system is ready to accept connections
psql_upgrade-1  | 2025-10-11 19:36:49.083 UTC [250] FATAL:  role "root" does not exist
psql_upgrade-1  | 2025-10-11 19:37:04.142 UTC [251] WARNING:  database "template1" has no actual collation version, but a version was recorded
psql_upgrade-1  | 2025-10-11 19:37:19.130 UTC [261] FATAL:  role "root" does not exist
psql_upgrade-1  | 2025-10-11 19:37:24.137 UTC [262] WARNING:  database "postgres" has no actual collation version, but a version was recorded
psql_upgrade-1  | 2025-10-11 19:37:49.179 UTC [273] FATAL:  role "root" does not exist
psql_upgrade-1  | 2025-10-11 19:38:04.149 UTC [274] WARNING:  database "template1" has no actual collation version, but a version was recorded
psql_upgrade-1  | 2025-10-11 19:38:19.225 UTC [284] FATAL:  role "root" does not exist
psql_upgrade-1  | 2025-10-11 19:38:24.155 UTC [285] WARNING:  database "postgres" has no actual collation version, but a version was recorded
psql_upgrade-1  | 2025-10-11 19:38:49.268 UTC [296] FATAL:  role "root" does not exist
psql_upgrade-1  | 2025-10-11 19:39:04.168 UTC [297] WARNING:  database "template1" has no actual collation version, but a version was recorded
psql_upgrade-1  | 2025-10-11 19:39:19.313 UTC [307] FATAL:  role "root" does not exist
psql_upgrade-1  | 2025-10-11 19:39:24.166 UTC [308] WARNING:  database "postgres" has no actual collation version, but a version was recorded

Let's set the image to the official PostgreSQL image:

services:
  psql_upgrade:
    image: postgres:18

After starting the container using the new image, the logs will look like this:

psql_upgrade-1  | 2025-10-11 19:41:00.695 UTC [32] LOG:  checkpoint starting: end-of-recovery immediate wait
psql_upgrade-1  | 2025-10-11 19:41:00.711 UTC [32] LOG:  checkpoint complete: wrote 97 buffers (0.6%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.006 s, total=0.019 s; sync files=26, longest=0.004 s, average=0.001 s; distance=377 kB, estimate=377 kB; lsn=0/9128118, redo lsn=0/9128118
psql_upgrade-1  | 2025-10-11 19:41:00.715 UTC [1] LOG:  database system is ready to accept connections

The database system is upgraded to version 18 now. And the errors saying

database "postgres" has no actual collation version, but a version was recorded

are not showing any more.

Disclaimer: Never ever upgrade your system without checking if

• your applications support the newer version
• database backups exist

And always try it first on a test system!