From: | Joseph Hammerman <joe(dot)hammerman(at)datadoghq(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Logical replication and pg_dump for out of band synchronization |
Date: | 2022-07-04 20:40:58 |
Message-ID: | CAHs7QM-myW7FiR=5HqAmS2NjpP7L=xjGHM8ELjcDyPNthpNQew@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2022-07-06 09:21:31 | Re: Logical replication and pg_dump for out of band synchronization |
Previous Message | Andrey Borodin | 2022-07-01 08:44:45 | Re: Long running backup preventing auto vacuum |