Re: Questions on logical replication

On Thu, Jun 13, 2024 at 6:01 AM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:

> > Why? what benefit does this provide you?? Add all the tables when
> creating the publication and be done with it... I get this when trying to
> understand how this all works on test boxes, but for production NO idea
> what you're trying to accomplish
>
> Adding all tables at once means adding the gigantic tables as well. Disk
> IO and Network traffic are a serious concern, increased CPU usage affecting
> queries of the live system, as well as transaction wraparound.
>
> Initial sync can be a serious concern, depending on the size of the table.
>

The number of initial sync workers can be controlled
via max_sync_workers_per_subscription
see https://www.postgresql.org/docs/current/logical-replication-config.html

if you want to do one table at a time just set sync workers to 1.

If bandwidth is a problem either from the disk or network, direct the
network traffic from the subscriber through a proxy or firewall to throttle
the network speed. Slowing the copy will cause the WAL to build up on the
publisher

CPU load on the publisher is very low its actually hard to see it doing
anything as its just reading the disk, streaming it to the subscriber..

For large tables with lots of indexes for the copy to complete as fast as
possible to prevent WAL build up, drop indexes. For me the WAL build up
has only been an issue when dealing with multi-TB sized tables when it
takes several days to copy the data for one table.

One trick is to remove all the indexes during the initial sync except for
the primary key so the subscriber has less work to do.

> Here's a nice guide where people did a logical replication upgrade,
> explaining why they did it this way:
> https://knock.app/blog/zero-downtime-postgres-upgrades
>

The blog suggests overly complicated things. only doing 100GB chunks of
data at one time. Maybe if the publisher was scarce on resources or the
table is multi-TB in size it requires days to weeks to copy...

If the publisher is so low on resources that Logical Replication is
problematic one can create a binary replica, promote it and convert it to
logical replication skipping the initial sync. Then upgrade that server.
There is a minor outage required to convert a binary replica to a logical
replica. I've done it in under 30 seconds.

>
> On Wed, Jun 12, 2024 at 7:01 PM Justin <zzzzz(dot)graf(at)gmail(dot)com> wrote:
>
>>
>>
>> On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>>
>>> > If there are any errors during the replay of WAL such as missing
>>> indexes for Replica Identities during an Update or Delete this will cause
>>> the main subscriber worker slot on the publisher to start backing up WAL
>>> files
>>>
>>> And also if the connection breaks, from what I understand, is that
>>> correct? Anything that stops the subscription, including disabling the
>>> subscription, is that right?
>>>
>>
>> Yes to all....
>>
>>
>>> > I suggest confirming all tables have replica identities or primary
>>> keys before going any further.
>>>
>>> Yes, I am aware of this. I made me a small script that prints which
>>> tables I have added to the publication and are done syncing, and which are
>>> currently not being replicated.
>>>
>>
>>
>>>
>>> > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan
>>> on the subscriber for PG 15 and earlier.
>>>
>>> I'm also aware of this. My plan is to create a publication with no
>>> tables, and add them 1 by 1, refreshing the subscriber each time.
>>>
>>
>> Why? what benefit does this provide you?? Add all the tables when
>> creating the publication and be done with it... I get this when trying to
>> understand how this all works on test boxes, but for production NO idea
>> what you're trying to accomplish
>>
>>
>>> I'm not planning on using "REPLICA IDENTITY FULL" anywhere.
>>>
>> Good
>>
>