Test Google CloudSQL & AWS RDS PostgreSQL Migration Process with v1

[t83714]

Test Google CloudSQL & AWS RDS PostgreSQL Migration Process with v1

• [x] Deploy Local Magda v0.0.60 with CloudSQL PostgreSQL 9.6
• [x] Migrate CloudSQL PostgreSQL 9.6 to CloudSQL PostgreSQL 13 using Google's DMS. More Info.
• [x] Upgrade v0.0.60 to v1.0.0
• [x] Deploy Magda v0.0.60 with RDS PostgreSQL 9.6
• [x] Upgrade RDS PostgreSQL 9.6 to RDS PostgreSQL 13 in AWS console
• [x] Upgrade Local Magda v0.0.60 to v1.0.0

[t83714]

~We should upgrade cloudSQL proxy default version to 1.26.0~. ~Test shows it's more stable than 1.11 when connect to Cloud SQL remotely (outside google cloud) with unstable network~ above was not true for some case

[t83714]

Steps for CloudSQL (using using Google's DMS):

Please note: Google DMS won't be able to handle everything --- see FAQ

• Stop all connectors / minions
• Prepare source instance:
  • Set cloudsql.logical_decoding and cloudsql.enable_pglogical flags to on and restart
  • Run SQL statement to check if any tables has no primary key.
  • run ALTER TABLE webhookevents ADD PRIMARY KEY (eventtypeid, webhookid); to add primary key
  • Add extension: CREATE EXTENSION IF NOT EXISTS pglogical; (this statement needs to be run on every databases)
  • Add replication role ALTER USER postgres with REPLICATION;
  • Create DMS job (with same default postgres user password for new DB) and Start the DMS migration. Once the migration enter CDC stage then:
  • Set registry api full to 0 to stop write operation to existing db (registry-read will still serve all read requests)
  • connect to new db instance:
    • you can connect to the db by:
    • gcloud config set project [project id]
    • gcloud sql connect [instance name] --user=postgres --quiet
    • check if data is alright
    • create db user client (with same password) and assign privileges in all Magda DBs by:
    • create user create user client with encrypted password '[password]';

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO client;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO client;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO client;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO client;

After grant permission to client user, you can login db as client user to verify you can see all tables.

• Wait till replication delay reach zero, then:

  • Promote the migration
  • make sure events table SEQUENCE value is correct in postgres DB (DMS won't migrate this):
  • SELECT last_value FROM events_eventid_seq;
  • Compare with SELECT eventid from events order by eventid DESC limit 1;
  • If not match, set current val SELECT setval('events_eventid_seq', xxxx);

• Then:

  • Upgrade Magda to the v1.0.0
  • Delete migration job
  • Delete replication master (auto-created by migration job)
  • Delete old database

[t83714]

AWS RDS Upgrade can be simply done by modify RDS instance and choose a new version. More info: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion

[t83714]

tested v1.0.0-alpha.5 in dev cluster with both cloudSQL and RDS ---- all worked well

[t83714]

@steve9164 this is the GCP postgreSQL upgrade instruction we talked the other day