Re: Disable WAL logging to speed up data loading

On Tue, Jan 5, 2021 at 10:54 AM osumi(dot)takamichi(at)fujitsu(dot)com
<osumi(dot)takamichi(at)fujitsu(dot)com> wrote:
>
> Hi, Sawada-San
>
>
> On Monday, December 28, 2020 7:12 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > On Mon, Dec 28, 2020 at 4:29 PM osumi(dot)takamichi(at)fujitsu(dot)com
> > <osumi(dot)takamichi(at)fujitsu(dot)com> wrote:
> > > On Monday, December 28, 2020 2:29 PM Masahiko Sawada
> > <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > > > On Thu, Dec 3, 2020 at 12:14 PM osumi(dot)takamichi(at)fujitsu(dot)com
> > > > <osumi(dot)takamichi(at)fujitsu(dot)com> wrote:
> > > > >
> > > > > I've made a new patch v05 that took in comments to filter out WALs
> > > > > more strictly and addressed some minor fixes that were discussed
> > > > > within past few days.
> > > > > Also, I changed the documentations, considering those modifications.
> > > >
> > > > From a backup management tool perspective, how can they detect that
> > > > wal_level has been changed to ‘none' (and back to the normal)? IIUC
> > > > once we changed wal_level to none, old backups that are taken before
> > > > setting to ‘none’ can be used only for restoring the database to the
> > > > point before the LSN where setting 'wal_level = none'. The users can
> > > > neither restore the database to any points in the term of 'wal_level
> > > > = none' nor use an old backup to restore the database to the point
> > > > after LSN where setting 'wal_level = none’. I think we might need to
> > > > provide a way to detect the changes other than reading
> > XLOG_PARAMETER_CHANGE.
> > > In the past, we discussed the aspect of backup management tool in [1]
> > > and concluded that this should be another patch separated from this
> > > thread because to compare the wal_level changes between snapshots
> > > applies to wal_level = minimal, too. Please have a look at the "second idea"
> > > in the e-mail in the [1] and responses to it.
> > >
> >
> > Thank you for telling me about the discussion!
> >
> > The discussion already started on another thread? I think it might be better to
> > consider it in parallel, if not started yet. We can verify beforehand that the
> > proposed solutions will really work well together with backup management
> > tools. And from the user perspective, I wonder if they would like to see this
> > feature in the same release where wal_level = none is introduced. Otherwise,
> > the fact that some backup management tools won’t be able to work together
> > with wal_level = none will be a big restriction for users. That patch would
> > probably not be a blocker of this patch but will facilitate the discussion.
> I don't think the new thread is created already.
>
> By the way, I checked documents and user manuals of backup management tools like
> pgBackRest, EDB's BART and pg_probackup respectively.
> These tools work when wal_level is higher than minimal
> because these use physical online backup or wal archiving and thus
> they don't need to work together with wal_level=minimal or none.

Thank you for checking! The use case I imagined is that the user
temporarily changes wal_level to 'none' from 'replica' or 'logical' to
speed up loading and changes back to the normal. In this case, the
backups taken before the wal_level change cannot be used to restore
the database to the point after the wal_level change. So I think
backup management tools would want to recognize the time or LSN
when/where wal_level is changed to ‘none’ in order to do some actions
such as invalidating older backups, re-calculating backup redundancy
etc.

Actually the same is true when the user changes to ‘minimal’. The
tools would need to recognize the time or LSN in this case too. Since
temporarily changing wal_level has been an uncommon use case some
tools perhaps are not aware of that yet. But since introducing
wal_level = ’none’ could make the change common, I think we need to
provide a way for the tools.

Regards,

--
Masahiko Sawada
EnterpriseDB: https://www.enterprisedb.com/