From: John Scalia <jayknowsunix@gmail.com>
To: Scott Whitney <swhitney@journyx.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Trying to recover a corrupted database
Date: Thu, 17 Jul 2014 15:24:47 -0400
Message-ID: <53C822FF.4070301@gmail.com> (raw)
In-Reply-To: <984033755.512124.1405624595840.JavaMail.zimbra@mail.int.journyx.com>
References: <53C81F98.9070405@gmail.com>
<984033755.512124.1405624595840.JavaMail.zimbra@mail.int.journyx.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
We're only working with a single database on this system, and yes, I attempted a pg_dump earlier today on just one single table - it failed (I know pg_dump and pg_basebackup use
different mechanisms.) Mind you it's a large table with 154,000 rows in it. The overall database is somewhere around 43GB.
On 7/17/2014 3:16 PM, Scott Whitney wrote:
> a) How many databases do you have?
> b) Have you tried to pg_dump the database(s) to see if that succeeds?
>
> If you _can_ pg_dump (which you might or might not be able to do), you could re-init the cluster and restore.
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Hi all,
>
> You may have seen my post from yesterday about our production database getting corrupted. Well, this morning we brought the system down to single user and ran an fsck which did
> report some drive errors. We repeated until no additional errors were reported. Then, we brought the system back to multi-user status and ran a successful pg_basebackup on the
> broken database. Since then we restarted the database and a ps -ef result looks like:
>
> /usr/pgsql-9.2/bin/postmaster -D /opt/datacenter -o -c zero_damaged_pages=true -i -N 384 -p 5431
>
> After the Db started up, we ran a VACUUM FULL ANALYZE which ran for about 3 hours, but the database is still showing the same type of errors in its log: invalid page header in
> block 29718... etc. What disturbed me a little, is that I don't think the zero_damaged_pages got applied. Checking the pg_settings table, we got:
>
> select name, setting, boot_val, reset_val from pg_settings where name = 'zero_damaged_pages';
> name | setting | boot_val | reset_val
> ---------------------------------------------------------------------------------
> zero_damaged_pages | on | off | on
>
> Now, my colleague ran this after he tried running some operations again after I told him how to set zero_damaged_pages again. He swears that that it was on when the first VACUUM
> FULL ANALYZE was run, but I'm not as sure. Plus, I don't understand why the boot_val shows as off. In any event, as we're still getting log errors like before, I don't really
> know
> what to try next other than rerunning the VACUUM FULL again. Help?
> --
> Jay
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
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: jayknowsunix@gmail.com, swhitney@journyx.com
Subject: Re: Trying to recover a corrupted database
In-Reply-To: <53C822FF.4070301@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