Add a new subscription with CREATE SUBSCRIPTION - PolarDB

CREATE SUBSCRIPTION adds a new subscription to the current database for logical replication.

Description

The subscription name must be distinct from any existing subscription in the database. Creating a subscription also creates a replication slot on the publisher. A logical replication worker starts replicating data for the new subscription at the commit of the transaction in which this command runs.

Synopsis

CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

Parameters

`subscription_name`

The name of the new subscription.

`CONNECTION 'conninfo'`

The connection string to the publisher.

`PUBLICATION publication_name`

The names of the publications on the publisher to subscribe to. Specify multiple publication names as a comma-separated list.

`WITH ( subscription_parameter [= value] [, ... ] )`

Optional parameters for the subscription, grouped by when they take effect.

Parameters that control subscription creation

`connect` (boolean)

Specifies whether CREATE SUBSCRIPTION connects to the publisher.

Setting this to false changes the defaults of enabled, create_slot, and copy_data to false. Combining connect = false with create_slot = true, enabled = true, or copy_data = true is not allowed.

When connect = false, tables are not subscribed. Run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe tables after enabling the subscription.

`create_slot` (boolean)

Specifies whether the command creates a replication slot on the publisher. The default is true.

`enabled` (boolean)

Specifies whether the subscription actively replicates, or is only set up but not yet started. The default is true.

`slot_name` (string)

The name of the replication slot to use. By default, the slot name matches the subscription name.

Set slot_name to NONE to create the subscription without an associated replication slot. When slot_name = NONE, enabled and create_slot must also be set to false. Use this option when you plan to create the replication slot manually later.

Parameters that control replication behavior

`copy_data` (boolean)

Specifies whether to copy existing data in the subscribed publications when replication starts. The default is true.

`synchronous_commit` (enum)

Overrides the synchronous_commit setting for the subscription's apply worker processes. The default is off.

The off setting is safe for logical replication: if the subscriber loses transactions due to missing synchronization, the publisher resends the data.

For synchronous logical replication, logical replication workers report write and flush positions to the publisher, which waits for the actual flush. Setting synchronous_commit = off in this case may increase COMMIT latency on the publisher.

Usage notes

Transaction block restriction

When creating a replication slot (the default behavior), CREATE SUBSCRIPTION cannot run inside a transaction block.

Same database cluster

Subscribing to the same database cluster—for example, to replicate between databases in the same cluster—only succeeds if the replication slot is not created as part of the same command. If it is, CREATE SUBSCRIPTION hangs.

To work around this:

Create the replication slot separately using pg_create_logical_replication_slot with the plugin name pgoutput.
Create the subscription with create_slot = false.

This is an implementation restriction that may be lifted in a future release.

Examples

Create a subscription to a remote server, subscribing to the mypublication and insert_only publications, starting replication immediately on commit:

CREATE SUBSCRIPTION mysub
    CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
    PUBLICATION mypublication, insert_only;

Create a subscription to the insert_only publication without starting replication immediately. Replication begins after you enable the subscription:

CREATE SUBSCRIPTION mysub
    CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
    PUBLICATION insert_only
    WITH (enabled = false);

PolarDB:CREATE SUBSCRIPTION