Re: Queries waiting on SyncRep even though synchronous_standby_names is absent

[khan Affan <bawag773@xxxxxxxxx>]

Hi

Other than that, if the standbys are slow, disconnected, or down, the primary will wait for acknowledgements from one or more synchronous standbys. By resetting the synchronous_standby_names parameter, you changed the configuration of which standby servers need to confirm transactions for them to be considered committed. Please execute the following command on the primary node after resetting the synchronous_standby_names to reload the latest configurations:

SELECT pg_reload_conf();

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 2:20 PM khan Affan <bawag773@xxxxxxxxx> wrote:

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@xxxxxxxxx> 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