Upgrade vets-api Postgres Database from v11 to v15

[ericboehs]

Product Outline

High Level User Story/ies

As a system administrator, I need to upgrade the vets-api Postgres database from version 11 to version 15 so I can ensure we are using a version that is supported and can take advantage of the latest features and performance enhancements.

Hypothesis or Bet

If we upgrade the vets-api Postgres database to version 15, then we expect to maintain or improve application performance, leverage new database features, and continue to receive support from the community.

OKR

Objective: Improve the system's reliability and performance. Key Result: Successfully upgrade the vets-api Postgres database from version 11 to version 15 with no critical issues or performance degradation.

Definition of done

• [ ] All the associated user stories are completed.
• [ ] All the acceptance criteria for each story are met.
• [ ] The vets-api Postgres database is successfully upgraded from version 11 to version 15 in the production environment.
• [ ] No major issues or performance degradation are observed post-upgrade.
• [ ] All stakeholders are informed about the successful upgrade.
• [ ] Monitoring is set up successfully and is operational.

Stories:

1. Story: Run Unit Tests

• Description: Run the existing RSpec tests in the testing environment with the upgraded Postgres version to ensure all tests pass.
• Acceptance Criteria:
  • [ ] All existing RSpec tests pass successfully on the upgraded Postgres version.
  • [ ] Any failure identified should be addressed and fixed.

2. Story: Decide on the Upgrade Method

• Description: Evaluate the pros and cons of using an in-place upgrade with pg_upgrade versus using logical replication or pg_dump and pg_restore to migrate data. Make a decision on the preferred method.
• Acceptance Criteria:
  • [ ] A detailed comparison report on the pros and cons of available upgrade methods.
  • [ ] A final decision on the upgrade method based on the report.

3. Story: Prepare Testing Environment

• Description: Prepare the lower environments for testing the upgrade. This includes swapping the current Postgres image to the equivalent official Postgis image and upgrading to the appropriate Postgis images for each version.
• Acceptance Criteria:
  • [ ] Lower environments are successfully prepared and ready for testing.
  • [ ] The correct Postgis images for each version are installed and running.

4. Story: Perform Performance Testing

• Description: Conduct performance tests to determine if the upgraded Postgres version affects the performance of the application.
• Acceptance Criteria:
  • [ ] Performance test results are satisfactory and meet the expected performance levels.
  • [ ] Any performance degradation identified should be addressed and rectified.

5. Story: Conduct Data Migration Testing

• Description: Perform data integrity checks and compare row counts in the upgraded Postgres version to ensure data was migrated correctly.
• Acceptance Criteria:
  • [ ] Data integrity checks pass successfully.
  • [ ] Row counts between the original and upgraded Postgres versions match.

6. Story: Conduct Failover and Recovery Testing

• Description: Test the backup and recovery processes with the upgraded Postgres version to ensure they work as expected.
• Acceptance Criteria:
  • [ ] Backup and recovery processes are successful and data integrity is maintained.
  • [ ] Failover mechanisms work correctly with the upgraded Postgres version.

7. Story: Perform User Acceptance Testing

• Description: Arrange for users to test the system in the lower environments and provide feedback on their experience.
• Acceptance Criteria:
  • [ ] User feedback is positive and no major usability issues are reported.
  • [ ] Any issues identified by users are addressed and fixed.

8. Story: Implement Monitoring

• Description: Set up regular reviews of graphs in DataDog to check for any anomalies or unexpected changes after the upgrade.
• Acceptance Criteria:
  • [ ] Monitoring is set up successfully and operational.
  • [ ] Any anomalies or unexpected changes identified from the monitoring results are investigated and resolved.

9. Story: Plan Production Deployment

• Description: Plan the maintenance window for the upgrade, communicate the plan to the users, prepare a recent backup of the production database, and have a detailed rollback procedure ready.
• Acceptance Criteria:
  • [ ] Maintenance window is planned and communicated to all stakeholders.
  • [ ] A recent backup of the production database is taken and a rollback procedure is established.

10. Story: Deploy to Production

• Description: Execute the upgrade process in the production environment, following the previously established plan.
  • Acceptance Criteria:
  • [ ] The upgrade is successfully executed in the production environment.
  • [ ] The application is functioning correctly post-upgrade and there are no reported issues.

11. Story: Post-Upgrade Monitoring

• Description: Monitor the application and database performance closely after the upgrade. Be ready to roll back to the previous version quickly if any critical issues are noticed.
  • Acceptance Criteria:
  • [ ] Post-upgrade monitoring has been established and is successfully tracking application and database performance.
  • [ ] Monitoring data indicates that the system's performance is stable or improved compared to pre-upgrade performance.
  • [ ] All identified anomalies or unexpected performance drops have been addressed and resolved.
  • [ ] The rollback procedure is tested, up-to-date, and ready to be deployed in case of any critical issue.
  • [ ] The system is continuously monitored for a defined period (e.g., a week or a month), and no critical issues triggering a rollback are discovered.

---

Research and Notes

See this Confluence doc for additional information and ideas - https://vfs.atlassian.net/wiki/spaces/TT4/pages/2636054965/vets-api+Postgres+Upgrade+From+v11+To+v15

Old Description

We're on Postgres 11.8 for Vets API Prod DB. 11.8 is no longer supported and we should upgrade.

Amazon also plans to stop supporting all 11.x versions late this year.

• [ ] Upgrade to at least 11.12
• [ ] Upgrade to 12.x or newer by November

Ideally, we upgrade to 14.x as 15.1 is out in the community and Amazon should be adding it shortly (in the next few weeks is my guess).

[ianhundere]

we have this in our backlog, but i'll close that one in favor of this time and include the appropriate labels.

also, this may need to be a collective/joint effort between devops and backend teams. going to add the backend label as well to track.

further info on EOL dates for postgres/rds versions:

• https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html