Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

share|improve this question
2  
probably your file system is broken on more places, and is better to use backup. If your backup is consistent, you can do: 1) stop postgres, 2) save current data directory, 3) replace data directory from backup, 4) start postgres. SEARCH REASON WHY FILESYSTEM IS BROKEN –  Pavel Stehule Jun 13 '13 at 11:41
    
Was there a recent system crash? Is the machine using an SSD and if so, what brand and model? Are you running with fsync=off? What's the host OS and version, disk type, filesystem, etc? –  Craig Ringer Jun 13 '13 at 23:45
add comment

migrated from stackoverflow.com Jun 14 '13 at 6:22

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.