-1

I've RDS PostgreSQL DB with read-replica. Also, have one script which frequently checks the blocking query but I faced one issue yesterday where the blocking query was a startup(backend_type)process on read-replica. I've detailed logs enabled but I couldn't find anything suspicious there. What could be the reason of startup process is blocking other queries?

4
  • 3
    Please post more details about this “blocking query”. How are you determining its “blocking”, and what does pg_stat_activity say it is doing?
    – dwhitemv
    Nov 24, 2022 at 15:48
  • @dwhitemv I'm using this query to find out the blocked queries. ``` SELECT activity.pid, activity.usename, activity.query, blocking.pid AS blocking_id, blocking.query AS blocking_query FROM pg_stat_activity AS activity JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid)); ``` The result is like this (2098, 'My select query', 365, '') Here 365 is pid of the backend_type as 'startup' Nov 25, 2022 at 9:27
  • prnt.sc/G0sdWL1Asc1m I'm not sure about what would be the values at the time of blocked transactions. Nov 25, 2022 at 9:31
  • I suggest you share the query used to find blocking queries, and its results. Right now your question looks a bit vague and there is not enough information to help you. Nov 29, 2022 at 18:34

1 Answer 1

0

Your screenshot shows the WAL Recovery reader process, which is handling receiving and processing incoming WAL as part of physical replication. It is working properly, and you should exclude it from your monitoring script.

Wait states are described in the documentation for pg_stat_activity.

2
  • But all the blocked queries were on that and db sessions were increasing exponentially. Nov 28, 2022 at 9:31
  • Large transactions on the primary will be large transactions on the replica. Not much you can do about that, apart from increasing the replica’s resources. Killing the WAL receiver will desynchronize your replica.
    – dwhitemv
    Nov 28, 2022 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.