Upgrade Postgres Version

When you want to upgrade a Postgres container that uses a data directory mounted from the host, the container will fail to start as the database and container versions are incompatible. To resolve this you will need to create a backup of the database and restore from that backup into the new container, the steps of which are outlined below.

Sample Setup

For the following tutorial we will work with the following docker container configuration:

version: "3.0"
services:
    pgsqlapi:
        image: cloudcix/pgsqlapi:1
        volumes:
            - './data_dir:/var/lib/postgresql/data/'
            - './backup_dir:/data/backups/

This compose file runs version 1 of the CloudCIX Pgsqlapi container. We will be using the default username postgres.

Step 1

Create a backup of the database:

$ sudo docker exec -it pgsqlapi pg_dumpall -U postgres --clean > ./backup_dir/backup.sql

Step 2

Stop the container

$ sudo docker compose stop pgsqlapi

As an extra precaution, move the data directory on the host so that it is not affected during the upgrade process.

$ mv data_dir data_dir_backup
$ ls
- backup_dir  data_dir_backup  docker-compose.yml

Step 3

Update the version of the Postgres container in the docker compose file

pgsqlapi:
    image: cloudcix/pgsqlapi:2

Run the container

$ docker compose up -d pgsqlapi

Step 4

Restore the database from the backup script

$ docker exec -it pgsqlapi psql -U postgres -f /data/backup/backup.sql