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.
