Skip to main content

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 (you can choose to use a name other than dbmarlin if you prefer).

CREATE USER dbmarlin WITH PASSWORD 'securepassword';
GRANT pg_monitor to dbmarlin; 

/* Needed to be able to get EXPLAIN PLAN for any statement */
ALTER USER dbmarlin WITH SUPERUSER;

Create a monitoring user with the correct permissions (Amazon RDS)​

CREATE USER dbmarlin WITH PASSWORD 'securepassword';
GRANT pg_monitor to dbmarlin; 

/* Needed to be able to get EXPLAIN PLAN for any statement */
GRANT rds_superuser TO dbmarlin;

Collecting SQL Statistics​

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 small overhead on PostgreSQL, which is why it is normally disabled. Users generally report less than a 1-2% overhead in CPU usage. 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