Обсуждение: Best way to sync possibly corrupted data?

On Fri, Dec 20, 2013 at 5:28 AM, Anand Kumar, Karthik
<Karthik.AnandKumar@classmates.com> wrote:
> HI,
>
> We have an issue with possibly corrupt data in our postgresql server. Errors
> like:
>
> ERROR:  index "photos_p00_n2" contains unexpected zero page at block 0
> ERROR:  invalid page header in block 12707 of relation
> pg_tblspc/5020557/PG_9.1_201105231/16393/9014673
>
> Thanks to all the suggestions from this list. We are in the process of
> moving our database out to a different server, and we'll then set
> zero_dameged_pages to on, run a full vacuum and reindex.
>
> The question I have is – what is the best method to transfer the data over
> to ensure we don't copy over bad/corrupt data? I would think a filesystem
> based copy (rsync, etc) should be avoided, and a pg_dump with a new initdb
> is best?
You should go with pg_dump if you are able to get a clean dump. Such
block errors happen because of hardware issues, so you are not safe
from additional failures that might happen while you do a copy of the
existing data folder to a new system.

Regards,
--
Michael

On 12/20/2013 01:01 AM, Michael Paquier wrote:

> You should go with pg_dump if you are able to get a clean dump. Such
> block errors happen because of hardware issues, so you are not safe
> from additional failures that might happen while you do a copy of the
> existing data folder to a new system.

I concur with this. However, if possible, try to reassign the LUN to
another system first. The problem you might have trying to get a full
dump of your database on a machine that's already creating random
corruptions, is that the dump might get invalid data in the process of
dumping, or crash outright before it finishes.

If you do have a non-corrupt binary backup and have been keeping your
WAL archives, you might be better off restoring that on a different
system so that it's as recent as possible, and getting the dump from
*that* copy. However, if you can reassign the LUN, just running the
database from a different machine would be a good test before you start
backing up and restoring a very large amount of SQL.

If you don't have a policy for this already, I strongly recommend moving
all backups to a separate storage area, in another data center if
possible. We use a machine mounted on a remote SAN, whose only purpose
is to store backups. We have a full month available at any time, along
with all necessary WAL archives to do PITR. That's probably overkill for
most companies, but some variant of that is a good level of protection.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Thank you. pg_dump is what we will use.

We did re-assign the LUN to a new system last night, and are monitoring.
Too early to say anything, but so far, we haven't seen the corruption.

And yes, we will get the dump from the new system.

We do not have the hardware to move the backups to a different storage
area yet, but its something we are working on, thank you

Thanks,
Karthik