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