Re: pglogical question

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