Upgrade postgres version on all RDS instances

[1t5j0y]

Postgres 12 is EOL in Nov 2024 and AWS RDS stops support on Feb 28, 2025 after which there will be additional extended support charges for running this version of postgres.

Extended support charges - 0.114/vCPU-hour for years 1 and 2; 0.228/vCPU-hour for year 3
This works out to 10 (current RDS vCPUs) * 0.114 * 730 (hours in month) = Additional $830 + tax / month

AC:

1. Determine best version to migrate to - preferably latest (17)
2. Upgrade RDS instances (Avni and RWB)
3. Upgrade Staging and Prerelease env DBs as well

Output

https://github.com/avniproject/data-fixes/blob/main/all/upgradeAvniPostgresServersTo16.md

Not included in this scope

• Upgradation of Reporting DB RDS, as there is another card, which would be better to use based on timeline discussions and avoiding repetition of testing and deployment effort.

[himeshr]

Relevant documents

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html
• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.html
• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.html
• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.ExtensionUpgrades.html
• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html

[himeshr]

PG Extensions used by Avni:

openchs=> SELECT * FROM pg_extension;
   oid    |  extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
----------+-----------+----------+--------------+----------------+------------+-----------+--------------
    16403 | plpgsql   |       10 |           11 | f              | 1.0        |           |
    16408 | hstore    |       10 |        16402 | t              | 1.4        |           |
    16409 | ltree     |       10 |        16402 | t              | 1.1        |           |
    16410 | tablefunc |       10 |        16402 | t              | 1.0        |           |
    16411 | uuid-ossp |       10 |        16402 | t              | 1.1        |           |
 94143243 | dblink    |       10 |        16402 | t              | 1.2        |           |
(6 rows)

[himeshr]

Targeting Major version 16.3

[himeshr]

• Use 16.3 parameter-group "openchs-postgres-modified-16" for all RDS, except for read-replica
• • rds.force_ssl => 0
• Use modified 16.3 parameter-group "openchs-postgres-read-modified-16" for prod-read-replica with following additional properties
• • hot_standby_feedback => 1
• • log_min_duration_statement => 20000
• • rds.force_ssl => 0

[himeshr]

Suggestion received regarding reporting testing:

Create duplicate reporting server instances and modify /etc/hostnames file to point to prod-dup-16-version rds and get sanity testing done.

[himeshr]

Local upgraded postgres 16 server tests for following repos completed successfully:

• avni-server
• avni-etl //Except for 1 expected error NoIAMAuthServiceTest > getUserForTokenShouldFailIfImproperlyConfigured()
• integration-service

[himeshr]

https://drive.google.com/file/d/1cUwnxv5to-mFdmpK3DJQxhS1Dt0fkDCZ/view?usp=sharing

[himeshr]

Notes:

• Assigned public ip to prerelease green db to be able to do traffic redirects
• Added /etc/hosts redirects in superset(container), jasper and metabase(container) green environments to redirect all prod db traffic to prerelease db green
• Increased prerelease ETL job gap to 600 minutes instead of prev 60 minutes
• Configured prerelease ETL to point to prereleasedb-green manually (Avoiding changes in infra, to avoid avni-server and etl permanent issues)
• cleared org schema etl for all orgs and retriggered ETL
• Created AWS SG for prerelease db access sg-046cd3bcef0ac4e19 , should be removed later
  Testing urls:
  Jasper - Yenepoya => /yenepoya__ETL_/Reports/Linelists/Blood_glucose_levels_in_Diabetic_patients_Line_list
  Superset - APFOdisha => [Phase 2][APFODISHA] Full vs Complete Vaccination
  Metabase - Ashwini

[himeshr]

Knowledge articles:

https://www.baeldung.com/linux/local-domain-redirection#modifyingetchosts
https://www.jasani.org/posts/2014/docker-now-supports-adding-host-mappings/

[himeshr]

Reporting Green servers pointing to Postgres 16 DB:

https://test-reporting-superset.avniproject.org/superset/welcome/
https://reporting-green.avniproject.org/auth/login?redirect=%2F
https://reporting-jasper-green.avniproject.org/jasperserver/login.html

[dinesh2096]

Done testing for metabase and superset

[himeshr]

*) Received feedback from Dinesh that, sanity testing completed successfully for reporting servers using the Postgres 16 DB, for

• Metabase
• Superset
• Jasper

*) Also, ETL tests across multiple organisations ran successfully on Postgres 16 DB, without any new issues reported.

[dinesh2096]

Done Sanity testing for Jasper

[himeshr]

Upgraded Prerelease DB to version 16 on 05 Dec 2024, 6pm IST