Subject: Re: Logical Replication Delay

From: Ramakrishna m <[email-masked]>
Date: Sun, 29 Sep 2024 15:58:46 +0530

Hi Justin and Greg,

Thank you for your input and recommendations. We understand your point
regarding separating the tables into different publications and
subscriptions.
However, due to certain business constraints, we are unable to implement
this approach at the moment.

*We are planning to set up logical replication from a standby to another
server. When the primary goes down, there is no issue as the standby
becomes the primary and the logical slots are already present. However,
when the standby goes down, these slots are not copied to the third node or
the primary by Patroni. Is there an option available to handle this
scenario? *

Regards,
Ram.

On Wed, 25 Sept 2024 at 20:12, Greg Sabino Mullane <[email-masked]>
wrote:

> On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m <[email-masked]> wrote:
>
>> I would greatly appreciate any suggestions you may have to help avoid
>> logical replication delays, whether through tuning database or operating
>> system parameters, or any other recommendations
>>
> In addition to the things already answered:
>
> * What is the use case for logical replication? I assume your local
> replicas are able to keep up just fine.
>
> * Check the nature of the work for problems, e.g. ORM doing
> unnecessary/redundant updates, maintaining indexes that are not really
> needed
>
> * Looks like your wal_segment_size was boosted to 1GB. What drove that
> change?
>
> * Yes, autovacuum could affect things - make sure
> log_autovacuum_min_durations is set
>
> Cheers,
> Greg
>
>
>

---

From: GF <[email-masked]>
Date: Sun, 29 Sep 2024 14:26:42 +0200

Hi Ram,

 29 set 2024, 12:29 Ramakrishna m <[email-masked]>:

> the primary by Patroni. Is there an option available to handle this

You could take a look at the pg_failover_slots extension (
https://www.enterprisedb.com/docs/pg_extensions/pg_failover_slots/), it is
aimed exactly at cloning the slot information to a standby.

Best,
giovanni