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 user that creates a subscription becomes the owner of the 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.

To be able to create a subscription, you must have the privileges of the the pg_create_subscription role, as well as CREATE privileges on the current database.

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] [, ... ] )

When specifying a parameter of type boolean, the = value part can be omitted, which is equivalent to specifying TRUE.

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. See Section 31.2.3 for examples. 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.

When using a subscription parameter combination of copy_data = true and origin = NONE, the initial sync table data is copied directly from the publisher, meaning that knowledge of the true origin of that data is not possible. If the publisher also has subscriptions then the copied table data might have originated from further upstream. This scenario is detected and a WARNING is logged to the user, but the warning is only an indication of a potential problem; it is the user's responsibility to make the necessary checks to ensure the copied data origins are really as wanted or not.

To find which tables might potentially include non-local origins (due to other subscriptions created on the publisher) try this SQL query:

# substitute <pub-names> below with your publication name(s) to be queried
SELECT DISTINCT PT.schemaname, PT.tablename
FROM pg_publication_tables PT,


     pg_subscription_rel PS


     JOIN pg_class C ON (C.oid = PS.srrelid)


     JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE N.nspname = PT.schemaname AND


      C.relname = PT.tablename AND


      PT.pubname IN (<pub-names>);

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))