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 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 stopto stop the whole deployment of the Mastodon stack.
- Backing up the data:
docker-compose.ymlI am using is already using volume, so I just simply copying the folder with new name
cp -r postgre12 old. Notice I am using
oldas my folder name. This folder will be used as a source directory for
- Make new directory for new data:
- 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
- Adjust your docker-compose for the new database folder and docker image. See example below:
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
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
postgresql.conf first beforehand. I had issue where the new
pg_hba.conf only listen to localhost only.