From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Dharmendra K <dharmendra.sql@gmail.com>
Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: pglogical question
Date: Tue, 20 Jul 2021 12:59:33 -0400
Message-ID: <65ffb871-3394-96ef-0e7f-20a6d9862d0e@sqlexec.com> (raw)
In-Reply-To: <CADGwYGX+4mDOfONq26N4Crv-acgh2wEPuwPRGz_ScSo-8S0BrQ@mail.gmail.com>
References: <CADGwYGVUuOp+tuu=U77TruDqAcCi6QGkSWaycXCFmA5AMXHxAA@mail.gmail.com>
<CAM+6J97UOqgUQ9TGJ_iOSNuKKnShKMniu-J5RCe=GFHWwFKYRg@mail.gmail.com>
<CADGwYGX+4mDOfONq26N4Crv-acgh2wEPuwPRGz_ScSo-8S0BrQ@mail.gmail.com>
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
> <mailto:vijaykumarjain.github@gmail.com>> wrote:
>
> On Tue, 20 Jul 2021 at 20:50, Dharmendra K
> <dharmendra.sql@gmail.com <mailto: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.
>
> logical_replication_using_pgdump
> <https://www.postgresql.org/message-id/CAE7uO5is2kYZVZOrMcNEmHM%3DpoXH8OPCJjp262V9WSQNtZhkKg%40mail.g...;
> [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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, dharmendra.sql@gmail.com, vijaykumarjain.github@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: pglogical question
In-Reply-To: <65ffb871-3394-96ef-0e7f-20a6d9862d0e@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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