Rebuilding corrupt PostgreSQL database
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
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:
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):
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