Thread

1. Long Running query and trace potential issues

   KK CHN <kkchn.in@gmail.com> — 2025-02-28T11:51:50Z

   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
   

2. Re: Long Running query and trace potential issues

   Greg Sabino Mullane <htamfids@gmail.com> — 2025-02-28T12:38:14Z

   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
   

3. Re: Long Running query and trace potential issues

   Ron <ronljohnsonjr@gmail.com> — 2025-02-28T13:34:44Z

   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!