Please note that the PostgreSQL database included with Control-M/Enterprise Manager & Control-M/Server for Unix and Microsoft Windows, comes with database maintenance enabled. The PostgreSQL vacuum, Analyze, re-index, garbage collection etc. processes maintain the database automatically and are enabled by default (autovacuum = on) in the Control-M PostgreSQL databases. However, here are some best practices to outline how to monitor and to maintain the Control-M/Enterprise Manager & Control-M/Server PostgreSQL database.
|
Below are the steps to consider when monitors and maintains the PostgreSQL Database:
1) BMC Software recommends to download and install the PGADMIN III Tool (freeware), which is a great Windows GUI to graphically view the PostgreSQL Database. More information about this tool can be on http://www.PGADMIN.org/download/, which also includes a Link to the download location. 2) Regularly check the status of the Control-M/Enterprise Manager(EM) PostgreSQL database with the Database Utilty - 'DBUStatus'. The output will look similar to the below output:
> DBUStatus
Database Owner Password:********
DB=PostgreSQL
Current DB status=Up
Up Time= 2010-02-26 15:29:04.741036+00
Is DB Remote=false
Server Host Name=XXXXX
Server Host Version=SunOS XXXXX 5.10 Generic sun4u sparc SUNW,Sun-Fire-V240
Client Host Name=XXXXX
Client Host Version=SunOS XXXXX 5.10 Generic sun4u sparc SUNW,Sun-Fire-V240
DB Client Version=8.2.4
DB Server Version=8.2.4
Connections To DB= 25
Port=8765
Archive Mode=off
For Control-M/Server database, use utilities like ctmdbcount, ctmdbcheck etc. to check the status of the database. 2) Regularly check the available space for the Control-M/EM PostgreSQL database with the Database Utility - 'DBUStorage'. Make sure there is always enough free disk space for the PostgreSQL database. The output will look similar to the below output: > DBUStorage Database Owner Password:******** DB name = emdb1 Type = data+log Size = 5372179 KB Free = 5307073 KB Used = 65105 KB Used_percentage = 1.21% Location = /home/emuser/pgsql/app_data/emdb1 Message = none Recommendation = none For Control-M/Server database, use utilities like ctmdbused, ctmdbcheck to check the space of the PostgreSQL database. 3) There is no need to extend the database size of the Control-M/EM or Control-M/Server PostgreSQL database. The PostgreSQL will automatically extend the database size of the of the Control-M/EM or Control-M/Server. The PostgreSQL does not use a traditional tablespace or segment to create objects on, but just uses any available disk space so the database is really as big as the free disk space. 4) Regularly monitor the size of the following directories and remove unnecessary files:
<home_em>/pgsql/log
<home_em>/pgsql/data/pg_log
(where <home_em> is the path of the Control-M/Enterprise Manager Home directory).
NOTE: You must not remove any other files under the <home_em>/pgsql/ directory!
<home_controlm>/pgsql/log
<home_controlm>/pgsql/data/pg_log
(where <home_controlm> is the path of the Control-M/Server for Unix and MS Windows Home directory). NOTE: You must not remove any other files under the <home_controlm>/pgsql/ directory! 5) After performing Hot Backups, remove any unneeded archive logs from the hard disk. This will prevent accumulation of unneeded archive logs and risk of file system full. See Knowledge Article: When can I delete the PostgreSQL log files? How do I manage the PostgreSQL archive log files? Legacy ID:KA318122 Additional Information: Customers viewing this solution may find value in the following self-help Connect with Control-M video. 6) Run 'check_schema' utility to ensure database schema has not been damaged., (em check_schema ) Control-M Server DB Schema Health Check Utility =============================================== All DB schema tables in place. Missing schema indexes: id_primary schema_objects_pkey All DB schema partition indexes and primary keys in place. |