Re: DDL support for logical replication

On Thu, Oct 10, 2019 at 3:09 PM Lev Kokotov <lev(dot)kokotov(at)gmail(dot)com> wrote:

> Hi Miles,
>
> One issue is keeping the subscriber and the publisher schema identical.
> Running migrations on both the publisher and subscriber does not seem
> atomic to me, therefore I don't have a way to enforce consistency between
> the two. The use case is simple: schemas change all the time, and keeping
> two databases (or more!) in sync manually is tough.
>

We had this requirement as well and this is why I wrote pgl_ddl_deploy
<https://github.com/enova/pgl_ddl_deploy> on top of pglogical to handle the
issue. It's by no means an all-encompassing solution, but it's worked
really well for us. It only supports pglogical currently (which could be
expanded for in-core logical... but we have not tried yet).

It basically uses event triggers, and inspecting what is being executed, as
an idea of how to propagate DDL.

However, I am very interested in future of this feature for in-core logical
replication in a much more natively-supported way. As such, I have not
heard anything for awhile. Schema mapping for logical replication is
another feature we really would want, and would also very much relate to
DDL replication. @Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> did a lot of
great work on support for in-core DDL replication a few years back. Here
is that thread:
https://postgr.es/m/CACACo5QQuAV+n4Gi+YA1JF_a+QenR6SJuP8CYdPSrXKa+FHS3A@mail.gmail.com

Thanks,
Jeremy