How to Fix Corrupt Tables

[JohanK]

Hi there, i'm busy with quite a large project using PostgreSQL. Now the problem is that one of our data tables seems to have been corrupted. I cant open the table certain SQL queries like select does return results but the returned results are selective meaning that only certain fields are returned and not every thing that you asked for.

Is there a way to fix this or would we need to recreate the table and reload it with the data from the begining.

PS: attempts to export the data in the table have failed thus far.

Thanx in advance

Johan Kestlmeier

[michaelb]

You did not give enough information to understand what's going on with your db.
I would suggest to open psql session and run these queries:

1.\d -- display table listing
2 \d my_bad_table -- display the table defiunition
3. select count (*) from my_bad_table ;
4. vacuum;
5 vacuum analyze;
6. vacuum full;

If you have a problem at some point one of these commands would most likely result in an obvious error.
Copy and paste here all output.


IMPORTANT:
Make sure that you created db backup before going through with this.
pg_dump may not work if you have corrupted db, stop database and create a recursive copy or tar-ball of your data directory.

[michaelb]

If you intend to continue using database replace it with your backup after you encountered errors from the commands I suggested above.
Make sure you stop the server before you create a backup or put it back in place.

[JohanK]

Thanx for the help

JohanK