Upgrading your PostgreSQL database

Posted on by

You might wonder what this post has to do with HCL products, but there are more and more HCL products using a PostgreSQL database. The HCL Leap version that’s based on OpenLiberty can, for example, use PostgreSQL as database backend. Also, in the container version of HCL DX, PostgreSQL is used for the Digital Asset Management container. I’m also using PostgreSQL as the backend for Keycloak, which I use for authentication method for both Domino and Connections.

PostgreSQL regularly releases new major versions, which offer benefits in the area of security and performance. You can’t just upgrade to these new major versions and expect PostgreSQL to do the update itself, so if you want to use a new major version, you’ll have to do some effort. This post describes what you have to do to upgrade the PostgreSQL version that is used by Keycloak using the Bitnami Keycloak and PostgreSQL container images.

In short, the idea is to start your PostgreSQL container without the Keycloak container, do a pg_dump to dump the data to a backup.sql, shut down the container, start a new container with the new PostgreSQL version and a clean data directory, import the datadump (psql), shut down the container, change your keycloak compose file to use the new version of PostgreSQL and the new data directory and start Keycloak. Let’s work this out in a bit more detail.

Step-by-step description

For the clarity of the given example, this is the part of my docker-compose file for the keycloak db:

version: '3.6'
services:
  postgresql:
    image: docker.io/bitnami/postgresql:11
    container_name: keycloak_db
    environment:
      - ALLOW_EMPTY_PASSWORD=no
      - POSTGRESQL_USERNAME=keycloakuser
      - POSTGRESQL_DATABASE=keycloak
      - POSTGRESQL_PASSWORD=<hidden>
    volumes:
      - '/<path-to>/keycloak/db:/bitnami/postgresql:Z'
    restart: always
    networks:
      - keycloak

networks:
  keycloak: {}

As you can see, I’m using bitnami’s image for PostgreSQL. I’m also using bitnami’s Keycloak image btw. If I would simply replace the version number of keycloak with 16, I would be greeted by this error message:

The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 16

So some sort of migration is needed. The easiest way is to create a backup of the PostgreSQL database and restore it in the new version, just like we would do with DB2. One way to do this is this:

  1. Start the PostgreSQL container, but don’t start any containers using it
    The easiest way to do this, is to create a separate docker-compose file wih just the PostgreSQL part
  2. Exec in this container
  3. Execute a pg_dump command and write the contents to a directory that’s on the persistent storage and exit the container. You will be asked for the password of the user.
Example
docker exec -it keycloak_db bash
# if you use a different image than bitnami's, the path below will be different
pg_dump -U keycloakuser keycloak > /bitnami/postgresql/backup.sql  # You will be asked for the password
exit
  1. Stop the container, remove it
  2. Create a directory for the new PostgreSQL data
  3. Move your backup.sql to this directory
  4. Make sure this directory is owned by the same user as your current PostgreSQL data directory
Example
docker-compose -f <path-to>/keycloak-db-only.yaml down
mkdir -p <path-to>/keycloak/db16
mv <pah-to>/keycloak/db/backup.sql <path-to>/keycloak/db16/ 
chown -R <pg-user>:<pg-user> <path-to>/keycloak/db16
  1. Change your docker-compose file to use the PostgreSQL version that you want to migrate to and make sure that your volume is pointing to the new directory
Example
image: docker.io/bitnami/postgresql:16
...
volumes:
      - '/<path-to>/keycloak/db16:/bitnami/postgresql:Z'
  1. Start the PostgreSQL container
  2. Import the backup.sql
Example
docker-compose -f <path-to>/keycloak-db-only.yaml up
docker exec -it keycloak_db bash
# if you use a different image than bitnami's, the path for the backup file below will be different
psql -U keycloakuser keycloak < /bitnami/postgresql/backup.sql  # You will be asked for the password
  1. If all went well, stop the container
  2. Move your old PostgreSQL directory a new directory
  3. Rename your new directory to the name of the old one
  4. Change the version for PostgreSQL in your docker-compose file to the new version
  5. Start keycloak
Example
docker-compose -f <path-to>/keycloak-db-only.yaml down
mv <path-to>/keycloak/db <path-to>/keycloak/db11
mv <path-to>/keycloak/db16 <path-to>/keycloak/db
vi <path-to>/keycloak.yml
docker-compose -f <path-to>/keycloak.yml up

Resources

https://docs.bitnami.com/aws/infrastructure/lapp/administration/backup-restore-postgresql