On Tue, 19 Nov 2019 at 05:37, Jeremy Finzel <finz...@gmail.com> 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? > I expect that you created the replica in a manner that preserved the logical replication slot on it. You also had hot_standby_feedback enabled. PostgreSQL standbys send the global xmin and (in Pg10+) catalog_xmin to the upstream when hot_standby_feedback is enabled. If there's a slot holding the catalog_xmin on the replica down, that'll be passed on via hot_standby_feedback to the upstream. On Pg 9.6 or older, or if the replica isn't using a physical replication slot, the catalog_xmin is treated as a regular xmin since there's nowhere in PGPROC or PGXACT to track the separate catalog_xmin. If the standby uses a physical slot, then on pg10+ the catalog_xmin sent by the replica is stored as the catalog_xmin on the physical slot instead. Either way, if you have hot_standby_feedback enabled on a standby, that feedback includes the requirements of any replication slots on the standby. -- Craig Ringer http://www.2ndQuadrant.com/ 2ndQuadrant - PostgreSQL Solutions for the Enterprise
