MIME-Version: 1.0
References: <CAKgGyB_a=D7fKf6Jh-2a9=jArKECODHn_xGRb=w6suXhS+SL-A@mail.gmail.com>
In-Reply-To: <CAKgGyB_a=D7fKf6Jh-2a9=jArKECODHn_xGRb=w6suXhS+SL-A@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 28 Feb 2025 08:34:44 -0500
Message-ID: <CANzqJaCt3FeOK7rgMO47X6d4LW+M980YaWRpUutc_-Yfn3YXxw@mail.gmail.com>
Subject: Re: Long Running query and trace potential issues
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003f7d97062f33df7e"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCt3FeOK7rgMO47X6d4LW%2BM980YaWRpUutc_-Yfn3YXxw%40mail.gmail.com>
Precedence: bulk

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

On Fri, Feb 28, 2025 at 6:50=E2=80=AFAM KK CHN <kkchn.in@gmail.com> wrote:

> List
>
> postgres=3D# SELECT PID, now() - pg_stat_activity.query_start AS duration=
,
> query, state FROM pg_stat_activity WHERE (now() - pg
> _stat_activity.query_start) > interval '5 minutes' AND state =3D 'active'=
;
>  pid  |        duration         |                  query
> | state
>
> ------+-------------------------+----------------------------------------=
--+--------
>  3957 | 17 days 12:00:30.782583 | START_REPLICATION B5/EE000000 TIMELINE =
1
> | active
> (1 row)
> postgres=3D#
>
> Postgres 16 and pgbackrest 2.52.1 on RHEL 9.4
>
>   Is this a potential issue ?
>

Only if you don't want to be replicating to a standby server.


> It has been running for 17 days .  ( I have configured WAL replication to
> a standby server and pgbackrest to a remote server )  is this  normal
> running 17 days START_REPLICATION B5/EE000000 TIMELINE1 active ?
>

Select more columns, like username,client_hostname and client_addr.

You started replicating to the standby server 17 and a half days ago. This
is how PG replicates to the standby server.


> Any hints  to further trace  where the query is executing, what it's
> trying to perform, how to optimize if this is not normal ? Any guidance i=
s
> much appreciated..
>

It's certainly normal if you want to replicate to a standby server.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Feb 28, 2025 at 6:50=E2=80=AFAM K=
K CHN &lt;<a href=3D"mailto:kkchn.in@gmail.com">kkchn.in@gmail.com</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=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">List=C2=A0<div><br></d=
iv><div><span style=3D"color:rgb(118,118,118);font-family:Arial,sans-serif;=
font-size:14px">postgres=3D#</span>=C2=A0SELECT PID, now() - pg_stat_activi=
ty.query_start AS duration, query, state FROM pg_stat_activity WHERE (now()=
 - pg<br>_stat_activity.query_start) &gt; interval &#39;5 minutes&#39; AND =
state =3D &#39;active&#39;;<br>=C2=A0pid =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=
=A0duration =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0query =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 | state<br>------+-------------------------+------=
------------------------------------+--------<br>=C2=A03957 | 17 days 12:00=
:30.782583 | START_REPLICATION B5/EE000000 TIMELINE 1 | active<br>(1 row)<b=
r><span style=3D"color:rgb(118,118,118);font-family:Arial,sans-serif;font-s=
ize:14px">postgres=3D#</span></div><div><br></div><div>Postgres 16 and pgba=
ckrest 2.52.1 on RHEL 9.4</div><div><br></div><div>=C2=A0 Is this a potenti=
al issue ?</div></div></blockquote><div><br></div><div>Only if you don&#39;=
t want to be replicating to a standby server.</div><div>=C2=A0</div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div> It has be=
en running for 17 days .=C2=A0 ( I have configured=C2=A0WAL replication to =
a standby server and pgbackrest to a remote server )=C2=A0 is this=C2=A0 no=
rmal running 17 days START_REPLICATION B5/EE000000 TIMELINE1 active ?=C2=A0=
 =C2=A0</div></div></blockquote><div><br></div><div>Select more columns, li=
ke username,client_hostname and client_addr.</div><div><br></div><div>You s=
tarted replicating to the standby server 17 and a half days ago. This is ho=
w PG replicates to the standby server.</div><div>=C2=A0</div><blockquote cl=
ass=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"><div>Any hints=C2=A0 t=
o further trace=C2=A0 where the query is executing, what it&#39;s trying to=
 perform, how to optimize if this is not normal=C2=A0? Any guidance is much=
 appreciated..</div></div></blockquote><div><br></div><div>It&#39;s certain=
ly normal if you want to replicate to a standby server.</div><div><br></div=
></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr=
" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and=
 butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt=
;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000003f7d97062f33df7e--