Re: Feature Request - DDL deployment with logical replication

On 31 March 2018 at 01:03, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote:

>
>
> On Fri, Mar 30, 2018 at 10:16 AM, Peter Eisentraut <
> peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
>> On 3/29/18 13:21, Jeremy Finzel wrote:
>> > Although we are thrilled with some of the features already in logical
>> > replication, this missing feature is the #1 reason that we don't plan to
>> > take a serious look at built-in logical replication even for pg11,
>> > because we have been able to use pglogical with our own extension
>> > pgl_ddl_deploy in order to broadly deploy logical replication without
>> > serious overhauls to our SDLC process, having schema changes managed
>> > well. We really want a mechanism to put through DDL changes at the same
>> > transactional point on the subscribers as we do on the publishers, which
>> > also answers any complexities around deploying master-first or
>> > slave-first in some interesting cases.
>> >
>> > Is there any particular vision for how the community might address this
>> > need in the future?
>>
>> I think nobody has completely figured this out yet. Whatever is in
>> pglogical and bdr and similar external projects are the best current
>> compromises. But they have lots of problems, so I don't know if anyone
>> is ready to propose something for in core yet.
>>
>

> IMO, it would be an acceptable and good first step to provide a function
> that will replicate a SQL command through the replication stream at the
> right point, even if there is still no automation around it.
>
>
I agree.

Using a regular table as a command queue like pglogical does probably won't
be acceptable in core. But I'd suggest using a very similar approach, just
with logical WAL messages as the replication mechanism. This is actually a
regression in terms of how visible/debuggable it is; the queue table is
great in that you can see what's happened recently. But it means we
wouldn't need the hack pglogical does of special casing inserts into that
table during apply of the normal change stream.

I guess we could add a system catalog that special cased by logical
decoding, invoking a plugin callback for DDL. That way we'd still have a
change history. But really, it's not like there's a DDL history table for
postgres normally, and there's no particular reason logical replication
should be different, even if it's very convenient in pglogical.

So I suggest a logical WAL message.

This would be a practical patch for you to tackle for Pg 12 if you're
interested.

Presently some of the people involved in in-core logical rep have been able
to dedicate less time due to other core patch needs and various other
priorities.

If I get time to work on logical rep for pg12, I expect to be looking
mainly at integrating hooks and extension points based on the work we've
done in pglogical3, so we can use in-core logical rep as the core transport
and wrap faster-iterating new functionality like multimaster around it. The
pace at which we're able to iterate in core is presently a bit of a
challenge in terms of logical replication enhancements.

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