pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Logical replication and pg_dump for out of band synchronization 2+ messages / 2 participants [nested] [flat]
* Logical replication and pg_dump for out of band synchronization @ 2022-07-04 20:40 Joseph Hammerman <joe.hammerman@datadoghq.com> 0 siblings, 1 reply; 2+ messages in thread From: Joseph Hammerman @ 2022-07-04 20:40 UTC (permalink / raw) To: pgsql-admin@lists.postgresql.org Hi pgsql-admins, 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. We are considering initiating logical replication on the Producer with no Subscriber in order to generate a point in time from which logical changes for the table in question will begin to accumulate in pg_xlog on the primary. 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. I would like to partition these relations in order to reduce the size of the individual objects to something more tractable, but that Data Migration is not feasible in the timeframe in which we have to be able to deliver this workflow split out. Does anyone know if there is a dire reason from a Consistency standpoint not to use this approach? Thanks in advance for any assistance anyone can provide! Joe Hammerman ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Logical replication and pg_dump for out of band synchronization @ 2022-07-06 09:21 Peter Eisentraut <peter.eisentraut@enterprisedb.com> parent: Joseph Hammerman <joe.hammerman@datadoghq.com> 0 siblings, 0 replies; 2+ messages in thread From: Peter Eisentraut @ 2022-07-06 09:21 UTC (permalink / raw) To: Joseph Hammerman <joe.hammerman@datadoghq.com>; pgsql-admin@lists.postgresql.org 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-07-06 09:21 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-07-04 20:40 Logical replication and pg_dump for out of band synchronization Joseph Hammerman <joe.hammerman@datadoghq.com> 2022-07-06 09:21 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox