Horton

Re: replication_slots usability issue

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: "Joshua D. Drake" <jd@commandprompt.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2018-10-29T19:13:04Z
Lists: pgsql-hackers 
On 2018-10-29 16:02:18 -0300, Alvaro Herrera wrote:
> On 2018-Oct-29, Joshua D. Drake wrote:
> 
> > -Hackers,
> > 
> > 
> > Working on 9.6 today (unsure if fixed in newer versions). Had an issue where
> > the wal was 280G despite max_wal_size being 8G. Found out there were stale
> > replication slots from a recent base backup. I went to drop the replication
> > slots and found that since the wal_level was set to minimal vs replica or
> > higher, I couldn't drop the replication slot. Clearly that makes sense for
> > creating a replication slot but it seems like an artificial limitation for
> > dropping them.
> 
> This sounds closely related to
> https://www.postgresql.org/message-id/20180508143725.mn3ivlyvgpul6ovr%40alvherre.pgsql
> (commit a1f680d962ff) wherein we made it possible to drop a slot in
> single-user mode.
> 
> Seems worth fixing.  Send a patch?

I don't think this quite is the problem. ISTM the issue is rather that
StartupReplicationSlots() *needs* to check whether wal_level > minimal,
and doesn't. So you can create a slot, shutdown, change wal_level,
startup. A slot exists but won't work correctly.

Greetings,

Andres Freund

Commits

  1. Fix copy-paste error in errhint() introduced in 691d79a07933.

  2. Lower error level from PANIC to FATAL when restoring slots at startup

  3. Fix error message typo introduced 691d79a07933.

  4. Disallow starting server with insufficient wal_level for existing slot.

  5. Allow replication slots to be dropped in single-user mode