Horton

Re: Support logical replication of DDLs

Zheng Li <zhengli10@gmail.com>

View thread
From: Zheng Li <zhengli10@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>, vignesh C <vignesh21@gmail.com>, li jie <ggysxcq@gmail.com>, Ajin Cherian <itsajin@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Japin Li <japinli@hotmail.com>, rajesh singarapu <rajesh.rs0541@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-01-24T03:27: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.

> > Yes, CREATE/ALTER SERVER commands are also supported by the current
> > DDL replication patch.
> >
> > >But what about data inserted by the publisher on the
> > > foreign server?
> >
> > I thought the data inserted to a foreign table will always be stored
> > on the foreign server unless I'm mistaken?
> >
>
> I also have the same understanding. It is not clear to me if there is
> a use case to just allow the foreign server set up without caring for
> data replication. So, what this will achieve is both publisher and
> subscriber will be allowed to perform operations on the same foreign
> server but not sure if that is expected by the user and is useful to
> them.
One use case I think of is using a logical replica for online major
version upgrade, users would want the foreign server on the
subscriber/replica set up identical to the publisher.
There may be cases when foreign server replication is not needed,
which justifies the motivation to define fine grained DDL replication
levels.

>
> > > > > We should also think
> > > > > about initial sync for all those objects as well.
> > > >
> > > > Agree, we're starting an investigation on initial sync. But I think
> > > > initial sync depends on
> > > > DDL replication to work reliably, not the other way around. DDL replication can
> > > > work on its own without the initial sync of schema, users just need to
> > > > setup the initial
> > > > schema just like they would today.
> > > >
> > >
> > > The difference is that today users need to take care of all schema
> > > setup on both and follow changes in the same on the publisher. But
> > > with DDL replication, there has to be a point prior to which both the
> > > nodes have the same setup. For that, before setting up DDL
> > > replication, users need to ensure that both nodes have the same
> > > schema, and then during setup, the user doesn't perform any DDL on the
> > > publisher.
> >
> > The users can perform DDL during the setup if they do the following:
> > 1. Create a logical replication slot to capture changes on the publisher
> > 2. Do a backup for the publisher
> > 3. Restore the backup as the subscriber
> > 4. Advance the logical slot to the last valid LSN of the restore
> > 5. Create pub/sub and use the above logical slot.
> >
>
> Are you talking about basebackup/restore or pg_dump? I have later in
> mind (with the snapshot option) to achieve it. However, I think it
> won't be convenient for users to do those steps by themselves as there
> is a risk of mistakes leading to wrong set up.

I'm talking about basebackup where the last valid LSN can be
identified from the Postgres logs. I don't think we can easily
identify the LSN to advance the slot to with pg_dump/pg_restore since
it's a logical copy.
I agree these steps are not straightforward, but some customers are
doing it themselves.

Regards,
Zane