Cloud SQL (PostgreSQL) Configure external replicas to VM

Cat Administrator

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:
1cloudsql.enable_pglogical = On2cloudsql.logical_decoding = On3max_replication_slots = 10 # use the minimum value4max_worker_processes = 8 # use the minimum value5max_wal_senders = 10 # use the minimum value
Login to PostgreSQL
Once the flags are set, log in to PostgreSQL using the psql
command:
1psql -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:
1CREATE 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:
1CREATE 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:
1SELECT 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:
1SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
You can also add individual tables to the replication set with the following command:
1SELECT 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:
1local all <REPLICATION_USER> md52host 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 ’*‘.
1listen_addresses to '*'
Restart PostgreSQL
Restart PostgreSQL with the following command:
1sudo systemctl restart postgresql-14
Create Replication User
Create the same replication user on the replica VM as you did on the primary:
1CREATE USER <REPLICATION_USER> WITH REPLICATION SUPERUSER LOGIN PASSWORD '<REPLICATION_USER_PASSWORD>';
Set Parameters
Set the following parameters with the following commands:
1ALTER SYSTEM SET shared_preload_libraries = 'pglogical';2ALTER SYSTEM SET max_replication_slots = <VALUE>; # use the same value as in the Cloud SQL3ALTER SYSTEM SET max_worker_processes = <VALUE>; # use the same value as in the Cloud SQL4\q
Restart PostgreSQL
Restart PostgreSQL with the following command:
1sudo systemctl restart postgresql-14
Login as <REPLICATION_USER>
1psql -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
1CREATE DATABASE <DATABASE_NAME>;2\q
Dump database table schema from Cloud SQL
Use the following command dump database table schema from Cloud SQL:
1pg_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:
1psql -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>
1psql -U <REPLICATION_USER> -d <DATABASE_NAME>
Enable pglogical extension
If you got an error, this means that the pglogical extension is already enabled
1CREATE EXTENSION pglogical;
Create a pglogical node:
1SELECT 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:
1SELECT 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 := true6);
Check the status of the subscription:
1SELECT 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:
1SELECT pglogical.replication_set_add_table('default', '<TABLE_NAME>', true);
Additional commands:
Remove replication set
1SELECT pglogical.replication_set_remove_table('default', '<TABLE_NAME>');
Remove pglogical subscription
1SELECT pglogical.drop_subscription('<SUBSCRIPTION_NAME>');
Remove pglogical node
1SELECT 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/