Re: Support logical replication of DDLs

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