Re: Finalizing logical replication limitations as well as potential features

On 22 December 2017 at 05:24, Joshua D. Drake <jd(at)commandprompt(dot)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