Обсуждение: xmin of slot is not moving | hot standby feedback sending old xmin.
Hi Team,
Issues :- xmin of physical replication slot is not moving/hung, slave is refering/stuck on old xmin. which causes it to send old xmin to primary when hot_standby_feedback is on.
PG Version :- 10.12
Slot Details : -
postgres=# select * from pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn ---------------+--------+-----------+--------+----------+-----------+--------+------------+-----------+--------------+--------------+--------------------- repmgr_slot_3 | | physical | | | f | t | 4841 | 193762035 | | 560/96081368 | repmgr_slot_1 | | physical | | | f | t | 18787 | 200856612 | | 560/96081368 | repmgr_slot_4 | | physical | | | f | t | 22825 | 200856607 | | 560/96081368 | (3 rows)
Observation :-
1. message in DB logs.
2022-07-28 08:51:43 UTC [32119]: [2-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:51:46 UTC [32119]: [61-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:51:56 UTC [32119]: [292-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:52:06 UTC [32119]: [583-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:52:16 UTC [32119]: [824-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:52:26 UTC [32119]: [1056-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:52:36 UTC [32119]: [1347-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:52:46 UTC [32119]: [1621-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:52:56 UTC [32119]: [1848-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0 2022-07-28 08:53:06 UTC [32119]: [2161-1] user=,db=,app=,client=DEBUG: sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 catalog_xmin_epoch 0
Tried troubleshooting :-
1. Checked for a long running query on primary and slave and found no SQL present.
2. Turn off hot_standby_feedback for sometime.Post turning on hot_standby_feedback the same xmin is assigned to the slot.
3. Killed wal receiver process.
4. Restarted slave/standby databases still hot standby feedback sending/refering old xmin.
Can you please help us to understand why this is happening, Is this a bug ?
Also is there any solution other than recreating the slot or turning off the hot_standby_feedback to fix this?
Hi, On 2022-07-28 15:08:07 +0530, Tushar Takate wrote: > *Issues :-* xmin of physical replication slot is not moving/hung, slave is > refering/stuck on old xmin. which causes it to send old xmin to primary > when hot_standby_feedback is on. > > *PG Version* :- 10.12 That's several years out of date. Update to a new version. > *Slot Details : - * > > postgres=# select * from pg_replication_slots ; > slot_name | plugin | slot_type | datoid | database | temporary | > active | active_pid | xmin | catalog_xmin | restart_lsn | > confirmed_flush_lsn > ---------------+--------+-----------+--------+----------+-----------+--------+------------+-----------+--------------+--------------+--------------------- > repmgr_slot_3 | | physical | | | f | > t | 4841 | *193762035* | | 560/96081368 | > repmgr_slot_1 | | physical | | | f | > t | 18787 | 200856612 | | 560/96081368 | > repmgr_slot_4 | | physical | | | f | > t | 22825 | 200856607 | | 560/96081368 | > (3 rows) > > > *Observation :- * > > 1. message in DB logs. > > 2022-07-28 08:51:43 UTC [32119]: [2-1] user=,db=,app=,client=DEBUG: > sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0 > catalog_xmin_epoch 0 This just looks like there's actually an old transaction alive on the standby. > 1. Checked for a long running query on primary and slave and found no SQL > present. What do you mean with "no SQL present"? > 2. Turn off hot_standby_feedback for sometime.Post turning on > hot_standby_feedback > the same xmin is assigned to the slot. > 3. Killed wal receiver process. > 4. Restarted slave/standby databases still hot standby feedback > sending/refering old xmin. > > Can you please help us to understand why this is happening, Is this a bug ? > > Also is there any solution other than recreating the slot or turning off > the hot_standby_feedback to fix this? Have you checked if there's a slot on the standby? Greetings, Andres Freund