Discovery Post-Postgres Upgrade: Possible data loss during database upgrade/ maintenance

[LindseySaari]

User Story

As the managers of the Postgres tool, We want to understand the risks around upgrades or maintenance to the tool and have a strategy documented for how said upgrades or maintenance are to be performed, So that we can avoid any data loss issues during future database upgrades and maintenance windows.

Description

The API application, which connects to an RDS database, can be facing data loss issues during database upgrades/ maintenance. When the database upgrade process is initiated, the API attempts to make calls to the database, but these calls are failing, resulting in data loss. We now have BlueGreen, and we want to do some discovery to see if this is something we want to test before the next Postgres Upgrade (date TBC).

Tasks

Confirm if there is dataloss:

• [x] Schedule a database upgrade in Dev.
• [x] Observe that the API application attempts to make calls to the database during the upgrade.
• [x] Confirm that these calls are failing, leading to data loss

Additional Information:

• Application Language: Ruby
• Currently, the application does not use a message queuing system for database operations.
• We are considering integrating a message queuing service (e.g., RabbitMQ, SQS, Kafka) to prevent data loss during database upgrades.

Proposed Actions:

• [ ] Investigate and implement a suitable message queuing system (e.g., RabbitMQ, SQS, Kafka) to decouple the API from direct database calls.
• [ ] Configure the message queuing system to ensure data durability and reliability.
• [ ] Modify the API to use the message queuing system for database interactions.
• [ ] Suggested Message Queuing Service:
• [ ] We need to evaluate and choose the best message queuing service for our Ruby-based application. Potential options include:
• RabbitMQ: A robust and widely-used message broker.
• Amazon SQS (Simple Queue Service): Managed message queuing service provided by AWS.
• Apache Kafka: A distributed event streaming platform.
• [ ] We should assess the specific requirements and constraints of our application to determine the most suitable service for our needs.

Success Metrics

• The team will be informed on best practices for upgrading Postgres and avoid dataloss

Acceptance Criteria

• [ ] Confirm that these calls from the API application to the database during the upgrade are failing, leading to data loss
• [ ] Document steps taken and final outputs
• [ ] Attach documentation to this ticket

• [ ] URL for documentation is here (Link is to come)

Validation

Assignee to add steps to this section. List the actions that need to be taken to confirm this issue is complete. Include any necessary links or context. State the expected outcome.

[LindseySaari]

Some things to explore:

1. Run a test on a replica and see what happens during the blue/green deployment
2. For Sidekiq: explore additional storage layers. Should we back Sidekiq by postgres?
3. Other options, etc

[Kshitiz-devops]

Limitation of Postgres blue green upgrade:

PostgresSQL logical replication limitations have the following implications on blue/green deployments: Data definition language (DDL) commands are not replicated to the green environment. NEXTVAL operations on sequence objects are not incremented in the green environment. However, during switchover, Amazon RDS increments sequence values in the green environment to match those in the blue environment. Large objects (pg_largeobject) are not replicated to the green environment. Materialized views on the green environment must be independently refreshed. All tables in the blue environment must have primary keys.

More Info: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html#blue-green-deployments-limitations-postgres

[jennb33]

Per 6/26 Standup: @Kshitiz-devops has been doing testing. Wondered if we can get a continuous connection to the database, so that we can set up notifications when data We could continually hit the endpoint; Kshitiz will thread a script for next steps.

[jennb33]

6/27 stand-up update: @Kshitiz-devops started testing on Wednesday and is starting to compare the update of the data sets in one of the tables.

[jennb33]

7/1: Blue/Green deploys are not seeing any data loss. Ticket also includes other steps for QA systems.