Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cFBkg-0004UA-HT for pgsql-general@arkaria.postgresql.org; Fri, 09 Dec 2016 03:21:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cFBkf-0002lH-T0 for pgsql-general@arkaria.postgresql.org; Fri, 09 Dec 2016 03:21:37 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cFBke-0002kd-Jc for pgsql-general@postgresql.org; Fri, 09 Dec 2016 03:21:36 +0000 Received: from mail-qk0-x236.google.com ([2607:f8b0:400d:c09::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cFBkb-0003cZ-JH for pgsql-general@postgresql.org; Fri, 09 Dec 2016 03:21:35 +0000 Received: by mail-qk0-x236.google.com with SMTP id q130so5771240qke.1 for ; Thu, 08 Dec 2016 19:21:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=djX2vQLy4Kmm24NiPP18o/T2SUUMAI1pryYiRhJcnv0=; b=QXyaabHvYc2mGzud3cpiWebk3FBNO3OJzC9WLkUHi0J1sE1gy3+iWxe8cinMNM3yxb tekKzkFydwpcI04oNPejbB07a+w/EZBsCYsmGqHdMpKo1/QKbF5lINplnbDNns/7ccPz zr4LIJfmgtcj/jToDxBJW78PWb0LPgmkgCvd78r6bwznK2SGUQqlUyChLJNkT6a/VKEj UVZOGr1IyXhHdh0XXOoAKF79IztUQ9bQF1AJjn5KKvFQ4MZ1fe57d94SQbwCGw4KTh5w yruvDHLTnvq+SGbeamWg1dv+CXAiSgSBR0zVD/YQHiedp1mQWglA+xYgrmnk69DLU+L7 aVvw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=djX2vQLy4Kmm24NiPP18o/T2SUUMAI1pryYiRhJcnv0=; b=VAV+S6dpE7L2H2pbTDatY9bk1t6r5Cvb416ZkfncPUhSxRcyhH1DvsqV26nf8M4UJm xj2p97JnQ1omuUtusw5eG5EeLIfAI89Qs4A1GdJg4x+y++pmgUohxHkg7pi6jpuzYT2N hCM3Yec+QHCAi6Zf2MMke8gv1epMbXSy/N0NJHT4SY9ceys6mB52PlyJVIMvNn9WMX4E wH8n8xzLc19OjulXqL4/qOG08jrnOYt17Hb3BX7wwG2d5pItqjGrS1uVqwlOEwImvxsn 0pMyOSGyqyDd0NCCvTX7JmU9SsQn91v002HdNeoKMKt70rGVa1xR30qeHC2WKSfWOZAI DVsQ== X-Gm-Message-State: AKaTC03dzhjAxRU3MEWa5vo6w5yJ1R4Ik32QiRbwvoBy607NQgjipommsjowlHd3S7wqwbuQHxGuiTC/5QmsDw== X-Received: by 10.55.18.30 with SMTP id c30mr76371177qkh.213.1481253692538; Thu, 08 Dec 2016 19:21:32 -0800 (PST) MIME-Version: 1.0 Received: by 10.200.38.85 with HTTP; Thu, 8 Dec 2016 19:21:32 -0800 (PST) In-Reply-To: References: From: Sreekanth Palluru Date: Fri, 9 Dec 2016 14:21:32 +1100 Message-ID: Subject: Re: Would like to below scenario is possible for getting page/block corruption To: Michael Paquier Cc: PostgreSQL mailing lists Content-Type: multipart/alternative; boundary=001a114757f82a852905433140ef X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org --001a114757f82a852905433140ef Content-Type: text/plain; charset=UTF-8 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 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 >> 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 --001a114757f82a852905433140ef Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Correcting typos
Michael,
Thanks for your pr= ompt reply

In my environment those two parameters are en= abled . Just give you brief of PG database envornment
Version 9.2= .4.1=C2=A0
Windows 7 Professional SP1
fsync=3Don
<= div>full_page_writes=3Don
wal_sync_method=3Dopen_datasync

My Customer is into building Cancer related systems and w= e ship Dell systems with our software image contains PG. Few of the custome= rs 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 =C2=A0Dell HW, Software image versions, Application= versions, write-cache settings RAID/Disk, RAID controllers with no battery= backup and power failures =C2=A0etc =C2=A0, I am trying to understand is t= here possibility that PG can end up in having corrupted blocks due to syste= m crash though we set these parameters

a)As I unde= rstand 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=C2=A0
=
b)and assume that full_page_writes=3Don has dumped the whole 8k block = into WAL
before it updates block i.e. after step 2) and before 3)= =C2=A0
c) if crash happens after step4) , since there is no PageH= eader data , after system restarts PG will complain that it is corrupted bl= ock or invalid header

Please correct me if my unde= rstanding about play fsync and full_page_writes are correct ? if so , I see= that there is possibility getting corruptions whenever PG extends a relati= on and crash happens just after step 4)=C2=A0

I am= not sure will the same applicable to =C2=A0existing page (not a new page) = and how it handles if there is PageHeader available as part of full_page_wr= ites, will same corruption can be happen or will PG can recover database as= I am not sure
recovery process can update the PageHeader =C2=A0 = from WAL records it wrote=C2=A0recptr as p= art of step 4) during the recovery process .

-Sreekanth


On Fri, Dec 9, 2016 at 2:09 PM, Sreekanth Palluru <s= ree4pg@gmail.com> wrote:
Michael,
Thanks for your prompt reply=

In my environment those two parameters are enabled . Ju= st give you brief of PG database envornment
Version 9.2.4.1=C2=A0=
Windows 7 Professional SP1
fsync=3Don
full_p= age_writes=3Don
wal_sync_method=3Dopen_datasync

My Customer is into building Cancer related systems and we ship Del= l systems with our software image contains PG. Few of the customers are fac= ing corruption issues say around 5% .
We are in process of reprod= ucing the issue , since there are different variables involved in reproduci= ng issue like =C2=A0Dell HW, Software image versions, Application versions,= write-cache settings RAID/Disk, RAID controllers with no backup and power = failures =C2=A0etc =C2=A0, I am trying to understand is there possibility t= hat 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 ca= che did not lie=C2=A0
2)and assume that full_page_writes=3Don has= dumped the whole 8k block into WAL
before it updates block i.e. = after step 2) and before 3)=C2=A0
3) if crash happens after step4= ) , since there is no PageHeader data , after system restarts PG will compl= ain that it is corrupted block or invalid header

P= lease correct me if my understanding about play fsync and full_page_writes = are correct ? if so , I see that there is possibility getting corruptions w= henever PG extends a relation and crash happens just after step 4)=C2=A0

I am not sure will the same applicable to =C2=A0exis= ting page (not a new page) and how it handles if there is PageHeader availa= ble 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 up= date the PageHeader =C2=A0 from WAL records it wrote=C2=A0recptr 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)=C2=A0 page ( new page or old p= age)=C2=A0 got
> written disk from buffer=C2=A0 and crash happens between step 4) and 5= )=C2=A0 we
> always get
> block corruption issues with Postgres which can only be recovered by s= etting
> 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 a= dvice
> 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
--001a114757f82a852905433140ef--