MIME-Version: 1.0
From: KK CHN <kkchn.in@gmail.com>
Date: Fri, 28 Feb 2025 17:21:50 +0530
Message-ID: <CAKgGyB_a=D7fKf6Jh-2a9=jArKECODHn_xGRb=w6suXhS+SL-A@mail.gmail.com>
Subject: Long Running query and trace potential issues
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000066925a062f3269f7"
Archived-At: <https://www.postgresql.org/message-id/CAKgGyB_a%3DD7fKf6Jh-2a9%3DjArKECODHn_xGRb%3Dw6suXhS%2BSL-A%40mail.gmail.com>
Precedence: bulk

--00000000000066925a062f3269f7
Content-Type: text/plain; charset="UTF-8"

List

postgres=# 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 = 'active';
 pid  |        duration         |                  query
| state
------+-------------------------+------------------------------------------+--------
 3957 | 17 days 12:00:30.782583 | START_REPLICATION B5/EE000000 TIMELINE 1
| active
(1 row)
postgres=#

Postgres 16 and pgbackrest 2.52.1 on RHEL 9.4

  Is this a potential issue ? 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 ?


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 is much
appreciated..

Thank you,
Krishane

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

<div dir=3D"ltr">List=C2=A0<div><br></div><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_activity.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/EE0=
00000 TIMELINE 1 | active<br>(1 row)<br><span style=3D"color:rgb(118,118,11=
8);font-family:Arial,sans-serif;font-size:14px">postgres=3D#</span></div><d=
iv><br></div><div>Postgres 16 and pgbackrest 2.52.1 on RHEL 9.4</div><div><=
br></div><div>=C2=A0 Is this a potential issue ? It has been 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 normal running 17 d=
ays START_REPLICATION B5/EE000000 TIMELINE1 active ?=C2=A0 =C2=A0</div><div=
><br></div><div><br></div><div>Any hints=C2=A0 to further trace=C2=A0 where=
 the query is executing, what it&#39;s trying to perform, how to optimize i=
f this is not normal=C2=A0? Any guidance is much appreciated..</div><div><b=
r></div><div>Thank you,</div><div>Krishane</div></div>

--00000000000066925a062f3269f7--