Feedback-ID: i131946ab:Fastmail
Message-ID: <4b3b8dae-fcd6-eb0f-c29f-f27a98a8d5b1@enterprisedb.com>
Date: Wed, 6 Jul 2022 11:21:31 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.11.0
Subject: Re: Logical replication and pg_dump for out of band synchronization
Content-Language: en-US
To: Joseph Hammerman <joe.hammerman@datadoghq.com>,
 pgsql-admin@lists.postgresql.org
References: 
 <CAHs7QM-myW7FiR=5HqAmS2NjpP7L=xjGHM8ELjcDyPNthpNQew@mail.gmail.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: 
 <CAHs7QM-myW7FiR=5HqAmS2NjpP7L=xjGHM8ELjcDyPNthpNQew@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4b3b8dae-fcd6-eb0f-c29f-f27a98a8d5b1%40enterprisedb.com>
Precedence: bulk

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.