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@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
