The Oracle Enterprise Manager Plugin for PostgreSQL provides visibility of PostgreSQL database health, availability, and metrics in the Enterprise Manager monitoring platform. This plugin is a great tool for those database administrators who want to monitor their PostgreSQL environment within the same monitoring platform they are administrating their Oracle products.
As an administrator, the benefits of reducing complexity and decreasing the amount of tooling needed in an environment will increase efficiency and productivity. Below I outline the step-by-step instructions for installing the Oracle Enterprise Manager Plugin for PostgreSQL.
The most important piece of the plugin installation is to make sure all of the prerequisites are met. With an out-of-the-box PostgreSQL database installation, some minor configuration changes to the Postgres environment will be needed to for the plugin to be able to monitor Postgres. Section 2 of the installation guide lists these prerequisites.
The user will need a minimum of READ permissions to the database being monitored. To do this, login to the PostgreSQL server and perform the following:
adduser <username>
passwd <username>
su – postgres
psql <database name>
CREATE USER <username> WITH PASSWORD ‘mypassword’;
GRANT READ PRIVILEGES ON DATABASE <database name> to <username>;
qThen test the database connection by typing:
psql –d <database name> -U <username>
The hba.conf file needs to be updated to allow Enterprise Manager IP Address to connect remotely as the new user created in step 1.1. To edit the file, perform the following:
cd <PostgreSQL data directory>
vi hba.conf
You will need to add an entry to allow the EM IP Address to connect remotely. It should look something like this:
host <database> <username> <Enterprise Manager IP> trust
(note: the EM IP must be in CIDR notation)
Then save the file.
Make sure that pg_stat views are working for the primary database. Find more information in the PostgreSQL documentation here.
Check to see if statistics monitoring is enabled by running the following query against the primary database:
SELECT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = ‘pg_stat_database’) AS exists;
Install the ‘postgresql-contrib’ package on the postgres server:
Debian/Ubuntu
Replace 9.X with your installed Postgres version:
sudo apt-get install postgresql-contrib-9.X
RedHat/CentOS/SLES
sudo yum install postgresql-contrib
The extension pg_stat_statements needs to be enabled within the Postgres configuration file, “postgresql.conf”.
Original line: shared_preload_libraries = ‘ ‘
Change to: shared_preload_libraries = ‘pg_stat_statements’
Also set the following values:
Save the file and restart Postgres:
Check to see if pg_stat_statements is working by running the following query on the primary database:
SELECT * FROM pg_stat_statements;
Once all the prerequisites are verified to work properly, continue with the plugin installation.
The plugin will need to be made available to the Enterprise Manager system, which means the provided “.opar” file will need to be imported via the command line into the repository.
emcli login -username=sysman
password=[sysman password]
emcli sync
emcli import_update -file=’[path to opar file]’ -omslocal
Once imported, go into the Enterprise Manager UI, go to Setup > Extensibility > Self Update
Click Plugins.
Find the PostgreSQL plugin, select it and click Apply.
Figure 1 – Add Targets Manually Screen
Figure 2 – Adding a Host Target
Figure 3 – Creating a Named Credential
Figure 4 – Adding Target Manually
Figure 5 – Entering PostgreSQL Database Details
Figure 6 – Test Connection Results Window
If the environment has multiple database instances, a separate target will need to added for each instance.
Note: After adding the target and data collection has processed, the overview page will look like the image below. If there are any yellow warnings, go back and check the prerequisites and make sure all the configurations are correct.
Figure 7 – PostgreSQL Overview Page
If you would like more information or a free trial of the Oracle Enterprise Manager Plugin for PostgreSQL, please visit the product page.