xmin of slot is not moving | hot standby feedback sending old xmin. - Mailing list pgsql-bugs

From Tushar Takate
Subject xmin of slot is not moving | hot standby feedback sending old xmin.
Date
Msg-id CAE4W+S+KYja6bTJGDqMe9B-Neq8wxNij98MPjo7ekHxRi97TPQ@mail.gmail.com
Whole thread Raw
Responses Re: xmin of slot is not moving | hot standby feedback sending old xmin.
List pgsql-bugs
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?


-
Thanks & Regards,

Tushar K Takate .
Mob-No : +91-860-030-2404
LinkedIn : Tushar Takate

pgsql-bugs by date:

Previous
From: Saravanan Shanmugam
Date:
Subject: Re: Create temporary table in replica instances issues
Next
From: Guillaume Lelarge
Date:
Subject: Re: Create temporary table in replica instances issues