Bidirectional Replication on PostgreSQL(GCP CloudSQL) with SSL

#Bidirectional Replication

Setup two database Instances

### Node 1

gcloud sql instances create node1 \
    --database-version=POSTGRES_13 \
    --cpu=2 \
    --memory=4GiB \
    --zone=europe-west2-a \
    --root-password=pwd \
    --no-deletion-protection \
    --database-flags=cloudsql.enable_pglogical=on,cloudsql.logical_decoding=on,max_replication_slots=10,max_worker_processes=8,max_wal_senders=10,track_commit_timestamp=on,pglogical.conflict_resolution=last_update_wins

Node 2

gcloud sql instances create node2 \
    --database-version=POSTGRES_13 \
    --cpu=2 \
    --memory=4GiB \
    --zone=europe-west2-a \
    --root-password=pwd \
    --no-deletion-protection \
    --database-flags=cloudsql.enable_pglogical=on,cloudsql.logical_decoding=on,max_replication_slots=10,max_worker_processes=8,max_wal_senders=10,track_commit_timestamp=on,pglogical.conflict_resolution=last_update_wins

Node 1

CREATE ROLE ru WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'rp';

CREATE EXTENSION pglogical;

GRANT USAGE ON SCHEMA pglogical TO ru;

SELECT pglogical.create_node(
     node_name := 'node1',
     dsn := 'host=34.142.26.74 port=5432 dbname=postgres user=ru password=rp sslmode=require'
 );

CREATE TABLE table1 (id int PRIMARY KEY, data text);

INSERT INTO table1 VALUES (1,'apple'), (2,'banana'), (3,'cherry');

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

## Node 2

CREATE ROLE ru WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'rp'

CREATE EXTENSION pglogical

GRANT USAGE ON SCHEMA pglogical TO ru;

SELECT pglogical.create_node(
     node_name := 'node2',
     dsn := 'host=35.188.121.215 port=5432 dbname=postgres user=ru password=rp sslmode=require'
 );

CREATE TABLE table1 (id int PRIMARY KEY, data text);

SELECT pglogical.create_subscription(
    subscription_name := 'node2to1',
    provider_dsn := 'host=34.142.26.74 port=5432 dbname=postgres user=ru password=rp sslmode=require',
    synchronize_data := false,
    forward_origins := '{}'
);

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

## Node 1

INSERT INTO table1 VALUES (100,'dragon fruit'), (101,'elderberry'), (102,'grape');

Node 1

SELECT * FROM table1;

Node 2

SELECT * FROM table1;

Node 2

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

Node 1

SELECT pglogical.create_subscription(
   subscription_name := 'node1to2',
   provider_dsn := 'host=35.189.100.190 port=5432 dbname=postgres user=ru password=rp sslmode=require',
   synchronize_data := false,
   forward_origins := '{}'
);

SELECT * FROM pglogical.show_subscription_status('node1to2);

Test the Bi-directional Replication

Node 1

INSERT INTO table1 VALUES (110,'melon'), (111,'strawberry');

Node 1 and Node 2

SELECT * FROM table1;

Node 2

INSERT INTO table1 VALUES (200,'jackfruit'), (201,'lemon');

Node 1 and Node 2

SELECT * FROM table1;

Verification if PGLogical using SSL

SELECT * FROM pg_stat_ssl
join pg_stat_activity USING(pid)

Cleanup

select pglogical.drop_subscription('node2to1', true)
select pglogical.drop_subscription('node1to2', true)
select pglogical.drop_node('node1', true)
select pglogical.drop_node('node2', true)

Do we need SSL in PGLogical Setup ?

PGLogical uses libpq library for creating connections to other postgres instances and by default ssl mode is prefer so when we setup pglogical replication it first tries with SSL connection and then fallback to non ssl connection.

https://www.postgresql.org/docs/9.5/libpq-connect.html#LIBPQ-CONNSTRING:~:text=an%20SSL%20connection-,prefer,-(default)

psql client also uses libpq library so we can verify by running locally psql client without any sslmode, we can see connection is still SSL encrypted.

> psql -h  35.189.100.190 -p 5432 -U postgres -d postgres           
psql: warning: extra command-line argument "sslmode=disable" ignored
Password for user postgres: 
psql (14.11 (Homebrew), server 13.20)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

with sslmode as disabled

psql "postgresql://postgres:pwd@35.189.100.190:5432/postgres?sslmode=disable"
psql (14.11 (Homebrew), server 13.20)
Type "help" for help.