Upgrade PostgreSQL Database (Major Version 12 Retirement)

[Jin-Sun-tts]

PostgreSQL version 12 is scheduled for retirement in February 2025. We need to upgrade the database to a supported version before this deadline.

database services that need to be upgraded are:

Org	Space	Instance name	Engine	Engine Version	Maintenance Window (UTC)
gsa-datagov	development	catalog-db	postgres	15.7	tue:08:47-tue:09:17
gsa-datagov	staging	catalog-db	postgres	12.17	wed:10:12-wed:10:42
gsa-datagov	prod	catalog-db	postgres	12.17	sun:12:05-sun:12:35
gsa-datagov	prod	inventory-db	postgres	12.17	tue:07:06-tue:07:36
gsa-datagov	prod	inventory-datastore	postgres	12.17	fri:13:05-fri:13:35
gsa-datagov	staging	inventory-db	postgres	15.7	sat:10:35-sat:11:05

[FuhuXia]

The current postgresql version is 15.7 if you run cf create-service aws-rds ....

catalog-next is running on 15.7. So CKAN has no problem running on the latest RDS postgresql offered by cloud.gov.

[FuhuXia]

This should be a simple db dump of the old database (version 12.17) and then restore it to a new database (version 15.7).

We have developed some automated scripts for db backup and restore. https://github.com/GSA/cf-backup-manager/tree/main/docs/backup_restore. Also there should be detailed steps in other tickets or slack discussion since we have done it multiple times.

To verify things are working as expected, we can try the steps on catalog development and inventory development, after verification, then move on to staging and prod.

Steps:

1. run cf command to create a catalog-db-new on development space
   cf create-service aws-rds someplan catalog-db-new.
   make sure someplan is the same as existing catalog-db
2. run catalog-db-backup.sh to backup the db. This will create a db dump file of catalog-db to S3.
3. run catalog-db-restore.sh to restore db dump file to catalog-db-new
4. bind the service catalog-db-new to catalog apps (web, admin, gather, fetch), run cf rename-service to rename existing catalog-db to catalog-db-old, and rename catalog-db-new to catalog-db.
5. restart catalog apps and verify things are working as expected. verify ckan apps are communicating with psql version 15.7
6. unbind-service catalog-db-old from catalog apps, then delete-service catalog-db-old.
7. repeat for inventory development.
8. repeat for catalog and inventory on staging and prod.

[rshewitt]

backup-manager is using pg 15.7 client in development

Scaling app backup-manager in org gsa-datagov / space development as [email protected]...

...

reidhewitt@Reids-MacBook-Pro cf-backup-manager % cf ssh backup-manager 
6bedc97a-085f-444d-5ee0-008d:~$ pg_dump --version
pg_dump (PostgreSQL) 15.7

[rshewitt]

catalog development upgraded to v15

reidhewitt@Reids-MacBook-Pro cf-backup-manager % cf connect-to-service backup-manager catalog-db

...
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.7 [omitted]
(1 row)

[rshewitt]

inventory staging upgraded to v15

reidhewitt@Reids-MacBook-Pro cf-backup-manager % cf connect-to-service inventory inventory-db

                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.7 [omitted]
(1 row)

[Bagesary]

Reid is facing few issues and debugging is in progress