Cloud SQL (PostgreSQL) Configure external replicas to VM

If you’re running a PostgreSQL database on Cloud SQL and need a replica for backup or scale-out purposes, you can set up replication to a replica VM using pglogical. In this blog post, we’ll guide you through the necessary steps to set up replication between Cloud SQL with PostgreSQL 14 as your primary and a replica VM running Red Hat Enterprise Linux 7.

First, let’s start with the primary (Cloud SQL) side.

Edit Cloud SQL Flags

The first step is to edit the Cloud SQL flags. Use the following flags to enable pglogical and logical decoding:

1
cloudsql.enable_pglogical = On
2
cloudsql.logical_decoding = On
3
max_replication_slots = 10 # use the minimum value
4
max_worker_processes = 8 # use the minimum value
5
max_wal_senders = 10 # use the minimum value

Login to PostgreSQL

Once the flags are set, log in to PostgreSQL using the psql command:

1
psql -h HOST -U postgres

Create Replication User

Create a replication user with the following command, replacing <REPLICATION_USER> and <REPLICATION_USER_PASSWORD> with your preferred values:

1
CREATE USER <REPLICATION_USER> WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD '<REPLICATION_USER_PASSWORD>';

Connect to the Database

Connect to the database you want to replicate using the replication user:

1
\c <DATABASE_NAME> <REPLICATION_USER>;

Enable pglogical Extension

Enable the pglogical extension with the following command:

1
CREATE EXTENSION pglogical;

Create a pglogical Node

Create a pglogical node with the following command, replacing <CLOUDSQL_IP>, <DATABASE_NAME>, <REPLICATION_USER>, and <REPLICATION_USER_PASSWORD> with your preferred values:

1
SELECT pglogical.create_node(
2
  node_name := 'provider',
3
  dsn := 'host=<CLOUDSQL_IP> port=5432 dbname=<DATABASE_NAME> user=<REPLICATION_USER> password=<REPLICATION_USER_PASSWORD>'
4
);

Add Tables to Replication Set

Add all tables to the replication set with the following command:

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

You can also add individual tables to the replication set with the following command:

1
SELECT pglogical.replication_set_add_table('default', '<TABLE_NAME>', true);

Now, let’s move on to the replica VM side.

Install PostgreSQL 14 and pglogical

Install PostgreSQL 14 and pglogical on your Red Hat Enterprise Linux 7 replica VM by following the instructions provided by PostgreSQL and pglogical.

Install Postgresql 14 https://www.postgresql.org/download/linux/redhat/

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

Edit pg_hba.conf

Edit the /var/lib/pgsql/14/data/pg_hba.conf file and add the following line:

1
local   all             <REPLICATION_USER>                      md5
2
host    all             all             0.0.0.0/0               md5

Edit postgresql.conf

Edit the /var/lib/pgsql/14/data/postgresql.conf file and set listen_addresses to ’*‘.

1
listen_addresses to '*'

Restart PostgreSQL

Restart PostgreSQL with the following command:

1
sudo systemctl restart postgresql-14

Create Replication User

Create the same replication user on the replica VM as you did on the primary:

1
CREATE USER <REPLICATION_USER> WITH REPLICATION SUPERUSER LOGIN PASSWORD '<REPLICATION_USER_PASSWORD>';

Set Parameters

Set the following parameters with the following commands:

1
ALTER SYSTEM SET shared_preload_libraries = 'pglogical';
2
ALTER SYSTEM SET max_replication_slots = <VALUE>; # use the same value as in the Cloud SQL
3
ALTER SYSTEM SET max_worker_processes = <VALUE>; # use the same value as in the Cloud SQL
4
\q

Restart PostgreSQL

Restart PostgreSQL with the following command:

1
sudo systemctl restart postgresql-14

Login as <REPLICATION_USER>

1
psql -U <REPLICATION_USER> -d postgres

Create a database

Create the same <DATABASE_NAME> as the database you want to replicate in Cloud SQL and exit

1
CREATE DATABASE <DATABASE_NAME>;
2
\q

Dump database table schema from Cloud SQL

Use the following command dump database table schema from Cloud SQL:

1
pg_dump -U <REPLICATION_USER> -h <CLOUDSQL_IP> <DATABASE_NAME> --schema-only > <DATABASE_NAME>.sql

Restore table schema

Restore the table schema to the replica database with the following:

1
psql -U <REPLICATION_USER> -d <DATABASE_NAME> -f <DATABASE_NAME>.sql

Login as <REPLICATION_USER>

Let’s login to the database you created previously as <REPLICATION_USER>

1
psql -U <REPLICATION_USER> -d <DATABASE_NAME>

Enable pglogical extension

If you got an error, this means that the pglogical extension is already enabled

1
CREATE EXTENSION pglogical;

Create a pglogical node:

1
SELECT pglogical.create_node(
2
  node_name := 'subscriber',
3
  dsn := 'host=<REPLICA_VM_IP> port=5432 dbname=<DATABASE_NAME> user=<REPLICATION_USER> password=<REPLICATION_USER_PASSWORD>'
4
);

Create a pglogical subscription:

1
SELECT pglogical.create_subscription(
2
    subscription_name := '<SUBSCRIPTION_NAME>',
3
    provider_dsn := 'host=<CLOUDSQL_IP> port=5432 dbname=<DATABASE_NAME> user=<REPLICATION_USER> password=<REPLICATION_USER_PASSWORD>',
4
    replication_sets := ARRAY['default'],
5
    synchronize_data := true
6
);

Check the status of the subscription:

1
SELECT subscription_name, status FROM pglogical.show_subscription_status();

If the status appears as replicating, then the setup is successful.

Insert some data into the PRIMARY and check the REPLICA to make sure the data appears there as well.

Notes:

Everytime you create a new table in the Cloud SQL (PRIMARY), you have to create the same table schema in REPLICA and add the table to replica set in the PRIMARY with the following command:

1
SELECT pglogical.replication_set_add_table('default', '<TABLE_NAME>', true);

Additional commands:

Remove replication set

1
SELECT pglogical.replication_set_remove_table('default', '<TABLE_NAME>');

Remove pglogical subscription

1
SELECT pglogical.drop_subscription('<SUBSCRIPTION_NAME>');

Remove pglogical node

1
SELECT pglogical.drop_node('<NODE_NAME>');

You can also check the complete documentation for pglogical here https://www.2ndquadrant.com/en/resources-old/pglogical/pglogical-docs/