Re: Disable WAL logging to speed up data loading

From: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "osumi(dot)takamichi(at)fujitsu(dot)com" <osumi(dot)takamichi(at)fujitsu(dot)com>, "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
Cc: "ashutosh(dot)bapat(dot)oss(at)gmail(dot)com" <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Disable WAL logging to speed up data loading
Date: 2020-10-29 20:00:39
Message-ID: b5e19187-3188-2842-b50a-3287ea95722c@oss.nttdata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

>
>> I'm still not sure if it's worth supporting this feature in core.
>> Because it can really really easily cause users to corrupt whole the database.
>
> You mean, if they take no backup before setting wal_level = none
> and then crash the database, so that they are stuck with an
> unrecoverable database?

Yes. Also if the safe guard that we discussed the above is missing,
even when a backup is taken before wal_level=none, recovery from
the backup can make the database corrupted.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Gustafsson 2020-10-29 21:26:54 Consistent error reporting for encryption/decryption in pgcrypto
Previous Message Michael Banck 2020-10-29 19:40:31 Re: [PATCH] remove pg_archivecleanup and pg_standby