Re: pg_replslotdata - a tool for displaying replication slot information

On Tue, Nov 30, 2021 at 9:47 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> On Wed, Dec 1, 2021 at 12:13 AM Bossart, Nathan <bossartn(at)amazon(dot)com>
> wrote:
> >
> > On 11/30/21, 6:14 AM, "Peter Eisentraut" <
> peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
> > > On 23.11.21 06:09, Bharath Rupireddy wrote:
> > >> The replication slots data is stored in binary format on the disk
> under
> > >> the pg_replslot/<<slot_name>> directory which isn't human readable. If
> > >> the server is crashed/down (for whatever reasons) and unable to come
> up,
> > >> currently there's no way for the user/admin/developer to know what
> were
> > >> all the replication slots available at the time of server crash/down
> to
> > >> figure out what's the restart lsn, xid, two phase info or types of
> slots
> > >> etc.
> > >
> > > What do you need that for? You can't do anything with a replication
> > > slot while the server is down.
> >
> > One use-case might be to discover the value you need to set for
> > max_replication_slots, although it's pretty trivial to discover the
> > number of replication slots by looking at the folder directly.
>
> Apart from the above use-case, one can do some exploratory analysis on
> the replication slot information after the server crash, this may be
> useful for RCA or debugging purposes, for instance:
> 1) to look at the restart_lsn of the slots to get to know why there
> were many WAL files filled up on the disk (because of the restart_lsn
> being low)
>

In a disk full scenario because of WAL, this tool comes handy identifying
which WAL files to delete to free up the space and also help assess the
accidental delete of the WAL files. I am not sure if there is a tool to
help cleanup the WAL (may be invoking the archive_command too?) without
impacting physical / logical slots, and respecting last checkpoint location
but if one exist that will be handy