Re: xmin of slot is not moving | hot standby feedback sending old xmin.

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