The following were the versions that were used for the purposes of this setup.
- VMware vRealize® Operations™, Version: 8.10.2 (21178503), Edition: Enterprise
- vRealize True Visibility Management Pack for PostgreSQL Version:220.127.116.1120907.202005
- VMware Data Services Manager Version 1.4
- VMware vCenter Version 7.0.3 Build: 20990077
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.