Re: Support logical replication of DDLs

On Fri, Feb 13, 2026 at 3:42 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, Feb  4, 2026 at 04:39:38PM +0900, Masahiko Sawada wrote:
> > On Tue, Feb 3, 2026 at 1:04 AM Vitaly Davydov <v.davydov@postgrespro.ru> wrote:
> > > 4. Another option is to create json/ddl-sql from system catalog changes without
> > > an intermediate representation, but, anyway, when we interpret system catalog
> > > changes we have to temporary save current data in some structures. Parsenodes
> > > is the already existing solution for it.
> >
> > IIUC, one of the main challenges of the "deparsing DDL parse tree"
> > idea is the maintenance burden. If we implement logic to deparse parse
> > nodes back to SQL text, we would end up updating that deparsing code
> > every time the underlying parse node definition changes (which happens
> > frequently in internal structures). This introduces a substantial and
> > ongoing maintenance cost.
>
> I agree maintenance is the big blocker, but the maintenance is two
> parts:
>
> 1.  writing the patch to adjust for new features in each major release
> 2.  testing the patch
>
> People create some strange database schemas, so testing will be
> difficult.
>
> pg_upgrade had a similar challenge, and I found that pushing as much of
> the changes _out_ of pg_upgrade and to other parts of the system, e.g,,
> pg_dump, was a big help.  I am not sure if that is possible for
> replicated DDL, but if it is, I would pursue it.
>

The other reason to pursue a deparsing approach is to allow "Schema
and Object Redirection" between source and target. For example, users
may want to redirect operations from a source object (e.g.,
HR.ACCOUNTS on the source) to a different target object (e.g.,
HR_BACKUP.ACCT on the target). Many people in the past supported such
flexibility in DDL replication which is one of the reasons we pursued
a deparsing approach in the past though that project/approach is
paused for the time being. I admit that there were many unresolved
challenges apart from the maintenance part of that approach.

Personally, I think it is better to first finish initial sync of DDLs
as previously proposed in thread [1]. That part is independently
useful and would be a good base step for the incremental replication
as discussed here. For example, users don't need to worry whether
their ALTER's will be replicated without an ERROR. OTOH, initial sync
of schema prevents users from snapshot related problems during initial
data transfer during set up of subscriber. Now, users need to use
pg_dump/restore kind of functionality to do initial setup between
publisher and subscriber then start replication. If there are more
DDLs ini-between those steps then the initial copy may turn out to be
not useful. Additionally, initial sync could be useful in a number of
other cases: (a) Many times
one needs a clone of the production schema to run tests against, (b)
Sometimes users want to run heavy, complex reporting queries without
slowing down the main application. So, one can clone required tables
and start such reporting queries, (c) In cases like (b), many times,
users may not want incremental sync that drops the index as it could
be required for reporting purposes.

[1]: https://www.postgresql.org/message-id/db02e6773adb4dbcb5b9bb3803ebe340%40amazon.com
-- 
With Regards,
Amit Kapila.