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.
