Re: Support logical replication of DDLs

On Thu, Jan 19, 2023 at 11:24 PM Zheng Li <zhengli10(at)gmail(dot)com> wrote:
>
> On Thu, Jan 19, 2023 at 2:05 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > >
> > > Foreign Tables can also be considered replicated with DDL replication because we
> > > don't even need to replicate the data as it resides on the external
> > > server. Users
> > > need to configure the external server to allow connection from the
> > > subscriber for
> > > foreign tables to work on the subscriber.
> > >
> >
> > So, this would mean that we expect the subscriber will also have the
> > same foreign server as the publisher because we will replicate the
> > entire connection/user information of the foreign server for the
> > publisher.
>
> 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.

> > > > 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.

--
With Regards,
Amit Kapila.