Re: pglogical question

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(dot)github(at)gmail(dot)com
> <mailto:vijaykumarjain(dot)github(at)gmail(dot)com>> wrote:
>
> On Tue, 20 Jul 2021 at 20:50, Dharmendra K
> <dharmendra(dot)sql(at)gmail(dot)com <mailto:dharmendra(dot)sql(at)gmail(dot)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.gmail.com>
> [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
>