Logical replication and pg_dump for out of band synchronization

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

Responses

Browse pgsql-admin by date

  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