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.

1. Meeting PostgreSQL Prerequisites

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.

1.1 Create a PostgreSQL user with permission to the database(s) to be monitored.

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>;
q

Then test the database connection by typing:

psql –d <database name> -U <username>

1.2 Enable remote access for the new user.

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.

1.3. Enable statistics monitoring

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;

1.4. Install PostgreSQL Statements extension

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:

• # Increase the max size of the query strings Postgres records, track_activity_query_size = 2048

• # Track statements generated by stored procedures as well, pg_stat_statements.track = all

Save the file and restart Postgres:

• # Increase the max size of the query strings Postgres records, track_activity_query_size = 2048

• # Track statements generated by stored procedures as well, pg_stat_statements.track = all

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.

2. Installing the Plugin in Enterprise Manager

2.1 Making the Plugin Available in Enterprise Manager

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.

2.2 Deploy an Oracle Management Agent to the database host.

1. From Enterprise Manager click on “Setup > Add Target > Add Targets Manually”.

Figure 1 – Add Targets Manually Screen

2. Select “Add Host Targets” and click on “Add Hosts”.
3. Click the “+ Add” button.
4. Type in the fully qualified host name (host.domain.com).
5. Select the appropriate platform on which the host resides.

Figure 2 – Adding a Host Target

6. Fill out the required information for the Installation Base Directory, Instance Directory, Named Credential, and Port. Click on the plus sign to add credentials for the PostgreSQL server.
7. Click Next.

Figure 3 – Creating a Named Credential

8. Review and make sure the information is correct, and click Deploy Agent.

2.3 Deploy the plugin to the OMA

1. From Enterprise Manager, click Setup > Add Target > Add Targets Manually.
2. Select “Add Targets Declaratively by Specifying Target Monitoring Properties.
3. For Target Type, select “PostgreSQL Database”.
4. Click the magnifying glass next to Monitoring Agent and click on the agent that you just deployed in Step 2.

Figure 4 – Adding Target Manually

5. Click “Select”
6. Next, click the “Add Manually …” button.
7. Fill out the required fields for Database Hostname > Database Login Name, Database Login Password, Database Port, and Primary Database.

Figure 5 – Entering PostgreSQL Database Details

8. Click the “Test Connection” button. This will test the connection to the PostgreSQL database and will either succeed or report an error which will need to be resolved.

Figure 6 – Test Connection Results Window

9. Once the Test Connection has succeeded, click OK.

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.