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

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Michael Paquier
Sent: Thursday, September 21, 2017 12:33 AM
To: Meel Velliste <meel(at)fivetran(dot)com>
Cc: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Logical decoding client has the power to crash the server

On Thu, Sep 21, 2017 at 1:09 PM, Meel Velliste <meel(at)fivetran(dot)com> wrote:
> In this situation, neither us, nor our customer has the power to
> install the required monitoring of pg_xlog. The database hosting
> provider would have to do it. In most cases (e.g. Amazon RDS) the
> hosting provider does provide a way of monitoring overall disk usage,
> which may be good enough. But I am thinking it would make sense for
> postgres to have default, built-in monitoring that drops all the slots
> when pg_xlog gets too full (based on some configurable limit).
> Otherwise everybody has to build their own monitoring and I imagine
> 99% of them would want the same behavior. Nobody wants their database
> to fail just because some client was not reading the slot.

(Please avoid top-posting, this breaks the logic of the thread and this is contrary to the practices of the Postgres mailing lists)

Note that on-disk lookup is not strictly necessary. If you know max_wal_size, pg_current_wal_lsn (or pg_last_wal_receive_lsn if working on a standby) and the restart_lsn of the slots that's enough.
If you don't have privileges sufficient to see that, well I guess that you will need to review the access permissions to your instance.
Postgres 9.6 offers better access control to system functions, so you could be granted access to just those resources to be fine using a SQL session.
--
Michael

______________________________________________________________________________________________________

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.

Regards,
Igor Neyman