You can uselogical replication and decodingfeatures in Cloud SQL for PostgreSQL. Thesefeatures enable logical replication workflows and change data capture (CDC)workflows.

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

Introduction

When PostgreSQL performs logical replication, the changes that are streamed toreplicas are extracted from the WAL logs using logical decoding. The decodedchanges are independent of the underlying physical storage format.The changes reflect only thechanges in data from a SQL level, in terms of INSERTs, UPDATEs, and DELETEs.This independence from the storage layer provides great flexibility and enablesa wide array of functionality by consumers of the change streams.

Logical replication is the flagship feature built on logical decoding.

Unlike PostgreSQL'sphysical replicationfeature, which requires both the source and destinationdatabases to be the same version, logical replication enables replication acrossPostgreSQL major versions. Logical replication in Cloud SQL is supported by thepglogical extension, available in all PostgreSQL versions, and PostgreSQL'snative logical replication, added in PostgreSQL 10.

The format in which changes are streamed can be configured with differentplugins. This allows for flexiblechange data capture(CDC) architectures.For example, thewal2jsonextension allows streaming all changes in a database to a consumer, formatted asJSON. Cloud SQL supports the built-inpgoutput decoder, thetest_decodingcontrib module, andwal2json. Cloud SQL currently supports bothwal2json variants of JSON output -format-version 1 which encodes the whole transaction as a single JSON object andformat-version 2 which outputs one JSON object per command. These plugins enablereplication to non-PostgreSQL databases.

Configure your PostgreSQL instance

If you plan to use the pglogical extension, pglogical must be added toshared_preload_libraries. Since Cloud SQL does not allow direct modificationof this flag, pglogical is enabled by settingcloudsql.enable_pglogical toon. (On a VM, sudo apt-get install postgresql-13-pglogical ) and restart thedatabase.

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

Enable network connectivity

Ensure that your primary instances accept connections from the replica instance.

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.

gcloudsqlinstancesdescribe[REPLICA_NAME]--format="default(ipAddresses)"

Allow connections

If the primary instance is a Cloud SQL instance, you can allow access from thereplica's outgoing IP address byadding it as an authorized network.

If your primary instance isn't running in Cloud SQL, and is running PostgreSQL9.6 or earlier, you must ensure that the instance'spg_hba.conffile is set to accept replication connections. Add thefollowing line to that file, usingall all for initialtesting only. For more security, limit users and IP addresses to just thoserequired, as in this example:

hostreplicationallallmd5

For additional information, seeThe pg_hba.conf File.

Create a replication user

To use logical decoding features, you create a PostgreSQL user with theREPLICATION attribute.

CREATEUSERreplication_userWITHREPLICATIONINROLEcloudsqlsuperuserLOGINPASSWORD'secret';

ALTERUSERexisting_userWITHREPLICATION;

When logical decoding is used, a background process on the primary PostgreSQLinstance transforms WAL changes into logical changes, using the selecteddecoding plugin, and relays them to a consumer (which could even be anon-PostgreSQL instance). This background process is called a WAL sender.The number of concurrent WAL senders that can be active in a PostgreSQLinstance is limited by themax_wal_sendersflag. This flag defaults to10 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 allconsumers, PostgreSQL useslogical replication slotsto track which data was sent to which consumer(and physicalreplication slotsfor read replicas). The number ofreplication slots that you can create for a PostgreSQL instance is limited by themax_replication_slotsflag. This flagdefaults to 10 and its limit grows with the memory of your Cloud SQLinstance, 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.

There generally is one replication slot and WAL sender per consumer, so theseflags should be set to roughly equal values. However, PostgreSQL recommendsproviding a small buffer formax_wal_senders to handle when connectionsunexpectedly die and new connections are made. Physical replication, as used byCloud SQL read replicas, also uses a replication slot and WAL sender, so countthose when calculating how many of each resource you need.

PostgreSQL native logical replication and pglogical require additionalbackground processes to run, both on the primary and replica instances. Thenumber of background processes that can run is limited by themax_worker_processesflag. The default is eightand its limit grows linearly with the memory of your Cloud SQL instance,allowing two additional processes per GB of memory. The exact number of workerprocesses used with these approaches is explained in their respective sections.

If this flag is set too low, and replication fails with the error messageworker registration failed in your logs, you likely need toincrease themax_worker_processes setting.

Note that WAL sendersdo not count as worker processes. Workers spawned forparallel query execution do count, so if the value ofmax_worker_processes isset too low, you may experience poor performancebecause PostgreSQL cannot leverage parallel query execution.

Using thepg_ls_waldir () function,you can determine the WAL disk usage. This function is restricted tocloudsqlsuperuser users such as the default admin userpostgres. Thisfunction is only available in PostgreSQL version 10 and above.

To calculate the total WAL disk usage:

postgres=>select*frompg_ls_waldir();

(2 rows)

postgres=>selectpg_size_pretty(sum(size))as"Total WAL disk usage"frompg_ls_waldir();

(1 row)

Set up logical replication with an external replica

SeeConfiguring external replicas for a complete example usingpglogical and logical decoding.

Set up logical replication with pglogical

To set up logical replication with pglogical, logical decoding must be enabledon the primary instance. Setcloudsql.logical_decoding=on on the Cloud SQLinstance, orwal_level=logical on an external instance. Additionally,pglogical must be enabled on both the primary and replica instance; setcloudsql.enable_pglogical=on on a Cloud SQL instance, or add pglogical toshared_preload_libraries on an external instance. Note that changing theseflags requires a restart of both the primary and replica instances.

If you encounter issues with these steps, seeTroubleshoot pglogical.

Create a user with replication privileges

You need a user withreplication privilegesand thecloudsqlsuperuser role onboth the primary and replica instances when using pglogical. Any commandsdescribed below should be executed by that user.

Install the pglogical extension

You need to install the pglogical extension on both the primary and replicainstances. On the primary, the replication user (that is, the user connecting tothe database) must install it.

CREATEEXTENSIONpglogical;

Create a pglogical node on each instance

A pglogicalnode represents a physical PostgreSQL instance, and storesconnection details for that instance. Both the primary and the replica instancemust register themselves as nodes:

source-instance$SELECTpglogical.create_node(node_name:='primary',dsn:='host=<primary-ip> port=5432 dbname=postgres user=replication_user password=secret');dest-instance$SELECTpglogical.create_node(node_name:='replica',dsn:='host=<replica-ip> port=5432 dbname=postgres user=replication_user password=secret');

Create a table with data to replicate

The pglogical extension allows for replicating only a subset of tables to adestination. As an example, we'll create a dummy table on the primary instanceand populate it with some data to test:

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

The table must also be created on the replica instance.

Add the table to a replication set

To support replicating different sets of data to different destinations,pglogical has the concept of a replication set. We can add our test table to thedefault replication set.

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

Create the pglogical subscription

Create the pglogical subscription on the destination instance by providingconnection details to the primary instance.

SELECTpglogical.create_subscription(subscription_name:='test_sub',provider_dsn:='host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword');SELECT*FROMpglogical.show_subscription_status('test_sub');

If the status appears as "replicating", then the setup is successful. Query thereplica_test table to ensure that data has been replicated. Insert and modifyrecords on the primary instance and verify that they then appear on the replicainstance.

On the primary, query thepg_replication_slots table to see the replicationslot created by the subscription.

Cleanup

After your test is successful, drop the subscription on the replica usingpglogical.drop_subscription('test_sub'). Verify that the replication slot isalso dropped on the primary. Otherwise, WAL segments continue to accumulate onthe replica instance.

For more about replication sets, partial data replication, DDL replication,other advanced configuration and limitations, see thepglogical documentation.

Resource usage

The pglogical extension runs multiple background processes that count towardsthemax_worker_processes limit.

In the steady state, it runs one "supervisor" process when enabled, one"manager" process per PostgreSQL database that has installed the extension (forexample, there could beD of these), and one "apply" process per pglogicalsubscription on the replica instance (for example, there could beS of these).The extension, however, may spawn additional worker processes when performing aninitial sync, and actually does spawn "manager" processes forevery databasein the instance, but if the database doesn't have the extension installed, itexits immediately.

Therefore, allocate a handful more worker processes thanneeded in the steady state. Worker processes are used by PostgreSQL for otherpurposes, such as parallel query processing. Ifmax_worker_processes is settoo low, replication may silently fail, or PostgreSQL may be unable to performparallel query processing.

In summary, these settings are recommended:

max_worker_processes>=1+D+8(onthesourceinstance)>=1+D+S+8(onthedestinationinstance)max_wal_senders>=S+2(onthesourceinstance)max_replication_slots>=S(onthesourceinstance)

Troubleshoot pglogical

Unable to create the pglogical extension

When trying to install the pglogical extension, you may see the error:

ERROR:pglogicalisnotinshared_preload_libraries

When you install pglogical on a Cloud SQL instance, ensure that you have setcloudsql.enable_pglogical=on. If using an external instance, add it directlyto theshared_preload_libraries flag, for example,shared_preload_libraries=pg_stat_statements,pglogical.These modifications require a restart of the primary instance.

Unable to create pglogical subscription

When creating a subscription, pglogical first checks that it can use theconnection details to connect to the instance. It first tries to create aregular connection, and if this fails, an error occurs:ERROR: could notconnect to the postgresql server.

If this error occurs, ensure that the primary instance is configured to allowconnections from the replica instance, and ensure that the connection detailsyou provided are correct. Additional details are provided about why PostgreSQLwas unable to establish a connection.

After creating a regular connection, pglogical tries to make a specialreplication connection. In PostgreSQL 9.6 and earlier, this type of connectioncould have a different authentication configuration. You need to update thepg_hba.conf file on the source instance if you see this error:ERROR: couldnot connect to the postgresql server in replication mode.

Thepg_hba.conf file used by Cloud SQL already has the necessary changes;this error only occurs when connecting to an external instance thatisn't managed by Cloud SQL.

Alternatively, the replication mode connection may fail if the source instancedoes not allow enough WAL senders. If you seeFATAL: number of requestedstandby connections exceeds max_wal_senders, then increasemax_wal_senders onthe primary instance.

pglogical subscription is down

A pglogical subscription may fail to replicate. To address this issue, firstensure that a background process is running on the replica instance. Querypg_stat_activity to verify that apglogical apply process is running. Ifnot, check the logs on the destination node. If you see the messageworkerregistration failed, you can increase themax_worker_processes setting.

Then, ensure a replication slot was created on the primary instance. On thereplica instance, the row inpglogical.subscription contains the name of theslot the subscription tries to create, and on the primary instance you can querypg_replication_slots to verify that the slot was successfully created.

If no replication slot was created, check the logs on the primary instance.

An error ofERROR: logical decoding requires wal_level >= logical impliesthat thewal_level flag wasn't set tological. Resolve this bysettingcloudsql.logical_decoding=on, on the primary instance if it is aCloud SQL instance.

Alternatively, if the instance is an external instance, setwal_level=logical.

Otherwise, you may seeERROR: all replication slots are in use, along withthe helpfulHINT: Free one or increase max_replication_slots.

Set up native PostgreSQL logical replication

Since PostgreSQL 10, PostgreSQL supports native built-in logical replication. Toset up native logical replication, logical decoding must be enabled on theprimary instance, by settingcloudsql.logical_decoding=on on a Cloud SQLinstance, orwal_level=logical on an external instance. Note that modificationof these flags requires a restart of the primary instance.

Ensure that your instances are properly configured(for network connectivity, etc.) by reviewing the sections inConfigure your PostgreSQL instance.This page provides steps for a proof-of-concept.If you encounter any issues while following steps in those sections, seeTroubleshoot pglogical.For more information, seeLogical Replication in the PostgreSQL documentation.

Create a table with data to replicate

Native PostgreSQL logical replication supports an entire database or justindividual tables. As an example, we'll create a dummy table on the primaryinstance and populate it with data to test.

CREATETABLEnative_test(idSERIALPRIMARYKEY,datatext);INSERTINTOnative_test(data)VALUES('apple'),('banana'),('cherry');

The table must also be created on the replica instance.

Create a publication on the primary instance

Native PostgreSQL logical replication deals with publishers and subscribers.Create a publication of the data innative_test:

CREATEPUBLICATIONpubFORTABLEnative_test;

Create a subscription on the replica instance

Here is an example of creating a subscription on the replica instance:

CREATESUBSCRIPTIONsubCONNECTION'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'PUBLICATIONpub;

Creating the subscription on the replica instance requires thecloudsqlsuperuser role. After creating the subscription, query thenative_test table to verify that the data has appeared in the replicainstance.

On the primary, you can query thepg_replication_slots table to see thereplication slot created by the subscription.

Cleanup

Once your test is successful, drop the subscription on the replica usingDROPSUBSCRIPTION sub;. Verify that the replication slot is also dropped on theprimary. Otherwise WAL segments continue to accumulate on the primary instance.

Limitations on native PostgreSQL logical replication

Access to thesubconninfo column of thepg_subscriptionsystem table is unavailable.

Runningpg_dump cannot dump information about subscriptions because it checkswhether the connecting user has superuser permissions.

Receive decoded WAL changes for change data capture (CDC)

As an alternative use case for CDC, logical decoding can stream changes from aPostgreSQL instance. The standard tool used for this ispg_recvlogical.

You can use thepg_recvlogical tool to create a replication slot and to streamchanges tracked by that slot. The format of the changes is determined by yourchoice of the decoding plugin. You can use:

• wal2json, to streamchanges formatted as JSON, or

• test_decoding,to stream changes formatted with a barebones text format

Create replication slot

To create a replication slot, run:

pg_recvlogical-h<instance_ip>\-U<replication_user>\-p5432\-dpostgres\--slot test_slot \--create-slot \-P<decoder_plugin>

Stream changes

In one Cloud Shell terminal, run:

pg_recvlogical-h<instance_ip>\-U<replication_user>\-p5432\-dpostgres\--slot test_slot \--start \-f-

While in another Cloud Shell terminal, connect to your database and run thefollowing commands:

CREATETABLEcdc_test(idSERIALPRIMARYKEY,datatext);INSERTINTOcdc_test(data)VALUES('apple','banana');UPDATEcdc_testSETdata='cherry'WHEREid=2;DELETEFROMcdc_testWHEREid=1;DROPTABLEcdc_test;

If you are using thewal2json decoder plugin, output similar to the followingis displayed in the first Cloud Shell terminal:

{"change":[]}{"change":[{"kind":"insert","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[1,"apple"]},{"kind":"insert","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[2,"banana"]}]}{"change":[{"kind":"update","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[2,"cherry"],"oldkeys":{"keynames":["id"],"keytypes":["integer"],"keyvalues":[2]}}]}{"change":[{"kind":"delete","schema":"public","table":"cdc_test","oldkeys":{"keynames":["id"],"keytypes":["integer"],"keyvalues":[1]}}]}{"change":[]}

If you are using thetest_decoding decoder plugin, output similar to thefollowing is displayed in the first Cloud Shell terminal:

BEGIN19460COMMIT19460BEGIN19461tablepublic.cdc_test:INSERT:id[integer]:1data[text]:'apple'tablepublic.cdc_test:INSERT:id[integer]:2data[text]:'banana'COMMIT19461BEGIN19462tablepublic.cdc_test:UPDATE:id[integer]:2data[text]:'cherry'COMMIT19462BEGIN19463tablepublic.cdc_test:DELETE:id[integer]:1COMMIT19463BEGIN19464COMMIT19464

(Your transaction IDs may differ.)

Cleanup

After you complete your testing, drop the replication slot you created byrunning:

pg_recvlogical-h<instance_ip>\-U<replication_user>\-p5432\-dpostgres\--slot test_slot \--drop-slot

Notes and limitations

The notes and limitations in this section apply to the logical replication anddecoding features of Cloud SQL for PostgreSQL.

• Thepglogical extension doesn't work in instances that haveconnector enforcement enabled. This limitation doesn't apply to the instances that haveprivate service access configured.

• When you restore an instance that hascloudsql.logical_decoding orcloudsql.enable_pglogical enabled, and is currently acting as a publisherfor logical replication, you must disable replication to all target instancesfirst. Otherwise, restoring to the instance fails with an error, but currentlythe details of the error are not visible.

• When you restore a backup of an instance that hadcloudsql.logical_decodingorcloudsql.enable_pglogical enabled (at the time of the backup), and youare restoring it to a new instance, the replication state is not restored tothe new instance. You must reconfigure replication manually afterwards.

• On a Cloud SQL instance with one or more Cloud SQL read replicas (usingphysical replication), if you enablecloudsql.logical_decoding orcloudsql.enable_pglogical, those flags are also enabled on the readreplica.

  • For Cloud SQL for PostgreSQL versions 15 and earlier, Cloud SQL read replica instances can't act as publishers for logicalreplication because PostgreSQL doesn't support logical decoding on read replicas for those older versions. However, to ensure that the instances can serve as a replacement for the primary instance in the event of a promotion, the logical flags are still enabled on the read replica instances for these earlier versions.

  • Starting with Cloud SQL for PostgreSQL version 16, Cloud SQL read replica instances can act as publishers for logicalreplication, if the primary instance has the logical flags set. The logical subscriber can be a Cloud SQL instance or an external instance. However, row deletion and vacuum operations on the primary might delete tuples that are still needed by logical decoding on the read replica. In those cases, the logical replication slot on the read replica isinvalidated to avoid inconsistencies.

  • Enablingcloudsql.logical_decoding orcloudsql.enable_pglogical on theprimary instance causes the flags to be enabled on all read replicas, andthis causes the primary and read replicas to be restarted in closesuccession. To avoid this situation and control when each instance isrestarted, you can (1) set the flags on each read replica in turn, and onlythen (2) set the flags on the primary instance.

  • Disablingcloudsql.logical_decoding orcloudsql.enable_pglogical on theprimary instance doesnot cause the flags to be disabled on all readreplicas. To disable the flags across the instances, you must perform theinverse of the steps described above: (1) disable the flags on the primaryinstance and then (2) disable the flags on each read replica in turn.