CREATE SUBSCRIPTION

CREATE SUBSCRIPTION — Define a new subscription

Synopsis

CREATE SUBSCRIPTION subscription_name

CONNECTION 'conninfo'

PUBLICATION publication_name [, ...]

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

Description

CREATE SUBSCRIPTION adds a new subscription to the current database. The subscription name must be distinct from any existing subscriptions in the database.

A subscription represents a replication connection to a publisher. Therefore, this command not only adds a definition to the local catalog but also creates a replication slot on the publisher.

When the transaction running this command commits, a logical replication worker will be started to replicate data for the new subscription.

Parameters

subscription_name

The name of the new subscription.

CONNECTION 'conninfo'

The connection string for connecting to the publisher.

PUBLICATION publication_name

The name(s) of the publication(s) on the publisher to subscribe to.

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

This clause specifies optional parameters for the subscription. The supported parameters are:

copy_data (boolean)

Specifies whether existing data in the publications being subscribed to should be copied after replication starts. The default value is true.

create_slot (boolean)

Specifies whether the command should create a replication slot on the publisher. The default value is true.

enabled (boolean)

Specifies whether the subscription should actively replicate, or whether it should just be set up but not yet started. The default value is true.

slot_name (string)

The name of the replication slot to use. The default behavior is to use the subscription name as the slot name.

When slot_name is set to NONE, no replication slot will be associated with the subscription. This is used when a replication slot needs to be set up manually later. Such a subscription must also have enabled and create_slot set to false.

synchronous_commit (enum)

The value of this parameter overrides the synchronous_commit setting. The default value is off.

Using off for logical replication is safe: if the subscriber loses transactions due to lack of synchronization, data will be resent from the publisher.

For synchronous logical replication, a different setting may be appropriate. The logical replication worker reports write and flush positions to the publisher, and when synchronous replication is used, the publisher waits for the actual flush. This means that when a subscription is used for synchronous replication, setting the subscriber's synchronous_commit to off may increase COMMIT latency on the publisher. In this case, it is advantageous to set synchronous_commit to local or higher.

connect (boolean)

Specifies whether CREATE SUBSCRIPTION should connect to the publisher. Setting this to false will change the defaults of enabled, create_slot, and copy_data to false.

It is not allowed to set connect to false while setting enabled, create_slot, or copy_data to true.

Because setting this option to false does not establish a connection, no tables are subscribed, so when the subscription is enabled, nothing will be replicated. You need to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe to tables.

Note

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

If the replication slot is not created as part of the same command, creating a subscription that connects to the same database cluster (for example, replicating between databases in the same cluster or replicating within the same database) will only succeed. Otherwise, the CREATE SUBSCRIPTION call will hang. To accomplish this, create the replication slot separately (using the function pg_create_logical_replication_slot with the plugin name pgoutput), and create the subscription with the parameter create_slot = false. This is an implementation limitation that may be lifted in a future release.

Examples

# Create a subscription to a remote server, replicating tables from the publications mypublication and insert_only, and starting replication immediately upon commit:

CREATE SUBSCRIPTION mysub

CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'

PUBLICATION mypublication, insert_only;

# Create a subscription to a remote server, replicating tables from the insert_only publication, and not starting replication until the subscription is enabled later:

CREATE SUBSCRIPTION mysub

CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'

PUBLICATION insert_only

WITH (enabled = false);

See Also

ALTER SUBSCRIPTION, DROP SUBSCRIPTION, CREATE PUBLICATION, ALTER PUBLICATION