Contents
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
- PostgreSQL 15
- parameter
two_phase
added (commit a8fd13ca) - parameter
disable_on_error
added (commit 705e20f8) - a
WARNING
will 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
:
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
References
- PostgreSQL documentation: CREATE SUBSCRIPTION