In my company, we use PostgreSQL, Postgres Plus (a commercial fork of PostgreSQL provided by EnterpriseDB) beside Oracle databases. Unfortunately different databases need different tools to monitor, and implementing a standard monitoring procedure for all databases becomes a real problem. Good thing is Oracle Enterprise Manager is an extendable software supporting 3rd party plugins, and there are companies like Blue Medora which focuses on the product enhancements for Enterprise Manager. Blue Medora released a plugin to monitor PostgreSQL for Oracle Enterprise Manager, and I just deployed it to our servers. In this blog post, I’ll try to give brief instructions to set up the plugin.

First you need to download the plugin from Blue Medora website. I do not know when it will be added to self-update of Enterprise Manager but for now, you need to downloaded it manually. After you download the zip file, copy the zip file to your Enterprise Manager 12c server, and unzip it. I unzip it to a folder called postgre_plugin (under home directory of oracle user):

The zip contains a file named “bm.em.xpgs_12.1.0.1.0_1.0.6.opar”. It’s the plugin file which we’ll need to import to Enterprise Manager. We’ll use “emcli” to login to Enterprise Manager, and then import the plugin:

I divided the last command to two lines, using \ symbol, so it fits into the code-window on my website but you may write it one line (without \ symbol).

Plugin imported

After you import the plugin, you’ll see it on the setup > extensibility > plugins page. First we need to deploy the plugin to our OMS server.

Start to deploy to OMS

Select the plugin and click deploy on management servers.

Ready to deploy to OMS

Enterprise Manager will do some prerequisite checks. Click next or continue buttons on the screens until you reach the deployment page. Do not forget:

  • OMS will be out of service while you are deploying a plugin to OMS server. You may check the deployment process by using emcli.
  • It’s highly recommended to backup your EM repository and software before deploying a plugin to OMS server.

If you’re ready (have backups), click “deploy” button to start deployment. It takes only a few minutes on my system but it may change depending on your hardware.

So we deployed the plugin to our OMS, now it’s time to deploy the plugin to an agent. I have already installed my agents to the servers hosting PostgreSQL. You do not need to deploy agent for each PostgreSQL Server. The PostgreSQL Plugin can remotely monitor databases, but I recommend you to monitor locally unless you have resource problems on the PostgreSQL database servers. In case you need to know how to deploy EM12c agents to host, you can read my previous blog posts.

Deploy to agents

Select the plugin and click deploy on management agent, click continue to move to the screen you select the target agent/host which you’ll deploy the plugin.

Select the agent

Select the agent and click continue. EM will do some prerequisite check, click continue to deploy page, click deploy (it will not restart the agent, so it can be deployed anytime).

Agent Deployed

When the deployment is done, you can add a PostgreSQL target to your system.

Add Target Manually

Go to “”add targets manually” page, check “add targets declaratively by specifying target monitoring properties”. Select “postgresql database” as target type. Select the monitoring agent (the one we already deployed the plugin). Click “add manually”.

Target Properties

Enter the required fields, test connection, and click OK to add it.

PostgreSQL Targets

You can reach the PostgreSQL databases by listing all targets, and click “PostgreSQL Database” in the databases section.

Main Screen

I have tested the plugin for only 2 days, but I can say that it’s a great addition to EM12c. The PostgreSQL plugin of Blue Medora helps you easily monitor PostgreSQL instance, examine tables, indexes, connections and queries.

Monitor Queries

You need to enable “pg_stat_statements” extension on PostgreSQL if you want to monitor queries.

Metrics

The PostgreSQL plugin collects important metrics, so you can define thresholds and use Incident Management features of Enterprise Manager. I have also tried to use this plugin to monitor Postgres Plus databases and it seems it works without any problems. By the way, Blue Medora provides fully functional free edition!

3 Responses to “Oracle Enterprise Manager Cloud Control 12c Plugin for PostgreSQL”

  1. BunditJ says:

    Nice article. It’s been long await to seek and implement on my environment as well :)

  2. Ignacio Davalos says:

    Something else that would have some extra value to add on regards of the SQL Monitoring, you need to be aware that enabling pg_stat_statements might vary depending on your PostgreSQL Version, here is the link to the 9.2, section Configuration Parameters

    http://www.postgresql.org/docs/9.2/static/pgstatstatements.html

    Something extra to be added into the PostgreSQL Configuration, at least for the first time that pg_stat_statements is been set, is to once you have added the entry shared_preload_libraries  to your postgresql.conf:

    # postgresql.conf
    shared_preload_libraries = ‘pg_stat_statements’

    You also need to Create the extension in a PSQL session, this is to create the necessary pg_stat_statements view that this plugin queries, as follows:

    # Validate available extensions:
    postgres=# select * from pg_available_extensions;

    # Create the extention:
    postgres=# create extension “pg_stat_statements”;

    # Check that PG_STAT_STATEMENTS view has been created:
    postgres=# \dS “pg_stat_statements”

    Else the whenever you go into the Queries section you will get Agent errors like:

    “An error occurred calling the metric data service. Unable to get metric sql_statements from agent for target ******_****_development bm_postgresql_db, root cause: oracle.sysman.emSDK.emd.comm.MetricGetException: Failed to execute query for sql_statements.
    . on UI item PostgreSQL0…LineChart756 on page PageActivityDef[homePg]”

Trackbacks/Pingbacks

  1. OBIEE Forum Week, All the Hotshots in Town | Addidici - 14 Years OBIEE and OBI Applications - […] 9. Oracle Enterprise Manager Cloud Control 12c Plugin for PostgreSQL […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">