Restore Postgresql data after hdd crash

Question

My HDD crashed, so, I can't make a Postgresql backup. It seems that my data tables are OK (I can view the data via pgAdmin). However, when I run pg_dump I get this:

pg_dump: Error message from server: ERROR:  could not read block 2 of relation 
base/16384/2607: IO error
pg_dump: The command was: SELECT tableoid, oid, conname, connamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = conowner) AS rolname FROM pg_conversion

It seems that the system catalog pg_conversion is broken. How can I fix this problem?

Answer 1

I'm sorry, I don't have a corrupt pg_conversion table to help perform any verifications. My answers here must, by necessity, be heavily based on speculation.

With the limited information we have, I am guessing that you are attempting to do a full database pg_dump, along the lines of

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... "database_name"

Alternative pg_dumps?

If your data tables are still OK, you could try to pg_dump the relevant schema(s)...

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... --schema "schema_name" "database_name"

or table(s), and use multiple table switches...

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... --table "table_name" "database_name"

With respect to these approaches, I have no idea if this will help, as I've simply never encountered a failing pg_dump for any other reason than a syntax error, or something else relatively benign.

Use COPY to make CSVs

As a last ditch effort, if you aren't able to pg_dump your schema or tables, in a compressed .backup format, you could always extract the data using the COPY command to make comma-separated variable (CSV) files.

COPY table TO '/folder/table_backup.csv' WITH FORMAT CSV;

Good luck, and let us know if you have any success.