Posted on October 9, 2010

How to VACUUM PostgreSQL when it does not want to start

If you are PostgreSQL admin and you are in PANIC because your database does not want to start and you see this Warning in postgresql log

WARNING: database "postgres" must be vacuumed within 999999 transactions 
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 
WARNING: database "postgres" must be vacuumed within 999998 transactions 
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 
WARNING: database "postgres" must be vacuumed within 999997 transactions 
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 
WARNING: database "postgres" must be vacuumed within 999996 transactions 
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 
WARNING: database "postgres" must be vacuumed within 999995 transactions 
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". 
...

or this warning on your PHP website:

Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL server: 
FATAL: database is not accepting commands to avoid wraparound data loss in database "YOUR_DATABSE" 
HINT: Stop the postmaster and use a standalone backend to vacuum database "YOUR_DATABSE". in ...

then this is what you are looking for:

[you@server ~]$ su root
Password:
[root@server ~]# /etc/init.d/postgresql stop                                     #stop database
[root@server ~]# su postgres                                                            # switch user to postgres
[postgres@server ~]$ touch /tmp/fix.sql                                          #create empty file
[postgres@server ~]$ echo "VACUUM FULL;" > /tmp/fix.sql               #write command to file
[postgres@server ~]$ postgres -D /var/lib/pgsql/data YOUR_DATABASE_NAME < /tmp/fix.sql       # execute command

Repeat above command for every database you have. It can take several minutes if your db is large.

Start postgresql

[root@server ~]# /etc/init.d/postgresql restart

Remember to vaccum your DB from time to time to avoid this problem.