Re: Support logical replication of DDLs

On 3/27/23 2:37 AM, Amit Kapila wrote:
> On Mon, Mar 27, 2023 at 2:52 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> And TBH, I don't think that I quite believe the premise in the
>> first place. The whole point of using logical rather than physical
>> replication is that the subscriber installation(s) aren't exactly like
>> the publisher. Given that, how can we expect that automated DDL
>> replication is going to do the right thing often enough to be a useful
>> tool rather than a disastrous foot-gun?
>>
>
> One of the major use cases as mentioned in the initial email was for
> online version upgrades. And also, people would be happy to
> automatically sync the schema for cases where the logical replication
> is set up to get a subset of the data via features like row filters.
> Having said that, I agree with you that it is very important to define
> the scope of this feature if we want to see it becoming reality.

To echo Amit, this is actually one area where PostgreSQL replication
lags behind (no pun intended) other mature RDBMSes. As Amit says, the
principal use case is around major version upgrades, but also migration
between systems or moving data/schemas between systems that speak the
PostgreSQL protocol. All of these are becoming more increasingly common
as PostgreSQL is taking on more workloads that are sensitive to downtime
or are distributed in nature.

There are definitely footguns with logical replication of DDL -- I've
seen this from reading other manuals that support this feature and in my
own experiments. However, like many features, users have strategies thy
use to avoid footgun scenarios. For example, in systems that use logical
replication as part of their HA, users will either:

* Not replicate DDL, but use some sort of rolling orchestration process
to place it on each instance
* Replicate DDL, but coordinate it with some kind of global lock
* Replica only a subset of DDL, possibly with lock coordination

I'll comment on the patch scope further downthread. I agree it's very
big -- I had given some of that feedback privately a few month back --
and it could benefit from the "step back, holistic review." For example,
I was surprised that a fairly common pattern[1] did not work due to
changes we made when addressing a CVE (some follow up work was proposed
but we haven't done it yet).

I do agree this patch would benefit from stepping back, and I do think
we can work many of the issues. From listening to users and prospective
users, it's pretty clear we need to support DDL replication in some
capacity.

Thanks,

Jonathan

[1]
https://www.postgresql.org/message-id/263bea1c-a897-417d-3765-ba6e1e24711e%40postgresql.org