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