Skip to content

Latest commit

 

History

History
52 lines (30 loc) · 3.1 KB

postgres_upgrade.md

File metadata and controls

52 lines (30 loc) · 3.1 KB

Upgrading a Major PostgreSQL Version

PostgreSQL data directories cannot be reused between major version upgrades. A data directory generated for PostgreSQL 14.x will not work with Postgres 15.x. When running Primero PostgreSQL with Docker, the data needs to be exported and reloaded, and the backing volume needs to be recreated.

In the instructions below, the Primero Server refers to the machine that runs a Dockerized Primero and the Ansible Server refers to the machine that is used to provision it with Ansible.

  1. Create a back up of the database volume. This will not be used for the migration, but can be used to roll back the upgrade if something goes wrong. Look at the Docker documentation for more information about backing up and restoring volumes. On the Primero Server:

     $ mkdir postgres_backup && chmod 777 postgres_backup
 $ docker run --rm --volumes-from primero-postgres-1 -v $(pwd)/postgres_backup:/backup busybox tar czvf /backup/primero-postgres-volume-backup.tar.gz /var/lib/postgresql/data
 $ chmod 700 postgres_backup

  2. Shut down all application containers. On the Primero Server:

     $ docker stop primero-application-1 primero-worker-1

  3. Create the migration data dump. On the Primero Server:

     $ mkdir migration_data
 $ docker exec -t primero-postgres-1 pg_dumpall -c -U primero > migration_data/primero_migration_data.sql

  4. Delete the PostgreSQL data volume. On the Primero Server:

     $ docker container stop primero-postgres-1
 $ docker container rm primero-postgres-1
 $ docker volume rm primero_database

  5. Deploy Primero v2.5+ with the new database version using Ansible. The inventory file will need to specify the major PostgreSQL version in the variable primero_postgres_version. This will deploy the new PostgreSQL image and recreate the data volume. On the Ansible Server:

     $ cd </path/to/primero>/ansible
 $ ./bin/activate
 $ ansible-playbook application-primero.yml --tags start -l <primero-instance> -i </path/to/inventory.yml>

  6. Restore the migration dump file to the new database. On the Primero Server:

     $ docker cp $(pwd)/migration_data/primero_migration_data.sql primero-postgres-1:/
 $ docker exec -t primero-postgres-1 psql -f /primero_migration_data.sql -U primero

  7. (Necessary if upgrading from PostgreSQL 10.) If the password encryption strategy is changing (MD5 to SCRAM), the primero role password will need to be reset. On the Primero Server:

     $ docker exec -t primero-postgres-1 bash -c 'echo "ALTER ROLE primero PASSWORD '"'"'${POSTGRES_PASSWORD}'"'"';" | psql -U primero'

  8. Restart again, from the *Ansible Server:

     $ ansible-playbook application-primero.yml --tags start -l <primero-instance> -i </path/to/inventory.yml>

  9. After verifying that the upgrade was successful, remember to delete both backup/primero-postgres-volume-backup.tar.gz and primero_migration_data.sql.