How to Vacuum Postgresql DB

sometimes, when too many deletion or updating opereations happened to the postgresql database, it would run into a transactionid problem. if you try to connect to postmysql db, it would prompt the following error messages and stop you from log into the db as Root.

following steps need to be taken to recover it..

1 stop postgresql services

# service postgresql stop

2 login the db as the supper user in single user model and vacumm the DB

# su  - postgres
-bash-3.00$ more tmp.sql 
VACUUM ANALYZE VERBOSE;
-bash-3.00$nohup  postgres -D /var/lib/pgsql/data postgres <tmp.sql &

the output would be like:

WARNING:  database "postgres" must be vacuumed within 999994 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING:  database "postgres" must be vacuumed within 999994 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "postgres".

PostgreSQL stand-alone backend 8.1.4
backend> WARNING:  database "postgres" must be vacuumed within 999993 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING:  database "postgres" must be vacuumed within 999992 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING:  database "postgres" must be vacuumed within 999991 transactions
....
HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a value over 175264.
WARNING:  database "companion" must be vacuumed within 999994 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "companion".

then, it is time to execute the same process on "companion" DB

-bash-3.00$nohup  postgres -D /var/lib/pgsql/data companion <tmp.sql &

repeat this until all the DBs are being vaccumed, the large DB (like companion, data1 etc)would take longer time to finish the process, usually it would take 2 -3 hours ..

start postgresql as root

# service postgresql start

-- WenjingWu - 27 Sep 2008