Re: Bump default wal_level to logical

On Tue, Jun 9, 2020 at 10:53 AM Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
wrote:

> At Tue, 9 Jun 2020 08:52:24 +0200, Peter Eisentraut <
> peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote in
> > On 2020-06-08 23:32, Andres Freund wrote:
> > > On 2020-06-08 13:27:50 -0400, Tom Lane wrote:
> > >> If we can allow wal_level to be changed on the fly, I agree that would
> > >> help reduce the pressure to make the default setting more expensive.
> > >> I don't recall why it's PGC_POSTMASTER right now, but I suppose there
> > >> was a reason for that ...
> > > There's reasons, but IIRC they're all solvable with reasonable
> > > effort. I
> > > think most of it boils down to only being able to rely on the new
> > > wal_level after a while. For minimal->recovery we basically need a
> > > checkpoint started after the change in configuration, and for
> > > recovery->logical we need to wait until all sessions have a) read the
> > > new config setting b) finished the transaction that used the old
> > > setting.
> >
> > The best behavior from a user's perspective would be if the WAL level
> > automatically switched to logical if logical replication slots are
> > present. You might not even need 'logical' as an actual value of
> > wal_level anymore, you just need to keep a flag in shared memory that
> > records whether at least one logical slot exists.
>
> Currently logical slots cannot be created while wal_level <
> logical. Thus a database that has a logical slot must have been once
> executed with wal_level >= logical before the creation of the slot.
>
> It seems to me setting wal_level = logical would be better than
> creating a dummy logical slot while initdb..
>
>
I don't think Peter is suggesting a dummy slot. What he's suggesting is
allow the creation of a logical slot even when wal_level is not set to
logical, and instead automatically raise the wal level to the equivalent of
logical when you do. That way, the operation becomes transparent to the
user.

> Coulnd't we add an option to speicfy wal_level for initdb? Or an
> option to append arbitrary config lines to postgresql.conf?
>

That wouldn't solve the problem David raised initially. The whole reason
for being able to do this is that you *didn't*k now when you did initdb
that you were going to need logical replication at a later stage. So you
are already in front of a running cluster with wal_level=replica, and now
the cost of turning it to logical includes restarting the db and kicking
all sessions out. If you know when you're setting the system up that you're
going to need it, then the work of setting it isn't that big.

It might be useful to have a functionality to append arbitrary config lines
in initdb, but then that's not all that different from just copying in a
pre-made postgresql.auto.conf file into the newly initialized cluster after
it's done -- I'm not sure it buys very much.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>