MIME-Version: 1.0
References: <CAG-eXHKr5pFzCKNqVhjQAUM0oxxCe8YUfjoLT8WmZx54Sk48kg@mail.gmail.com>
 <CAKAnmmLmvuo=TORx=hDw2y5fFOUc0sHb88O+6Z-Cs=oVtN9W4A@mail.gmail.com>
In-Reply-To: <CAKAnmmLmvuo=TORx=hDw2y5fFOUc0sHb88O+6Z-Cs=oVtN9W4A@mail.gmail.com>
From: Ramakrishna m <ram.pgdb@gmail.com>
Date: Sun, 29 Sep 2024 15:58:46 +0530
Message-ID: <CAG-eXHJNRhnjEbCYHdpcQULxaGqV0_JR4nSPxndRRUdshMNZ0A@mail.gmail.com>
Subject: Re: Logical Replication Delay
To: Greg Sabino Mullane <htamfids@gmail.com>, Justin <zzzzz.graf@gmail.com>
Cc: pgsql-general@lists.postgresql.org, ravisql09@gmail.com
Content-Type: multipart/alternative; boundary="0000000000005ac61806233f8e96"
Archived-At: <https://www.postgresql.org/message-id/CAG-eXHJNRhnjEbCYHdpcQULxaGqV0_JR4nSPxndRRUdshMNZ0A%40mail.gmail.com>
Precedence: bulk

--0000000000005ac61806233f8e96
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 <htamfids@gmail.com>
wrote:

> On Sat, Sep 21, 2024 at 3:08=E2=80=AFPM Ramakrishna m <ram.pgdb@gmail.com=
> 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
>
>
>

--0000000000005ac61806233f8e96
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br><div><font face=3D"arial, sans-serif"=
 color=3D"#000000" style=3D"background-color:rgb(255,255,255)">Hi Justin an=
d Greg,</font></div><div><span style=3D"background-color:rgb(255,255,255)">=
<font color=3D"#000000" face=3D"arial, sans-serif"><br></font></span></div>=
<div><font color=3D"#000000" style=3D"background-color:rgb(255,255,255)" fa=
ce=3D"arial, sans-serif">Thank you for your input and recommendations. We u=
nderstand your point regarding separating the tables into different publica=
tions and subscriptions.<br></font></div><div><font face=3D"arial, sans-ser=
if" color=3D"#000000" style=3D"background-color:rgb(255,255,255)">However, =
due to certain business constraints, we are unable to implement this approa=
ch at the moment.</font></div><div><font face=3D"arial, sans-serif" color=
=3D"#000000" style=3D"background-color:rgb(255,255,255)"><br></font></div><=
div><font face=3D"arial, sans-serif" color=3D"#000000" style=3D"background-=
color:rgb(255,255,255)"><b>We are planning to set up logical replication fr=
om a standby to another server. When the primary goes down, there is no iss=
ue as the standby becomes the primary and the logical slots are already pre=
sent. However, when the standby goes down, these slots are not copied to th=
e third node or the primary by Patroni. Is there an option available to han=
dle this scenario?=C2=A0</b><br></font></div><div><font face=3D"arial, sans=
-serif" color=3D"#000000" style=3D"background-color:rgb(255,255,255)"><br><=
/font></div><div><font face=3D"arial, sans-serif" color=3D"#000000" style=
=3D"background-color:rgb(255,255,255)">Regards,</font></div><div><font face=
=3D"arial, sans-serif" color=3D"#000000" style=3D"background-color:rgb(255,=
255,255)">Ram.</font></div><div><br></div></div><br><div class=3D"gmail_quo=
te"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 25 Sept 2024 at 20:12, Gr=
eg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail.=
com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><div dir=3D"ltr"><div dir=3D"ltr">On Sat, Sep 21, 2024 at 3:08=E2=80=AFP=
M Ramakrishna m &lt;<a href=3D"mailto:ram.pgdb@gmail.com" target=3D"_blank"=
>ram.pgdb@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><p></p><p><s=
pan style=3D"color:rgb(0,0,0);font-family:arial,sans-serif">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</span></p></div></blockquote><div>In addition to =
the things=C2=A0already answered:</div><div><br></div><div>* What is the us=
e case for logical=C2=A0replication? I assume your local replicas are able =
to keep up just fine.</div><div><br></div><div>* Check the nature of the wo=
rk for problems, e.g. ORM doing unnecessary/redundant updates, maintaining =
indexes that are not really needed</div><div><br></div><div>* Looks like yo=
ur wal_segment_size was boosted to 1GB. What drove that change?</div><div><=
br></div><div>* Yes, autovacuum could affect things - make sure log_autovac=
uum_min_durations is set</div><div><br></div><div>Cheers,</div><div>Greg</d=
iv><div><br></div><div>=C2=A0</div></div></div>
</blockquote></div><div><br></div></div>

--0000000000005ac61806233f8e96--