Upgrade Major PostgreSQL version on Docker

Upgrading a PostgreSQL database server to a newer version is a common task for database administrators and developers. In this post, we will discuss how to upgrade a PostgreSQL database server running in a Docker container.

On 1 January, I decided to upgrade the PostgreSQL used by my Mastodon instance. It was using PostgreSQL 12 and I think it could use the upgrade to PostgreSQL 15 for that “extra performance and features”. For the upgrade process, basically you need to run pg_upgrade tool. pg_upgrade allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades, e.g., from 9.5.8 to 9.6.4 or from 10.7 to 11.2. It is not required for minor version upgrades, e.g., from 9.6.2 to 9.6.3 or from 10.1 to 10.2.

Fortunately, I found a github repository from Tianon that contains a ready-to-use container image to upgrade PostgreSQL. I will share my experience using them.

• Shutting down the database: I am using docker-compose for the deployment, so I just run docker-compose stop to stop the whole deployment of the Mastodon stack.
• Backing up the data: docker-compose.yml I am using is already using volume, so I just simply copied the folder with new name cp -r postgre12 old. Notice I am using old as my folder name. This folder will be used as a source directory for pg_upgrade later.
• Make new directory for new data: mkdir new
• Pull the docker image: docker pull tianon/postgres-upgrade:12-to-15. Please adjust with the Postgre version you have and the target version you want. See above repo to check whether your version is available.
• Run the docker image and wait: docker run --rm -v /path/to/folder/mastodon/old:/var/lib/postgresql/12/data -v /path/to/folder/mastodon/new:/var/lib/postgresql/15/data tianon/postgres-upgrade:12-to-15. Make sure the version of image and version to postgresql path is already correct
• Rename the folder: I rename the new folder to postgres15.
• Adjust your docker-compose for the new database folder and docker image. See example below:

Before:

  db:
    restart: always
    image: postgres:12-alpine
    shm_size: 256mb
    networks:
      - internal_network
    healthcheck:
      test: ['CMD', 'pg_isready', '-U', 'postgres']
    volumes:
      - ./postgres12:/var/lib/postgresql/data

After

  db:
   restart: always
   image: postgres:15-alpine
   shm_size: 256mb
   networks:
     - internal_network
   healthcheck:
     test: ['CMD', 'pg_isready', '-U', 'postgres']
   volumes:
     - ./postgres15:/var/lib/postgresql/data

I ran docker-compose up -d to check whether everything works perfectly. You might want to check and compare the configuration on pg_hba.conf and postgresql.conf first beforehand. I had an issue where the new pg_hba.conf only listened to localhost.