Design for In-Core Logical Replication

At PgCon we discussed that Petr Jelinek would be working on the code for an
in-core logical replication implementation, while I would work on user
interface/security models. Petr has been actively working on the code and
will post patch in a few weeks, as discussed and agreed. Craig Ringer is
also active in coding necessary aspects. None of those things are discussed
further here at this time.

In this post, Petr and I present a joint view on a design for how this
should work in-core, based upon our implementation experiences with
physical replication, pglogical and various comments so far.

Note that this has substantial user-visible differences from pglogical,
though much of the underlying architecture is reused.

I should stress that not all of the aspects are implemented yet. The post
here today is a combination of all of our attempts to bring architecture,
usability and security into one place, including a coherent way of
describing the features and how they work.

Your comments and questions are sought now as we begin the main development
effort to get this into PostgreSQL 10.0

<!-- doc/src/sgml/logical-replication.sgml -->

<chapter id="logical-replication">

<title>Logical Replication</title>
<para>
Logical Replication is a method of replicating data objects and their
changes, based upon their Primary Keys (or Replication Identity). 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
<xref linkend="high-availability">. Logical Replication allows
fine-grained control over both data replication and security.
</para>
<para>
Logical Replication uses a Publish and Subscribe model with one or
more Subscribers subscribing to one or more Publications on a
Provider node. Subscribers pull data from the Publications they
subscribe to and may subsequently re-publish data to allow
cascading replication or more complex configurations.
</para>
<para>
Data for committed transactions is streamed in real-time to each
Subscriber. Logical replication might also be described as Change
Data Capture (CDC) or Transactional Replication.
</para>
<para>
The typical use-cases for logical replication are:
</para>
<itemizedlist>
<listitem>
<para>
Replicating between different major versions of the PostgreSQL
</para>
</listitem>
<listitem>
<para>
Replicating a database in full to another master node.
</para>
</listitem>
<listitem>
<para>
Replicating a subset of a database to another master node.
</para>
</listitem>
<listitem>
<para>
Firing triggers for individual changes as they are incoming to
subscriber.
</para>
</listitem>
<listitem>
<para>
Gathering data from multiple databases into a single one (for
example
for analytical purposes).
</para>
</listitem>
</itemizedlist>

<sect1 id="publication">
<title>Publication</title>
<para>
A Publication object can be defined on any master node, owned by one
user. A Publication is a set of changes generated from a group of
tables, and might also be described as a Change Set or Replication Set.
Each Publication exists in only one database.
</para>
<para>
Publications are different from table schema and do not affect
how the table is accessed. Each table can be added to multiple
Publications if needed. Publications may include both tables
and materialized views. Objects must be added explicitly, except
when a Publication is created for "ALL TABLES". There is no
default name for a Publication which specifies all tables.
</para>
<para>
Tables added to a Publication must be accessible via SELECT
privilege for the user owning the Publication. Usage on the
Publication can be GRANTed to other users.
</para>
<para>
Publications can choose to limit the changes they show using any
combination of INSERT, UPDATE, DELETE and TRUNCATE in a similar
way to the way triggers are fired by particular event types.
</para>
<para>
When UPDATEs and DELETEs are replicated by a Publication, all tables
added must have a unique index present on the REPLICA IDENTITY for
the table, or the addition will be refused.
</para>
<para>
The definition of a Publication object will be included within
pg_dump by default when all of the objects in the Publication are
requested as part of the dump specification.
</para>
<para>
Every Publication can have zero, one or more Subscribers.
</para>
<para>
Publications are created using the <xref
linkend="sql-createpublication">
command and may be later altered or dropped using corresponding
commands.
</para>
<para>
The individual tables can be added and removed dynamically using
<xref linkend="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.
</para>
</sect1>
<sect1 id="subscription">
<title>Subscription</title>
<para>
A Subscription is the downstream side of the Logical Replication. The
node where Subscription is defined is referred to as Subscriber.
Subscription defines the connection to another database and set of
Publications (one or more) to which it wants to be subscribed.
It is possible to have a Subscription that currently has no
Publications.
</para>
<para>
The Subscriber database behaves in a same way as any other
PostgreSQL instance and can be used as a Provider for other
databases by defining its own Publications.
</para>
<para>
A Subscriber may have multiple Subscriptions if desired. It is
possible to define multiple Subscriptions between single
Provider-Subscriber pair, provided that each Publications can only
be subscribed to from one Subcriber.
</para>
<para>
Each Subscription will receive changes via one replication slot (see
<xref linkend="streaming-replication-slots">). Additional temporary
replication slots may be required for the initial data synchronizations
of pre-existing table data.
</para>
<para>
Subscriptions are not dumped by pg_dump by default, but can be
requested using --subscriptions parameter.
</para>
<para>
The Subscription is added using <xref linkend="sql-createsubscription">
and can be stopped/resumed at any time using
<xref linkend="sql-altersubscription"> command or removed using
<xref linkend="sql-dropsubscription">.
</para>
<para>
When a subscription is dropped and recreated the synchronization
information is lost. This means that the data has to be
resynchronized afterwards.
</para>
<para>
Changes at the Subscriber are applied as normal database changes.
If the Subscriber allows it these changes could conflict with
changes made locally which could cause apply conflicts.
In general, it is recommended that local changes be disallowed,
treating data as read-only on the Subscriber side.
</para>
<para>
Conflicts happen when the replicated changes is breaking any
specified constraints (with the exception of foreign keys which are
not checked). Currently conflicts are not resolved automatically and
cause replication to be stopped with an error until the conflict is
manually resolved.
</para>
</sect1>
<sect1 id="logical-replication-architecture">
<title>Architecture</title>
<para>
Logical replication starts by copying a snapshot of the data on
the Provider database. Once that is done, the changes on Provider
are sent to Subscriber as they occur in real-time. The Subscriber
applies the data in the order in which commits were made on the
Provider so that transactional consistency is guaranteed for the
Publications within any single Subscription.
</para>
<para>
The Logical Replication is built on the similar architecture as the
physical streaming replication
(see <xref linkend="streaming-replication">). It is implemented by
WalSender and the Apply processes. The WalSender starts the logical
decoding (described in <xref linkend="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 <xref linkend="protocol-logical-replication">) and filters the data
according to Publication specifications. The data are then continuously
transferred using the streaming replication protocol to the Apply worker
which maps them to the local tables and applies the individual changes
as
they are received in exact transactional order.
</para>
<para>
The Apply process on Subscriber database always runs with
session_replication_role set to replica, which produces the normal
effects
on triggers and constraints.
</para>
<sect2 id="logical-replication-snapshot">
<title>Initial snapshot</title>
<para>
The initial snapshot is taken when the replication slot for
Subscription is created. The existing data at that snapshot are
then sent over using the streaming replication protocol between
WalSender and Apply processes in similar way the changes are sent.
Once the initial data are copied, the Apply enters catch up phase
where it replays the changes which happened on the Provider while
the initial snapshot was being copied. Once the replication catches
up the Apply switches to normal replication streaming mode and
replicates transactions as they happen.
</para>
</sect2>
<sect2 id="logical-replication-table-resync">
<title>Individual table resynchronization</title>
<para>
The table can be resynchronized at any point during the normal
replication operation. When the table resynchronization is
requested a parallel instance of special kind of the Apply process
is started which registers its own temporary replication slot and
does new snapshot. Then it works same way as the initial snapshot
<xref linkend="logical-replication-snapshot"> with the exception that
it only does data copy of single table and once the catchup phase is
finished the control of the replication of the table is given back to
the main Apply process.
</para>
</sect2>
</sect1>
<sect1 id="logical-replication-monitoring">
<title>Monitoring</title>
<para>
pg_stat_replication
</para>
<para>
pg_stat_subscription
</para>
</sect1>
<sect1 id="logical-replication-security">
<title>Security</title>
<para>
Replication connection can occur in the same way as physical streaming
replication. It requires access to be specifically given using
pg_hba.conf. The role used for the replication must have
<literal>REPLICATION</literal> privilege <command>GRANTED</command>.
This gives a role access to both logical and physical replication.
</para>
<para>
In addition, logical replication can be accessed with the
<literal>SUBSCRIPTION</literal> privilege. This allows you to create
roles which can pull data from Publications yet cannot request
physical replication.
</para>
<para>
To create or subscribe to a Publication the user must have the
REPLICATION role, the SUBSCRIPTION role or be a superuser.
</para>
<para>
<literal>SELECT</literal> privilege is required when the user
adds a table to a Publication.
To subscribe to a Publication, user must be owner or have USAGE
privileges granted to the Publication.
</para>
<para>
To create a Subscription the user must have the
REPLICATION role, the SUBSCRIPTION role or be a superuser.
The Subscription Apply process will run in local database
with the privileges of the owner of the Subscription. In practice this
means that the owner of the Subscription must have <literal>INSERT</>,
<literal>UPDATE</>, <literal>DELETE</> and <literal>TRUNCATE</>
privileges on Subscriber to the tables that are being replicated by the
Subscription, or be superuser, though this is not recommended.
</para>
<para>
In particular, note that privileges are not re-checked as each change
record is read from the Provider, nor are they re-checked for each
change
when applied. Security is checked once at startup. Concurrent REVOKEs
of privilege will interrupt logical replication if they have a material
affect on the security of the change stream.
</para>
</sect1>
<sect1 id="logical-replication-gucs">
<title>Logical replication related configuration parameters</title>
<para>
The Logical Replication requires several configuration options to be
set.
</para>
<para>
On the provider side the <varname>wal_level</> must be set to
<literal>logical</>, <varname>max_replication_slots</> has to be set to
at least number of Subscriptions expected to connect with some reserve
for table synchronization as well. And <varname>max_wal_senders</>
should be set to at least same as <varname>max_replication_slots</> plus
the number of physical replicas that are connected at the same time.
</para>
<para>
The Subscriber also requires the <varname>max_replication_slots</> to
be set. In this case it should be set to at least the number of
Subscriptions that will be added to the Subscriber. The
<varname>max_logical_replication_workers</> has to be set to at least
the number of Subscriptions again with some reserve for the table
synchronization. Additionally the <varname>max_worker_processes</> may
need to be adjusted to accommodate for replication workers at least
(<varname>max_logical_replication_workers</> + <literal>1</>). Please
note that some extensions and parallel queries also take worker slots
from <varname>max_worker_processes</>.
</para>
</sect1>
<sect1 id="logical-replication-quick-setup">
<title>Quick setup</title>
<para>
First set the configuration options in the postgresql.conf:
<programlisting>
wal_level = logical
max_worker_processes = 10 # one per subscription + one per instance needed
on subscriber
max_logical_replication_workers = 10 # one per subscription + one per
instance needed on subscriber
max_replication_slots = 10 # one per subscription needed both provider and
subscriber
max_wal_senders = 10 # one per subscription needed on provider
</programlisting>
</para>
<para>
The 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):
<programlisting>
host replication repuser 0.0.0.0/0 md5
</programlisting>
</para>
<para>
Then on Provider database:
<programlisting>
CREATE PUBLICATION mypub;
ALTER PUBLICATION mypub ADD TABLE users, departments;
</programlisting>
</para>
<para>
And on Subscriber database:
<programlisting>
CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar
user=repuser</quote> PUBLICATION mypub;
</programlisting>
</para>
<para>
The above will start the replication process which synchronizes the
initial table contents of <literal>users</literal> and
<literal>departments</literal> tables and then starts replicating
incremental changes to those tables.
</para>
</sect1>
</chapter>

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services