From: Bimal <internetuser2008@yahoo.com>
To: Greg Clough <Greg.Clough@ihsmarkit.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Trying to handle db corruption 9.6
Date: Mon, 20 May 2019 16:20:45 +0000 (UTC)
Message-ID: <1909968146.4951657.1558369245203@mail.yahoo.com> (raw)
In-Reply-To: <CA+t6e1=j8hwY-LCgN461-bCNB4ODxG6LNTHD9=uHko7ZAHy-fA@mail.gmail.com>
References: <CA+t6e1=AHht9vqazjB8s15KH2Y7Vs1br3AVZpU5v3fJZ-Eg9Pw@mail.gmail.com>
<CAHogYcXfPf6gJbXQgkOcRfjL-pJdxApvkzF3YowNz5r_5KMG7w@mail.gmail.com>
<CA+t6e1=uKKccO4cGPdXgM4K3+fa1sY9-PE+w8V7QAHS_P1ot-g@mail.gmail.com>
<DM6PR12MB27969B7AF5108691B55A00DEEC060@DM6PR12MB2796.namprd12.prod.outlook.com>
<CA+t6e1=j8hwY-LCgN461-bCNB4ODxG6LNTHD9=uHko7ZAHy-fA@mail.gmail.com>
I had ran into same issue about year back, luckily I had standby to quickly promote. But, I wish there was better a documentation on how to handle WAL log fill up and resetting them.
On Monday, May 20, 2019, 9:08:19 AM PDT, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
A backup was made after the corruption appeared but before I tried using the pg_resetxlog command. Basically I just want to start the database with the 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 from here ?I also sent this mail to pgadmin mail list..
בתאריך יום ב׳, 20 במאי 2019 ב-18:59 מאת Greg Clough <Greg.Clough@ihsmarkit.com>:
> 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 and 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 will need to manually verify it’s OK because you have no idea which dirty blocks from memory have been written to disk and which have not. Without the WAL you have no way of making it consistent, and if they have been destroyed then you’re out of luck.
If you don’t have backups and archived WAL then fixing what you’ve got may be your only option, but you should only go down that route if you have to. If you have to “repair”, then I’d recommend engaging a reputable PostgreSQL consultancy to help you.
Regards,
Greg.
P.S. This conversation should probably be moved to something like pgsql-admin
This e-mail, including accompanying communications and attachments, is strictly confidential and only for the intended recipient. Any retention, use or disclosure not expressly authorised by IHSMarkit is prohibited. This email 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.
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-performance@postgresql.org
Cc: internetuser2008@yahoo.com, Greg.Clough@ihsmarkit.com, mariel.cherkassky@gmail.com
Subject: Re: Trying to handle db corruption 9.6
In-Reply-To: <1909968146.4951657.1558369245203@mail.yahoo.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