Horton

Support logical replication of DDLs

Phil Florent <philflorent@hotmail.com>

View thread
From: Phil Florent <philflorent@hotmail.com>
To: vignesh C <vignesh21@gmail.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Amit Kapila <amit.kapila16@gmail.com>, Ajin Cherian <itsajin@gmail.com>, "wangw.fnst@fujitsu.com" <wangw.fnst@fujitsu.com>, Runqi Tian <runqidev@gmail.com>, Peter Smith <smithpb2250@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 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>, Zheng Li <zhengli10@gmail.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Date: 2023-04-02T09:55:47Z
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.

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