This document describes how to vacuum the postgres database in Microsoft Windows and Linux.
It is absolutely critical to vacuum postgres databases in order for the databases to properly function. Through the life of the database, new entries are added and current entries are updated. By design, postgres does not immediately remove the iterations of a record as it gets updated. Therefore, postgres databases can contain a large number of stale, unused records. These old records should be removed at least every two weeks with the vacuum function in order to reduce disk usage and improve the speed of database queries. It is even more effective if you configure postgres to automatically vacuum the database without the need to stop the Data Center Network Manager (DCNM) services.
Vacuum DCNM's Postgresql Database in Windows
- Stop the DCNM services as shown here.
You can also enter:
- Obtain the database name, username, and password. Locate the postgresql.cfg.xml file on the DCNM server.
- Open PgAdmin III.exe, which is a helpful GUI for the postgres database. Then, right-click the object in the list and connect to the database. Enter the password from Step 2 here.
- Navigate through the drop-down menus to the dcmdb database.
- Right-click dcmdb and select Maintenance. Select the Vacuum, Full, Analyze, and Verbose options in the Maintain Database dcmdb dialog box.
Vacuum DCNM's Postgresql Database in Linux
- Stop DCNM services:
- Open the psql prompt:
$INSTALLDIR/dcm/db/bin/psql -U <dbUsername> dcmdb
- Run the database vacuum and quit:
dcmdb=> VACUUM FULL ANALYZE VERBOSE;
Many pages of output pass on the screen. The vacuum is finished when you see a message similiar to this one:
Current limits are: 532000 page slots, 1000 relations, using 3182 kB.
The previous command exits the sql prompt.
- Start DCNM services:
Configure Autovacuum for Postgres Database
- Stop DCNM services and the postgres database.
In Microsoft Windows, both scripts can be found in the Start menu.
- Open $INSTALLDIR/dcm/db/data/postgresql.conf and uncomment these lines:
Uncomment these 11 entries [remove the # from the beginning of the line]:
#track_counts = on
these autovacuum statements:
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 2min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20
#autovacuum_vacuum_cost_limit = -1
- Save postgreql.conf and restart the postgres and DCNM services.