Re: Queries waiting on SyncRep even though synchronous_standby_names is absent - Mailing list pgsql-admin
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:
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@gmail.com> wrote:
HiCheck 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.6I 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 SyncRepdatid | 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: