Re: physical slot xmin dependency on logical slot? - Mailing list pgsql-hackers

From Andres Freund
Subject Re: physical slot xmin dependency on logical slot?
Date
Msg-id 20191118221254.pebvps42b6p3nfux@alap3.anarazel.de
Whole thread Raw
In response to physical slot xmin dependency on logical slot?  (Jeremy Finzel <finzelj@gmail.com>)
List pgsql-hackers
Hi,

On 2019-11-18 15:36:47 -0600, Jeremy Finzel wrote:
> We had a scenario today that was new to us.  We had a logical replication
> slot that was severely far behind.  Before dropping this logical slot, we
> made a physical point-in-time-recovery snapshot of the system with this
> logical slot.

> This logical slot was causing severe catalog bloat.  We proceeded to drop
> the logical slot which was over 12000 WAL segments behind.  The physical
> slot was only a few 100 segments behind and still in place.
> 
> But now proceeding to VAC FULL the catalog tables did not recover any bloat
> beyond the now-dropped logical slot.  Eventually to our surprise, we found
> that dropping the physical slot allowed us to recover the bloat.
> 
> We saw in forensics after the fact that xmin of the physical slot equaled
> the catalog_xmin of the logical slot.  Is there some dependency here where
> physical slots made of a system retain all transactions of logical slots it
> contains as well?  If so, could someone help us understand this, and is
> there documentation around this?  Is this by design?
> 
> We had thought that the physical slot would only retain the WAL it needed
> for its own restart_lsn, not the segments needed by only logical slots as
> well.  Any explanation would be much appreciated!

The logical slot on the standby affects hot_standby_feedback, which in
turn means that the physical slot also transports xmin horizons to the
primary.

Note that our docs suggest to drop slots when cloning a node (and
pg_basebackup/basebackup on the server side do so automatically):
   <para>
    It is often a good idea to also omit from the backup the files
    within the cluster's <filename>pg_replslot/</filename> directory, so that
    replication slots that exist on the master do not become part of the
    backup.  Otherwise, the subsequent use of the backup to create a standby
    may result in indefinite retention of WAL files on the standby, and
    possibly bloat on the master if hot standby feedback is enabled, because
    the clients that are using those replication slots will still be connecting
    to and updating the slots on the master, not the standby.  Even if the
    backup is only intended for use in creating a new master, copying the
    replication slots isn't expected to be particularly useful, since the
    contents of those slots will likely be badly out of date by the time
    the new master comes on line.
   </para>

It's generally useful to look at pg_stat_replication for these kinds of
things...

Greetings,

Andres Freund



pgsql-hackers by date:

Previous
From: Andrew Gierth
Date:
Subject: Re: Reverse collations (initially for making keyset pagination cover more cases)
Next
From: Tom Lane
Date:
Subject: Re: Invisible PROMPT2