Text of docs.cloud.google.com › sql › docs › postgres › replication › configure-logical-replica… as Styx crawled it on Nov 19, 2026. The page may have changed since. Only its words are kept, without pictures or layout, and only the first part of a long page.

Open the live page

Set up logical replication and decoding | Cloud SQL for PostgreSQL | Google Cloud Documentation

Skip to main content Console English Deutsch Español Español – América Latina Français Indonesia Italiano Português Português – Brasil עברית 中文 – 简体 中文 – 繁體 日本語 한국어 Sign in Cloud SQL PostgreSQL Start free Home Documentation Databases Cloud SQL PostgreSQL Guides Set up logical replication and decoding Stay organized with collections Save and categorize content based on your preferences.

MySQL | PostgreSQL | SQL Server You can use logical replication and decoding features in Cloud SQL for PostgreSQL. These features enable logical replication workflows and change data capture (CDC) workflows. For general information about replication, see About replication in Cloud SQL . Introduction When PostgreSQL performs logical replication, the changes that are streamed to replicas are extracted from the WAL logs using logical decoding.

The decoded changes are independent of the underlying physical storage format. The changes reflect only the changes in data from a SQL level, in terms of INSERTs, UPDATEs, and DELETEs. This independence from the storage layer provides great flexibility and enables a wide array of functionality by consumers of the change streams. Logical replication is the flagship feature built on logical decoding. Unlike PostgreSQL's physical replication feature, which requires both the source and destination databases to be the same version, logical replication enables replication across PostgreSQL major versions.

Logical replication in Cloud SQL is supported by the pglogical extension, available in all PostgreSQL versions, and PostgreSQL's native logical replication, added in PostgreSQL 10. The format in which changes are streamed can be configured with different plugins. This allows for flexible change data capture (CDC) architectures. For example, the wal2json extension allows streaming all changes in a database to a consumer, formatted as JSON.

Cloud SQL supports the built-in pgoutput decoder, the test_decoding contrib module, and wal2json . Cloud SQL currently supports both wal2json variants of JSON output - format-version 1 which encodes the whole transaction as a single JSON object and format-version 2 which outputs one JSON object per command. These plugins enable replication to non-PostgreSQL databases. Configure your PostgreSQL instance PostgreSQL supports logical decoding by writing additional information to its write-ahead log (WAL).

In Cloud SQL, you enable this feature by setting the cloudsql.logical_decoding flag to on . This setting is different from the setting used in standard PostgreSQL. If you change an external PostgreSQL instance, you enable this feature by setting the wal_level configuration parameter to logical . If you plan to use the pglogical extension, pglogical must be added to shared_preload_libraries . Since Cloud SQL does not allow direct modification of this flag, pglogical is enabled by setting cloudsql.enable_pglogical to on . (On a VM, sudo apt-get install postgresql-13-pglogical ) and restart the database.

If you are using pglogical to replicate between two PostgreSQL instances, logical decoding only needs to be enabled on the primary instance, and not on the replica instance (unless that instance itself is a primary for other replicas). However, the pglogical extension must be enabled on both instances. For examples of how the terms "primary" and "replica" are used and their meanings, see About replication in Cloud SQL .

Enable network connectivity Ensure that your primary instances accept connections from the replica instance. Primary Replica Configuration Cloud SQL (public IP) Cloud SQL (public IP) Add the replica's outgoing IP address to the primary's authorized networks . Cloud SQL (private IP) Cloud SQL (private IP) If both instances are in the same Google Cloud project, then add the allocated IP range of the replica's VPC network to the authorized network that hosts the instances.

To find the allocated IP range in the Google Cloud console: Navigate to the VPC networks page. Select the VPC network you're using. Select the Private service connection tab. Select the Allocated IP ranges tab. External Cloud SQL You can use the Database Migration Service . Cloud SQL External See Configure external replicas for more information. Get the outgoing IP address of a replica instance If the replica instance is a Cloud SQL instance and has a public IP address, perform the following steps to get its outgoing IP address.

Console Open the Cloud SQL Instances page . Next to the Cloud SQL replica's public IP address, hover over the More info tool tip and retrieve the outgoing IP address. Note that the outgoing IP address is not the IP address displayed in the main listing for the replica in the Google Cloud console. If the replica instance isn't a Cloud SQL instance, refer to the relevant documentation. For more information about how to get an instance's public IP address, see Get the Cloud SQL replica's outgoing IP address . gcloud You can use the following gcloud command: gcloud sql instances describe [ REPLICA_NAME ] --format="default(ipAddresses)" Allow connections If the primary instance is a Cloud SQL instance, you can allow access from the replica's outgoing IP address by adding it as an authorized network .

Enable replication connections for PostgreSQL 9.6 and earlier If your primary instance isn't running in Cloud SQL, and is running PostgreSQL 9.6 or earlier, you must ensure that the instance's pg_hba.conf file is set to accept replication connections. Add the following line to that file, using all all for initial testing only. For more security, limit users and IP addresses to just those required, as in this example: host replication all all md5 For additional information, see The pg_hba.conf File .

Create a replication user To use logical decoding features, you create a PostgreSQL user with the REPLICATION attribute. Examples CREATE USER replication_user WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secret' ; Alternatively, you can set this attribute on an existing user: ALTER USER existing_user WITH REPLICATION ; PostgreSQL resources When logical decoding is used, a background process on the primary PostgreSQL instance transforms WAL changes into logical changes, using the selected decoding plugin, and relays them to a consumer (which could even be a non-PostgreSQL instance).

This background process is called a WAL sender. The number of concurrent WAL senders that can be active in a PostgreSQL instance is limited by the max_wal_senders flag. This flag defaults to 10 and its limit grows linearly with the memory of your Cloud SQL instance, allowing 8 WAL senders per GB of memory. To ensure that WAL segments are not discarded before being sent to all consumers, PostgreSQL uses logical replication slots to track which data was sent to which consumer (and physical replication slots for read replicas).

The number of replication slots that you can create for a PostgreSQL instance is limited by the max_replication_slots flag. This flag defaults to 10 and its limit grows with the memory of your Cloud SQL instance, allowing between 2 and 8 replication slots per GB of memory. The following table shows the relationship between the maximum memory of a Cloud SQL instance and the maximum replication slots for the instance. Maximum memory (GB) Maximum replication slots 4 10 16 32 32 128 64 256 128 512 256 1024 512 2048 512+ 4096 There generally is one replication slot and WAL sender per consumer, so these flags should be set to roughly equal values.

However, PostgreSQL recommends providing a small buffer for max_wal_senders to handle when connections unexpectedly die and new connections are made. Physical replication, as used by Cloud SQL read replicas, also uses a replication slot and WAL sender, so count those when calculating how many of each resource you need. PostgreSQL native logical replication and pglogical require additional background processes to run, bo

The rest of this page is on the live site.