Re: Best way to sync possibly corrupted data?

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(at)optionshouse(dot)com

______________________________________________

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