Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m5t5r-00012f-Ut for pgsql-admin@arkaria.postgresql.org; Tue, 20 Jul 2021 16:59:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m5t5q-0004O1-UM for pgsql-admin@arkaria.postgresql.org; Tue, 20 Jul 2021 16:59:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m5t5q-0004Nt-Jx for pgsql-admin@lists.postgresql.org; Tue, 20 Jul 2021 16:59:42 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m5t5n-0006Qj-QX for pgsql-admin@lists.postgresql.org; Tue, 20 Jul 2021 16:59:42 +0000 Received: from [192.168.1.167] ([72.205.28.227]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1MKtBx-1lpCDi46gf-00LI6F; Tue, 20 Jul 2021 18:59:36 +0200 Subject: Re: pglogical question To: Dharmendra K Cc: Vijaykumar Jain , Pgsql-admin References: From: MichaelDBA Message-ID: <65ffb871-3394-96ef-0e7f-20a6d9862d0e@sqlexec.com> Date: Tue, 20 Jul 2021 12:59:33 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.48 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------5C757B58C29C06C395BBE4F2" Content-Language: en-US X-Provags-ID: V03:K1:6RffopsBLo70OJK7rKcTsmp5l5HK6qaoGuI4DnGcMU7mIm10HgS UsrTvHUqHxi5hPQvz4JxIC4J8QIaIv7YKNhbcLneiAIAxkoLtfeGBXGviweP4uyC0usw5xj r9PZT09JQzZTjq0KY17eLzPj1TDGPhA42TL52j2jzHuTPwnpTOSG/Ta1NfJFU9ttK6QmWU/ ZvXCaACFw30N9V5A0AKFQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:2KHtn79hBCk=:uxd3j4iJ+UTCDhNtRBkM8T qh5OVGDsnAsa9zENgweaOdq7zveDWpv6ehP0EGOi8rctgmnhJdhf4mR4/OU0VHvKSQpyHZ96+ u7qbKamlfKi/bs0u4CVDwyRCc/jlvh3TlkjfW4DIlus5+x5A0O2xBPWjXAxEu1NgixzvKOE7s 2qOWSXWoqQPfbfigycPKcabnhEdP55JMsK+VvfsUtxIEQOmFWzZv69gC+cEylW+3pXz0R/qDe w4WeVRSDxPLZazNIqHsp0pGiKYeX3hWhiSOwx0CCnxFqsUc5JpPZ1kF88TMhC5CU3BRrZkno8 1Z3zvBEZncnu/lLTmX/zc10TLMkE0LCGceMCACJHuFxSZmuAPsUWE+BJhY/RUlc38uzZPD4qP tfUsB0IvELAOqnxt13Ex9IRMX0VOld2dFIJlflxL6pdttuUrY37ylsRRSA+NiqnEdwPLHZihX ceazwlBIBIrKzB//lsowOyU0aPRiJY0BuR+2MwBcWffY5Rm2w6cWg7ovnVGQ6ziKLaar8aGPr gW7U9u1AUE7eE+lgWkCk9HwrYq6/IXC0u3YkowgthI6Z8v99LNe0kVzkfVv20POD9zIIzISEh GhBECro0kZvB/p2IgtCcSJvIAaD6wMVhDhA+fqQsjJ1TLYCypgPpxgU65wwMPv1PtjAfLT8xB Dg10Al05EQ3rv+XfJ7Qa4t1kRhiPomOOJOroXtOGCIzj8lbGWpHhXC8FXUy1502hlwXCTcx4Y JgpehFfQnDmhano+0MSe/6ugmU3HRgvM4jX2Lda+zkCE3pVT32C8Qe82klVGCsHXTixlk9T50 XsG7Wsd/jZynhPTyoAAMkqZBp6cNiFJ1JqOEPT/0PUS+7+4hL4fyRLKaMgbBUUAROI0ly5S List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------5C757B58C29C06C395BBE4F2 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit gosh i hate that sequence part of the equation! Dharmendra K wrote on 7/20/2021 12:53 PM: > Thanks, Vijaykumar for the inputs. > > On Tue, Jul 20, 2021 at 9:51 AM Vijaykumar Jain > > wrote: > > On Tue, 20 Jul 2021 at 20:50, Dharmendra K > > wrote: > > Hi, > I am trying to explore Pglogical to migrate the database from > one host to other and I have some questions. > 1.How do the Pglogical handle if provider or subscriber is > down for a significant time? Does it synch up automatically > once the Db is up on both the nodes? > > subscriber creates a slot on the publisher, so WALs would be > retained on the publisher if the subscriber is down. it will auto > connect from where it stopped. > but make sure you have monitoring for the same as logical > replication is slow. if it is broken and ignored for long, WALs > pile up fast on a busy server. > > > 2.I have a very big table(around 800GB) that needs to be > migrated using Pglogical, does it generates a lot of WAL on > provider, and how to handle the situation going out of control. > > The initial sync occurs with a COPY command. so it should be fast. > I dropped all but the primary key of the large table, reduced the > time to COPY from almost a day to a few hours. > So if it is just one table, that has many indexes, you can try > dropping the indexes but the primary key index on the subscriber > in the beginning, and then rebuild the relevant index > concurrently. This will save a lot of time. > > 3.Can we restore the database using pg dump or some other > means on the subscriber node and after restore can we start > Pglogical replication ? Does it synch the tables fast this way > as most of the data is already there on subscribers? > > a long time back, i tried experimenting the same as you wanted > [1], it worked for me. but since i never got any confirmation, so > i did not go with it. I do not know if this is the right way or if > there are any gotchas. but i tried a demo just now on pg14, it > still works fine today. The only reason I wanted to do this was, I > could parallelism in restore, and compression in transfer. > > logical_replication_using_pgdump > > [1] > > Keep in mind the restrictions on logical replication. no DDL, > sequences need to be manually copied, etc. > > I did the mentioned a couple of years back, i hope someone with > more knowledge would give a better answer. > > -- > Thanks, > Vijay > Mumbai, India > --------------5C757B58C29C06C395BBE4F2 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit gosh i hate that sequence part of the equation!

Dharmendra K wrote on 7/20/2021 12:53 PM:
Thanks, Vijaykumar for the inputs.

On Tue, Jul 20, 2021 at 9:51 AM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
On Tue, 20 Jul 2021 at 20:50, Dharmendra K <dharmendra.sql@gmail.com> wrote:
Hi,
I am trying to explore Pglogical to migrate the database from one host to other and I have some questions.
1.How do the Pglogical handle if provider or subscriber is down for a significant time? Does it synch up automatically once the Db is up on both the nodes? 
subscriber creates a slot on the publisher, so WALs would be retained on the publisher if the subscriber is down. it will auto connect from where it stopped.
but make sure you have monitoring for the same as logical replication is slow. if it is broken and ignored for long, WALs pile up fast on a busy server.
 

2.I have a very big table(around 800GB) that needs to be migrated using Pglogical, does it generates a lot of WAL on provider, and how to handle the situation going out of control.
The initial sync occurs with a COPY command. so it should be fast. I dropped all but the primary key of the large table, reduced the time to COPY from almost a day to a few hours.
So if it is just one table, that has many indexes, you can try dropping the indexes but the primary key index on the subscriber in the beginning, and then rebuild the relevant index concurrently. This will save a lot of time.
 
 
3.Can we restore the database using pg dump or some other means on the subscriber node and after restore can we start Pglogical replication ? Does it synch the tables fast this way as most of the data is already there on subscribers?

a long time back, i tried experimenting the same as you wanted [1], it worked for me. but since i never got any confirmation, so i did not go with it. I do not know if this is the right way or if there are any gotchas. but i tried a demo just now on pg14, it still works fine today. The only reason I wanted to do this was, I could parallelism in restore, and compression in transfer.


Keep in mind the restrictions on logical replication. no DDL, sequences need to be manually copied, etc.

I did the mentioned a couple of years back, i hope someone with more knowledge would give a better answer. 

--
Thanks,
Vijay
Mumbai, India

--------------5C757B58C29C06C395BBE4F2--