WEM Database Maintenance


WEM Database Maintenance
 
 
 
The following table lists the PostgreSQL databases that can be created during the installation of the WEM application. Certain databases are only created if their respective functionality is enabled in the application. Refer to the Determining Available Databases section of this chapter for information on determining which databases have been created.
Databases Created by the WEM
This chapter provides information and instructions for performing the following routine maintenance on these databases to ensure proper data processing and integrity:
Caution: To ensure proper operation and data integrity, the PostgreSQL database instances installed with the WEM should only be used by the application.
This chapter includes the following topics:
 
Determining Available Databases
Follow the instructions in this section to determine which databases were created during the installation of the WEM application. These instructions assume that you are logged in to the server on which the application is installed as the user root and have access to the server’s command line.
Step 1
 
cd /users/ems/postgres/bin
Step 2
 
./psql -U postgres_name template1
postgres_name is the name of the PostgreSQL database administrator (postgres by default).
The following prompt appears:
 
template1#
Step 3
 
select datid,datname from pg_stat_database;
A table appears listing the database name and its corresponding identification number.
Step 4
 
\q
 
“Vacuuming” the Databases
Standard PostgreSQL operation periodically leaves older versions of records in the database even after the record has been updated or deleted. This method allows the record to remain available to other processes or transactions. As a result of frequent database deletes and/or updates, the number of these “old” records can grow and hinder database operation and performance. Excessive numbers of these “old” and unused records increase the time required for queries and consume disk space that could be better allocated for current records and processes. For these reasons, it is recommended that these “old” records be removed regularly.
The removal of these “old” records is done by “vacuuming” the database using the PostgreSQL vacuumdb command.
It is recommended that all WEM databases be “vacuumed” at least once a week. However, databases that undergo frequent record updates/deletions be vacuumed on a daily basis. Two such databases are the trap database (trapdb) and the bulk statistics database (bsdb).
Caution: Database vacuuming should only be performed at a time when there are minimal database transactions. Typically, this would correspond to a time frame in which no configurations are being performed, infrequent alarm updates, and/or infrequent bulk statistics transfers. Before vacuuming the database, the server must check for already running vacuuming. If this is already running, then the next scheduled vacuum process must be skipped. Hence, WEM must execute only one vacuum query at a time for every database.
Periodic, automatic database vacuuming can be performed through the configuration of a text file as described in the Configuring Automated Periodic Database Vacuuming section that follows. This is the preferred method. One-time automatic vacuuming can be performed through the use of the UNIX cron application as described in the Using Cron to Automate Database Vacuuming section. Vacuuming can also be performed manually as described in the Manually Vacuuming WEM Databases section.
 
Configuring Automated Periodic Database Vacuuming
The WEM application contains a configuration file called vacuum.cfg that contains parameters related to database vacuuming operation and frequency. The file is located in the /users/ems/server/etc directory by default.
The parameters provide the flexibility to vacuum either the database as whole or just various tables within the database. The values for these parameters can be modified using a text editor (such as Vi Editor) if required.
Each database and table is configured with a start time, and a frequency that specifies the number of hours that must pass prior to performing the vacuuming function again. For example, if vacuuming of the Configuration database is to start at 12:00 PM, and repeat after 24 hours, the parameter in the vacuum.cfg file would appear as follows:
ConfigDB = 12,24
Important: A value of 00,00 disables vacuuming for both databases and tables.
Note that the vacuuming of the MIB database is not supported because the content changes infrequently.
The following table provides a list of the parameters within the vacuum.cfg file.
Database and Table Parameters Within the vacuum.cfg File
VACUUM ANALYZE : Performs the vacuum function as described previously in this chapter but also collects statistics about the proportions of duplicate values within a column and the min/max value.
VACUUM : Performs only the vacuum function.
VACUUM FULL : Physically reorders the tables. This requires an exclusive lock on each table while it is being processed.
* Any non-zero number specified for a database in the Database Name section supersedes the CompleteDB parameter setting for the database. For example, if the start value for the Configuration database is 13 and the databases corresponding CompleteDB parameter is set to 0, then vacuuming is to be done for the entire database and all of the tables. However, if there is a conflict between the start times specified for any of the tables within the database and that specified for the database itself, vacuuming will not be started for that table.
 
Using Cron to Automate Database Vacuuming
As mentioned previously, the UNIX cron application can be used to automate database vacuuming. It is daemon that is capable of executing commands or scripts at regular time intervals. Additional information on cron is available through its manual pages or on the Internet.
To use cron to automate the vacuum process, add an entry to a crontab on the server on which the WEM is installed. The entry specifies the time and database to be vacuumed. For example, if you wanted to vacuum the bulk statistics database (bsdb) every day at 3:00 AM, you might add the following entry in the crontab:
00 03 * * * /users/ems/postgresx.x.x/bin/vacuumdb -U postgres -d bsdb > /dev/null 2>&1
From the above example, you can see that the vacuumdb command is located in the bin sub-directory of the PostgreSQL installation directory (/users/ems/postgresx.x.x by default). The -U parameter indicates the PostgreSQL database administrator name (postgres by default).
 
Manually Vacuuming WEM Databases
Follow the instructions in this section to manually vacuum databases used by the WEM application. These instructions assume that you are logged in to the server on which the application is installed as the user root and have access to the server’s command line.
Step 1
Ensure that the database(s) are backed-up prior to vacuuming. Refer to the Backing-up WEM Databases section of this chapter for information on performing a database backup.
Step 2
 
cd /users/ems/postgres/bin
Step 3
 
./vacuumdb -U postgres_name -d db_name &
The name of the database to be vacuumed. Refer to the Determining Available Databases section of this chapter for information on determining which databases are available.
Step 4
 
select datid,datname from pg_stat_database;
 
Backing-up WEM Databases
To minimize the risk of data loss, WEM databases should be backed up on a regular basis. It is recommended that databases be backed up at least once a week. However, depending on the environment and frequency of record updates/deletes, you may choose to backup the data more often.
Caution: Database backups should only be performed at a time when there are minimal database transactions. Typically, this would correspond to a time frame in which no configurations are being performed, infrequent alarm updates, and/or infrequent bulk statistics transfers.
The database backup creates a number of text files in a directory of your choosing. These text files contain the records from the various tables in the available databases. In the event of data corruption or loss, these text files can be used to restore the information.
Caution: Database restoration should only be performed after the WEM application has been successfully un- and re-installed. Additionally, it should only be performed with the assistance of a qualified customer support representative.
Follow the instructions below to backup WEM application databases. These instructions assume that you are logged in to the server on which the application is installed as the user root and have access to the server’s command line.
Step 1
 
cd /users/ems/server/dbscripts
Step 2
 
./backup.sh backup /users/ems/server/postgres /tmp/backup_dir backup_log_file backup_err_file
Various parameters used in the above command line are described below:
backup: This is the key word for taking the backup.
postgres_dir_path: Path of postgres directory.
backup_dir_path: Full path name of backup directory.
logfilename: Name of the log file for generating the log of backup. This parameter is used by WEM when called through periodic backup functionality. This file needs to be removed once backup is complete.
errfilename: Name of the error log file. In case of failure error code / reason of failure will be logged in this file. This parameter is used by WEM when called through periodic backup functionality. This file also needs to be removed after backup is complete.
 
Backup Script Error Codes
This script returns the error code, which is used to understand the script result if it is succeeded or failed. This will be helpful for client application (caller of script) to take necessary action.
Following are the supported error codes along with their description:
 
Restoring WEM Database
This option is also used with the backup script backup.sh. It restores the WEM databases. No extra parameters are required for restoring the database other than the ‘restore’ keyword. Script itself asks for more parameters like postgres dir path, backup file location, etc.
For restoring the WEM database, enter the following command:
./backup.sh restore
Caution: back.sh script is used by WEM Periodic backup functionality to the backup of WEM database. It is recommended to use the WEM Periodic backup functionality to take the WEM database backup.
 

Cisco Systems Inc.
Tel: 408-526-4000
Fax: 408-527-0883