Skip to main content

Postgres on Instances

Overview

PostgreSQL on instances is monitored using sfAgent configured with postgres plugin

Metrics plugin

Collects metric data organized in following documentTypes in metrics index:

  • serverDetails
  • databaseDetails
  • tableDetails
  • IndexDetails
  • queryDetails

Logger plugin

Collects general logs and slow query logs. General logs are sent to log index under documentType: postgres-general and slow queries logs are parsed and data is sent metrics index in documentType: postgres-slowquery

Pre-requisites

Enable PostgreSQL general logs

Logging needs to be configured in the postgresql.conf file. This file can be located by executing the command shown below:

postgres=# show config_file; 
config_file
----------------------------------
/data/pgsql/data/postgresql.conf
(1 row)

In postgresql.conf file, uncomment and configure the variables shown below:

 log_min_messages = warning  # set level as appropriate 
log_line_prefix = '< %m > '

Enable Slow Query Logs

Configuring log_min_duration_statement = 200 will log any query which takes more than 200ms to execute which. Set the value to appropriate value

Set access permissions

Username used for DB access should have appropriate permissions

grant SELECT ON pg_stat_database to <username>; 
grant pg_monitor to <username>;
note

root user has these permissions by default

Configuration

Refer to sfAgent section for steps to install and automatically generate plugin configurations. User can also manually add the configuration shown below to config.yaml under /opt/sfagent/ directory

key: <profile_key> 
tags:
Name: <name>
appName: <app_name>
projectName: <project_name>
metrics:
plugins:
- name: postgres
enabled: true
interval: 60
config:
documentsTypes:
- databaseDetails
- indexDetails
- queryDetails
- serverDetails
- tableDetails
host: 127.0.0.1
password: <password>
port: 5432
user: <username>
logging:
plugins:
- name: postgres-general
enabled: true
config:
log_level:
- error
- warning
- info
- log
log_path: /var/log/postgresql/postgresql-10-main.log
- name: postgres-slowquery
enabled: true
config:
log_path: /var/log/postgresql/postgresql-10-main.log

Viewing data and dashboards

  • Data generated by plugin can be viewed in browse data page inside the respective application under plugin=postgres and documentType= serverDetails, databaseDetails, tableDetails, IndexDetails, queryDetails, postgres-slowquery
  • Dashboard for this data can be instantiated by Importing dashboard template PostgreSQL to the application dashboard