Data Center Network Manager (DCNM) - Vacuum and Autovacuum Postgres Databases

Introduction

This document describes how to vacuum the postgres database in Microsoft Windows and Linux.

Background Information

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.

Note: $INSTALLDIR throughout this article refers to "C:\Program Files\Cisco Systems\" or "/usr/local/cisco/" based on the operating system, Microsoft Windows or Linux respectively. The install path could be changed from these defaults during installation.

Vacuum DCNM's Postgresql Database in Windows

1. Stop the DCNM services as shown here.
   
   

   

   
   
   You can also enter:
   
   

   $INSTALLDIR/dcm/dcnm/bin/stopLANSANserver.bat

   
   
   
2. Obtain the database name, username, and password. Locate the postgresql.cfg.xml file on the DCNM server.
   
   
   • In DCNM Version 6.2.x, enter:
     
     

     $INSTALLDIR/dcm/jboss-4.2.2.GA/server/dcnm/conf/database/postgresql.cfg.xml

   • In DCNM Version 6.3.x, enter:
     
     

     $INSTALLDIR/dcm/Jboss-as-7.2.0.Final/standalone/conf/postgresql.cfg.xml

     
     
     

     

     
     
     In the example:
     
     
     • Database name: dcmdb
     • Username: dcnmuser
     • Database password: dcnmuser
   
   
   
3. 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.
   
   

   

   
   
   
4. Navigate through the drop-down menus to the dcmdb database.
   
   

   

   
   
   
5. Right-click dcmdb and select Maintenance. Select the Vacuum, Full, Analyze, and Verbose options in the Maintain Database dcmdb dialog box.
   
   

   

   
   
   

   Note: The vacuum operation usually completes within an hour, but can take much longer for larger databases. Remember to restart the DCNM services.

Vacuum DCNM's Postgresql Database in Linux

1. Stop DCNM services:
   
   

   $INSTALLDIR/dcm/dcnm/bin/stopLANSANServer.sh

   
   
   
2. Open the psql prompt:
   
   

   $INSTALLDIR/dcm/db/bin/psql -U <dbUsername> dcmdb

   
   
   
3. 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.
   VACUUM
   dcmdb=>
   dcmdb=> \q

   
   
   The previous command exits the sql prompt.
   
   
4. Start DCNM services:
   
   

   $INSTALLDIR/dcm/dcnm/bin/startLANSANServer.sh

Configure Autovacuum for Postgres Database

1. Stop DCNM services and the postgres database.
   
   In Linux:
   
   

   $INSTALLDIR/dcm/dcnm/bin/stopLANSANserver.sh
   /etc/init.d/postgres-8.3 stop

   
   
   In Microsoft Windows, both scripts can be found in the Start menu.
   
   

   

   
   
   

   

   
   
   
2. 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

   
   
                 and
   
                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

   
   
   
3. Save postgreql.conf and restart the postgres and DCNM services.