Menu
in the pre-8.x days I have run numerous PGSQL databases on Linux, and did the database vacuuming explicitly via maintenance script.
When 8.x came along I did not move to autovacuum on those systems on the basis that it was working fine so no need to change anything. So I am not an autovacuum expert.
Now in the post-8.x era I find myself with some PGSQL databases running on Windows Server 2003 or newer.
It looks like autovacuum should be running because we have followed the instructions and left it turned on in the .conf file.
However connecting up PGAdmin to the database prompts us that we should run VACUUM.
How can you tell if the autovacuum daemon is running and looking after your database(s), and vacuuming them correctly?
798
If you want to see the vacuum settings for a specific table: SELECT relname, reloptions FROM pg_class WHERE relname='tablename'; The general vacuum settings can be seen in postgresql. conf .
You can start the vacuum manually also. By running psql command vacuum full analyze verbose . It will take some time. Beware that this exclusively locks the tables and therefore might not be recommended for large productive databases.
This is on by default; however, track_counts must also be enabled for autovacuum to work. This parameter can only be set in the postgresql. conf file or on the server command line; however, autovacuuming can be disabled for individual tables by changing table storage parameters.
In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set. The “autovacuum daemon” actually consists of multiple processes.
There are columns in pg_stat_user_tables that list when the last autovacuum and autoanalyze was run on a table - that's a good starting point. And of course, the logfile will contain information about what autovacuum is doing.
You can look specifically at the postgres.exe processes with a tool like Process Explorer. There will be a mutex specifically named to emulate the ps commandline information available on Unix. It should be easy to find. And you can just search for "autovacuum" to make sure it's running.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
