Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSl25-0007g0-Rf for pgsql-performance@arkaria.postgresql.org; Mon, 20 May 2019 16:21:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hSl24-00010U-2c for pgsql-performance@arkaria.postgresql.org; Mon, 20 May 2019 16:21:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSl23-00010M-7i for pgsql-performance@lists.postgresql.org; Mon, 20 May 2019 16:20:59 +0000 Received: from sonic302-2.consmr.mail.bf2.yahoo.com ([74.6.135.41]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSl20-0006Dn-2u for pgsql-performance@postgresql.org; Mon, 20 May 2019 16:20:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1558369254; bh=1u8crJz/YXDtqddE326+t0bSFyvshGcadBIHclaZNn4=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=FMo6lHpKV3gzCCXadaCMQE9Bib88K6V1LSKUYjguNjgwtNDE4GEdBc0TpIXfL/Fl3NOLPV594pnx2FnvD14d0/RJX9JCCaVbGbFqa+FhHktNEqXKRnGBxcF1npWBIlWcquI/PE/QkHT54UxcGgs/7xySBTo6/WlsttpNxo/Z9rIcxHv1pxGdT5hZCDHuYr9+YGZ9/aoljmZ3b9xIvpgj9Um2Eb0SOS9h8eyEuWdOlrwevU4XuN/03Er3M+BNk5LPm3KYMSV+SAv543qgp4ZzYDEi9IdMHBw9ROMR4H7fPftTC1z9MloIbknrvHTxXlcXP83lsOJ0ojPvDFrsWiCXRA== X-YMail-OSG: _7UZCaMVM1nNT8e.4Hv480XqGemryc1YYzwnFkjLKfBzH90HBQNV6_f.YND8oHA QLLw9GwFQqXmjI2EMU9KaN4kUSlDJ9tzaiM8.p001snqiYeLdvG2wvEtEVpARkeJnr5MHI01YpzH ruMkAaGYgVfBXf1rOYSk2MUSPm8Ua_RlziW867GY35h7zTSe5A2HDrFQ8Z.KNcsWwzcFBmTZbncU 5C.ArotltBsr2uTGamiefhfi1y6rozD5fGEiICOxM9vbvFmiErgQCCE7uj02au6BJlDYyDwQ0SAc z6ANv4NLzQ1BKSw1c0F73uND7_aI2Bo9KkdFLf3By_sBz1tkXJUJ79Wm2yXH_OIqjTtBJhXgMS_J 8eDrkXzWdmFloKhwWWyDMcL12ovnvfn0sc8TfFkYv.FfWygmEVCOU35YzgQEYzfRd.IGBJi2wtO9 8gxDq1vZWG1IsAZOWv5YbRK4VOnMJjYIVUL2WKSKIv9Qxs5oj7dPexmq1Z_g4FYeoKwdY.jHtnPc 6_LjBS224ohFz.AmSxn4ywfjOESIVrylx6Thp.uKDtvWAZoYu0MB5CgbyBxNoWzm7Xu.MtSM35wl OtfaCE6avta8ljr8fgitPM3v8M2ZChKUwS_Uc3.f3sv0.fi4oc1uBDSvv7BfhAAbvICbjb_WVqPe meKyBhRbvoBll4wtktSrfTytFFp1jWUXs8VhzPXcGqBDYDb.H8Y43WSHsJN6rbvYXy_ONuY5pk1Y TP2d7I8f2j_tPWhbVk6b1n_SPo5AZKflYt0VTTLEWVz4pmWVVRi4XiE6Me69DXccDZqBduFs3Mlc 8m5YwPRLLO0JpM0imJsMB972hFJ7hdxydX5Xw96jfeVSRyoa_36N.0WMtADS7DmDJ_TWpm6_oIAY wdQkwALrw9z991D3kKbT8kCimLggeRIxaPhLVqn6S5rQMc.4HEcgEPHyeJ4XkiEJ9w2rf0soxviy 3nfLiNbKLcIabmzYFjp7jgtF10TmLJlz7Tb4VrQpNF3CiLwJGMv2m7FQAZwJpL8Hu37HFERWGTVI PDwntS7P7U9vblXybWXqk8pR1PhJzzA2b8KSkq2PFqbgoQgxe0T7d4csZHrWvX5qIUnzF19n3xn7 OqfRpGbCw_l_oMHlLRcZFvZUWgLdEsYcavGjZ17UVXNcA7oqXgxOhx_vXR1avhWd6S1GW1BNR3C3 O24eLSZ1IslmxMRjm3k_folC6cCfyYYqAUbuwUkBIlNrqf_2r.O.7sFFah6.hrBiqHw9UyjUCwx9 iIz3UL4dlxcyr99qX_FCczUS0icELcJWpd8utzAdZa6Ovp2.oPzIGnvsEQCsLUJQ- Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.bf2.yahoo.com with HTTP; Mon, 20 May 2019 16:20:54 +0000 Received: from sonic307-9.consmr.mail.ne1.yahoo.com (EHLO sonic307-9.consmr.mail.ne1.yahoo.com) ([66.163.190.32]) by smtp405.mail.bf1.yahoo.com (Oath Hermes SMTP Server) with ESMTPA ID eb0444da13658acc105b29652a6af882; Mon, 20 May 2019 16:20:52 +0000 (UTC) X-YMail-OSG: uDyjOPcVM1ksK1f0tqBQSwZ_1FOH9XH3F3kHPil8AgL.RSAP_z41WRnvy1eNjfQ 5Xg1lPbux1jFWe4NBB6OjjlUAd7rs3BYMa7Zccz5d3yLy_bHKsGZYxzri8L3fL_Lu6G2QP5fcRn0 m07J5UrhCNkwMnbwWssHKIHXCPzUQfkfTwpu3Ah6Fpc41HW4W5Yu.Fp703j3JCAac4CILSw4vsvY O0BubfzSlhi1hOfCvSc4WGndURdKpFvyaNHZ3BHcqaLKF9.whVe_crpPejND5aXsRxthkH6qJb0y ZglLf1H8RThc0CsZmAGRXoLjQYmHhNdBToliwFS.jr0tsP4iJprjwVdp5Dx6_1B7VmuP8S82oCzO ZpKBC9Qb8500zeu_r_ihF19mtzxAb0p6Lf8xjhvr_2opUT4KXmcOEheuGojzdHvaWtQ25.7AeeAq 6.XLFM2a766q02r8PdXrZ8OW9p0ssLRj0JV_GdGIf1CEU1fOUaOiihkzYq6zc7x4.H2eGuig6pJc 8NSnmNtYhnMB6fWMEYlCyyNIC1iMWbUKAVEOVjbqSY.cd6wJG5YzmMPN0X7kXS2iSaUIjHRZ1zbo 88omiZjmpSleevAiYgEyeeLswRg1GTf.JpVEASyImYR92qaXDJz4TF8qzJAT17bgnwipyxXLTEXk V5MqbpT.5Fz.9f7sTy45FLvyjsNwFs.7HLOrIMNQjKa.JdkaLbeyxRjPq9iIMk7WkmqEduX3fFkA wmTBqACl53uhSdT08M5BMrGYeBwDlPNeBS8cKH4z3v0Pi_nO6Bm9dhAeAPzX5SCelV.R06ShV2Yx 4P6hN_bt2VTiXk2wFQVWILkBeLfR0ZCvy6i4GZz.BRXJOHKZlRuLt0D6kglgdUurMvllWUlxZVaL WS9PjwJBx8SC85AJjk8znAxSdwK1KLNLpzTG7Dr.98JgTfxEx6UXXkGPBIQ1IbsBvh81PqnZY5VK 865vKf0b6V3dQ9JWzxjij0hVBCJXjE4.1ErXjuIq1oMogad.GMgWfjfqUqxJEyiw6IPW.TzIWxAB oDEjfgvKS_dhA.DkqThWWSYX0oSmlNzWrln6v9DBeYxNwxuPwlILnf8rpRc3ikdWJxXPwQ2MFCiq cHfvY5yePHUGmKFYq61ZVmPC_fzRs9BQQzaZ1Vu7bdj3RniKSMHCwYiQAmzhKhzDcv5ItCVD2lc_ koF6DTaYF3TTANKKg0hJP.ClFvGmijXL.O6bQ9iaGz_31Am8n4AyPIkOakaXw6VrE7hhgLOdq Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.ne1.yahoo.com with HTTP; Mon, 20 May 2019 16:20:50 +0000 Date: Mon, 20 May 2019 16:20:45 +0000 (UTC) From: Bimal Reply-To: Bimal To: Greg Clough , Mariel Cherkassky Cc: "pgsql-performance@postgresql.org" Message-ID: <1909968146.4951657.1558369245203@mail.yahoo.com> In-Reply-To: References: Subject: Re: Trying to handle db corruption 9.6 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4951656_1244405636.1558369245201" X-Mailer: WebService/1.1.13634 YMailNorrin Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.23088 Safari/537.36 Sparrow/0.1 Content-Length: 7843 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_4951656_1244405636.1558369245201 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I had ran into same issue about year back, luckily I had standby to quickl= y promote.=C2=A0 But, I wish there was better a documentation on how to han= dle WAL log fill up and resetting them.=C2=A0=C2=A0 On Monday, May 20, 2019, 9:08:19 AM PDT, Mariel Cherkassky wrote: =20 =20 A backup was made after the corruption appeared but before I tried using t= he pg_resetxlog command. Basically I just want to start the database with t= he data that is available in the files(I'm ok with loosing data that was in= the cache and wasnt written to disk).My question is how can I continue fro= m here ?I also sent this mail to pgadmin mail list.. =E2=80=AB=D7=91=D7=AA=D7=90=D7=A8=D7=99=D7=9A =D7=99=D7=95=D7=9D =D7=91=D7= =B3, 20 =D7=91=D7=9E=D7=90=D7=99 2019 =D7=91-18:59 =D7=9E=D7=90=D7=AA =E2= =80=AAGreg Clough=E2=80=AC=E2=80=8F <=E2=80=AAGreg.Clough@ihsmarkit.com=E2= =80=AC=E2=80=8F>:=E2=80=AC > Yes I understand that.. I'm trying to handle it after the backup that I h= ave taken.. IMHO the best option here is to keep safe a copy as you have already done a= nd then restore from a backup, and replay whatever WAL you have.=C2=A0 The = database you have is terminally corrupted, and should never be relied upon = going forward. =C2=A0 You can try to get it running, and then extract the data with pg_dump... bu= t even then you will need to manually verify it=E2=80=99s OK because you ha= ve no idea which dirty blocks from memory have been written to disk and whi= ch have not.=C2=A0 Without the WAL you have no way of making it consistent,= and if they have been destroyed then you=E2=80=99re out of luck. =C2=A0 If you don=E2=80=99t have backups and archived WAL then fixing what you=E2= =80=99ve got may be your only option, but you should only go down that rout= e if you have to.=C2=A0 If you have to =E2=80=9Crepair=E2=80=9D, then I=E2= =80=99d recommend engaging a reputable PostgreSQL consultancy to help you. =C2=A0 Regards, Greg. P.S.=C2=A0 This conversation should probably be moved to something like pgs= ql-admin=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 This e-mail, including accompanying communications and attachments, is stri= ctly confidential and only for the intended recipient. Any retention, use o= r disclosure not expressly authorised by IHSMarkit is prohibited. This emai= l is subject to all waivers and other terms at the following link: https://= ihsmarkit.com/Legal/EmailDisclaimer.html Please visit www.ihsmarkit.com/about/contact-us.html for contact informatio= n on our offices worldwide. =20 ------=_Part_4951656_1244405636.1558369245201 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I had ran into same issue about year back, luckily I had stand= by to quickly promote.  But, I wish there was better a documentation o= n how to handle WAL log fill up and resetting them.  
<= br>
=20
=20
On Monday, May 20, 2019, 9:08:19 AM PDT, Mariel Cherkas= sky <mariel.cherkassky@gmail.com> wrote:


A backup was made after the corruption appeared but before I tried us= ing the pg_resetxlog command. Basically I just want to start the database w= ith the data that is available in the files(I'm ok with loosing data that w= as in the cache and wasnt written to disk).
My questi= on is how can I continue from here ?
I also sent this= mail to pgadmin mail list..

=E2=80=AB=D7=91= =D7=AA=D7=90=D7=A8=D7=99=D7=9A =D7=99=D7=95=D7=9D =D7=91=D7=B3, 20 =D7=91= =D7=9E=D7=90=D7=99 2019 =D7=91-18:59 =D7=9E=D7=90=D7=AA =E2=80=AAGreg Cloug= h=E2=80=AC=E2=80=8F <=E2=80=AAGreg.Clough@ihsmarkit.com=E2=80=AC=E2=80=8F>:=E2=80=AC

> Yes I understand that= .. I'm trying to handle it after the backup that I have taken..


IMHO the best option here is to keep safe a copy as you have already done a= nd then restore from a backup, and replay whatever WAL you have.  The = database you have is terminally corrupted, and should never be relied upon = going forward.

 

You can try to get= it running, and then extract the data with pg_dump... but even then you wi= ll need to manually verify it=E2=80=99s OK because you have no idea which d= irty blocks from memory have been written to disk and which have not.  Without the WAL you have no way of making it consistent, a= nd if they have been destroyed then you=E2=80=99re out of luck.

 

If you don=E2=80= =99t have backups and archived WAL then fixing what you=E2=80=99ve got may = be your only option, but you should only go down that route if you have to.=   If you have to =E2=80=9Crepair=E2=80=9D, then I=E2=80=99d recommend = engaging a reputable PostgreSQL consultancy to help you.

 

Regards,=

Greg.

P.S.  This conversation should probably be moved to something like pgs= ql-admin             

 



This e-mail, including accompanying communications and attachments, is stri= ctly confidential and only for the intended recipient. Any retention, use o= r disclosure not expressly authorised by IHSMarkit is prohibited. This emai= l is subject to all waivers and other terms at the following link: https://ihsmarkit.= com/Legal/EmailDisclaimer.html

Please visit www.ihsmarkit.com/about/contact-us.html= for contact information on our offices worldwide.
------=_Part_4951656_1244405636.1558369245201--