Skip to main content

Production-PostgreSQL-upgrade-from-9.6-to-14.5.0

Production PostgreSQL upgrade from 9.6 to 14.5.0:​

Full story publicly available at Terabyte-scale PostgreSQL upgrade from 9.6 to 14

  1. Having h25-h26-(h27, h28) replicas running and aligned with primary instance. This allows an easy restore of the actual situation if something goes wrong.
  2. Check DB backup status
  3. Enter in the current primary instance and copy both postgresql.conf and pg_hba.conf from bitnami's conf directory to the data directory with the following command: cp /bitnami/postgresql/conf/* /bitnami/postgresql/data/
  4. Move to the main host where primary instance is running and edit pg_hba.conf in the instance volume setting trust to local all all entry
  5. Stop all DB LBs with IaC
  6. Shutdown current primary instance and any other replica with IaC as well as backup cluster (h25-h26-h27-h28)
  7. Enter in the migration container in host machine (using tmux) with the following command:
docker run -it --entrypoint /bin/bash --rm \
-v "/home/persistent_volumes/pvc-5f523276-382a-4940-b072-0b7bf17246c5_be-main-env-prod_database-0-pg-data":/var/lib/postgresql/all \
-e PGDATAOLD=/var/lib/postgresql/all/data \
-e PGDATANEW=/var/lib/postgresql/all/dataNEW \
tianon/postgres-upgrade:9.6-to-14
  1. Install all extensions needed to start the OLD PG version like: apt update && apt install postgresql-9.6-pgaudit
  2. Create and set permissions for the dataNEW folder in all folder with: mkdir dataNEW && chown -R postgres:1000 dataNEW
  3. Run the following command in all folder to execute the migration: docker-upgrade pg_upgrade --link
  4. Remove old config files in data folder: rm postgresql.conf pg_hba.conf
  5. Remove cluster cleanup script: rm delete_old_cluster.sh
  6. Exit from the migration container
  7. Fix folders' permissions (from pvc-[...]pg-data folder) with:
chown -R 1001:1000 .
chown -R 1001:1000 data
chown -R 1001:1000 dataNEW
chmod -R 700 data
chmod -R 700 dataNEW
  1. Sync changes with buffer replica (from pvc-[...]pg-data folder) using rsync --archive --delete --hard-links --size-only --no-inc-recursive --info=progress2 --stats data dataNEW h33.uala.land:/home/persistent_volumes/pvc-941e6e1f-5090-4a1b-b7f2-b2f2422f6307_be-main-env-prod_database-1-pg-data
  2. Sync changes with replicas (from pvc-[...]pg-data folder) from buffer replica host using rsync --archive --delete --hard-links --size-only --no-inc-recursive --info=progress2 --stats data dataNEW h34.uala.land:/home/persistent_volumes/pvc-48a2c294-06ab-4b38-8343-5933a3f3915f_be-main-env-prod_database-2-pg-data and rsync --archive --delete --hard-links --size-only --no-inc-recursive --info=progress2 --stats data dataNEW h35.uala.land:/home/persistent_volumes/pvc-bcae1761-aa34-4095-a11e-d43ff62635c1_be-main-env-prod_database-3-pg-data
  3. In all the nodes remove old data folder with rm -r data, rename dataNEW with mv dataNEW data
  4. Using IaC, update the image for the primary instance to the new version keeping replica count to 0 for other replicas
  5. Using IaC, update the image for the buffer instance to the new version keeping replica count to 0 for other replicas
  6. Using IaC, update the image for the replicas instance to the new version
  7. Enter in the new updated primary instance and run the following commands psql -U postgres < /bitnami/postgresql/update_extensions.sql and /opt/bitnami/postgresql/bin/vacuumdb --all --analyze-in-stages -U postgres
  8. Start all DB LBs with IaC