Description
Playing on pre production environment I've just stumbled into this exception raised by subscriber: Npgsql.PostgresException (0x80004005): 55000: cannot read from logical replication slot "slot_name".
Publication comes from a low frequency use case, hence no surprise - even if max_slot_wal_keep_size value can easily accomodate over two days of transaction logs.
The WAL LSN (Log Sequence Number) the subscriber is referring to in received_lsn from see pg_stat_subscription can become unreachable when the belonging wal segment becomes unreachable due to retention policy value, causing any attempt to resume reading from the stream failing miserably.
Force recreating the subscription slot is in this case is my suggested approach to recover the flow, since this is a perfectly legit use case in our scenario *...In the end we are just cherry picking our business use case from the whole db transaction log, so we have to prepare some unplanned stuff.
* I simply extended the worker policy with specific code see
For convenience I've just added a playground to test the use case - run the file in a pws shell and follow the instructions