Auth Database Migration Scripts

[bolyachevets]

• Job to pull data from OCP into GCP: https://github.com/bolyachevets/db-migration-tool

Dev

• Create CloudSQL instance
• Create GCP Cloud Job
• Test Data Migration

Test

• Create CloudSQL instance
• Create GCP Cloud Job
• Test Data Migration

Prod

• Create CloudSQL instance
• Create GCP Cloud Job
• Test Data Migration
• Prod to Postgres15 upgrade strategy

Sandbox

• Create CloudSQL instance
• Test Data Migration

[bolyachevets]

For upgrade to Postgres15 in prod:

1. Upgrade image for backup containers to postgres15
2. Run custom pg_dump commands (as backup container is not flexible enough):

pg_dump -Fp -h postgresql-prod -p 5432 -U postgres -d "auth-db" --no-owner --no-acl -n public > backup.sql
sed '/^CREATE SCHEMA public;/d' backup.sql > filtered_backup.sql
sed '/^ALTER SCHEMA public OWNER TO/d' filtered_backup.sql > final_backup.sql
sed -i '1i CREATE EXTENSION IF NOT EXISTS "uuid-ossp";' final_backup.sql

3. Trigger Cloud Job from BCOnline Prod project in gcp, updating relevant parameters (e.g. set DUMP_FILE to final_backup.sql)

[bolyachevets]

Can manually move data between prod and sandbox:

gcloud sql export sql auth-db-prod gs://auth-db-dump-prod/dump.sql.gz --database=auth-db
gcloud sql databases create auth-db --instance=auth-db-tools
gcloud --quiet sql import sql auth-db-tools gs://auth-db-dump-prod/dump.sql.gz --database=auth-db --user=postgres

[thorwolpert]

possibly, the DBAs have always asked us to obfuscate/mask data moved to the lower environments.
Auth might be a little different as we want to use the same IDIM between Prod/Sandbox, like our IDir users, our BCSC users don't have test accounts.

[thorwolpert]

or maybe we entertain a registry-id for sandbox work.