Re: pglogical question

Thanks, Vijaykumar for the inputs.

On Tue, Jul 20, 2021 at 9:51 AM Vijaykumar Jain <
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>
> 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
>