Re: Long Running query and trace potential issues

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

On Fri, Feb 28, 2025 at 6:50 AM KK CHN <kkchn.in@xxxxxxxxx> 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!