Re: Queries waiting on SyncRep even though synchronous_standby_names is absent

[khan Affan <bawag773@xxxxxxxxx>]

Hi Nikhil

Reloading the configuration allows PostgreSQL to re-read its configuration files and apply any changes to dynamic parameters without restarting the server. This operation is lightweight and does not disrupt ongoing transactions or connections.

 'show synchronous_standby_names' is a dynamic parameter; that's why it needs to reload to take effect

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 4:38 PM Nikhil Shetty <nikhil.dba04@xxxxxxxxx> wrote:

Hi Affan,

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();

I had done a database reload for the synchronous_standby_names setting to take effect and that is why when I do 'show synchronous_standby_names' it shows empty. Without reload it would show an old setting.

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

This is an easy way to get the system back which I already did but I would like to understand the reason behind this or at least see how we can debug ?

Thanks,

Nikhil

On Thu, Jul 18, 2024 at 3:02 PM khan Affan <bawag773@xxxxxxxxx> wrote:

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