Support logical replication of DDLs

From: Phil Florent <philflorent(at)hotmail(dot)com>
To: vignesh C <vignesh21(at)gmail(dot)com>, "houzj(dot)fnst(at)fujitsu(dot)com" <houzj(dot)fnst(at)fujitsu(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Ajin Cherian <itsajin(at)gmail(dot)com>, "wangw(dot)fnst(at)fujitsu(dot)com" <wangw(dot)fnst(at)fujitsu(dot)com>, Runqi Tian <runqidev(at)gmail(dot)com>, Peter Smith <smithpb2250(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, li jie <ggysxcq(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Japin Li <japinli(at)hotmail(dot)com>, rajesh singarapu <rajesh(dot)rs0541(at)gmail(dot)com>, Zheng Li <zhengli10(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Support logical replication of DDLs
Date: 2023-04-02 09:55:47
Message-ID: PA4P191MB160046A45A44A25B69009478BA8D9@PA4P191MB1600.EURP191.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hi,

As an end-user, I am highly interested in the patch https://commitfest.postgresql.org/42/3595/ but I don't fully get its main goal in its first version.
It's "for all tables" that will be implemented ?
If one needs a complete replication of a cluster, a hot standby will always be more efficient than a publication right ? I use both for different needs in public hospitals I work for (hot standby for disaster recovery & logical replication for dss)
The main interest of a publication is to be able to filter things on the publisher and to add stuff on the replicated cluster.
If you compare PostgreSQL with less avanced RDBMS that don't really implement schemas (typically Oracle), the huge advantage of Postgre is that many things (e.g security) can be dynamically implemented via schemas.
Developers just have put a table in the "good" schema and that's all. Logical DML replication now fully implements this logic since PostgreSQL 15. Only remaining problem is that a "for tables in schema" publication has to be owned by a superuser (because a normal user can have tables that don't belong to him in a schema it owns ?) If DDL replication only works with "FOR ALL TABLES " and not "FOR TABLES IN SCHEMA" it reduces its interest anyway.
The main problem with DML replication as of PostgreSQL 15 is that some DDL can interrupt the logical replication (new tables, new columns etc.).
If DDL replication was able to avoid dynamically those incidents, it would be great. If many features are missing it's normal. But if the first version was able be to deal with the majority of DDL commands that can cause incidents with the current DML implementation (a new table can break logical DML replication but a new index cannot etc.) and to implement the very same logic that is used with DML replication in terms of granularity it would be a huge plus.
Thoughts ?

Best regards,
Phil

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Wienhold 2023-04-02 13:21:51 Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account
Previous Message jian he 2023-04-01 06:02:47 ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Borodin 2023-04-02 15:28:54 Re: Add "host" to startup packet
Previous Message Drouvot, Bertrand 2023-04-02 08:27:45 Re: Minimal logical decoding on standbys