This page describes how to configure a Cloud SQL instance that replicatesto a replica external to Cloud SQL using thepglogical extension withlogical decoding.

For more information about replication, seeAbout replication in Cloud SQL.

Set up the external replica configuration

Before you begin

Before you start this task, you must have a Cloud SQL instance and anexternal PostgreSQL instance that meets therequirements for externalreplicas.

Configure the primary instance

1. Go to theCloud SQL Instances page in the Google Cloud console.
2. Enable access on the primary instance for the IP address of the external replica.

   For information about enabling IP access, seeConfiguring access for IP connections.

3. Record the public IP address and the public outgoing IP address of the primary instance for later use. You can find these values on the instance'sOverview page.
4. Click the Cloud Shell icon in the upper right corner.
5. At the Cloud Shell prompt, use the built-in PostgreSQL client to connect to your primary instance:

   gcloudsqlconnectPRIMARY_INSTANCE_NAME\--user=postgres

6. Enter your root password. You should then see the postgres prompt.
7. Create a PostgreSQL user with theREPLICATION attribute.

   CREATEUSERREPLICATION_USERWITHREPLICATIONINROLEcloudsqlsuperuserLOGINPASSWORD'REPLICATION_USER_PASSWORD';

8. Install and configure the pglogical extension:

   Edit the Cloud SQL instance to add and set the following flags:

   • cloudsql.enable_pglogical
   • cloudsql.logical_decoding
   • max_replication_slots
   • max_worker_processes
   • max_wal_senders

   • For more information about these flags, see thePostgreSQL resources page.

   Restart the database, then login, change to the replication_user, create thepglogical extension:

   CREATEEXTENSIONpglogical;

9. Create a pglogical node:

   A pglogical _node_ represents a physical PostgreSQL instance, and stores connection details for that instance.

   SELECTpglogical.create_node(node_name:='provider',dsn:='host=PRIMARY_PUBLIC_IP_ADDRESS port=5432 dbname=DATABASE_NAME user=REPLICATION_USER password=REPLICATION_USER_PASSWORD');

10. If you are starting with a new database, create the same database and tables on both the primary and replica instances. For example:

    CREATEDATABASEtest;\connecttest;CREATETABLEreplica_test(idSERIALPRIMARYKEY,datatext);INSERTINTOreplica_test(data)VALUES('apple'),('banana'),('cherry');CREATEEXTENSIONpglogical;

11. If you already have a database on the primary instance, you must create the same on the replica. To do this, export the database from the primary instance to a Cloud Storage bucket and import it into the replica. Learn more about Exporting data from Cloud SQL to a SQL dump file in Cloud Storage.
12. To support replicating different sets of data to different destinations, pglogical has the concept of a replication set. For example, to add a table to the default replication set:

    SELECTpglogical.replication_set_add_table('default','replica_test',true);

Configure the external replica

1. Create a special user for replication and grant replication privileges:

   CREATEUSERREPLICATION_USERWITHREPLICATIONSUPERUSERLOGINPASSWORD'REPLICATION_USER_PASSWORD';

2. If you are starting with a new database, use theREPLICATION_USER to create the same database and tables on both the primary and replica instances. For example:

   CREATEDATABASEtest;\connecttest;CREATETABLEreplica_test(idSERIALPRIMARYKEY,datatext);INSERTINTOreplica_test(data)VALUES('apple'),('banana'),('cherry');

3. If you are seeding the external replica instance with a file you exported file from the primary instance, download the exported file from Cloud Storage. If your external replica is on a Compute Engine instance, you can download the file using thegcloud storage command:

   gcloudstoragecpgs://BUCKET_NAME/DUMP_FILE_NAME.

4. Import the file into your database.

   psql --user=postgres --password <DUMP_FILE_NAME.

5. Installpglogical according to your OS. For example, on Debian systems running PostgreSQL version 13,sudo apt-get install postgresql-13-pglogical.
6. Login to the database as the replication_user and set the following parameters:

   ALTERSYSTEMSETshared_preload_libraries='pglogical';ALTERSYSTEMSETmax_replication_slots=#;(where#isthesameasyousetontheprimary).ALTERSYSTEMSETmax_worker_processes=#;(where#isthesameasyousetontheprimary).#Logoutofthedatabaseandrestartit.Forexample,#sudo/etc/init.d/postgresqlrestart#Logbackinthedatabaseasthereplication_user.#Sincethepglogicalextensioniscreatedlocaltoeachdatabase,youneedto#executeCREATEEXTENSIONpglogicalineachdatabaseyoucreate,soifyou#haven'talreadydonethat:CREATEEXTENSIONpglogical;
   Formoreinformationabouttheseflags,seethePostgreSQLresourcespage.
   

7. Create a pglogical node:

   SELECTpglogical.create_node(node_name:='subscriber',dsn:='host=REPLICA_PUBLIC_IP_ADDRESS port=5432 dbname=DATABASE_NAME user=REPLICATION_USER password=REPLICATION_USER_PASSWORD');

8. Create a pglogical subscription:

   SELECTpglogical.create_subscription(subscription_name:='SUBSCRIPTION_NAME',provider_dsn:='host=PRIMARY_PUBLIC_IP_ADDRESS port=5432 dbname=DATABASE_NAME user=REPLICATION_USER password=REPLICATION_USER_PASSWORD');

9. Check the status of the subscription:

   SELECT*FROMpglogical.show_subscription_status('SUBSCRIPTION_NAME');

10. If the status appears asreplicating, then the setup is successful.
11. Insert some data into the primary and check the replica to make sure the data appears there as well.

Troubleshoot

SeeTroubleshooting pglogical

What's next

• Learn how tomanage replicas.
• Learn aboutrequirements and best practices for the external replica configuration.
• Learn more aboutPostgreSQL replication.
• Learn more aboutreplication configuration settings.
• Learn more aboutreplicating from an external server.