From: Sreekanth Palluru <sree4pg@gmail.com>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-general@postgresql.org>
Subject: Re: Would like to below scenario is possible for getting page/block corruption
Date: Fri, 9 Dec 2016 14:21:32 +1100
Message-ID: <CAP+fnpg1dPBTMppay7WMu9EEea_F4Ah2Z1JguOQXvFsHremCeQ@mail.gmail.com> (raw)
In-Reply-To: <CAP+fnpiz8udmeT+bixPSBXStHVfFJ7JXmAEQw9XtLEkkAb_g7Q@mail.gmail.com>
References: <CAP+fnpj3J_6dczUvmGvjQoc=-zYhg2YiKFXCHLrvWLo7MM53_A@mail.gmail.com>
<CAB7nPqQxHoyTT=2jcXQzVOOFxqL=q4xU2w-pNS+Bf-HZ+mTERg@mail.gmail.com>
<CAP+fnpi7i4N_oqR9MmiqXueZN=p45Mop+HQNHT3feaW+OS28yA@mail.gmail.com>
<CAB7nPqRadNQX0QiiO8=AKUc7RZR=Z1M-Y4K7NP1AVcPNpB8_6Q@mail.gmail.com>
<CAP+fnpiz8udmeT+bixPSBXStHVfFJ7JXmAEQw9XtLEkkAb_g7Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Correcting typos
Michael,
Thanks for your prompt reply
In my environment those two parameters are enabled . Just give you brief of
PG database envornment
Version 9.2.4.1
Windows 7 Professional SP1
fsync=on
full_page_writes=on
wal_sync_method=open_datasync
My Customer is into building Cancer related systems and we ship Dell
systems with our software image contains PG. Few of the customers are
facing corruption issues say around 5% .
We are in process of reproducing the issue , since there are different
variables involved in reproducing issue like Dell HW, Software image
versions, Application versions, write-cache settings RAID/Disk, RAID
controllers with no battery backup and power failures etc , I am trying
to understand is there possibility that PG can end up in having corrupted
blocks due to system crash though we set these parameters
a)As I understand fsycn will write the block from memory to disk and block
just after step 4) would have written disk assuming disk cache did not lie
b)and assume that full_page_writes=on has dumped the whole 8k block into WAL
before it updates block i.e. after step 2) and before 3)
c) if crash happens after step4) , since there is no PageHeader data ,
after system restarts PG will complain that it is corrupted block or
invalid header
Please correct me if my understanding about play fsync and full_page_writes
are correct ? if so , I see that there is possibility getting corruptions
whenever PG extends a relation and crash happens just after step 4)
I am not sure will the same applicable to existing page (not a new page)
and how it handles if there is PageHeader available as part of
full_page_writes, will same corruption can be happen or will PG can recover
database as I am not sure
recovery process can update the PageHeader from WAL records it wrote recptr
as part of step 4) during the recovery process .
-Sreekanth
On Fri, Dec 9, 2016 at 2:09 PM, Sreekanth Palluru <sree4pg@gmail.com> wrote:
> Michael,
> Thanks for your prompt reply
>
> In my environment those two parameters are enabled . Just give you brief
> of PG database envornment
> Version 9.2.4.1
> Windows 7 Professional SP1
> fsync=on
> full_page_writes=on
> wal_sync_method=open_datasync
>
> My Customer is into building Cancer related systems and we ship Dell
> systems with our software image contains PG. Few of the customers are
> facing corruption issues say around 5% .
> We are in process of reproducing the issue , since there are different
> variables involved in reproducing issue like Dell HW, Software image
> versions, Application versions, write-cache settings RAID/Disk, RAID
> controllers with no backup and power failures etc , I am trying to
> understand is there possibility that PG can end up in having corrupted
> blocks due to system crash.
>
> 1)As I understand fsycn will write the block from memory to disk and block
> just after step 4) would have written disk assuming disk cache did not lie
> 2)and assume that full_page_writes=on has dumped the whole 8k block into
> WAL
> before it updates block i.e. after step 2) and before 3)
> 3) if crash happens after step4) , since there is no PageHeader data ,
> after system restarts PG will complain that it is corrupted block or
> invalid header
>
> Please correct me if my understanding about play fsync and
> full_page_writes are correct ? if so , I see that there is possibility
> getting corruptions whenever PG extends a relation and crash happens just
> after step 4)
>
> I am not sure will the same applicable to existing page (not a new page)
> and how it handles if there is PageHeader available as part of
> full_page_writes, will same corruption can be happen or will PG can recover
> database as I am not sure
> recovery process can update the PageHeader from WAL records it wrote recptr
> as part of step 4) during the recovery process .
>
>
> -Sreekanth
>
>
>
> On Fri, Dec 9, 2016 at 12:44 PM, Michael Paquier <
> michael.paquier@gmail.com> wrote:
>
>> (Please top-post that's annoying)
>>
>> On Fri, Dec 9, 2016 at 10:28 AM, Sreekanth Palluru <sree4pg@gmail.com>
>> wrote:
>> > Can I generalize that, if after step 4) page ( new page or old page)
>> got
>> > written disk from buffer and crash happens between step 4) and 5) we
>> > always get
>> > block corruption issues with Postgres which can only be recovered by
>> setting
>> > zero_damaged_pages if we just have pg_dump backups and we are OK lose
>> data
>> > in the affected blocks?
>> >
>> > I am also looking at ways of reproducing the issue ? appreciate your
>> advice
>> > on it ?
>>
>> Postgres is designed to avoid such corruption problems if
>> full_page_writes and fsync are enabled, that's a base stone of its
>> reliability. If you can create a self-contained scenario able to
>> reproduce a failure, that could be treated as a Postgres bug, but you
>> are giving no evidence that this is the case.
>> --
>> Michael
>>
>
>
>
> --
> Regards
> Sreekanth
>
--
Regards
Sreekanth
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sree4pg@gmail.com, michael.paquier@gmail.com, pgsql-general@postgresql.org
Subject: Re: Would like to below scenario is possible for getting page/block corruption
In-Reply-To: <CAP+fnpg1dPBTMppay7WMu9EEea_F4Ah2Z1JguOQXvFsHremCeQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox