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.
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.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
