RE: Disable WAL logging to speed up data loading - Mailing list pgsql-hackers

Hi,


On Tuesday, January 5, 2021 5:45 PM
Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> On Tue, Jan 5, 2021 at 10:54 AM osumi.takamichi@fujitsu.com
> <osumi.takamichi@fujitsu.com> wrote:
> > On Monday, December 28, 2020 7:12 PM Masahiko Sawada
> <sawada.mshk@gmail.com> wrote:
> > > On Mon, Dec 28, 2020 at 4:29 PM osumi.takamichi@fujitsu.com
> > > <osumi.takamichi@fujitsu.com> wrote:
> > > > On Monday, December 28, 2020 2:29 PM Masahiko Sawada
> > > <sawada.mshk@gmail.com> wrote:
> > > > > On Thu, Dec 3, 2020 at 12:14 PM osumi.takamichi@fujitsu.com
> > > > > <osumi.takamichi@fujitsu.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.
Sawada-san, thanks a lot for your explanation and
for sure it's about time to launch a new thread for us.

This discussion can be separated from this wal_level thread and I agree with
your idea that what we are talking now would not be a blocker of the new wal_level patch.
So, kindly don't think that the new thread damages the content of this wal_level=none discussion first of all.
I'll create a new one soon.

Best Regards,
    Takamichi Osumi