A subscription is the downstream side of logical replication. The node where a subscription is defined is referred to as the subscriber. A subscription defines the connection to another database and set of publications (one or more) to which it wants to subscribe.
The subscriber database behaves in the same way as any other PostgreSQL instance and can be used as a publisher for other databases by defining its own publications.
A subscriber node may have multiple subscriptions if desired. It is possible to define multiple subscriptions between a single publisher-subscriber pair, in which case care must be taken to ensure that the subscribed publication objects don't overlap.
Each subscription will receive changes via one replication slot (see Section 27.2.6). Additional replication slots may be required for the initial data synchronization of pre-existing table data and those will be dropped at the end of data synchronization.
A logical replication subscription can be a standby for synchronous
replication (see Section 27.2.8). The standby
name is by default the subscription name. An alternative name can be
specified as application_name in the connection
information of the subscription.
Subscriptions are dumped by pg_dump if the current user
is a superuser. Otherwise a warning is written and subscriptions are
skipped, because non-superusers cannot read all subscription information
from the pg_subscription catalog.
The subscription is added using CREATE SUBSCRIPTION and
can be stopped/resumed at any time using the
ALTER SUBSCRIPTION command and removed using
DROP SUBSCRIPTION.
When a subscription is dropped and recreated, the synchronization information is lost. This means that the data has to be resynchronized afterwards.
The schema definitions are not replicated, and the published tables must exist on the subscriber. Only regular tables may be the target of replication. For example, you can't replicate to a view.
The tables are matched between the publisher and the subscriber using the fully qualified table name. Replication to differently-named tables on the subscriber is not supported.
Columns of a table are also matched by name. The order of columns in the
subscriber table does not need to match that of the publisher. The data
types of the columns do not need to match, as long as the text
representation of the data can be converted to the target type. For
example, you can replicate from a column of type integer to a
column of type bigint. The target table can also have
additional columns not provided by the published table. Any such columns
will be filled with the default value as specified in the definition of the
target table.
As mentioned earlier, each (active) subscription receives changes from a replication slot on the remote (publishing) side.
Additional table synchronization slots are normally transient, created
internally to perform initial table synchronization and dropped
automatically when they are no longer needed. These table synchronization
slots have generated names: “pg_%u_sync_%u_%llu”
(parameters: Subscription oid,
Table relid, system identifier sysid)
Normally, the remote replication slot is created automatically when the
subscription is created using CREATE SUBSCRIPTION and it
is dropped automatically when the subscription is dropped using
DROP SUBSCRIPTION. In some situations, however, it can
be useful or necessary to manipulate the subscription and the underlying
replication slot separately. Here are some scenarios:
When creating a subscription, the replication slot already exists. In
that case, the subscription can be created using
the create_slot = false option to associate with the
existing slot.
When creating a subscription, the remote host is not reachable or in an
unclear state. In that case, the subscription can be created using
the connect = false option. The remote host will then not
be contacted at all. This is what pg_dump
uses. The remote replication slot will then have to be created
manually before the subscription can be activated.
When dropping a subscription, the replication slot should be kept.
This could be useful when the subscriber database is being moved to a
different host and will be activated from there. In that case,
disassociate the slot from the subscription using ALTER
SUBSCRIPTION before attempting to drop the subscription.
When dropping a subscription, the remote host is not reachable. In
that case, disassociate the slot from the subscription
using ALTER SUBSCRIPTION before attempting to drop
the subscription. If the remote database instance no longer exists, no
further action is then necessary. If, however, the remote database
instance is just unreachable, the replication slot (and any still
remaining table synchronization slots) should then be
dropped manually; otherwise it/they would continue to reserve WAL and might
eventually cause the disk to fill up. Such cases should be carefully
investigated.
Create some test tables on the publisher.
test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); CREATE TABLE test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c)); CREATE TABLE test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e)); CREATE TABLE
Create the same tables on the subscriber.
test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); CREATE TABLE test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c)); CREATE TABLE test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e)); CREATE TABLE
Insert data to the tables at the publisher side.
test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); INSERT 0 3 test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C'); INSERT 0 3 test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii'); INSERT 0 3
Create publications for the tables. The publications pub2
and pub3a disallow some publish
operations. The publication pub3b has a row filter (see
Section 31.3).
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1; CREATE PUBLICATION test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate'); CREATE PUBLICATION test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate'); CREATE PUBLICATION test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5); CREATE PUBLICATION
Create subscriptions for the publications. The subscription
sub3 subscribes to both pub3a and
pub3b. All subscriptions will copy initial data by default.
test_sub=# CREATE SUBSCRIPTION sub1 test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1' test_sub-# PUBLICATION pub1; CREATE SUBSCRIPTION test_sub=# CREATE SUBSCRIPTION sub2 test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2' test_sub-# PUBLICATION pub2; CREATE SUBSCRIPTION test_sub=# CREATE SUBSCRIPTION sub3 test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3' test_sub-# PUBLICATION pub3a, pub3b; CREATE SUBSCRIPTION
Observe that initial table data is copied, regardless of the
publish operation of the publication.
test_sub=# SELECT * FROM t1; a | b ---+------- 1 | one 2 | two 3 | three (3 rows) test_sub=# SELECT * FROM t2; c | d ---+--- 1 | A 2 | B 3 | C (3 rows)
Furthermore, because the initial data copy ignores the publish
operation, and because publication pub3a has no row filter,
it means the copied table t3 contains all rows even when
they do not match the row filter of publication pub3b.
test_sub=# SELECT * FROM t3; e | f ---+----- 1 | i 2 | ii 3 | iii (3 rows)
Insert more data to the tables at the publisher side.
test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six'); INSERT 0 3 test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F'); INSERT 0 3 test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi'); INSERT 0 3
Now the publisher side data looks like:
test_pub=# SELECT * FROM t1; a | b ---+------- 1 | one 2 | two 3 | three 4 | four 5 | five 6 | six (6 rows) test_pub=# SELECT * FROM t2; c | d ---+--- 1 | A 2 | B 3 | C 4 | D 5 | E 6 | F (6 rows) test_pub=# SELECT * FROM t3; e | f ---+----- 1 | i 2 | ii 3 | iii 4 | iv 5 | v 6 | vi (6 rows)
Observe that during normal replication the appropriate
publish operations are used. This means publications
pub2 and pub3a will not replicate the
INSERT. Also, publication pub3b will
only replicate data that matches the row filter of pub3b.
Now the subscriber side data looks like:
test_sub=# SELECT * FROM t1; a | b ---+------- 1 | one 2 | two 3 | three 4 | four 5 | five 6 | six (6 rows) test_sub=# SELECT * FROM t2; c | d ---+--- 1 | A 2 | B 3 | C (3 rows) test_sub=# SELECT * FROM t3; e | f ---+----- 1 | i 2 | ii 3 | iii 6 | vi (4 rows)