Re: Long Running query and trace potential issues

On Fri, Feb 28, 2025 at 6:50 AM KK CHN <kkchn(dot)in(at)gmail(dot)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!