Re: Logical decoding client has the power to crash the server

On Fri, Sep 22, 2017 at 5:44 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
> I think the difference between pg_current_wal_lsn() and confirmed_flush_lsn form pg_catalog.pg_replication_slots for specific replication slot:
>
> SELECT (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance
> FROM pg_catalog.pg_replication_slots
> WHERE slot_name = '<some_subscibtion_name>';
>
> provides a measure in Logical Replication environment of how far did (or did not) Subscriber fell behind Publisher, and hence some kind of measure of how much "extra" WALs is stored on the Publisher.

More or less. I also make use of the value of max_wal_size in
pg_settings to do the decision-making, value set depending on a
fraction of the size of the partition dedicated to pg_xlog. By
applying pg_size_bytes() which is new to 9.6 on top of the value
fetched from pg_settings you can make a direct comparison and decide
if a slot can be dropped or not. Make sure that things are casted to
bigint though.
--
Michael