Explore a custom RDS monitoring solution so we can create a retention and storage policy

[dianagriffin]

As a VRO Platform team member, I want to explore ways to monitor RDS through a custom solution after discovering that I do not have access to RDS metrics from LHDI.

Acceptance Criteria:

the essential metrics for monitoring are CPU Utilization, Memory, and Storage. Outline the best approach and plan for implementing the custom monitoring solution. Include details on tools, technologies, and methods to be used. Provide an outline for an implementation path. Create tickets for subsequent work as needed (if possible) Amy mentioned whoever picks up this ticket should work with Cory on any ideas he may have. Collaborate with Megan to ensure this work is prioritized and integrated into the team's roadmap if possible

[dianagriffin]

Notes from team discussion:

• What do we already know about data storage and retention needs?
• What are the limits of budget/allocation for our use of LHDI RDS?
• What's the daily anticipated volume of contention and va.gov claim activity data?
• What's the cost/effort of configuring RDS based on what we know now and adjusting that configuration as we learn more about future needs?

[msnwatson]

The engineer working on this should at least look at native Postgres DB mechanisms for achieving retention such as partitioning by date with an expiration period or submitting jobs through AWS client libraries for performing operations on DB tables.

DB schema alterations should be carefully considered

[meganhicks]

might be good to pair with Mason, Cheng or Erik

[nelsestu]

pg_cron is a custom extension for PostgreSQL that allows you to schedule jobs directly within the database. While pg_cron would provide an ideal implementation path, we need someone to validate our RDS version and validate that we can pass the appropriate ACK config in through LHDI. This is because RDS does not support installing custom extensions directly.

• RDS Version validation is needed because the pg_cron extension is supported on RDS for PostgreSQL engine versions 12.5 and higher. To verify postgres engine version you need to ssh to a dev-tools pod in production, connect to the database and run the query: SELECT version();
• AWS documents the pg_cron feature here
  • enable the extension which is disabled by default
  • set the permissions appropriately so that the pg_cron and pg_jobs tables are made available for the VRO team to query
• AWS documents the AWS Controller for Kubernetes (ACK)
• Between flyway and helm, identify whether these RDS changes can be automated, but it isn't guaranteed
• Assuming we can automate, how long might that aspect take? that effort may not be zero time

Some very useful examples of using pg_cron include the following: Collect Native Postgresql Metrics pgwatch2 - a PostgreSQL metrics monitor implementation postgres_exporter - PostgreSQL metric exporter for Prometheus

From the examples listed above, confirm that we can capture all the metrics listed in the AC and feel free to specify your top recommendation.

[PaulKBaumann]

This is blocked because we need superuser access to the database in order to set up the pg_cron plugin.

https://github.com/department-of-veterans-affairs/lighthouse-di-tenant-support/issues/43

[PaulKBaumann]

Unblocked enough to continue making progress on this research. We're still missing a way to log into the prod RDS instance