postgres vacuum

Question

Ok guys, I've moved to a new company and I see they have a single postgresql database here to run opennms.

I'm a mysql person, not familiar AT ALL with postgresql, but I've at least learned how to run psql and postgres -D to get in single user mode.

Anyway, opeenms crashed. I got in and tracked it down to postgresql giving this error: WARNING: database "template1" must be vacuumed within 965550 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in "template1".

It actually started at 1 million and I'm down to 965500 as you can see. I've gotten to this point by logging in single user (postgres -D) and running Vacuum full. Now I expected this to clean it up, but the results are exactly 55 rows of:

WARNING:  database "template1" must be vacuumed within 938861 transactions
HINT:  To avid a database shutdown, execute a full-database VACUUM in "template1".
WARNING:  database "template1" must be vacuumed within 938860 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "template1".
WARNING:  database "template1" must be vacuumed within 938861 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "template1".
WARNING:  database "template1" must be vacuumed within 938860 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "template1".

So the question I have is, do I just need to keep running this until I reach 0 or am I doing it wrong?

Answer 1

I have hit this problem before. Below are my notes from how I fixed it

You need to fix the problem by running a vacuum, as the error conveniently hints to. In order to do this, first, shutdown postgres sudo /sbin/service postgresql stop

Now, you'll need to go through each database and perform a vaccum with the stand-alone back-end. As the postgres user Run: postgres -D /opt/pgsql/data/ postgres

Where /opt/pgsql/data is the path to postgres's data directory, and postgres is the name of the database you want to fix.

You will get a prompt like:

PostgreSQL stand-alone backend 8.1.18

backend>

Simply enter VACUUM at the backend> prompt.

You will now most likely get something like WARNING: database "template1" must be vacuumed within 999407 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in "template1".

Just keep repeating the steps, changing the DBNAME each time until you don't get the warnings anymore. When that happens, you can restart postgres and all will be right again.

Edit: I should also mention, that after we did this process a few times; we decided to implement the autovacuum process: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM