Design for In-Core Logical Replication

Поиск

Список

Период

Сортировка

От	Simon Riggs
Тема	Design for In-Core Logical Replication
Дата	20 июля 2016 г. 11:08:18
Msg-id	CANP8+j+NMHP-yFvoG03tpb4_s7GdmnCriEEOJeKkXWmUu_=-HA@mail.gmail.com обсуждение исходный текст
Ответы	Re: Design for In-Core Logical Replication ("Joshua D. Drake" <jd@commandprompt.com>) Re: Design for In-Core Logical Replication (Rod Taylor <rod.taylor@gmail.com>) Re: [HACKERS] Design for In-Core Logical Replication (Dmitriy Sarafannikov <d.sarafannikov@bk.ru>) Re: Design for In-Core Logical Replication (Jim Nasby <Jim.Nasby@BlueTreble.com>) Re: Design for In-Core Logical Replication (Hannu Krosing <hkrosing@gmail.com>) Re: Design for In-Core Logical Replication (Robert Haas <robertmhaas@gmail.com>)
Список	pgsql-hackers

Дерево обсуждения

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

<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>

<para>

Replicating between different major versions of the PostgreSQL

</para>

</listitem>

<para>

Replicating a database in full to another master node.

</para>

</listitem>

<para>

Replicating a subset of a database to another master node.

</para>

</listitem>

<para>

Firing triggers for individual changes as they are incoming to

subscriber.

</para>

</listitem>

<para>

Gathering data from multiple databases into a single one (for example

for analytical purposes).

</para>

</listitem>

</itemizedlist>

<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>

<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>

<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>

<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>

<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>

<title>Monitoring</title>

<para>

pg_stat_replication

</para>

<para>

pg_stat_subscription

</para>

</sect1>

<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>

<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>

<title>Quick setup</title>

<para>

First set the configuration options in the postgresql.conf:

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):

host replication repuser 0.0.0.0/0 md5

</programlisting>

</para>

<para>

Then on Provider database:

CREATE PUBLICATION mypub;

ALTER PUBLICATION mypub ADD TABLE users, departments;

</programlisting>

</para>

<para>

And on Subscriber database:

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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

В списке pgsql-hackers по дате отправления:

Предыдущее

От: Andres Freund
Дата: 20 июля 2016 г., 10:39:59
Сообщение: Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <

Следующее

От: anantbhasu
Дата: 20 июля 2016 г., 12:21:58
Сообщение: Re: FDW handling count(*) through AnalyzeForeignTable or other constant time push-down

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Design for In-Core Logical Replication

Предыдущее

Следующее