CREATE SUBSCRIPTION

A DDL command for defining a subscription

CREATE SUBSCRIPTION is a DDL command for defining a logical replication subscription.

CREATE SUBSCRIPTION was added in PostgreSQL 10.

Usage

A corresponding publication does not need to exist on the provider. Note that by default an active replication slot will be created.

Change history

Examples

Basic execution example for CREATE SUBSCRIPTION:

subtestdb=# CREATE SUBSCRIPTION test_subscription
  CONNECTION 'host=node1 dbname=testdb user=produser'
  PUBLICATION test_publication;
NOTICE:  created replication slot "test_subscription" on publisher
CREATE SUBSCRIPTION

List available subscriptions in psql:

postgres=# \dRs
                    List of subscriptions
       Name        |  Owner   | Enabled |    Publication     
-------------------+----------+---------+--------------------
 test_subscription | postgres | t       | {test_publication}
(1 row)

Attempting to create a duplicate subscription:

postgres=# CREATE SUBSCRIPTION test_subscription
             CONNECTION 'host=node1 dbname=testdb user=produser'
             PUBLICATION test_publication;
ERROR:  subscription "test_subscription" already exists

Attempting to create a subscription with an invalid conninfo string:

postgres=# CREATE SUBSCRIPTION fake_subscription
             CONNECTION 'host=foo.bar dbname=testdb user=produser'
             PUBLICATION fake_publication;
ERROR:  could not connect to the publisher: could not translate host name "foo.bar" to address: Name or service not known

Categories

DDL, Logical replication, SQL command

See also

ALTER SUBSCRIPTION, DROP SUBSCRIPTION, CREATE PUBLICATION