PostgreSQL

Pre-requisites

Check the supported versions

DBmarlin supports most versions of PostgreSQL from v9 and above. Please check the list of supported platforms before continuing and contact us if you are not sure.

Create a monitoring user with the correct permissions

Before adding a target PostgreSQL instance in the UI you should first create a dbmarlin user in the target database instance.

CREATE USER dbmarlin WITH PASSWORD 'securepassword';
ALTER USER dbmarlin WITH SUPERUSER;

Collecting SQL statement executions

In order to collect executions counts at the SQL statement level, the pg_stat_statements library must be loaded and the extension created within PostgreSQL. There may be some increased overhead on PostgreSQL, which is why it is normally disabled. Having execution counts is very useful for performance tuning and it allows the calculation of average response time. Here are the steps to enable pg_stat_statements.

  1. Edit postgresql.conf and add pg_stat_statements to the list of shared_preload_libraries E.g. shared_preload_libraries = 'pg_stat_statements, ...'
  2. There are options that can also be set in postgresql.conf : pg_stat_statements.max (default 5000), pg_stat_statements.track (default top), pg_stat_statements.track_utility (default on), pg_stat_statements.save (default off). These can be left at there default values.
  3. Restart PostgreSQL to pick up the change.
  4. Log in as administrator using: psql postgres
  5. Run: create extension pg_stat_statements
  6. This installs the pg_stat_statements view in the public schema in the postgres database. We assume that the dbmarlin postgres user has been created in the postgres database.
  7. As the dbmarlin user in psql run: select * from public.pg_stat_statements to verify that the view exists and contains data.

Adding the Database Target

This is done through the User Interface using the Add Database screen