Migrate Postgres RDS from AWS to Philly Cluster

[TineoC]

Description

The goal of this task is to migrate the final AWS-hosted component—Postgres—to the Philly Cluster. This involves deploying a new pgcluster with pgvector features enabled and performing a data migration from the current AWS RDS instance.

Scope of Work

• Infrastructure Setup: Deploy pgcluster in both cfp-sandbox and cfp-live environments.
• Feature Enablement: Enable pgvector features on the new clusters.
• Data Migration: Migrate existing data from AWS RDS to Philly Cluster.
• Traffic Shifting: Implement a Blue-Green deployment to route a percentage of requests to the new pgcluster to avoid disruption.
• Validation: Monitor performance and parity before 100% cutover.

Strategy: Blue-Green Deployment

To avoid disruption in the dev and live environments, we will shift a percentage of requests from the current app to RDS and another to the new pgcluster.

Related Pull Requests

• PR # (To be linked): Infrastructure deployment for pgcluster
• PR # (To be linked): Configuration changes for pgvector and traffic splitting
• PR # (To be linked): Final cutover and RDS cleanup

Staheholders

• @taichan03
• @sahilds1
• @themightychris

[themightychris]

we've been wanting to deploy CNPG to the clusters and start time that to host databases for projects, instead of the usual creating a statefulset within each project. Anyone game to try helping stand that up as part of this?

[TineoC]

Yea I would first learn how to deploy PG vector on k8s first and then would see how this could be solved for the sandbox environment.

[themightychris]

The process will be very different between using CNPG and not using CNPG

[sahilds1]

@TineoC Thanks for the helpful information in this ticket! Here are my questions right now

• What would be the recommended local dev setup after this migration? Would we be able to connect to the sandbox?
• Would we be able to read from the new pgcluster for analytical queries?
• Shall we create a Slack channel for this migration so devs can watch for updates?

cc @themightychris @taichan03

[themightychris]

What would be the recommended local dev setup after this migration? Would we be able to connect to the sandbox?

ideally in local dev you have a script that can initialize a local DB. Long term using a shared DB for local dev becomes challenging without heavy coordination.

If a seed script or data generator isn't practical because the system relies on a bunch of real data being loaded, another good approach is to maintain a dumb of the sandbox DB in a cloud bucket and have a script local devs can run to bootstrap that downloads and loads that