Problem
At PHZ Full Stack we have all environments virtualized and running e.g. PostgreSQL mostly only on Docker (Docker-compose, Swarm or Kubernetes). However, Docker brings unique problems how to upgrade the environment that are not present in "traditional" virtual machine or bare metal environments. Note that upgrade requires downtime. If you just upgrade the Postgresql container version, you will either loose all data if you delete the tablespace (data/), or you get an error message: ``` FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.16 (Debian 11.16-1.pgdg90+1). ``` Difficulties with docker: pg_upgrade requires both old and new psql binaries to exist on the computer that performs the upgrade. Since docker containers typically have just the one version, you have a problem. Solution: Use postgres upgrade container by tianon. See https://hub.docker.com/r/tianon/postgres-upgrade We must upgrade Postgres version, and migrate the data. We aim to make a dump of data with Postgres 10 and then input that data into Postgres 11 (or any other version pair). Note that you can upgrade also v12 to v15 or v10 to v13 etc. # Upgrade Container Here is an example Docker Swarm docker-compose.yml version: '3.8' services: postgres-upgrade: # # see https://hub.docker.com/r/tianon/postgres-upgrade # image: tianon/postgres-upgrade:12-to-14 deploy: replicas: 1 placement: constraints: - node.labels.type == db preferences: - spread: node.labels.host - spread: node.labels.region restart_policy: #Run only once per deployment condition: none volumes: - /db/postgres/project-prod/12:/var/lib/postgresql/12/data - /db/postgres/project-prod/14:/var/lib/postgresql/14/data Note! A typical issue when you are doing your first upgrade is that you notice that you have just one directory (volume) for your current PostgreSQL -version. You need to
1. Shutdown the current environment
2. Move the current data to e.g. directory called "12" if you have PostgreSQL v12 3. Create a new directory for PostgreSQL v14 called e.g. "14"
4. Run the migration container 5. Modify the docker-compose.prod.yml volume by upgrading the container to the new version, AND changing the volume to the new path (e.g. "14") 6. Start the environment. #4. Migration ## 4.1 Shutdown current database ```bash export DOCKER_HOST=docker-swarm-master.in.phz.fi export DOCKER_REGISTRY= docker stack rm phzproject-prod ``` ## 4.2 Run Upgrade Note! Upgrade needs two separate directories. In stg/prod they are (on NVMe disks) e.g. /db/postgresql/my-project-stg/12 and 14 (Note! Here we use PHZ project template standard environment available from https://github.com/phzfi/project-template ) ./deploy.sh stg-upgrade-psql stg Verify ```bash docker stack ps phz-project-stg-upgrade-psql ``` ## 4.3 Deploy new version Update postgresql version in your docker-compose.<stg/prod>.yml from 12 to 14. Commit & Push Build on CI Inspect the file pg_hba.conf, it should contain ``` host all all all md5 ``` That definition enable authenticates the project main user. If it is not there add it with your favorite editor (on the server/volume). ## 4.3.1 Verify ```bash docker stack ps phz-project-stg ``` Check that services are up and running. Login into the database. ```bash psql -h db.in.phz.fi -p 5432 -U myuser schemaname ... ``` Use CURL ```bash curl http://docker-swarm-master.in.phz.fi:8002/status ``` should return "OK". ## 4.4 Cleanup You can now delete the old table space (e.g. dir "12"). It's on NFS mount so you can do it from any docker swarm node (e.g. nob, gift, tuk etc).