Re: Queries waiting on SyncRep even though synchronous_standby_names is absent

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(at)gmail(dot)com> 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(dot)dba04(at)gmail(dot)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
>>
>