Postgres Pro
Downloads
Home   >   mailing lists

Re: Queries waiting on SyncRep even though synchronous_standby_names is absent - Mailing list pgsql-admin

From khan Affan
Subject Re: Queries waiting on SyncRep even though synchronous_standby_names is absent
Date
Msg-id CAF4emOmFwBMtYnzGf7gw-tFvr6szJd96CVygTnRRQZNAd2mnrw@mail.gmail.com
Whole thread Raw
In response to Queries waiting on SyncRep even though synchronous_standby_names is absent  (Nikhil Shetty <nikhil.dba04@gmail.com>)
Responses Re: Queries waiting on SyncRep even though synchronous_standby_names is absent
List pgsql-admin
Tree view
Hi 

Check the stuck transactions by

SELECT pid, username, state, query, xact_start
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL;

Forcefully truncate long or stuck transactions; if it still doesn't help, restart the instance.

Thanks 


Muhammad Affan (아판)

PostgreSQL Technical Support Engineer / Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan


On Thu, Jul 18, 2024 at 1:43 PM Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
Hi Team,

PostgreSQL : 13.6

I saw a strange behavior today. All write queries were stuck on the 'SyncRep' wait event so I reset the synchronous_standby_names.

postgres=# show synchronous_standby_names ;
 synchronous_standby_names 
---------------------------
 
(1 row)

We still saw many queries waiting on SyncRep  

 datid | datname  |  pid  | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start   
       |         state_change          | wait_event_type | wait_event | state  | backend_xid | backend_xmin |                                        query                                        |  backend_type  
-------+----------+-------+------------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+------------------------
-------+-------------------------------+-----------------+------------+--------+-------------+--------------+-------------------------------------------------------------------------------------+----------------
 16401 | repmgr | 24344 |            |    16385 | postgres   | psql             |             |                 |          -1 | 2024-07-14 09:55:09.615682+00 | 2024-07-14 09:55:09.622254+00 | 2024-07-14 09:55:09.622
254+00 | 2024-07-14 09:55:09.622255+00 | IPC             | SyncRep    | active |   343975184 |    343974710 | UPDATE repmgr.sync_check SET row_update_time = (select timezone('UTC', now())) | client backend
(1 row)



Has anyone faced this issue or have any inputs 


Thank you,

Nikhil

pgsql-admin by date:

Previous
From: Nikhil Shetty
Date:
Subject: Queries waiting on SyncRep even though synchronous_standby_names is absent
Next
From: khan Affan
Date:
Subject: Re: Queries waiting on SyncRep even though synchronous_standby_names is absent