Contents
CREATE SUBSCRIPTION
An SQL command for creating 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
- PostgreSQL 16
- parameter
originadded (commit 36628396)
- parameter
- PostgreSQL 15
- parameter
two_phaseadded (commit a8fd13ca) - parameter
disable_on_erroradded (commit 705e20f8) - a
WARNINGwill now be emitted if the target publication does not exist (commit 8f2e2bbf)
- parameter
- PostgreSQL 14
- PostgreSQL 10
- added (commit 665d1fad)
Examples
Basic execution example for CREATE SUBSCRIPTION:
postgres=# 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 incorrect conninfo string:
postgres=# CREATE SUBSCRIPTION dummy_subscription
CONNECTION 'host=foo.bar dbname=testdb user=produser'
PUBLICATION dummy_publication;
ERROR: could not connect to the publisher: could not translate host name "foo.bar" to address: Name or service not known
Attempting to create a subscription with an invalid conninfo string:
postgres=# CREATE SUBSCRIPTION dummy_subscription
CONNECTION 'hostname=foo.bar dbname=testdb user=produser'
PUBLICATION dummy_publication;
ERROR: invalid connection string syntax: invalid connection option "hostname"
References
- PostgreSQL documentation: CREATE SUBSCRIPTION