Censornet - internet filtering on your terms

Re-building a corrupt CensorNet Professional database

August 03, 2011 • Customer Services       

Synopsis

Article

The CensorNet Professional database (postgres) can become corrupt if the server is shut down unexpectedly, for example due to a power failure.

The primary symptom of a corrupt database is browsing will be extremely slow (if at all), rebooting the server will not resolve the issue and you notice that the server CPU is at 100% utilisation.

You can confirm the CPU utilisation by typing top at the CensorNet command line whilst logged in as root. At the top of the %CPU column there will be a "postgres" process. Follow these instructions to attempt to correct the problem.

NOTE: There is no guarantee that this procedure will work, although it does 8 out of 10 times. Hopefully you have a recent backup to fall back on if required.

First of all, stop the proxy process, web server process and replication process, so that there are no connections open to the database:

/etc/init.d/cnv4 stop

/etc/init.d/apache2 stop

/etc/init.d/slony1 stop

Now stop the database itself (this could take a while) and wait for the prompt to return:

/etc/init.d/postgresql-8.4 stop (note if using Ubuntu 8.04, use postgresql-8.3)

When the prompt returns you can restart the database:

/etc/init.d/postgresql-8.4 start

This will most likely start the "autovacuum" process. You should stop this. You will need to get the process ID first, by typing:

ps aux | grep vac

This will return something like the following, where the process ID is in the second column:

postgres  5149  0.0  0.0  41384   980 ?        Ss   Jul18   0:28 postgres: autovacuum launcher process

Where 5149 is the process ID. Now type:

kill -9 5149

Repeat the "ps aux" step to make sure there are no other autovacuum's running.

Now type:

su -c 'psql censornet postgres' postgres

This will log you in to the database command line.

Now type:

DELETE FROM pg_statistic;

This will remove the statistics table, to allow you to re-index the database properly.

Next type:

reindex DATABASE censornet;

This will run through each table and re-index it. When this has finished, we suggest a manual vacuum:

vacuum full analyze;

This command can take anywhere from 1 minute to 45 minutes to complete, depending on the size of your database.

Once complete, you should exit the database command prompt with Ctrl+D and then type reboot to restart the server. Hopefully when it boots back up, everything is back to normal

Related articles