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 used 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 contain ready-to-use container image to upgrade PostgreSQL. I will share how my experience in 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 copying 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. Plase adjust with the Postgre version you have and the target version you want. See above repo to check wether 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 run the docker-compose up -d to check wether everythings work perfectly. You might want to check and compare the configuration on pg_hba.conf and postgresql.conf first beforehand. I had issue where the new pg_hba.conf only listen to localhost only.

Upgrade Major PostgreSQL version on Docker

Related Posts