CloudSQL logical replication - ghdrako/doc_snipets GitHub Wiki

Resource

Setup pglogical https://github.com/2ndQuadrant/pglogical

Terminology:

  • Nodes - PostgreSQL database instances
  • Providers and Subscribers - roles taken by Nodes
  • Replication Set - a collection of tables

Architectural details:

  • pglogical works on a per-database level, not whole server level like physical streaming replication
  • One Provider may feed multiple Subscribers without incurring additional disk write overhead
  • One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution (some, but not all aspects required for multi-master).
  • Cascading replication is implemented in the form of changeset forwarding.
  1. Allow connections If the primary instance is a Cloud SQL instance, you can allow access from the replica's outgoing IP address by adding it as an authorized network.
# Get outgoing IP address of a replica instance
gcloud sql instances describe [REPLICA_NAME] --format="default(ipAddresses)"
  1. Create a replication user on both side
CREATE USER replication_user WITH REPLICATION
IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secret';

# for existing user
ALTER USER existing_user WITH REPLICATION;
  1. Set up logical replication with pglogical
  • Primary instance
cloudsql.logical_decoding=on 
cloudsql.enable_pglogical=on
  • Logical replica
cloudsql.enable_pglogical=on
  1. Install the pglogical extension On both side using replication_user
CREATE EXTENSION pglogical;
  1. https://cloud.google.com/sql/docs/postgres/replication/configure-logical-replication#create-a-pglogical-node-on-each-instance
source-instance$ SELECT pglogical.create_node(
    node_name := 'primary',
    dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=secret'
);

dest-instance$ SELECT pglogical.create_node(
    node_name := 'replica',
    dsn := 'host=<replica-ip> port=5432 dbname=postgres user=replication_user password=secret'
);
  1. Create a table with data to replicate
CREATE TABLE replica_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO replica_test (data) VALUES ('apple'), ('banana'), ('cherry');

The table must also be created on the replica instance.

  1. Add the table to a replication set
SELECT pglogical.replication_set_add_table('default', 'replica_test', true);
  1. Create the pglogical subscription
SELECT pglogical.create_subscription(
    subscription_name := 'test_sub',
    provider_dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
);

SELECT * FROM pglogical.show_subscription_status('test_sub');
-- SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

Set up native PostgreSQL logical replication https://www.postgresql.org/docs/current/logical-replication.html

  1. Create a replication user on replicate side
CREATE USER replication_user WITH REPLICATION
IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secret';

# for existing user
ALTER USER existing_user WITH REPLICATION;
  1. logical decoding must be enabled on the primary instance
cloudsql.logical_decoding=on
  1. Create a table with data to replicate
CREATE TABLE native_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO native_test (data) VALUES ('apple'), ('banana'), ('cherry');

The table must also be created on the replica instance

  1. Create a publication on the primary instance
CREATE PUBLICATION pub FOR TABLE native_test;
  1. Create a subscription on the replica instance
CREATE SUBSCRIPTION sub
    CONNECTION 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
    PUBLICATION pub;

Creating the subscription on the replica instance requires the cloudsqlsuperuser role.

⚠️ **GitHub.com Fallback** ⚠️