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.