Logical Replication
===================

Logical replication is a method of replicating data objects and their
changes, based upon their replication identity (usually a primary key).
We use the term logical in contrast to physical replication, which uses
exact block addresses and byte-by-byte replication. PostgreSQL supports
both mechanisms concurrently, see [???](#high-availability). Logical
replication allows fine-grained control over both data replication and
security.

Logical replication uses a publish and subscribe model with one or more
subscribers subscribing to one or more publications on a publisher node.
Subscribers pull data from the publications they subscribe to and may
subsequently re-publish data to allow cascading replication or more
complex configurations.

Logical replication of a table typically starts with taking a snapshot
of the data on the publisher database and copying that to the
subscriber. Once that is done, the changes on the publisher are sent to
the subscriber as they occur in real-time. The subscriber applies the
data in the same order as the publisher so that transactional
consistency is guaranteed for publications within a single subscription.
This method of data replication is sometimes referred to as
transactional replication.

The typical use-cases for logical replication are:

-   Sending incremental changes in a single database or a subset of a
    database to subscribers as they occur.

-   Firing triggers for individual changes as they arrive on the
    subscriber.

-   Consolidating multiple databases into a single one (for example for
    analytical purposes).

-   Replicating between different major versions of PostgreSQL.

-   Replicating between PostgreSQL instances on different platforms (for
    example Linux to Windows)

-   Giving access to replicated data to different groups of users.

-   Sharing a subset of the database between multiple databases.

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. When the subscriber is treated as read-only by
application, there will be no conflicts from a single subscription. On
the other hand, if there are other writes done either by an application
or by other subscribers to the same set of tables, conflicts can arise.

Publication {#logical-replication-publication}
===========

A publication can be defined on any physical replication primary. The
node where a publication is defined is referred to as publisher. A
publication is a set of changes generated from a table or a group of
tables, and might also be described as a change set or replication set.
Each publication exists in only one database.

Publications are different from schemas and do not affect how the table
is accessed. Each table can be added to multiple publications if needed.
Publications may currently only contain tables and all tables in schema.
Objects must be added explicitly, except when a publication is created
for `ALL TABLES`.

Publications can choose to limit the changes they produce to any
combination of `INSERT`, `UPDATE`, `DELETE`, and `TRUNCATE`, similar to
how triggers are fired by particular event types. By default, all
operation types are replicated.

A published table must have a "replica identity" configured in order to
be able to replicate `UPDATE` and `DELETE` operations, so that
appropriate rows to update or delete can be identified on the subscriber
side. By default, this is the primary key, if there is one. Another
unique index (with certain additional requirements) can also be set to
be the replica identity. If the table does not have any suitable key,
then it can be set to replica identity "full", which means the entire
row becomes the key. This, however, is very inefficient and should only
be used as a fallback if no other solution is possible. If a replica
identity other than "full" is set on the publisher side, a replica
identity comprising the same or fewer columns must also be set on the
subscriber side. See [???](#sql-altertable-replica-identity) for details
on how to set the replica identity. If a table without a replica
identity is added to a publication that replicates `UPDATE` or `DELETE`
operations then subsequent `UPDATE` or `DELETE` operations will cause an
error on the publisher. `INSERT` operations can proceed regardless of
any replica identity.

Every publication can have multiple subscribers.

A publication is created using the
[`CREATE PUBLICATION`](#sql-createpublication) command and may later be
altered or dropped using corresponding commands.

The individual tables can be added and removed dynamically using
[`ALTER PUBLICATION`](#sql-alterpublication). Both the `ADD
   TABLE` and `DROP TABLE` operations are transactional; so the table
will start or stop replicating at the correct snapshot once the
transaction has committed.

Subscription {#logical-replication-subscription}
============

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
[???](#streaming-replication-slots)). 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 [???](#synchronous-replication)). 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`](#sql-createsubscription) and can be
stopped/resumed at any time using the
[`ALTER SUBSCRIPTION`](#sql-altersubscription) command and removed using
[`DROP SUBSCRIPTION`](#sql-dropsubscription).

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.

Replication Slot Management {#logical-replication-subscription-slot}
---------------------------

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.

Conflicts {#logical-replication-conflicts}
=========

Logical replication behaves similarly to normal DML operations in that
the data will be updated even if it was changed locally on the
subscriber node. If incoming data violates any constraints the
replication will stop. This is referred to as a conflict. When
replicating `UPDATE` or `DELETE` operations, missing data will not
produce a conflict and such operations will simply be skipped.

Logical replication operations are performed with the privileges of the
role which owns the subscription. Permissions failures on target tables
will cause replication conflicts, as will enabled [row-level
security](#ddl-rowsecurity) on target tables that the subscription owner
is subject to, without regard to whether any policy would ordinarily
reject the `INSERT`, `UPDATE`, `DELETE` or `TRUNCATE` which is being
replicated. This restriction on row-level security may be lifted in a
future version of PostgreSQL.

A conflict will produce an error and will stop the replication; it must
be resolved manually by the user. Details about the conflict can be
found in the subscriber\'s server log.

The resolution can be done either by changing data or permissions on the
subscriber so that it does not conflict with the incoming change or by
skipping the transaction that conflicts with the existing data. When a
conflict produces an error, the replication won\'t proceed, and the
logical replication worker will emit the following kind of message to
the subscriber\'s server log:

    ERROR:  duplicate key value violates unique constraint "test_pkey"
    DETAIL:  Key (c)=(1) already exists.
    CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378

The LSN of the transaction that contains the change violating the
constraint and the replication origin name can be found from the server
log (LSN 0/14C0378 and replication origin `pg_16395` in the above case).
The transaction that produced the conflict can be skipped by using
`ALTER SUBSCRIPTION ... SKIP` with the finish LSN (i.e., LSN 0/14C0378).
The finish LSN could be an LSN at which the transaction is committed or
prepared on the publisher. Alternatively, the transaction can also be
skipped by calling the
[`pg_replication_origin_advance()`](#pg-replication-origin-advance)
function. Before using this function, the subscription needs to be
disabled temporarily either by `ALTER SUBSCRIPTION ... DISABLE` or, the
subscription can be used with the `disable_on_error` option. Then, you
can use `pg_replication_origin_advance()` function with the `node_name`
(i.e., `pg_16395`) and the next LSN of the finish LSN (i.e., 0/14C0379).
The current position of origins can be seen in the
[pg\_replication\_origin\_status](#view-pg-replication-origin-status)
system view. Please note that skipping the whole transaction includes
skipping changes that might not violate any constraint. This can easily
make the subscriber inconsistent.

Restrictions {#logical-replication-restrictions}
============

Logical replication currently has the following restrictions or missing
functionality. These might be addressed in future releases.

-   The database schema and DDL commands are not replicated. The initial
    schema can be copied by hand using `pg_dump
         --schema-only`. Subsequent schema changes would need to be kept
    in sync manually. (Note, however, that there is no need for the
    schemas to be absolutely the same on both sides.) Logical
    replication is robust when schema definitions change in a live
    database: When the schema is changed on the publisher and replicated
    data starts arriving at the subscriber but does not fit into the
    table schema, replication will error until the schema is updated. In
    many cases, intermittent errors can be avoided by applying additive
    schema changes to the subscriber first.

-   Sequence data is not replicated. The data in serial or identity
    columns backed by sequences will of course be replicated as part of
    the table, but the sequence itself would still show the start value
    on the subscriber. If the subscriber is used as a read-only
    database, then this should typically not be a problem. If, however,
    some kind of switchover or failover to the subscriber database is
    intended, then the sequences would need to be updated to the latest
    values, either by copying the current data from the publisher
    (perhaps using `pg_dump`) or by determining a sufficiently high
    value from the tables themselves.

-   Replication of `TRUNCATE` commands is supported, but some care must
    be taken when truncating groups of tables connected by foreign keys.
    When replicating a truncate action, the subscriber will truncate the
    same group of tables that was truncated on the publisher, either
    explicitly specified or implicitly collected via `CASCADE`, minus
    tables that are not part of the subscription. This will work
    correctly if all affected tables are part of the same subscription.
    But if some tables to be truncated on the subscriber have
    foreign-key links to tables that are not part of the same (or any)
    subscription, then the application of the truncate action on the
    subscriber will fail.

-   Large objects (see [???](#largeobjects)) are not replicated. There
    is no workaround for that, other than storing data in normal tables.

-   Replication is only supported by tables, including partitioned
    tables. Attempts to replicate other types of relations, such as
    views, materialized views, or foreign tables, will result in an
    error.

-   When replicating between partitioned tables, the actual replication
    originates, by default, from the leaf partitions on the publisher,
    so partitions on the publisher must also exist on the subscriber as
    valid target tables. (They could either be leaf partitions
    themselves, or they could be further subpartitioned, or they could
    even be independent tables.) Publications can also specify that
    changes are to be replicated using the identity and schema of the
    partitioned root table instead of that of the individual leaf
    partitions in which the changes actually originate (see
    [`CREATE PUBLICATION`](#sql-createpublication)).

Architecture {#logical-replication-architecture}
============

Logical replication starts by copying a snapshot of the data on the
publisher database. Once that is done, changes on the publisher are sent
to the subscriber as they occur in real time. The subscriber applies
data in the order in which commits were made on the publisher so that
transactional consistency is guaranteed for the publications within any
single subscription.

Logical replication is built with an architecture similar to physical
streaming replication (see [???](#streaming-replication)). It is
implemented by "walsender" and "apply" processes. The walsender process
starts logical decoding (described in [???](#logicaldecoding)) of the
WAL and loads the standard logical decoding plugin (pgoutput). The
plugin transforms the changes read from WAL to the logical replication
protocol (see [???](#protocol-logical-replication)) and filters the data
according to the publication specification. The data is then
continuously transferred using the streaming replication protocol to the
apply worker, which maps the data to local tables and applies the
individual changes as they are received, in correct transactional order.

The apply process on the subscriber database always runs with
`session_replication_role` set to `replica`, which produces the usual
effects on triggers and constraints.

The logical replication apply process currently only fires row triggers,
not statement triggers. The initial table synchronization, however, is
implemented like a `COPY` command and thus fires both row and statement
triggers for `INSERT`.

Initial Snapshot {#logical-replication-snapshot}
----------------

The initial data in existing subscribed tables are snapshotted and
copied in a parallel instance of a special kind of apply process. This
process will create its own replication slot and copy the existing data.
As soon as the copy is finished the table contents will become visible
to other backends. Once existing data is copied, the worker enters
synchronization mode, which ensures that the table is brought up to a
synchronized state with the main apply process by streaming any changes
that happened during the initial data copy using standard logical
replication. During this synchronization phase, the changes are applied
and committed in the same order as they happened on the publisher. Once
synchronization is done, control of the replication of the table is
given back to the main apply process where replication continues as
normal.

Monitoring {#logical-replication-monitoring}
==========

Because logical replication is based on a similar architecture as
[physical streaming replication](#streaming-replication), the monitoring
on a publication node is similar to monitoring of a physical replication
primary (see [???](#streaming-replication-monitoring)).

The monitoring information about subscription is visible in
[pg\_stat\_subscription](#monitoring-pg-stat-subscription). This view
contains one row for every subscription worker. A subscription can have
zero or more active subscription workers depending on its state.

Normally, there is a single apply process running for an enabled
subscription. A disabled subscription or a crashed subscription will
have zero rows in this view. If the initial data synchronization of any
table is in progress, there will be additional workers for the tables
being synchronized.

Security {#logical-replication-security}
========

A user able to modify the schema of subscriber-side tables can execute
arbitrary code as the role which owns any subscription which modifies
those tables. Limit ownership and `TRIGGER` privilege on such tables to
trusted roles. Moreover, if untrusted users can create tables, use only
publications that list tables explicitly. That is to say, create a
subscription `FOR ALL TABLES` or `FOR ALL TABLES IN SCHEMA` only when
superusers trust every user permitted to create a non-temp table on the
publisher or the subscriber.

The role used for the replication connection must have the `REPLICATION`
attribute (or be a superuser). If the role lacks `SUPERUSER` and
`BYPASSRLS`, publisher row security policies can execute. If the role
does not trust all table owners, include `options=-crow_security=off` in
the connection string; if a table owner then adds a row security policy,
that setting will cause replication to halt rather than execute the
policy. Access for the role must be configured in `pg_hba.conf` and it
must have the `LOGIN` attribute.

In order to be able to copy the initial table data, the role used for
the replication connection must have the `SELECT` privilege on a
published table (or be a superuser).

To create a publication, the user must have the `CREATE` privilege in
the database.

To add tables to a publication, the user must have ownership rights on
the table. To add all tables in schema to a publication, the user must
be a superuser. To create a publication that publishes all tables or all
tables in schema automatically, the user must be a superuser.

To create a subscription, the user must be a superuser.

The subscription apply process will run in the local database with the
privileges of the subscription owner.

On the publisher, privileges are only checked once at the start of a
replication connection and are not re-checked as each change record is
read.

On the subscriber, the subscription owner\'s privileges are re-checked
for each transaction when applied. If a worker is in the process of
applying a transaction when the ownership of the subscription is changed
by a concurrent transaction, the application of the current transaction
will continue under the old owner\'s privileges.

Configuration Settings {#logical-replication-config}
======================

Logical replication requires several configuration options to be set.

On the publisher side, `wal_level` must be set to `logical`, and
`max_replication_slots` must be set to at least the number of
subscriptions expected to connect, plus some reserve for table
synchronization. And `max_wal_senders` should be set to at least the
same as `max_replication_slots` plus the number of physical replicas
that are connected at the same time.

`max_replication_slots` must also be set on the subscriber. It should be
set to at least the number of subscriptions that will be added to the
subscriber, plus some reserve for table synchronization.
`max_logical_replication_workers` must be set to at least the number of
subscriptions, again plus some reserve for the table synchronization.
Additionally the `max_worker_processes` may need to be adjusted to
accommodate for replication workers, at least
(`max_logical_replication_workers` + `1`). Note that some extensions and
parallel queries also take worker slots from `max_worker_processes`.

Quick Setup {#logical-replication-quick-setup}
===========

First set the configuration options in `postgresql.conf`:

    wal_level = logical

The other required settings have default values that are sufficient for
a basic setup.

`pg_hba.conf` needs to be adjusted to allow replication (the values here
depend on your actual network configuration and user you want to use for
connecting):

    host     all     repuser     0.0.0.0/0     md5

Then on the publisher database:

    CREATE PUBLICATION mypub FOR TABLE users, departments;

And on the subscriber database:

    CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;

The above will start the replication process, which synchronizes the
initial table contents of the tables `users` and `departments` and then
starts replicating incremental changes to those tables.
