NAME¶

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 logical-replication subscription. The subscription name must be distinct from the name of any existing subscription in the current database.

A subscription represents a replication connection to the publisher. Hence, in addition to adding definitions in the local catalogs, this command normally creates a replication slot on the publisher.

A logical replication worker will be started to replicate data for the new subscription at the commit of the transaction where this command is run, unless the subscription is initially disabled.

Additional information about subscriptions and logical replication as a whole is available at Section 31.2 and Chapter 31.

PARAMETERS¶

subscription_name

CONNECTION 'conninfo'

PUBLICATION publication_name [, ...]

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

NOTES¶

See Section 31.9 for details on how to configure access control between the subscription and the publication instance.

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

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

If any table in the publication has a WHERE clause, rows for which the expression evaluates to false or null will not be published. If the subscription has several publications in which the same table has been published with different WHERE clauses, a row will be published if any of the expressions (referring to that publish operation) are satisfied. In the case of different WHERE clauses, if one of the publications has no WHERE clause (referring to that publish operation) or the publication is declared as FOR ALL TABLES or FOR TABLES IN SCHEMA, rows are always published regardless of the definition of the other expressions. If the subscriber is a PostgreSQL version before 15, then any row filtering is ignored during the initial data synchronization phase. For this case, the user might want to consider deleting any initially copied data that would be incompatible with subsequent filtering. Because initial data synchronization does not take into account the publication publish parameter when copying existing table data, some rows may be copied that would not be replicated using DML. See Section 31.2.2 for examples.

Subscriptions having several publications in which the same table has been published with different column lists are not supported.

We allow non-existent publications to be specified so that users can add those later. This means pg_subscription can have non-existent publications.

EXAMPLES¶

Create a subscription to a remote server that replicates tables in the publications mypublication and insert_only and starts replicating 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 a remote server that replicates tables in the insert_only publication and does not start replicating until enabled at a later time.

CREATE SUBSCRIPTION mysub


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


        PUBLICATION insert_only


               WITH (enabled = false);

COMPATIBILITY¶

CREATE SUBSCRIPTION is a PostgreSQL extension.

SEE ALSO¶

ALTER SUBSCRIPTION (ALTER_SUBSCRIPTION(7)), DROP SUBSCRIPTION (DROP_SUBSCRIPTION(7)), CREATE PUBLICATION (CREATE_PUBLICATION(7)), ALTER PUBLICATION (ALTER_PUBLICATION(7))