5

We have some serious issues with our PostgreSQL-Server (Version 8.4). Our webapplication uses jdbc to connect to the PostgreSQL Server. Suddenly our webapplication cant connect to the PostgreSQL Server. We get the PSQLException: Broken Pipe. Cause of this, i tried to connect via pg_admin. This works, but i'm getting errors like this:

ERROR: could not read block 32570 of relation base/16390/2663: read only 0 of 8192 bytes

I tried to make a dump to backup the data and this doesnt work too: (Look on the edit at the bottom of the side)

pg_dump: SQL command failed

pg_dump: Error message from server: ERROR: could not read block 32570 of relation base/16390/2663: read only 0 of 8192 bytes

pg_dump: The command was: LOCK TABLE public.results_233_top100_disease_state_karyotype IN ACCESS SHARE MODE

I tried to get some information with

SELECT oid, relname FROM pg_class WHERE oid=2663

The result was this:

2663 ; "pg_class_relname_nsp_index"

During my effort of collecting some information, i read, that recreating that specific index could help. So reindexed it with the following command:

REINDEX INDEX pg_class_relname_nsp_index

This didnt helped at all, and now i'm pretty helpless. Has somebody an idea what i can do ? Another point is, that we do weekly backups. Is it possible to overwrite the data folder: /var/lib/postgresql/8.4./main with our data from the backup?

EDIT: I fixed the problem with reindexing the table that had the error. Now i'm getting another error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: invalid page header in block 1047 of relation base/16390/16398
pg_dump: The command was: COPY public.data_1 (sampleid, feature, value) TO stdout;

Improve this question
4
  • 1
    Just wanted to add that on my 9.2 Postgres the above SELECT oid, relname FROM pg_class WHERE oid=2663 always showed empty results. The select below gave the correct results select oid, relfilenode, relname from pg_class where relfilenode = 2663 - note the 2663 is from the above example.
    – user85114
    Commented Jan 20, 2016 at 15:12
  • This question is high in search result, so I just wanted to point out that you can get some data back if you set set zero_damaged_pages = on;. This way Postgres will skip rows that are corrupted and keep fetching forward instead of aborting query with error. I've had such corruption and recovered over 99% data.
    – Łukasz Kamiński
    Commented Sep 13, 2017 at 12:28
  • @ŁukaszKamiński That has no effect for me. I'm trying to recover a corrupted table, that still has some rows that are selectable, but when I try to query the entire table, I get ERROR: missing chunk number 0 for toast value 151065 in pg_toast_106852
    – Cerin
    Commented Jan 17, 2019 at 15:32
  • This issue is also occurring on M1 Macs (Apple Silicon) -- in my case a MacBook Air -- without apparent corruption of the data. Accessing the same database from an Intel-based Mac results in no errors.
    – Ben Wilson
    Commented Feb 16, 2021 at 23:08

1 Answer 1

Reset to default
4

Before doing anything else, read and act on: http://wiki.postgresql.org/wiki/Corruption .

Most likely you have disk or file system problems.

If you suspect any kind of DB corruption for whatever reason you should stop the DB and copy the entire database at the file system level before attempting any recovery.

Once you've done that, then you can look into possible repairs. You'll probably have some significant data loss, so your goal should be to get it working to the point where you can pg_dump the damaged databases, re-initdb, and reload.

If you have a recent backup, now would be a good time to think about using it.

Improve this answer
4
  • 1
    I understand this advice (as also listed in the postgresql wiki) but in my case because the block was corrupt I couldn't make a copy of the file. I ended up truncating the table and rebuilding the data to get things working.
    – jwadsack
    Commented Aug 20, 2015 at 15:14
  • @jwadsack Makes sense
    – Craig Ringer
    Commented Aug 20, 2015 at 23:28
  • This answer is unclear. Are you implying that, once the damaged database has been copied, a simple dump/load will repair the files?
    – Cerin
    Commented Jan 17, 2019 at 15:23
  • @Cerin It certainly won't repair the files. You'd usually dump, delete or move aside the corrupt database, initdb a new blank database and reload into that.
    – Craig Ringer
    Commented Jan 18, 2019 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.