Re: Logical replication and pg_dump for out of band synchronization

On 04.07.22 22:40, Joseph Hammerman wrote:
> We have been trying to use Logical Replication to synchronize some
> oversized tables (1Tb+).
> We are replicating from 9.6 -> 11.x. The long sync times for the initial
> snapshots of these large tables  have been causing incidents however,
> since autovacuum cannot clean up anything older than the xmin horizon.

What replication system are you using with PG 9.6? If you are using
pglogical, then it contains a program pglogical_create_subscriber that
addresses this.

> We then intend to take a dump, restore the table, and play back
> the changes by enabling the Subscription. This way the bulk data
> transfer is out of band from the production application. Our testing
> shows that this works cleanly, and that new changes replicate correctly
> to the target relations. Additionally, pg_dump has a —snapshot flag
> which it appears was added to support this sort of workflow.

Yes, this would also be a valid solution.