Postgres Pro
Downloads
Home   >   mailing lists

Thread: Long Running query and trace potential issues

Long Running query and trace potential issues

From
KK CHN
Date:
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

Re: Long Running query and trace potential issues

From
Greg Sabino Mullane
Date:
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

--
Enterprise Postgres Software Products & Tech Support

Re: Long Running query and trace potential issues

From
Ron Johnson
Date:
On Fri, Feb 28, 2025 at 6:50 AM KK CHN <kkchn.in@gmail.com> wrote:
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 ?

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

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

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