1

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.