Configure external replicas

This page describes how to configure a Cloud SQL instance that replicates to a replica external to Cloud SQL using the pglogical extension with logical decoding.

For more information about replication, see About 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 an external PostgreSQL instance that meets the requirements for external replicas.

Configure the primary instance

  1. Go to the Cloud 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, see Configuring 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's Overview 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 the REPLICATION 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 the PostgreSQL resources page.

    Restart the database, then login, change to the replication_user, create the pglogical 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 the REPLICATION_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 the gcloud storage command: 
    gcloudstoragecpgs://BUCKET_NAME/DUMP_FILE_NAME.
  4. Import the file into your database. 
     psql --user=postgres --password < DUMP_FILE_NAME.
  5. Install pglogical 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 as replicating, 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

See Troubleshooting pglogical

What's next