PostgreSQL multi-master replication between AWS RDS and GCP Cloud SQL

Question

I found lots of articles on how to migrate data from RDS to Cloud SQL, however all of them do just master-slave replication and then the switch. However I couldn't find anything about master-master replication between two clouds.

We need that to switch our backend to Cloud SQL after sync is done, but we want to keep a way of easy switch back without losing any data.

Let's say we switch to Cloud SQL and then we decide that it doesn't work as expected due to some currently unknown issues and we want to switch back. So we'd have to migrate all data with new data back to RDS and then switch back to RDS. This will take time and we'll have downtime of an hour at least (set up replication CloudSQL -> RDS, switch; Or export data from CloudSQL and import into RDS, switch). So we want to avoid this downtime in case if we decide to switch back.

Is there any way to set up master-master replication between two clouds?

Answer 1

Disclaimer: Please test it on some test instances before applying this in production. I didn't go with migration yet, so this should be tested yet.

So after some investigation and research I was able to set up master-master replication between GCP and AWS. Here's the howto:

1. Create new parameter group and enable it for the instance (instance restart required) on AWS
2. Enable logical replication by setting following settings

setting	value
rds.logical_replication	1
track_commit_timestamp	1
pglogical.conflict_resolution	last_update_wins
shared_preload_libraries	add pglogical

3. Add flags on GCP

It’s recommended to set track_commit_timestamp to ON before setting other flags. Also all other flags should be set at once, otherwise it might fail.

setting	value
cloudsql.enable_pglogical	ON
cloudsql.logical_decoding	ON
max_replication_slots	10
max_worker_processesset	8
max_wal_senders	10
track_commit_timestamp	ON
pglogical.conflict_resolution	add last_update_wins

4. Create pguser on both ends:

AWS

CREATE USER pguser NOLOGIN;
\password pguser
ALTER USER pguser LOGIN;
GRANT rds_superuser TO pguser;

GCP

CREATE USER pguser NOLOGIN;
\password pguser
ALTER USER pguser LOGIN;
GRANT cloudsqlsuperuser TO pguser;

5. Grant all privileges on all tables to pguser on both GCP and AWS

GRANT ALL ON ALL TABLES IN SCHEMA public TO pguser;
GRANT ALL ON SCHEMA pglogical TO pguser;

6. Enable extension on both ends

CREATE EXTENSION pglogical;

7. Create nodes

AWS

SELECT pglogical.create_node(
    node_name := 'aws-node1',
    dsn := 'host=x.x.x.x port=5432 sslmode=require dbname=postgres user=pguser password=secur3p455word');

GCP

SELECT pglogical.create_node(
    node_name := 'gcp-node1',
    dsn := 'host=y.y.y.y port=5432 sslmode=require dbname=postgres user=pguser password=secur3p455word');

8. Add all tables to replication on AWS

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

9. Create subscription on GCP

SELECT pglogical.create_subscription(
    subscription_name := 'aws_sub',
    provider_dsn := 'host=x.x.x.x port=5432 sslmode=require dbname=postgres user=pguser password=secur3p455word',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}' );

10. Check subscription status

SELECT * FROM pglogical.show_subscription_status('aws_sub');

11. Enable replication the other way. Add replication set on GCP

SELECT pglogical.replication_set_add_table('default', 'order_details', true);

12. Create subscription on AWS

SELECT pglogical.create_subscription(
    subscription_name := 'aws_sub',
    provider_dsn := 'host=y.y.y.y port=5432 sslmode=require dbname=postgres user=pguser password=7wjFnMMT8GL5cJLO',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}' );

Notes

• After setting up the replication, data writes to both tables works and the data is appearing in each database regardless where it's written. If the row was inserted with same id error is shown and that doesn’t mess the data.

• Make sure you use primary keys and ids to avoid messing up the data

• Turning on the replication in 11th step didn’t copy data from GCP to AWS which was created between enabled replication AWS → GCP and 11th step (AWS ← GCP). So you must enable AWS → GCP replication first, then enable GCP → AWS replication and only after that switch backend to new database, so that data would start flowing back to AWS.

Basically I was following these two official guides to set this up:

• https://cloud.google.com/blog/products/databases/setting-bi-directional-replication-for-cloud-sql-for-postgresql
• https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/

Answer 2

how about position of 2 step 5 and 6 ? If you don't run step 6 first, will be occur error 'schema "pglogical" does not exist' in command GRANT ALL ON SCHEMA pglogical TO pguser;

5.Grant all privileges on all tables to pguser on both GCP and AWS
GRANT ALL ON ALL TABLES IN SCHEMA public TO pguser;
GRANT ALL ON SCHEMA pglogical TO pguser;

6.Enable extension on both ends
CREATE EXTENSION pglogical;
------