DDL support for logical replication

Hi Lev,

While I don't have an answer to your roadmap question, you've raised a
different question for me.

What are you expecting to get from logical replication of DDL commands that
is not served by binary replication? I ask because typically someone would
want to use logical replication if they wanted triggers to fire on the
subscriber, they only a subset of all tables replicated, etc.

Perhaps a better question would be "What problem are you trying to solve?"
rather than focus on how you expected to solve that problem.

Cheers,

Miles Elam

On Thu, Oct 10, 2019 at 11:08 AM Lev Kokotov <lev.kokotov@gmail.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.

Another issue is migrating large databases (in the terabytes) to a new
version of Postgres with minimal downtime. Say it takes a week or two to
sync up the subscriber with the publisher, we have to enforce a migration
freeze for that duration. That's often inconvenient.

We often want to have a database with a subset of data of a particular
table somewhere else, and logical replication is great, since it allows me
to run write operations on the subscriber. Binary replication forces me to
have two identical databases.

Best,
Lev

On Thu, Oct 10, 2019 at 12:49 PM Miles Elam <miles.elam@productops.com>
wrote:

Another use case is installations where there is heavy reliance on
temporary tables in queries. Since you can't run queries that create
temporary tables on servers that are binary replicas, this leaves the
master (not horizontally scalable) or logical replicas

--cnemelka

On Thu, Oct 10, 2019 at 2:09 PM Lev Kokotov <lev.kokotov@gmail.com> wrote:

On Thu, Oct 10, 2019 at 3:09 PM Lev Kokotov <lev.kokotov@gmail.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&gt; 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@2ndquadrant.com> did a lot of
great work on support for in-core DDL replication a few years back. Here
is that thread:
/messages/by-id/CACACo5QQuAV+n4Gi+YA1JF_a+QenR6SJuP8CYdPSrXKa+FHS3A@mail.gmail.com

Thanks,
Jeremy