Site icon CormacHogan.com

Aria Operations integration with VMware Data Services Manager v1.4

This is a post to address a query that I recently received  regarding the use of Aria Operations (formerly vRealize Operations) with VMware Data Services Manager. The question was whether or not the vRealize True Visibility Suite for PostgreSQL and MySQL would work with the databases that are provisioned by DSM. tl;dr The answer is yes, the True Visibility management packs will successfully gather metrics from databases deployed via DSM, and allow you to build monitoring dashboards, etc. In this post, I will demonstrate the steps involved in configuring the integration for PostgreSQL as there are a few specific steps that needs to be followed. I have not yet done a verification on MySQL integration, but I will go out on a limb and assume that this will also work with MySQL databases deployed via DSM – I can’t see why it wouldn’t. I will try to verify for sure at a future date.

Versions

The following were the versions that were used for the purposes of this setup.

vRealize True Visibility Management Pack for PostgreSQL

Setting up of Aria Operations and VMware DSM are beyond the scope of this post. Instead, the focus will be on the installation and configuration of the management packs, with specific focus on PostgreSQL. Instructions on how to download and install the management packs are available here.

Once the pack has been downloaded from VMware Customer Connect, it needs to be  added under the Integration section of the vRealize Operations UI as shown below.

After adding the management pack, the vRealize True Visibility Management Pack for PostgreSQL should now be visible in the Repository. The next step is to click on the “ADD ACCOUNT” link to a PostgreSQL database to begin the collection process. If you try to use the dbaas user configured via DSM, you will note that it does not have the appropriate credentials to connect to the database to collect the necessary management pack metrics and events. An attempt to use dbaas credentials will fail as follows:

The  authentication type 10 is not supported. Check that you have configured  the pg_hba.conf file to include the client’s IP address or subnet, and  that it is using an authentication scheme supported by the driver. Check  connection settings. Failed to run query: SELECT datname FROM  pg_database WHERE datistemplate = FALSE AND datname != ‘postgres’

Shortly, we will show how to create a user called lpu with the least amount of privileges necessary to connect to the database and gather metrics. We will now switch focus to getting the database setup correctly for metric gathering, and come back to connectivity shortly.

Prepping PostgreSQL DB for Views, Tables, and Functions

We need to make some changes to the database configuration which will enable the PostgreSQL integration to gather appropriate metrics. On PostgreSQL databases that are provisioned by VMware Data Services Manager, one method to do this is to SSH onto the database VM, and change directory to /opt/vmware/dbaas/data-mount/data. The required SSH privileges are available in the Provider UI. You may now make changes to the postgresql.conf file located in that directory. First, the following field must be modified to allow for any function tracking to happen. By default, it is set to none.

track_functions = all

To track queries, these settings also need to be added to the postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

After, making the changes, the database should be restarted. Use the command systemctl stop dbengine and systemctl start dbengine. This will shutdown the database, and on restart, it should have the necessary changes in place.

Now, as user postgres (or a user with superuser privileges), run the following pSQL commands. This command creates the required extension in the database. PostgreSQL extensions are built-in modules that provide additional functionality to a database. For more information into how PostgreSQL databases, which have been provisioned by VMware DSM, work with extensions, check out the official docs on the topic.

postgres=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements; 
CREATE EXTENSION

If the settings have taken effect successfully,  this select statement shown below should show the installed_version. The version may differ depending on the version of PostgreSQL that you are using.

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' AND installed_version IS NOT NULL; 
name        | default_version | installed_version |                                comment                              
--------------------+-----------------+-------------------+------------------------------------------------------------------------ 
pg_stat_statements  | 1.8             | 1.8               | track planning and execution statistics of all SQL statements executed 
(1 row)

This all looks good. We can now proceed to the next step of the setup.

Least Privileged User Setup

We now return to connectivity. The following script creates a user called lpu who has the minimum privileges necessary to gather the necessary metrics from the database. This is the script taken from the vRealize True Visibility MP for PostgreSQL documentation. As the documentation states, this script will help to automate the creation of a least-privileged user (lpu) for your PostgreSQL instance. You must run the script on the database you plan to monitor. If you’re monitoring multiple databases, you should run the script on the postgres database.

postgres=# CREATE SCHEMA bluemedora;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.explain_this (
l_query text,
out explain json
)
RETURNS SETOF json AS
$$
BEGIN
RETURN QUERY EXECUTE 'explain (format json) ' || l_query;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

CREATE USER lpu WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO lpu;
GRANT SELECT ON pg_stat_bgwriter TO lpu;
GRANT SELECT ON pg_stat_database TO lpu;
GRANT SELECT ON pg_stat_user_indexes TO lpu;
GRANT SELECT ON pg_stat_user_tables TO lpu;
GRANT SELECT ON pg_statio_all_sequences TO lpu;
GRANT SELECT ON pg_statio_user_indexes TO lpu;
GRANT SELECT ON pg_statio_user_tables TO lpu;
GRANT SELECT ON pg_tables TO lpu;
GRANT SELECT ON pg_tablespace TO lpu;
GRANT SELECT ON pg_user TO lpu;
GRANT SELECT ON pg_stat_replication TO lpu;
GRANT SELECT ON pg_stat_database_conflicts TO lpu;
GRANT SELECT ON pg_trigger TO lpu;
GRANT SELECT ON pg_stat_activity TO lpu;
GRANT SELECT ON pg_stat_statements TO lpu;
GRANT USAGE ON SCHEMA bluemedora TO lpu;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO lpu;

Once the script has been run on the postgres database, switch to database that you want to capture stats on (e.g. dbaas), and run the following:

dbaas=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION
dbaas=#

The extension is database bound and must be created for each database, including the dbaas database.

We should now be able to retrieve appropriate events and metrics from the PostgreSQL databases and build dashboards in Aria Operations to display them. This should now allow the PostgreSQL integration to now successfully connect to the database using the lpu user.

Clicking on the “VALIDATE CONNECTION” link should return a successful connection.

Success! Shortly, you should be able to see various metrics related to PostgreSQL appear in the Aria Operations inventory.

Build some dashboards to display DB Metrics

The PostgreSQL integration comes with a set of default dashboards as you can imagine. Here is a screenshot of one of them from my deployment.

You can also build your own sample dashboards based on the metrics being gathered by the PostgreSQL integration. Some sample dashboards are available on https://aria.vmware.com/sample-exchange/vmware-aria-operations-sample-exchange. Using one of the sample dashboards, I was able to very quickly build the following overview myself:

The final thing to point out is that this will show metrics around functions and queries. If you also wish to capture table information, you will need to edit the integration, go to the advanced settings and set the Monitor Tables entry from false (default) to true, as shown below.

I need to caveat this post to say that I am far from being a database administrator, nor am I anything like an expert with Aria Operations. However, I think the post does answer the pressing question around whether or not we can use Aria Operations to monitor the databases which are provisioned via VMware Data Services Manager. I think we can now say with certainty that this is a resounding yes. Thanks for reading.

Exit mobile version