Horton

Re: Support logical replication of DDLs

Jonathan S. Katz <jkatz@postgresql.org>

View thread
From: "Jonathan S. Katz" <jkatz@postgresql.org>
To: Amit Kapila <amit.kapila16@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: vignesh C <vignesh21@gmail.com>, Ajin Cherian <itsajin@gmail.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, "wangw.fnst@fujitsu.com" <wangw.fnst@fujitsu.com>, Runqi Tian <runqidev@gmail.com>, Peter Smith <smithpb2250@gmail.com>, li jie <ggysxcq@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Masahiko Sawada <sawada.mshk@gmail.com>, Japin Li <japinli@hotmail.com>, rajesh singarapu <rajesh.rs0541@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Zheng Li <zhengli10@gmail.com>
Date: 2023-03-28T14:51:50Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add a run_as_owner option to subscriptions.

  2. Refactor pgoutput_change().

  3. Print the correct aliases for DML target tables in ruleutils.

  4. Fix object identity string for transforms

  5. Add grantable MAINTAIN privilege and pg_maintain role.

  6. Get rid of recursion-marker values in enum AlterTableType

  7. Release cache tuple when no longer needed

  8. Empty search_path in logical replication apply worker and walsender.

  9. Refactor format_type APIs to be more modular

  10. Use wrappers of PG_DETOAST_DATUM_PACKED() more.

On 3/27/23 2:37 AM, Amit Kapila wrote:
> On Mon, Mar 27, 2023 at 2:52 AM Tom Lane <tgl@sss.pgh.pa.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