PostgreSQL Upgrade

Dockerized PostgreSQL

For deployments that use dockerized PostgreSQL, OLMIS provides a custom image - https://github.com/OpenLMIS/postgres

Upgrading Image

OLMIS Implementers will most often skip this step, as most likely the correct image has already been prepared by the Core team.

• Modify the base image to the Debezium version of the target PostgreSQL

  • Example: https://github.com/OpenLMIS/postgres/commit/4b166c0ba6fb51ee2d50edcc1c3559282a486589

• Push the new image, take note of the versioning convention - https://hub.docker.com/r/openlmis/postgres/tags

  • The DockerHub credentials can be found at https://openlmis.atlassian.net/wiki/spaces/OP/pages/41517067

• Review of CI/CD tools as they might use Docker version incompatible with the new openlmis postgres image

Upgrading instance

Upgrade instructions for each version can be found on the PostgreSQL page - https://www.postgresql.org/docs/12/upgrading.html

To properly upgrade the database, additional steps might be required, depending on the deployed instance:

• PostGIS extension upgrade. See the compatibility matrixhttps://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS and upgrade instructions here https://postgis.net/workshops/postgis-intro/upgrades.html

• Review of custom Jasper reports (might contain custom SQL, which is not compatible)

AWS PostgreSQL RDS

For deployments that use the RDS service to host the database, we suggest following AWS best practices.

For OLMIS Core instances, the procedure described below has been used with success.

Core RDS Upgrade

The strategy is based on the following premises:

• We must retain the data

• We can allow hours of downtime

• We have full control over the AWS account

• The OLMIS services are compatible with the new version of PostgreSQL

Procedure

This involves ~3 hours of downtime.

1. Review AWS Best Practices and take note of extension upgrades.

2. Stop OLMIS App server.

3. Make an RDS Snapshot of the current DB server.

4. Restore the RDS Snapshot into a new RDS - Upgrade Instance.

   1. Regardless of the target instance type, use a server with at least 16GB of RAM - this affects how fast the upgrade will go.

5. Perform RDS Major Upgrade

   1. The progress may appear to be stuck. Verify the memory usage - Freeable memory and SwapUsage - if the former rises, it means there is not enough RAM, restart the process on a new instance.

6. Make a snapshot of the Upgrade Instance.

7. Restore the Upgrade Instance Snapshot into the target type of RDS.

8. Reconfigure the OLMIS App to connect to the new RDS.

9. Test.

10. Delete old RDS, and delete the Upgrade Instance.