Console
    Contact Us Start free

    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 .

    Source instance requirements

    The source instance for an external read replica must be a primary or standalone instance. You can't use a Cloud SQL read replica as the source instance for an external read replica. Read replicas are sometimes recreated from the disk clone of their primary instance and their replication state to an external read replica can't be maintained by the read replica.

    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 iconin the upper right corner.
    5. At the Cloud Shell prompt, use the built-in PostgreSQL client to connect to your primary instance: 
        
gcloud  
sql  
connect  
 PRIMARY_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. 
       CREATE  
USER  
 REPLICATION_USER 
  
WITH  
REPLICATION  
IN  
ROLE  
cloudsqlsuperuser  
LOGIN  
PASSWORD  
 ' 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:

       CREATE 
  
 EXTENSION 
  
 pglogical 
 ;
    9. Create a pglogical node:

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

       SELECT 
  
 pglogical 
 . 
 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: 
       CREATE 
  
 DATABASE 
  
 test 
 ; 
 \ 
 connect 
  
 test 
 ; 
 CREATE 
  
 TABLE 
  
 replica_test 
  
 ( 
 id 
  
 SERIAL 
  
 PRIMARY 
  
 KEY 
 , 
  
 data 
  
 text 
 ); 
 INSERT 
  
 INTO 
  
 replica_test 
  
 ( 
 data 
 ) 
  
 VALUES 
  
 ( 
 'apple' 
 ), 
  
 ( 
 'banana' 
 ), 
  
 ( 
 'cherry' 
 ); 
 CREATE 
  
 EXTENSION 
  
 pglogical 
 ;
    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: 
       SELECT 
  
 pglogical 
 . 
 replication_set_add_table 
 ( 
 'default' 
 , 
  
 'replica_test' 
 , 
  
 true 
 );

    Configure the external replica

    1. Create a special user for replication and grant replication privileges: 
       CREATE  
USER  
 REPLICATION_USER 
  
WITH  
REPLICATION  
SUPERUSER  
LOGIN  
PASSWORD  
 ' 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: 
       CREATE 
  
 DATABASE 
  
 test 
 ; 
 \ 
 connect 
  
 test 
 ; 
 CREATE 
  
 TABLE 
  
 replica_test 
  
 ( 
 id 
  
 SERIAL 
  
 PRIMARY 
  
 KEY 
 , 
  
 data 
  
 text 
 ); 
 INSERT 
  
 INTO 
  
 replica_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: 
      gcloud  
storage  
cp  
gs:// 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: 
       ALTER 
  
 SYSTEM 
  
 SET 
  
 shared_preload_libraries 
  
 = 
  
 'pglogical' 
 ; 
 ALTER 
  
 SYSTEM 
  
 SET 
  
 max_replication_slots 
  
 = 
  
  # 
 
 ; 
  
 ( 
 where 
  
  # 
 
  
 is 
  
 the 
  
 same 
  
 as 
  
 you 
  
 set 
  
 on 
  
 the 
  
 primary 
 ). 
 ALTER 
  
 SYSTEM 
  
 SET 
  
 max_worker_processes 
  
 = 
  
  # 
 
 ; 
  
 ( 
 where 
  
  # 
 
  
 is 
  
 the 
  
 same 
  
 as 
  
 you 
  
 set 
  
 on 
  
 the 
  
 primary 
 ). 
 # 
  
 Logout 
  
 of 
  
 the 
  
 database 
  
 and 
  
 restart 
  
 it 
 . 
  
 For 
  
 example 
 , 
 # 
  
  sudo 
  
 / 
 etc 
 / 
 init 
 . 
 d 
 / 
 postgresql 
  
 restart 
 
 # 
  
 Log 
  
 back 
  
 in 
  
 the 
  
 database 
  
 as 
  
 the 
  
 replication_user 
 . 
 # 
  
 Since 
  
 the 
  
 pglogical 
  
 extension 
  
 is 
  
 created 
  
 local 
  
 to 
  
 each 
  
 database 
 , 
  
 you 
  
 need 
  
 to 
 # 
  
 execute 
  
  CREATE 
  
 EXTENSION 
  
 pglogical 
 
  
 in 
  
 each 
  
 database 
  
 you 
  
 create 
 , 
  
 so 
  
 if 
  
 you 
 # 
  
 haven 
 ' 
 t 
  
 already 
  
 done 
  
 that 
 : 
 CREATE 
  
 EXTENSION 
  
 pglogical 
 ; 
  
 
       For 
  
 more 
  
 information 
  
 about 
  
 these 
  
 flags 
 , 
  
 see 
  
 the 
  
  PostgreSQL 
  
 resources 
 
  
 page 
 .
    7. Create a pglogical node: 
       SELECT 
  
 pglogical 
 . 
 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: 
       SELECT 
  
 pglogical 
 . 
 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 
  
 * 
  
 FROM 
  
 pglogical 
 . 
 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
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: