Re: Queries waiting on SyncRep even though synchronous_standby_names is absent

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