Finalizing logical replication limitations as well as potential features

On 22 December 2017 at 05:24, Joshua D. Drake <jd@commandprompt.com> wrote:

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

Yep, that's a case of "not implemented yet, patches welcome"

There are a lot of other areas where that applies too. In-core logical rep
needed a ton of work to get the basics in, so there wasn't a great deal of
time left for bells and whistles.

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.

Same deal. AFAIK it's feasible but not implemented.

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.

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

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