Sysdig Documentation

PostgreSQL

PostgreSQL is a powerful, open-source, object-relational database system that has earned a strong reputation for reliability, feature robustness, and performance.

If PostgreSQL is installed in your environment, the Sysdig agent will automatically connect in most cases. In some conditions, you may need to create a specific user for Sysdig and edit the default entries to connect.

See the Default Configuration section, below. The Sysdig agent automatically collects all metrics with the default configuration when correct credentials are provided.

This page describes the default configuration settings, how to edit the configuration to collect additional information, the metrics available for integration, and a sample result in the Sysdig Monitor UI.

PostgreSQL Setup

PostgreSQL will be auto-discovered and the agent will connect through the Unix socket using the Default Configuration with the postgres default user. If this does not work, you can create a user for Sysdig Monitor and give it enough permissions to read Postgres stats. To do this, execute the following example statements on your server:

create user sysdig-cloud with password 'password';
grant SELECT ON pg_stat_database to sysdig_cloud;

Sysdig Agent Configuration

Review how to Edit dragent.yaml to Integrate or Modify Application Checks.

Default Configuration

By default, Sysdig's default.dragent.yaml uses the following code to connect with Postgres.

app_checks:
  - name: postgres
    pattern:
      comm: postgres
      port: 5432
    conf:
      unix_sock: "/var/run/postgresql/"
      username: postgres

If a special user for Sysdig is created, then update dragent.yaml file with the Expanded Example, below.

Warning

Never edit default.dragent.yaml directly; always edit only dragent.yaml.

Example 1: Special User

Update the username and password created for the Sysdig agent in the respective fields, as follows:

app_checks:
  - name: postgres
    pattern:
      comm: postgres
      port: 5432
    conf:
      username: sysdig-cloud
      password: password

Example 2: Connecting on Unix Socket

If Postgres is listening on Unix socket /tmp/.s.PGSQL.5432, set value of unix_sock to /tmp/

app_checks:
  - name: postgres
    pattern:
      comm: postgres
      port: 5432
    conf:
      unix_sock: "/tmp/"
      username: postgres

Example 3: Relations

Lists of relations/tables can be specified to track per-relation metrics.

A single relation can be specified in two ways:

  • Single relation with exact name against relation_name.

  • Regex to include all matching relation against relation_regex.

If schemas are not provided, all schemas will be included. dbname is to be provided if relations is specified.

app_checks:
  - name: postgres
    pattern:
      comm: postgres
      port: 5432
    conf:
      username: <username>
      password: <password>
      dbname: <user_db_name>
      relations:
        - relation_name: <table_name_1>
          schemas:
            - <schema_name_1>
        - relation_regex: <table_pattern>

Example 4: Other Optional Parameters

app_checks:
  - name: postgres
    check_module: postgres
    pattern:
      comm: postgres
      port: 5432
    conf:
      username: postgres
      unix_sock: "/var/run/postgresql"
      dbname: <user_db_name>
      #collect_activity_metrics: true 
      #collect_default_database: true
      #tag_replication_role: true
Table 3. Optional Parameters

Config Parameter

Description

Default Value

collect_activity_metrics

When set to true, it will enable metrics from pg_stat_activity. New metrics added will be:

  • postgresql.active_queries

  • postgresql.transactions.idle_in_transaction

  • postgresql.transactions.open

  • postgresql.waiting_queries

false

collect_default_database

When set to true, it will collect statistics from default database which is postgres. All metrics from postgres database will have tag db:postgres

false

tag_replication_role

When set to true, metrics and checks will be tagged with replication_role:<master|standby>

false



Example 5: Custom Metrics Using Custom Queries

Personalized custom metrics can be collected from Postgres using custom queries.

app_checks:
  - name: postgres
    pattern:
      comm: postgres
      port: 5432
    conf:
      unix_sock: "/var/run/postgresql/"
      username: postgres
      custom_queries:
        - metric_prefix: postgresql.custom
          query: <QUERY>
          columns:
            - name: <COLUNMS_1_NAME>
              type: <COLUMNS_1_TYPE>
            - name: <COLUNMS_2_NAME>
              type: <COLUMNS_2_TYPE>
          tags:
            - <TAG_KEY>:<TAG_VALUE>
Table 4. Optional Parameters

Option

Required

Description

metric_prefix

Yes

Each metric starts with the chosen prefix.

query

Yes

This is the SQL to execute. It can be a simple statement or a multi-line script. All of the rows of the results are evaluated. Use the pipe if you require a multi-line script

columns

Yes

This is a list representing each column ordered sequentially from left to right. The number of columns must equal the number of columns returned in the query. There are 2 required pieces of data:- name: This is the suffix to append to the metric_prefix to form the full metric name. If the type is specified as tag, the column is instead applied as a tag to every metric collected by this query.- type: This is the submission method (gauge, count, rate, etc.). This can also be set to ‘tag’ to tag each metric in the row with the name and value of the item in this column

tags

No

A list of tags to apply to each metric (as specified above).



Metrics Available

See PostgreSQL Metrics.

Result in the Monitor UI

Default Dashboard

The default PostgreSQL dashboard includes combined metrics and individual metrics in an overview page.

postgres_default.png

Other Views

You can also view individual metric charts from a drop-down menu in an Explore view.

postgres_2.png