Rebuilding corrupt PostgreSQL database

From CobaltFAQs

At times, the quota table in the RaQ 3 and RaQ 4 PostgreSQL database can get corrupted. This procedure will help you rebuild that table.

CAUTION: This procedure completely deletes and recreates the 'cobalt' database. Use with care, as this database holds all the info about 

every domain on your server. If you mess it up, you might as well reformat the drive and start over...

NOTE: This procedure should work for both RaQ 3 and RaQ 4.  RaQ 3 is typically the server model that has this problem, but the database is the same between the 2 systems.

A corrupt quota table generally manifests itself with the following error messages (or something similar):

 NOTICE: Index pg_attribute_attrelid_index: NUMBER OF INDEX' TUPLES (574) IS NOT THE SAME AS HEAP' (561)
 NOTICE: Index pg_attribute_relid_attnum_index: NUMBER OF INDEX' TUPLES (574) IS NOT THE SAME AS HEAP' (561)
 NOTICE: Index pg_attribute_relid_attnam_index: NUMBER OF INDEX' TUPLES (574) IS NOT THE SAME AS HEAP' (561)

sent via email from the cron program.

First, establish a shell session as root. Then we'll grant access to the cobalt database, become the postgres user to access it.

 cd /home/pgsql
 cp pg_hba.conf pg_hba.conf.safe

Edit pg_hba.conf and change the line

 local all crypt

to read

 local all trust

Then we'll become the postgres user and open the database in the psql commandline tool:

 su - postgres
 psql cobalt

We want to make sure the quota table is really corrupt before moving ahead with this procedure.

At the cobalt=> PostgreSQL prompt (reached via the psql command), type

 SELECT * FROM quota;

and press Enter. If you see ERROR: cannot find attribute 1 of relation quota, the quota table is corrupt. If you do NOT get an error, STOP HERE! The quota table is not the problem.

If we are continuing, first exit the psql tool:

 \q

then do a database schema dump. This generates a text file which contains all the commands to rebuild the database tables as they currently exist, plus commands to insert all the current data into the new database.

 pg_dump cobalt > cobalt-schema

We want to edit the schema dump and remove the section that would try to recreate the quota table, since it's corrupt. Using your favorite editor, edit the file cobalt-schema and delete the

 CREATE TABLE "quota" ( );

section (spread over 2-3 lines, usually near the end of the file). Also remove the

 COPY "quota" FROM stdin;

line (at the end of the cobalt-schema file). Exit your editor and save changes.

Now, we're ready to delete the corrupt cobalt database and make a new clean one to hold all the good data. We will delete the old file and then make a new empty database and populate it with the data and commands from the schema dump:

 destroydb cobalt
 createdb cobalt
 psql cobalt < cobalt-schema

Now become root again, and run the swatch program that will rebuild the quota table (among many other things):

 exit
 /usr/local/sbin/swatch

Check to make sure it worked:

su - postgres
psql cobalt
SELECT * FROM quota;

You should not get an error message this time... Exit psql, go into the web admin UI and try stuff to make sure UI works. If it's all working properly, clean everything up. First, become root again, then remove the schema dump file and put back the original PostgreSQL config file:

 \q
 exit
 rm cobalt-schema
 mv pg_hba.conf.safe pg_hba.conf

That's it!