Обсуждение: Finalizing logical replication limitations as well as potentialfeatures

-Hackers,


As I continue to test Logical Replication and its native capabilities I 
have found the following:


It appears it is not possible to do this:

publisher->public.foo replicates to subscriber->private.foo

This one seems like a relatively simple fix (theoretically) and I think 
should be done. I can imagine dozens of scenarios where I want a source 
to replicate to a destination but within a different schema.

It also appears I can't do this:

publisher->public.foo replicates to subscriber->partitioned->public.foo

Here I was hoping that we could have multiple sources replicating into 
single partitioned table that would automatically segregate the database 
based on the partition key.

Lastly, I noted that a full sync of a replication set is performed by a 
COPY, this is fine for small sets but if we have a large data set that 
may take some time it may be a problem with overall performance and 
maintenance. We may want to see if we can do an initial sync 
incrementally (optional) via a cursor (?) and queue all changed rows 
until the sync completes?

Just some thoughts, I tried to ask on Slack as well as IRC but it 
doesn't appear many of the community are doing much with Logical Rep yet.


Thanks in advance,


JD


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

On 12/21/2017 06:15 PM, Craig Ringer wrote:
> On 22 December 2017 at 05:24, Joshua D. Drake <jd@commandprompt.com 
> <mailto:jd@commandprompt.com>> wrote:
>
>     -Hackers,
>
>
>     Lastly, I noted that a full sync of a replication set is performed
>     by a COPY, this is fine for small sets but if we have a large data
>     set that may take some time it may be a problem with overall
>     performance and maintenance. We may want to see if we can do an
>     initial sync incrementally (optional) via a cursor (?) and queue
>     all changed rows until the sync completes?
>
>
> I'm not sure I understand this.
>
> The COPY is streamed from source to destination, IIRC it's not 
> buffering to a tempfile or anything. So I fail to see what using a 
> cursor would gain you. No matter whether you're using a cursor, a 
> COPY, or something else, you have to hold down a specific xmin and 
> work with the same snapshot for the whole sync operation. If you 
> instead did something like incremental SELECTs, each with a new 
> xmin+snapshot, across ranges of a PK your copy would see changes from 
> different points in time depending on where in the copy it was up to, 
> and you'd get an inconsistent view. It could possibly be worked around 
> with some tricky key-range-based filtering of the applied 
> change-stream if you were willing to require that no PK updates may 
> occur, but it'd probably be bug city. It's hard enough to get sync 
> correct at all.

I am not sure that this is entirely true. Granted it is easiest just to 
do everything within a snapshot but we shouldn't have to. It would be 
possible to perform incremental (even parallel) syncs whether copy or 
other mechanism. We would have to track changes to the table as we sync 
but that isn't impossible either (especially if we have a PK). I would 
think that this would only be valid within async replication but it is 
possible. We just queue/audit the changes as they happen and sync up the 
changes after the initial sync completes. Multi-phase sync baby :D

Thanks,

JD

-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

Joshua D. Drake wrote:

> We just queue/audit the changes as they happen and sync up the changes
> after the initial sync completes.

This already happens.  There is an initial sync, and there's logical
decoding that queues any changes that exist "after" the sync's snapshot.

What you seem to want is to have multiple processes doing the initial
COPY in parallel -- each doing one fraction of the table.  Of course,
they would have to use the same snapshot.  That would make sense only
if the COPY itself is the bottleneck and not the network, or the I/O
speed of the origin server.  This doesn't sound a common scenario to me.

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

On 01/04/2018 01:26 PM, Alvaro Herrera wrote:
> Joshua D. Drake wrote:
>
>> We just queue/audit the changes as they happen and sync up the changes
>> after the initial sync completes.
> This already happens.  There is an initial sync, and there's logical
> decoding that queues any changes that exist "after" the sync's snapshot.
>
> What you seem to want is to have multiple processes doing the initial
> COPY in parallel -- each doing one fraction of the table.  Of course,
> they would have to use the same snapshot.  That would make sense only
> if the COPY itself is the bottleneck and not the network, or the I/O
> speed of the origin server.  This doesn't sound a common scenario to me.

Not quite but close. My thought process is that we don't want to sync 
within a single snapshot a 100-500mil row table (or worse). Unless I am 
missing something there, that has the potential to be a very long 
running transaction especially if we are syncing more than one relation.

JD

-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

On 05/01/18 05:35, Joshua D. Drake wrote:
> On 01/04/2018 01:26 PM, Alvaro Herrera wrote:
>> Joshua D. Drake wrote:
>>
>>> We just queue/audit the changes as they happen and sync up the changes
>>> after the initial sync completes.
>> This already happens.  There is an initial sync, and there's logical
>> decoding that queues any changes that exist "after" the sync's snapshot.
>>
>> What you seem to want is to have multiple processes doing the initial
>> COPY in parallel -- each doing one fraction of the table.  Of course,
>> they would have to use the same snapshot.  That would make sense only
>> if the COPY itself is the bottleneck and not the network, or the I/O
>> speed of the origin server.  This doesn't sound a common scenario to me.
>
> Not quite but close. My thought process is that we don't want to sync 
> within a single snapshot a 100-500mil row table (or worse). Unless I 
> am missing something there, that has the potential to be a very long 
> running transaction especially if we are syncing more than one relation.
>
> JD
>

     That's indeed the way it works, you need to hold the snapshot 
possibly for a long time. But not doing so seems to go a very complex, 
even though it's not impossible. Changes after initial sync are 
definitely registered (via logical decoding), that's not an issue. But 
if you don't keep a snapshot of the database, you will also see some or 
all of these changes applied to the tables mid-way. How to make the 
whole table copy consistent with potential mid-way changes and the 
recorded ones on logical decoding is difficult and bug-prone.

     Surprisingly, this is how MongoDB replication works, as they don't 
have the equivalent of a snapshot facility. But actually they need to do 
really weird stuff, like re-applying changes up to 3 (why?) times and 
comments on the source code point to strange hacks to make all 
consistent. I (want to) believe they made it correctly, but it is hacky, 
complicated, and MongoDB doesn't support FKs and other features that I'm 
sure complicate matters even more.

     I'm not a PG hacker, but all this sounds too complicated to me. I'd 
keep the snapshot open that makes things very easy. If inside you want 
to do parallel COPY, that's fine (if, as the other Álvaro said, it is 
COPY the limiting factor).


     Cheers,

     Álvaro

-- 

Alvaro Hernandez


-----------
OnGres