Обсуждение: pglogical question

Thanks, Vijaykumar for the inputs.

On Tue, Jul 20, 2021 at 9:51 AM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:

On Tue, 20 Jul 2021 at 20:50, Dharmendra K <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  [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