pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpglogical question
5+ messages / 3 participants
[nested] [flat]
* pglogical question
@ 2021-07-20 15:20 Dharmendra K <dharmendra.sql@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dharmendra K @ 2021-07-20 15:20 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
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.
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?
Appreciate any points.
Thanks,
Kumar
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pglogical question
@ 2021-07-20 16:50 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Dharmendra K <dharmendra.sql@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Vijaykumar Jain @ 2021-07-20 16:50 UTC (permalink / raw)
To: Dharmendra K <dharmendra.sql@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
<https://www.postgresql.org/message-id/CAE7uO5is2kYZVZOrMcNEmHM%3DpoXH8OPCJjp262V9WSQNtZhkKg%40mail.g...;
[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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pglogical question
@ 2021-07-20 16:53 Dharmendra K <dharmendra.sql@gmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Dharmendra K @ 2021-07-20 16:53 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
> <https://www.postgresql.org/message-id/CAE7uO5is2kYZVZOrMcNEmHM%3DpoXH8OPCJjp262V9WSQNtZhkKg%40mail.g...;
> [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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pglogical question
@ 2021-07-20 16:59 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Dharmendra K <dharmendra.sql@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: MichaelDBA @ 2021-07-20 16:59 UTC (permalink / raw)
To: Dharmendra K <dharmendra.sql@gmail.com>; +Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.github@gmail.com
> <mailto:vijaykumarjain.github@gmail.com>> wrote:
>
> On Tue, 20 Jul 2021 at 20:50, Dharmendra K
> <dharmendra.sql@gmail.com <mailto: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
> <https://www.postgresql.org/message-id/CAE7uO5is2kYZVZOrMcNEmHM%3DpoXH8OPCJjp262V9WSQNtZhkKg%40mail.g...;
> [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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pglogical question
@ 2021-07-26 16:05 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Dharmendra K <dharmendra.sql@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Vijaykumar Jain @ 2021-07-26 16:05 UTC (permalink / raw)
To: Dharmendra K <dharmendra.sql@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
So, there is an official blog for your case 3 from percona.
https://www.percona.com/blog/postgresql-logical-replication-using-an-rds-snapshot/?utm_source=employ...
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-07-26 16:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-20 15:20 pglogical question Dharmendra K <dharmendra.sql@gmail.com>
2021-07-20 16:50 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-07-20 16:53 ` Dharmendra K <dharmendra.sql@gmail.com>
2021-07-20 16:59 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-07-26 16:05 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox