Re: Disable WAL logging to speed up data loading

On Fri, 30 Oct 2020 at 05:00, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>
>
>
> On 2020/10/29 19:21, Laurenz Albe wrote:
> > On Thu, 2020-10-29 at 11:42 +0900, Fujii Masao wrote:
> >>> But what if someone sets wal_level=none, performs some data modifications,
> >>> sets wal_level=archive and after dome more processing decides to restore from
> >>> a backup that was taken before the cluster was set to wal_level=none?
> >>> Then they would end up with a corrupted database, right?
> >>
> >> I think that the guard to prevent the server from starting up from
> >> the corrupted database in that senario is necessary.
> >
> > That wouldn't apply, I think, because the backup from which you start
> > was taken with wal_level = replica, so the guard wouldn't alert.
> >
> > But as I said in the other thread, changing wal_level emits a WAL
> > record, and I am sure that recovery will refuse to proceed if
> > wal_level < replica.
>
> Yes. What I meant was such a safe guard needs to be implemented.
>
> This may mean that if we want to recover the database from that backup,
> we need to specify the recovery target so that the archive recovery stops
> just before the WAL record indicating wal_level change.

Yeah, it also means that setting wal_level to none makes the previous
backup no use even if the user has some generations of backup.

Does it make things simple if the usage of wal_level = 'none' is
limited to initial data loading for example? I mean we add a special
flag to initdb that sets wal_level to 'none' after initialization and
the user does initial data loading and set wal_level to >= minimal.
That is, we allow users to set from none to >= minimal but not for the
reverse. Since we prevent the database cluster from backup when
wal_level is none, the recovery never across wal_level = none. Not
sure this idea can address the case Osumi-san concerned though.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services