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

Hello


On Thursday, Nov 19, 2020 12:45 PM Tsunakawa, Takayuki/綱川 貴之 <tsunakawa.takay@fujitsu.com>
> > On Tuesday, Nov 3, 2020 3:02 AM Stephen Frost <sfrost@snowman.net>
> > wrote:
> > > Checking the WAL level certainly seems critical to anything that's
> > > reading the WAL.  We certainly do this already when running as a
> > > replica:
> > >
> > > ereport(WARNING,
> > >         (errmsg("WAL was generated with wal_level=minimal, data may
> > > be missing"),
> > >         errhint("This happens if you temporarily set
> > > wal_level=minimal without taking a new base backup.")));
> > >
> > > There's definitely a question about if a WARNING there is really
> > > sufficient or not, considering that you could end up with 'logged'
> > > tables on the replica that are missing data, but I'm not sure that
> > > inventing a new, independent, mechanism for checking WAL level
> > > changes makes
> > sense.
> I don't know why WARNING was chosen.  I think it should be FATAL,
> resulting in the standby shutdown, disabling restarting it, and urging the user
> to rebuild the standby.  (I guess that's overreaction because the user may
> not perform operations that lack WAL while wal_level is minimal.)
Yeah, I agree that WARNING is not sufficient.


> > The second idea is incremental counter that indicates drop of
> > wal_level from replica to minimal (or like from minimal to none).
> > Its purpose is to compare the wal_level changes between snapshots.
> > When any monitoring tools detect any difference of the counter, we can
> > predict something happened immediately without checking WAL in
> > between.
>
> Let's depict the situation.  I may be misunderstanding, so any correction
> would be much welcome.  Here, I call the new field
> wal_level_change_counter, which should be changed to a proper name.
>
> 1. wal_level = replica.  Take a base backup and store it in
> $BACKUPDIR/20201119/.
>   wal_level_change_counter = 0
>
> 2. Set wal_level = minimal or none, and restart the instance.  Perform some
> operations.
>   wal_level_change_counter = 1
>
> 3. Set wal_level = replica, and restart the instance.
>   wal_level_change_counter = 1
>
> 4. Some monitoring system compares the values of
> wal_level_change_counter in $BACKUPDIR/20201119/ and $PGDATA/, and
> notices the difference (0 and 1 respectively.)
> It warns the user that he/she should take a full backup because some WAL
> may be missing to recover the latest data from the last backup in
> $BACKUPDIR/20201119/.
My understanding is completely same as the description Tsunakawa-San wrote above.

> But I think this is a separate patch, because the issue already applies to
> wal_level = minimal.
OK. The range of this patch's responsibility was obscure, but now I'm fine.
I understand that I don't need to incorporate those mechanisms in the new wal_level patch.


Best,
    Takamichi Osumi