RE: Initial Schema Sync for Logical Replication

> From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> >
> > One related idea is that currently, we fetch the table list
> > corresponding to publications in subscription and create the entries
> > for those in pg_subscription_rel during Create Subscription, can we
> > think of postponing that work till after the initial schema sync? We
> > seem to be already storing publications list in pg_subscription, so it
> > appears possible if we somehow remember the value of copy_data. If
> > this is feasible then I think that may give us the flexibility to
> > perform the initial sync at a later point by the background worker.

Maybe we need to add column to pg_subscription to store copy_data state ?

>
> It sounds possible. With this idea, we will be able to have the apply worker
> restore the table schemas (and create pg_subscription_rel
> entries) as the first thing. Another point we might need to consider is that the
> initial schema sync (i.e. creating tables) and creating pg_subscription_rel entries
> need to be done in the same transaction.
> Otherwise, we could end up committing either one change. I think it depends on
> how we restore the schema data.

I think we have to add one more column to pg_subscription to track the initial sync
state {OFF, SCHEMA_DUMPED, SCHEMA_RESTORED, COMPLETED} (COMPLETED will
show that pg_subscription_rel is filled) . I don’t think we won't be able
to do pg_restore and pg_subscription_rel in single transaction, but we can use
initial_sync_state to start from where we left after a abrupt crash/shutdown.

Regards
Sachin