Subject: Long Running query and trace potential issues

From: KK CHN <[email-masked]>
Date: Fri, 28 Feb 2025 17:21:50 +0530

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

---

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 28 Feb 2025 07:38:14 -0500

That's harmless, it is the walsender process, and it is meant to be
long-running. You can modify your query and add this:

AND backend_type = 'client backend'

to filter out any background processes.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support