Thread: Design for In-Core Logical Replication

On 07/20/2016 01:08 AM, Simon Riggs wrote:

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

Do we want a limitation based on Primary Key, or would it be possible to 
use just UNIQUE or is that covered under Replication Identity?

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

Is that somehow different than Origin/Subscriber or Master/Slave? If 
not, why are we using yet more terms?

> <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.
      Then on Provider database:
> <programlisting>
> CREATE PUBLICATION mypub;
> ALTER PUBLICATION mypub ADD TABLE users, departments;
> </programlisting>
>    </para>

Outside of my previous comments on reusing terminology that is known to 
our community, I like this. Basically a user creates a pool that is 
replicating, throws various ducks and small children into the pool and 
then replicates. Nice.

Sincerely,

JD


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On 20/07/16 19:07, Simon Riggs wrote:
> On 20 July 2016 at 16:39, Joshua D. Drake <jd@commandprompt.com
> <mailto:jd@commandprompt.com>> wrote:
>             <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.
>
>
>     Is that somehow different than Origin/Subscriber or Master/Slave? If
>     not, why are we using yet more terms?
>
>
> Thanks for asking, an important question that we have a chance to get
> right before we go too far down the road of implementation.
>
> I'll explain my thinking, so we can discuss the terms I've recommended,
> which can be summarized as:
> A Provider node has one or more Databases, each of which can publish its
> data in zero, one or more PUBLICATIONs. A Subscribing node can receive
> data in the form of zero, one or more SUBSCRIBERs, where each SUBSCRIBER
> may bring together data from one or more PUBLICATIONs
>
> Here's why...
>

Just to add to what Simon wrote. There is one more reason for not using 
term origin for this - origin of data does not necessarily have to be on 
the provider database once there is a cascading so it does not really 
map all that well.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On 07/20/2016 06:35 PM, Craig Ringer wrote:

> First, I'd like to emphasise that logical replication has been stalled
> for ages now because we can no longer make forward progress on core
> features needed until we have in-core logical replication (they're
> dismissed as irrelevant, no in core users, etc) - but we have also had
> difficulty getting logical replication into core. To break this impasse
> we really need logical replication in core and need to focus on getting
> the minimum viable feature in place, not trying to make it do everything
> all at once. Point-to-point replication with no forwarding should be
> just fine for the first release. Lets not bog this in extra "must have"
> features that aren't actually crucial.

I don't think any person who actually works on postgresql with customers 
and clearly deals with "competition" can state with any sanity that we 
don't need logical replication in core.

JD


-- 
Command Prompt, Inc.                  http://the.postgres.company/                        +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

><programlisting>
>CREATE PUBLICATION mypub;
>ALTER PUBLICATION mypub ADD TABLE users, departments;

>CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar user=repuser</quote> PUBLICATION mypub;

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

Hi, hackers.

it is very good to have logical replication in core. Also i have some proposal. What if we would have ability to
executecustom trigger functions on events on particular table? Also it would be useful if would have ability to ignore
sometables in publication or replicatie with some WHERE condition. For example, we want replicate table "users" as is
(maybewith some WHERE conditions), but on events on table "departments" we want execute trigger function
departments_event_handler().And we don't want handle events on third table which was added to publication. 

Something like this:

CREATE PUBLICATION mypub;
ALTER PUBLICATION mypub ADD TABLE users, departments, unnecessary_tbl;

CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar user=repuser</quote> PUBLICATION mypub;
ALTER SUBSCRIPTION mysub ADD TABLE users REPLICATE TO LOCAL TABLE my_users INSERT WHERE new.id_user > 1000 UPDATE WHERE
old.id_user< 1000; -- we don't want replicate deletes, for example. 
ALTER SUBSCRIPTION mysub ADD TABLE departments ON INSERT WHEN (new.id_user > 1000)  EXECUTE PROCEDURE
departments_event_handler();-- just like trigger 


Regards,
Dmitriy Sarafannikov

On 22 July 2016 at 09:54, Dmitriy Sarafannikov <d.sarafannikov@bk.ru> wrote:
>
>><programlisting>
>>CREATE PUBLICATION mypub;
>>ALTER PUBLICATION mypub ADD TABLE users, departments;
>
>>CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar user=repuser</quote> PUBLICATION mypub;
>
>>    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.
>
> Hi, hackers.
>
> it is very good to have logical replication in core. Also i have some proposal.

Thanks for your input.

> What if we would have ability to execute custom trigger functions on events on particular table? Also it would be
usefulif would have ability to ignore some tables in publication or replicatie with some WHERE condition.
 

Both of those requirements are eventual goals for this. The initial
commits of Logical Replication for 10.0 won't include those features,
keeping the scope tight so we make sure we get this into 10.0, but we
want to design it with those things in mind and we may be lucky enough
to get it in the first release.

> For example, we want replicate table "users" as is (maybe with some WHERE conditions), but on events on table
"departments"we want execute trigger function departments_event_handler().
 

> ALTER SUBSCRIPTION mysub ADD TABLE users REPLICATE TO LOCAL TABLE my_users INSERT WHERE new.id_user > 1000 UPDATE
WHEREold.id_user < 1000; -- we don't want replicate deletes, for example.
 
> ALTER SUBSCRIPTION mysub ADD TABLE departments ON INSERT WHEN (new.id_user > 1000)  EXECUTE PROCEDURE
departments_event_handler();-- just like trigger
 

Filtering data at both the sender and receiver has been requested, so
I guess it makes sense to have a WHERE clause on both the Publication
and the Subscription, but I will think more on that.

Transforming data at the receiver/Apply side we had envisaged would be
done using standard triggers. So we had envisaged you would add the
trigger onto the table using the existing trigger syntax, so that when
an INSERT was applied the trigger would execute, using the standard
mechanisms.
So I don't think we need to add trigger style syntax onto the
Subscription, but it is an option to consider, I guess.

(Note to self: make sure we get the dependencies right here so when we
drop objects they are automatically removed from Subscriptions, whilst
allowing Subscriptions to be dropped without causing tables to be
dropped (and Publications also)).

> And we don't want handle events on third table which was added to publication.

Not sure what you mean for that part. Could you explain a little more?

(I'm on leave for some days, so I don't respond quickly I will respond
eventually)

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

ISTM pg_dump should alter publication/subscription when doing partial 
dumps, similar to how it handles foreign keys.

Subscribed tables should be read-only by default. If you need something 
more advanced than that adding an extra option to the subscription (or 
wherever else) is going to be the least of your worries. If we don't 
want to get too wrapped up in config/syntax I think there should at 
least be a read-only statement level trigger installed by default that 
users can then drop or disable if desired.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 7/20/16 8:35 PM, Craig Ringer wrote:
> I'm not sure how Petr's current design for in-core replication addresses
> this, if it does, or whether it's presently focused only on
> point-to-point replication like pglogical. As far as I'm concerned so
> long as it does direct point-to-point replication with no forwarding
> that's good enough for a first cut feature, so long as the UI, catalog
> and schema design leaves room for adding more later.

That's the part I'm pretty worried about. I'd feel a lot better if there 
was at least a wiki page of future topology/communication features and a 
sketch of a design.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 07/20/2016 10:08 AM, Simon Riggs wrote:
> <sect1 id="logical-replication-monitoring">
>   <title>Monitoring</title>
>   <para>
>     pg_stat_replication
>   </para>
>   <para>
>     pg_stat_subscription
>   </para>
> </sect1>

and probably also `pg_stat_publication` or some other way to see, what
tables are currently in a PUBLICATION, who has subscribed etc.

> <programlisting>
> CREATE PUBLICATION mypub;
> ALTER PUBLICATION mypub ADD TABLE users, departments;
> </programlisting>
Would a subscription just be a logical grouping or would it be something
stronger
like meaning atomic subscriptions and/or a dedicated replication slot ?

Can you subscribe to multiple publications through single SUBSCRIPTION ?

What is supposed to happen if table A is in two subscriptions S1 and S2,
and you
subscribe to both? Will you get table a only once (both initial copy and
events)?

Would a subscription of "mypub" pop up on subscriber side atomically, or
will subscribed
tables appear one-by one when they are ready (initial copy + catchup
event replay completed) ?

I recall that one of the drivers of developing pgq/skytools to replace
Slony was the
fact that Slony's "replication group" design made it very easy to
blunder subscription
changes in more complex topologies which manifested in deadlocks.

PGQ-s table-by-table subscription avoided this entirely at the cost on
non-atomic
subscribed table appearance.

Of course once subscribed, everything was transaction-consistent again.

> <programlisting>
> CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar
> user=repuser</quote> PUBLICATION mypub;
> </programlisting>
For the pgq-like version which consider a PUBLICATION just as list of
tables to subscribe, I would add

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

ALTER SUBSCRIPTION mysub DROP PUBLICATION mypub1;

ALTER SUBSCRIPTION mysub ADD PUBLICATION mypub2;





-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On 26/07/16 00:05, Hannu Krosing wrote:
>> <programlisting>
>> CREATE PUBLICATION mypub;
>> ALTER PUBLICATION mypub ADD TABLE users, departments;
>> </programlisting>
> Would a subscription just be a logical grouping or would it be something
> stronger
> like meaning atomic subscriptions and/or a dedicated replication slot ?
>

Not sure what you mean by atomic subscription but subscription creation 
adds replication slot to the provider node. Other than that subscription 
lives on the downstream node only.

> Can you subscribe to multiple publications through single SUBSCRIPTION ?
>

Yes.

> What is supposed to happen if table A is in two subscriptions S1 and S2,
> and you
> subscribe to both? Will you get table a only once (both initial copy and
> events)?

Yes only once, the replication works with tables, publication is really 
just grouping/filtering, what you get is union of tables in the 
publications.

>
> Would a subscription of "mypub" pop up on subscriber side atomically, or
> will subscribed
> tables appear one-by one when they are ready (initial copy + catchup
> event replay completed) ?
>

Yes that's my plan as that makes it easier to parallelize and recover 
from crashes (also makes this faster as tables that are already done 
don't need to be copied again) during the initialization. Also makes it 
easier to reuse the table initialization code for adding new tables at 
later time.

>> <programlisting>
>> CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar
>> user=repuser</quote> PUBLICATION mypub;
>> </programlisting>
> For the pgq-like version which consider a PUBLICATION just as list of
> tables to subscribe, I would add
>
> CREATE SUBSCRIPTION mysub WITH CONNECTION 'dbname=foo host=bar
> user=repuser' PUBLICATION mypub, mypub1;
>

Yes that works as well.

> ALTER SUBSCRIPTION mysub DROP PUBLICATION mypub1;
>
> ALTER SUBSCRIPTION mysub ADD PUBLICATION mypub2;
>

This does not yet, but I agree we should have it.

--   Petr Jelinek                  http://www.2ndQuadrant.com/  PostgreSQL Development, 24x7 Support, Training &
Services

On Wed, Jul 20, 2016 at 4:08 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> 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

Thanks for publishing this.

One minor comment is that this document makes extensive use of Terms
With Initial Capitals, which seems stylistically odd, although I'm not
precisely sure what would be better.

I would have expected that there would be a concept of a REPLICATION
SET, defining which tables are to be replicated; here, that seems to
be the Publication.  That may fine, but I wonder if there is any value
in separating those things.  It's clear, for example, that a
replication set can be dumped: which tables are members of which
replication sets is durable metadata.  It's less clear that a
publication can be dumped; that might include things which are not
durable metadata, such as associations with slots.

It's generally not really clear to me based on reading this exactly
what information is encapsulated in a Publication or a Subscription,
which makes it hard to evaluate design decisions like this one:

>   <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>
>     Subscriptions are not dumped by pg_dump by default, but can be
>     requested using --subscriptions parameter.
>   </para>

I think that to really understand exactly what you and Petr have in
mind, we'd need a description of where publication and subscription
data is stored within the server, and exactly what gets stored.
Perhaps that will come in a later email.  I'm not bashing the design,
exactly, I just can't quite see how all of the pieces fit together
yet.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:

> One minor comment is that this document makes extensive use of Terms
> With Initial Capitals, which seems stylistically odd, although I'm not
> precisely sure what would be better.

We use <firstterm>publication</> on the first use only, which is turned
into italics.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 29 July 2016 at 16:53, Robert Haas <robertmhaas@gmail.com> wrote:

> I think that to really understand exactly what you and Petr have in
> mind, we'd need a description of where publication and subscription
> data is stored within the server, and exactly what gets stored.
> Perhaps that will come in a later email.  I'm not bashing the design,
> exactly, I just can't quite see how all of the pieces fit together
> yet.

Sure no problem.

It's clear there are about 12 ways of putting this together and each
way has various terms needed.

We need input from many people to get this right, since this is much
more about UI than system architecture.

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