Postgis v17

[DerekRoberts]

This repo is using Postgis 13. Please update to 16 or 17.

Acceptance Criteria

• Update image used for Postgis version at Dockerfile and docker-compose.

• Verify overall database is working without error, for example by submitting spatial file.

• shut down the app, do final DB backup

• check extension and compatibility

• check if in place upgrade works

• do an analyze scan --> follow instructions for upgrading postgres versions

[ianliuwk1019]

More information for postgis upgrade:

• https://postgis.net/workshops/postgis-intro/upgrades.html
• AWS guide for How to perform a major version upgrade for RDS for PostgreSQL (including postgis extension): https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.html
• Some AI gathered steps (not verified if valid)

• We will need (at this time) manual steps, because:
  
  

[ianliuwk1019]

Upgrade execution plan:
2025-06-19, 5:00pm

Upgrade:
	- Take some note on FOM prod public/admin, some FOM details.
	- Review, get approval and merge pr: #803 - 742 Postgre database major version upgrade (prep pull request).
	- Wait for TEST deployment (merge) completed.
	- Verify on OpenShift TEST, new PVC is present (-dbupgrade-v17).
	- Verify FOM on Github, the new action (Database Upgrade - dispatch) can be triggered.
	- Trigger the new action (Database Upgrade - dispatch), input target - "test".
	  This is to trigger TEST environment upgrade.
	- Wait for upgrade action to finish and observe TEST deployment.
	- When successful, verify on OpenShift:
	  - old db is stopped
	  - new db is up (labled -v17)
	  - api is up and point to new db (-v17). Check the DB_HOST
	  - 4 cronJobs are all pointing to new db.
	  - open public and admin side, search some FOM and get the detail to confirm 
	  
	- Deploy current main to Prod (this is prep for prod)
	- Wait for PROD deployment (dispatch)
	- Verify on OpenShift PROD, new PVC is present (-dbupgrade-v17).
	- Trigger the new action (Database Upgrade - dispatch), input target - "prod".
		 This is to trigger PROD environment upgrade.
	- Wait for upgrade action to finish and observe TEST deployment.
	- When successful, verify on OpenShift:
	  - old db is stopped
	  - new db is up (labled -v17)
	  - api is up and point to new db (-v17). Check the DB_HOST
	  - 4 cronJobs are all pointing to new db.
	  - open public and admin side, search some FOM and get the detail to 
	    confirm (with previous notes) app is running fine and data is correct.
	  
Post upgrade:
	- Create branch for post upgrade from main branch: 
		* can reference pr #806.
		* or reuse branch from #806, sync with main branch.
	- Prepare necessary change on "name" and "label" (for -v17 suffix)
	    * refer to pr #806
	- Create the post upgrade PR against main branch
	- Have Basil to review the PR once again and get approval.
	- Merge to main to update TEST environment.
	- Wait for TEST deployment finish. Verify pods running normal and 
	  pod name/labels are correct and pointing to correct db.
	- After everything is verified, trigger deployment to prod.
	- Wait for Prod deployment finish. Verify pods running normal and 
	  pod name/labels are correct and pointing to correct db.
	  
Clean up:
	- Can be done few days later (old db is stopped)
	- Delete old db pod
	- Delete olr db PVC

[ianliuwk1019]

First attempt to trigger db upgrade to TEST failed at the script is missing OpenShift deployment environment specific input to run at (OC_NAMESPACE). This did not fail at dev due to Github default to dev environment if not specifically specify.
Reschedule db upgrade again to Tuesday 2025-06-24