This page describes how to configure your external server for replication toCloud SQL, create a source representation instance on Cloud SQL, andreplicate the data to Cloud SQL. You need to go through all the steps onthis page before proceeding to the replication steps.

An alternative to the steps described on this page is theDatabase Migration Service, which offerscontinuous replication or one-time database migration from an externalserver to Cloud SQL.

Fastmigration for Cloud SQL is now available. This feature improves the performance of data migrations from an external source to a destination Cloud SQL instance.

Before you begin

Terminology

• External server. The PostgreSQL server external to Cloud SQL thatyou want to replicate data from. It's also referred to as the sourcedatabase or the external database server. It can be another Cloud SQLinstance or any other database server, such as on-premises, Amazon RelationalDatabase Service (RDS), and so on.

• Source representation instance. A mock of a Cloud SQL instancethat represents the external server to the Cloud SQL replica.It's visible in the Google Cloud console and appears like a regularCloud SQL instance, but it doesn't contain data, requireconfiguration or maintenance, or affect billing.

• Cloud SQL replica. The Cloud SQL instance that replicatesfrom the external server. Also known as the external primaryread replica.

• Replication user account. The PostgreSQL user account on the externalserver with sufficient permissions to allow replication between theexternal server and the Cloud SQL replica.

• Managed import. The process of importing data directly from the externalserver to the Cloud SQL replica. In this situation, Cloud SQLconnects to the external server using the replication user account andruns the data dump directly on the external server to import data to theCloud SQL replica.

Set up a Google Cloud project

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin API.

Enable the API

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

Enable the Cloud SQL Admin API.

Enable the API

1. Make sure you have the Cloud SQL Admin, Storage Admin, and Compute Viewer roles on your user account.

   Go to the IAM page

Install the Google Cloud SDK

To configure replication,install Google Cloud SDKfor your external server. You might want to install the SDK on yourexternal server unless it's already installed elsewhere.

Set up the external server for replication

Cloud SQL supports continuous migrations from source databases toCloud SQL destination databases.

Supported source databases for PostgreSQL include:

• Self-managed PostgreSQL (on premises or on any cloud VM that you fully control) 9.4, 9.5, 9.6, 10, 11, 12, 13, 14, 15, 16, and 17
• Amazon RDS 9.6.10+, 10.5+, 11.1+, 12, 13, 14, 15, 16, and 17
• Amazon Aurora 10.11+, 11.6+, 12.4+, 13.3+, 14.6+, 15.2+, 16, and 17
• Microsoft Azure Database for PostgreSQL Flexible Server 11 and later
• Cloud SQL 9.6, 10, 11, 12, 13, 14, 15, 16, and 17

Configuring your source requires configuring both the source instance andunderlying source databases.

External server checklist

If the Cloud SQL replica is enabled with a private IP address because theoutgoing private IP address isn't static, configure theexternal server's firewall to allow the internal IP range allocated for theprivate services access of the VPC network that the Cloud SQL replica uses as its private network.

The source database server's firewall must be configured to allow the entireinternal IP range allocated for theprivate service connection of the VPC network that the Cloud SQL destination instance uses as theprivateNetwork field of itsipConfiguration settings.

To find theinternal IP range:

1. In the Google Cloud console, go to theVPC networks page.

   Go to the VPC networks page

2. Select the VPC network that you want to use.

3. Click thePrivate service connection tab.

Configure your source instance

To configure your source instance, follow these steps:

1. If your source instance does not include thepostgres database,create it.
2. Install the pglogical package on the source instance.

3. Set the following parameters, as needed.

   1. If the source PostgreSQL instance is Amazon RDS, then include these parameters in a new parameter group and attach the parameter group to the instance.

   • If the source is Cloud SQL, set thecloudsql.logical_decoding andcloudsql.enable_pglogical flags toon.

     To enable flags in Cloud SQL, seeConfiguring database flags.

     Note: Thepglogical extension can logcredentials in plain text on the source instance. This behavior is caused bythe extension, and is unrelated to Cloud SQL.

   • Setshared_preload_libraries to includepglogical by using the following command:

     ALTERSYSTEMSETshared_preload_libraries='pglogical';

     Note: To view a list of the existing libraries for your instance, run theshow shared_preload_libraries command.

   • Setwal_level tological by using the following command:

     ALTERSYSTEMSETwal_level='logical';

     If the source PostgreSQL instance is Amazon RDS, to enable WAL logs at thelogical level, set therds.logical_replication parameter to1.

   • Setwal_sender_timeout to0 by using the following command:

     ALTERSYSTEMSETwal_sender_timeout=0;

     The value0 disables the timeout mechanism that's used to terminate inactive replication connections.

   • Setmax_replication_slots to the maximum number of replication slots that the source instance can support. Use the following command, after replacingMAX_REPLICATION_SLOTS with the number:

     ALTERSYSTEMSETmax_replication_slots=MAX_REPLICATION_SLOTS;

     Cloud SQL requires one slot for each database that's migrated. Specify at least the number of subscriptions expected to connect, with some reserves for table synchronization.

     For example, if the source instance has 5 databases and 2 migration jobs are created for the source, then the number of replication slots must be at least 5 * 2 = 10, in addition to the number of replication slots that you already use.

   • Setmax_wal_senders to at least the same asmax_replication_slots, in addition to the number of senders already used on your instance. Use the following command, replacingMAX_WAL_SENDERS with the total number of WAL sender processes running simultaneously:

     ALTERSYSTEMSETmax_wal_senders=MAX_WAL_SENDERS;

     For example, if themax_replication_slots parameter is set to10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.

   • Setmax_worker_processes to at least the number of databases in the source instance, in addition to the number of worker processes already used on your instance. Use the following command, after replacingMAX_WORKER_PROCESSES with the total number:

     ALTERSYSTEMSETmax_worker_processes=MAX_WORKER_PROCESSES;

   2. If the source PostgreSQL instance is Microsoft Azure Database for PostgreSQL Flexible Server, then perform the following actions to support migrating data from an external server to a Cloud SQL instance:

      • Setshared_preload_libraries to includepglogical by using the following command:

        ALTERSYSTEMSETshared_preload_libraries='pglogical';

        Note: To view a list of the existing libraries for your instance, run theshow shared_preload_libraries command.
      • Setwal_level tological. For more information, seeLogical replication and logical decoding in Azure Database for PostgreSQL - Flexible Server.

      • Setmax_replication_slots to the maximum number of replication slots that the source instance can support. Use the following command, after replacingMAX_REPLICATION_SLOTS with the number:

        ALTERSYSTEMSETmax_replication_slots=MAX_REPLICATION_SLOTS;

        Cloud SQL requires one slot for each database that's migrated. Specify at least the number of subscriptions expected to connect, with some reserves for table synchronization.

        For example, if the source instance has 5 databases and 2 migration jobs are created for the source, then the number of replication slots must be at least 5 * 2 = 10, in addition to the number of replication slots that you already use.

      • Setmax_wal_senders to at least the same asmax_replication_slots, in addition to the number of senders already used on your instance. Use the following command, replacingMAX_WAL_SENDERS with the total number of WAL sender processes running simultaneously:

        ALTERSYSTEMSETmax_wal_senders=MAX_WAL_SENDERS;

        For example, if themax_replication_slots parameter is set to10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.

      • Setmax_worker_processes to at least the number of databases in the source instance, in addition to the number of worker processes already used on your instance. Use the following command, after replacingMAX_WORKER_PROCESSES with the total number:

        ALTERSYSTEMSETmax_worker_processes=MAX_WORKER_PROCESSES;

      • Setazure.extensions to includepglogical. For more information, seeServer parameters in Azure Database for PostgreSQL - Flexible Server.

        Note:By default, Microsoft Azure has therequire_secure_transport parameter set toon. In this case, when you useDatabase Migration Service to create your source connection profile for Microsoft Azure, specify eitherServer-only authentication orServer-client authentication for the SSL/TLS configuration. If a connection is made over a public network (by using IP allowlists), then SSL/TLS encryption can be used to connect between Microsoft Azure and Cloud SQL securely.

        If therequire_secure_transport parameter is set tooff, then specifyNone for the SSL/TLS configuration. The Cloud SQL destination instance connects to the source Microsoft Azure database without encryption.

The parameters that you're setting in this step apply to a PostgreSQL database server that's running. You can also make these changes persistent by including them in thepostgresql.conf file.

4. If the Cloud SQL replica will use a private IP, configure the external server's firewall to allow the internal IP range allocated for theprivate services access of the VPC network of the replica.
5. To apply the configuration changes, restart the source instance.

Enable replication delay monitoring for PostgreSQL versions preceding 9.6

If you're migrating from a PostgreSQL version lower than 9.6, then thereplication delay metric isn't available by default. You can use one of threealternatives to track this metric and ensure minimal downtime when you promotethe database:

• Option 1: Enable the Cloud SQL external server to track the replicationdelay by granting access to a specific query. Using a user with theSUPERUSER privilege, perform the following:

  1. Define the following function to allow the external server toquery for the replication delay.

     CREATEORREPLACEFUNCTIONpg_stat_replication_user()RETURNSTABLE(pidinteger,usesysidoid,usernamename,application_nametext,client_addrinet,client_hostnametext,client_portinteger,backend_starttimestampwithtimezone,backend_xminxid,statetext,sent_locationpg_lsn,write_locationpg_lsn,flush_locationpg_lsn,replay_locationpg_lsn,sync_priorityinteger,sync_statetext)LANGUAGESQLSECURITYDEFINERAS$$SELECT*FROMpg_catalog.pg_stat_replication;$$;

  2. Grant theEXECUTE permission to the user by running thefollowing commands:

     1. REVOKE EXECUTE ON FUNCTION pg_stat_replication_user() FROM public;
     2. GRANT EXECUTE ON FUNCTION pg_stat_replication_user() to {replication_user};

• Option 2: Grant theSUPERUSER privilege directly to the userused to connect to the source instance. This allows the external server to readthe replication delay directly.

• Option 3: Track the replication delay independently by using thefollowing query:

  Note: For PostgreSQL versions earlier than 10, run thiscommand as asuperuser.

  SELECTcurrent_timestamp,application_name,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.sent_location)ASsent_location_lag,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.write_location)ASwrite_location_lag,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.flush_location)ASflush_location_lag,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.replay_location)ASreplay_location_lagFROMpg_stat_replicationWHEREapplication_namelike'cloudsql%';

  In this option, Cloud SQL doesn't reflect the replication delay metricin the graphs or API responses.

Configure your source databases

The Cloud SQL external server migrates all databases under your sourceinstance other than the following:

• For on-premise sources: template databasestemplate0 andtemplate1
• For Amazon RDS sources:template0,template1, andrdsadmin
• For Cloud SQL sources: template databasestemplate0 andtemplate1

Do the following on each database in your source instance that isn't includedin the preceding list:

1. To install thepglogical extension, run the following command onevery database on your source instance:

   CREATEEXTENSIONIFNOTEXISTSpglogical

   For tables that don't have primary keys, Cloud SQL supports themigration of the initial snapshot andINSERT statements duringthe change data capture (CDC) phase. MigrateUPDATE andDELETE statements manually.

   Note: SeeDebugging and other toolsto learn how to generate a query to list tables in a PostgreSQL databasewithout primary keys. You mustalter these tables so that they have a primary key each. For tables thatdon't have primary keys, only the initial snapshot andINSERTstatements are migrated.

   Note: For PostgreSQL 9.4, install thepglogical_origin extension on every database by running thefollowing commands:

   CREATEEXTENSIONIFNOTEXISTSpglogical_origin;CREATEEXTENSIONIFNOTEXISTSpglogical;

2. Connect to the instance and run the following commands to set privileges forthe user on each of the migrated databases, as well as the defaultpostgres database.

   The user that you use to connect to the source instance is configured as theuser in theConnection Profiles page.You can create a new user or reuse an existing one.

   • On all schemas on each database to migrate, except for the informationschema and schemas starting withpg_, run the following command:

     GRANTUSAGEonSCHEMAschematousername;GRANTSELECTonALLSEQUENCESinSCHEMAschematousername;GRANTSELECTonALLTABLESinSCHEMAschematousername;

   • On each database to migrate, run the following command:

     GRANTUSAGEonSCHEMApglogicaltoPUBLIC;

   • To get replication information from source databases, run the followingcommand on all databases:

     GRANTSELECTonALLTABLESinSCHEMApglogicaltousername;

     Note:If the following error message appears, then you can ignore it.

     postgres=> GRANT SELECT on ALL TABLES in SCHEMA pglogical to demo;
ERROR: permission denied for table node_interface

   • If your source is Amazon RDS, then run the following command:

     GRANTrds_replicationtousername;

     If your source is Microsoft Azure Database for PostgreSQL version earlier than 14, then run the following command:

     GRANTSELECTonALLTABLESinSCHEMApglogicaltousername;

     You can ignore an error that appears on thepglogical.node interface.

     If your source is Microsoft Azure Database for PostgreSQL version 14 and later, then run the following command:

     GRANTpg_read_all_datatousername;

     Otherwise, run the following command:

     ALTERUSERusernamewithREPLICATION;

Set up a source representation instance

The source representation instance references the external server. Itcontains only the request data from the external server. Create the request dataand use it in acurl command that creates the source representation instancein Cloud SQL.

Create the request data

The request data contains basic information about your external serverin JSON format. The request data can be configured for aCloud SQL replica on a public or private networkand should contain this information.

{"name":"SOURCE_NAME","region":"REGION","databaseVersion":"DATABASE_VERSION","onPremisesConfiguration":{"selectedObjects":"SELECTED_OBJECTS","hostPort":"SOURCE_HOST","username":"USERNAME","password":"PASSWORD","caCertificate":"SOURCE_CERT","clientCertificate":"CLIENT_CERT","clientKey":"CLIENT_KEY"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromaCloudSQLmanagedimport//-doesn'tuseSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"POSTGRES_9_6","onPremisesConfiguration":{"selectedObjects":[{"database":"db1"},{"database":"db2"}],"hostPort":"192.0.2.0:3306","username":"replicationUser","password":"486#@%*@"}}

Before you start this step,create a JSON file that contains your source request data.

Then, to create the source representation instance in Cloud SQL, open aterminal and run the following commands:

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data@JSON_PATH\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-access-token)"curl--header "Authorization: Bearer ${ACCESS_TOKEN}" \--header 'Content-Type: application/json' \--data @./source.json \-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances

Update a source representation instance

If you update therequest data from the externalserver, you can update the existing source representation instance touse the modified values.

Modify the request data

Update the request data to include any fields that have changed. This includes thehostPort,username,password,caCertificate,clientCertificate, andclientKey fields. After updating the request data, use it in acurl command to update the instance in Cloud SQL.

The following example shows updating theusername andpasswordfields with a different username and password:

{"name":"SOURCE_NAME","region":"REGION","databaseVersion":"DATABASE_VERSION","onPremisesConfiguration":{"selectedObjects":"SELECTED_OBJECTS","username":"NEW_USERNAME","password":"NEW_PASSWORD"}}

//exampleofsource.jsonforexternalserverthat//-initiatesreplicationfromaCloudSQLmanagedimport//-doesn'tuseSSL/TLS{"name":"cloudsql-source-instance","region":"us-central1","databaseVersion":"POSTGRES_9_6","onPremisesConfiguration":{"selectedObjects":[{"database":"db1"},{"database":"db3"}],"username":"newReplicationUser","password":"525#@%*@"}}

Modify a source representation instance

Before you start this step,create a JSON file that contains your modified request data.

Then, to modify the source representation instance in Cloud SQL, open aterminal and run the following commands:

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data@JSON_PATH\-XPATCH\https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances/SOURCE_NAME

gcloudauthloginACCESS_TOKEN="$(gcloud auth print-access-token)"curl--header "Authorization: Bearer ${ACCESS_TOKEN}" \--header 'Content-Type: application/json' \--data @./source.json \-XPATCH\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances/cloudsql-source-instance

Set up a Cloud SQL replica

The Cloud SQL replica eventually contains the data fromthe external server. In this step, you create the requestdata and use it in acurl command that creates theCloud SQL replica in Cloud SQL.

Create the request data

The request data contains basic information about your externalserver and Cloud SQL replica in JSON format. The request data can beconfigured for a Cloud SQL replica on a public or private networkand should contain this information:

{"settings":{"tier":"TIER","dataDiskSizeGb":"DISK_SIZE","ipConfiguration":{"ipv4Enabled":"PUBLIC_IP_STATUS","privateNetwork":"projects/PROJECT_ID/global/networks/NETWORK_NAME"},"availabilityType":"AVAILABILITY_TYPE"},"masterInstanceName":"SOURCE_REPRESENTATION_INSTANCE_NAME","region":"SOURCE_REGION","databaseVersion":"DATABASE_VERSION","name":"REPLICA_NAME"}

{"settings":{"tier":"db-custom-4-15360","dataDiskSizeGb":"100"},"masterInstanceName":"source-instance","region":"us-central1","databaseVersion":"POSTGRES_16","name":"replica-instance"}

Create the Cloud SQL replica

Before you start this step,create a JSON file that contains your replica request data.Then, to create a Cloud SQL replica, open a Cloud Shell terminal and run these commands:

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data@JSON_PATH\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT-ID/instances

gcloudauthloginACCESS_TOKEN="$(gcloudauthprint-access-token)"curl--header"Authorization: Bearer${ACCESS_TOKEN}"\--header'Content-Type: application/json'\--data@./replica.json\-XPOST\https://sqladmin.googleapis.com/sql/v1beta4/projects/MyProject/instances

Verify your setup

To ensure your instances were set up correctly, go to theCloud SQL Instances page.

You should see your source representation instance and the Cloud SQLreplica, in a listing similar to the following:

Also make sure that you have thecloudsql.instances.migrate permission onthe Cloud SQL replica. This permission is included in thecloudsql.adminorcloudsql.editorIAM roles.

Add users to the Cloud SQL replica

You cannot import database user accounts from the external server, butyou cancreate them on a Cloud SQL replica.Do this before you replicate from the external server.

Get the Cloud SQL replica's outgoing IP address

You can use the outgoing IP address of the Cloud SQL replica to create asecure connection between the external server and theCloud SQL replica. You won't be charged for this IP address.

gcloudsqlinstancesdescribeREPLICA_NAME--format="default(ipAddresses)"

Allow incoming connections on the external server

The Cloud SQL replica needs to connect to the external serverfor replication to succeed. You must configure the network firewall for yourexternal server to accept connections from theCloud SQL replica'soutgoing IP address if thefollowing conditions apply:

• The external server is behind a firewall or some other networkrestriction.
• Your Cloud SQL replica is using a public IP.

To connect to the Cloud SQL replica, you use the replica'sprimary IP address. This IP address is displayed in the Google Cloud console.

Update the source representation instance to allow replication to the Cloud SQL replica

After you set up the source representation instance for the Cloud SQLreplica, you might need toupdate the source representation instance. For example, thesescenarios require an update to your configurations:

• The host, port, or IP of the external server changes.
• You want to use a different PostgreSQL replication user.
• The password of the PostgreSQL replication user changes.
• The SSL certificates used to securely connect to theexternal server change.

Seed the Cloud SQL replica

For the initial loading of data from the external server into the Cloud SQLreplica, use a managed import. It uses a service that extracts data from theexternal server and imports it into the Cloud SQL instance directly.For more information, seeUsing a managed import to set up replication from external databases.

Monitor replication

When the Cloud SQL replica finishes the initial data load, it connects tothe external server and applies all updates that were made after the exportoperation.Confirm your replication status.

It's important to check the replication status before promoting thereplica to a standalone instance. If the replication process isn't successfullycompleted, a promoted replica doesn't have all the changes from yourexternal server.

If replication delay is not trending toward 0,take steps to address it.You might want to check these metrics:/postgresql/external_sync/initial_sync_complete,postgresql/external_sync/max_replica_byte_lag, anddatabase/replication/state. View the list ofCloud SQL metrics.

If you want to migrate a subset of databases from the source representationinstance to the destination Cloud SQL instance, then check thefollowing per-database metrics:

After the Cloud SQL replica has caught up with the external server andthere's no replication delay on the Cloud SQL replica, connect to yourdatabase. Run the appropriate database commands to make sure that the contentsare as expected when compared with the external server. Retain your externalserver until the necessary validations are done.

Set up a cascading replica

After migration, you can create cascading read replicas under your Cloud SQL replica beforepromoting the Cloud SQL replica.

To create a cascading replica, run the following commands:

Promote the replica

Promote your replica by following these steps:

1. Promote the replica to a primary instance.
2. Add read replicas to your instance.
3. Optional:Configure your instance for high availability (HA).To prevent additional downtime, you can enable HA whilesetting up a replica by settingAVAILABILITY_TYPE toREGIONAL.

Limitations

• If you install extensions on your external source databases that Cloud SQL doesn't support, then when you migrate the databases to a destination instance, Cloud SQL won't migrate these extensions. To ensure a smooth migration, verify that no objects or applications reference the extensions. Before proceeding with the migration, we recommend removing the extensions along with any references from the source databases.

  For more information about the extensions that Cloud SQL supports, seeConfigure PostgreSQL extensions.

• If you install thepg_cron extension on your external source databases, then when you migrate the databases to a destination instance, Cloud SQL doesn't migrate the extension or anycron settings associated with the extension. After you migrate the databases and promote the replica, Google recommends that you re-enable thepg_cron extension on each migrated database.

• You can't migrate data from version 11 of a source Microsoft Azure server toversion 11 of a destination Cloud SQL for PostgreSQL instance. To resolve this,migrate the data to a Cloud SQL instance that has a version of 12 orlater.

What's next

• Learn how to use amanaged import to set up replication from external databases.