Upgrade PostgreSQL on Docker

A step-by-step tutorial on safely migrating your PostgreSQL data between major versions on Docker.

Upgrading a major version of PostgreSQL, such as from 17 to 18, isn’t a simple in-place process because the underlying data storage format can change. When running PostgreSQL in Docker, the recommended method is a classic “dump and restore.” This guide will walk you through the entire process, ensuring a safe and successful migration.

A key change to be aware of is that starting with version 18, the official PostgreSQL Docker image uses a version-specific data directory path (/var/lib/postgresql/18/data instead of the generic /var/lib/postgresql/data).

Important Change: the PGDATA environment variable of the image was changed to be version specific in PostgreSQL 18 and above⁠. For 18 it is /var/lib/postgresql/18/docker. Later versions will replace 18 with their respective major version (e.g., /var/lib/postgresql/19/docker for PostgreSQL 19.x). The defined VOLUME was changed in 18 and above to /var/lib/postgresql. Mounts and volumes should be targeted at the updated location. This will allow users upgrading between PostgreSQL major releases to use the faster --link when running pg_upgrade and mounting /var/lib/postgresql.

Let’s start with the existing docker-compose.yml for our PostgreSQL 17 instance.

services:
  db:
    image: postgres:17
    restart: unless-stopped
    environment:
      - POSTGRES_USER=example_user
      - POSTGRES_PASSWORD=example_password
      - POSTGRES_DB=example_db
    ports:
      - '5432:5432'
    volumes:
      - ./db:/var/lib/postgresql/data

Step 1: Backup Your Database

The most critical step is to create a complete backup of your entire PostgreSQL instance. We’ll use pg_dumpall to create a single script file containing all databases, users, and global objects. Ensure your container is running before executing this command.

We’ll use the container name postgres-db-1 as specified.

docker exec postgres-db-1 pg_dumpall -U example_user > backup.sql

This command connects to the running container and pipes a full SQL dump into a backup.sql file on your host machine.

Step 2: Stop the Old Container

With the backup secured, you can safely shut down the old container. It’s also best practice to rename the old data volume directory. This prevents the new PostgreSQL 18 instance from accidentally trying to use the old, incompatible data files.

docker compose down
mv ./db ./db-old

Step 3: Update docker-compose.yml for PostgreSQL 18

Now, modify your docker-compose.yml file. You need to update the image tag to postgres:18 and, most importantly, update the volume path to reflect the new version-specific directory structure used by the official image.

services:
  db:
    image: postgres:18 # Change version number
    restart: unless-stopped
    environment:
      - POSTGRES_USER=example_user
      - POSTGRES_PASSWORD=example_password
      - POSTGRES_DB=example_db
    ports:
      - '5432:5432'
    volumes:
      # IMPORTANT: The PGDATA path is now version-specific in the official image
      - ./db:/var/lib/postgresql

This change ensures that your data volume is mapped to the correct PGDATA location inside the new container.

Step 4: Start the New Container

It’s time to bring up the new PostgreSQL 18 container. Docker Compose will pull the postgres:18 image and create a new, empty ./db directory on your host, which will be used to initialize the new database instance.

docker compose up -d

Step 5: Restore Your Data

Once the new container is up and running, you can restore your data from the backup file created in Step 1. We will pipe the backup.sql file into the psql command inside the new container.

docker exec -i postgres-db-1 psql -U example_user -d example_db < backup.sql

This command may take some time, depending on the size of your database.

Step 6: Clean Up (Optional)

After the restore process finishes, connect to your database with your application or a database client to ensure all data has been restored correctly. Once you have fully verified the integrity of the migration, you can safely remove the old data directory and the SQL backup file to reclaim disk space.

rm -rf ./db-old
rm backup.sql

Step 7: Check the Version

As a final confirmation, you can execute a command inside the container to check the running PostgreSQL version.

docker exec -it postgres-db-1 psql -U example_user -d example_db

After connecting, run the following SQL query:

SELECT version();

You should see an output confirming you are now running PostgreSQL 18. Congratulations on your successful upgrade!

Source: https://henrywithu.com/upgrade-postgresql-from-17-to-18-on-docker/