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