Integrations

Integrations let you collect metrics from other systems associated with your PostgreSQL server, to provide a comprehensive picture of the entire system status at one place.

pgDash currently supports two integrations: AWS CloudWatch and PgBouncer.

AWS CloudWatch

AWS exposes information and metrics about the system on which your RDS instance or Aurora replica is running, via AWS CloudWatch. It also provides access to the PostgreSQL logs via AWS CloudWatch Logs.

If you are using pgDash to monitor an RDS instance or an Aurora replica, you can collect metrics and logs via CloudWatch/CloudWatch Logs and send it to pgDash. pgDash can then display a dashboard like this:

AWS CloudWatch Dashboard

The "AWS CloudWatch" page under the "Integrations" heading in the sidebar brings up this dashboard. Some of the graphs (like Load Average and Memory Usage) will also appear in other pages (like Overview).

Memory and Disk sections of AWS CloudWatch Dashboard
Process List section of AWS CloudWatch Dashboard

The information from the PostgreSQL logs are used to show query execution plans for individual queries and to show autovacuum run history.

Enhanced Monitoring

By default, CloudWatch provides a basic set of metrics about the system at the hypervisor level, including CPU, disk, memory and network. If you enable "Enhanced Monitoring" for your RDS instance or Aurora replica, AWS will collect information from an agent running alongside your PostgreSQL server/engine to provide more information, including process information, replica lag and commit latency. We recommend enabling Enhanced Monitoring for your RDS instances and Aurora replicas.

AWS Charges

Note that enabling Enhanced Monitoring and using CloudWatch APIs to collect metrics will incur extra charges from AWS. For more details, see the pricing and documentation pages of AWS RDS, Aurora, CloudWatch and CloudWatch Logs.

Setting Up The AWS CloudWatch Integration

While collecting PostgreSQL metrics from your RDS instance or Aurora replica, pgmetrics can additionally use the CloudWatch APIs to collect the metrics provided by AWS. It will automatically collect Enhanced Monitoring metrics also, if available.

To start collecting data, you need to know the database identifier of the RDS instance or the Aurora replica. This can be seen in the AWS Console. In the screenshots below they are highlighted in red:

The Database Identifier of an RDS Instance
The Database Identifier of an Aurora Replica

You'll also need to set up the credentials for the AWS API calls to go through successfully. As a best practice, AWS recommends that you specify credentials in the following order:

  • Use IAM roles for Amazon EC2 (if your application is running on an Amazon EC2 instance). See AWS docs here.

  • Use a shared credentials file (~/.aws/credentials). See AWS docs here.

  • Use environment variables (AWS_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY)

Once these are available, you can add an extra option (--aws-rds-dbid) to your pgmetrics invocation to enable collection via CloudWatch API. The value of this option should be set to the database identifier of your RDS instance or Aurora replica. Here is an example that uses environment variables for credentials:

export AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE
export AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
export AWS_DEFAULT_REGION=us-west-2
pgmetrics --aws-rds-dbid=MYDBIDENTIFIER {..other args..} | pgdash {..other args..}

The MYDBIDENTIFIER above should be replaced with the actual value.

Collecting Logs

Specifying the --aws-rds-dbid option will result in the collection of PostgreSQL logs also. By default, the last 5 minutes worth of logs are collected. If your pgmetrics cron job runs less frequently than once in 5 minutes, then use the --log-span parameter to collect more logs. For example, if you are running pgmetrics only once every 10 minutes, add the parameter --log-span=10 to the pgmetrics command line.

PgBouncer

PgBouncer is a popular connection pooler for PostgreSQL. It is typically deployed either alongside each PostgreSQL server, or alongside each application/web node.

Monitoring your PgBouncer's pooling efficiency, load and client wait times ensures PgBouncer does not have a negative impact on your applications' query latency.

If you are monitoring a PostgreSQL server using pgDash, and it has one or more PgBouncers that pool incoming connections, you can collect metrics from these PgBouncers and send it to pgDash. pgDash can then display a dashboard for each PgBouncer. Here is how the dashboard looks:

PgBouncer dashboard

The "PgBouncer" page under the "Integrations" menu in the pgDash sidebar shows the dashboards for all the PgBouncer instances that are associated with the PostgreSQL server being monitored. Apart from the overall server-side (PgBouncer to Postgres) and client-side (application to PgBouncer) connection graphs, you can find more information and graphs related to each database configured in PgBouncer, and each pool that PgBouncer has created:

Per-database information & graphs in PgBouncer

The dashboard lists the details of each upstream database, along with graphs for the number of transactions and queries per hour.

Per-pool information & graphs in PgBouncer

Information about each connection pool that PgBouncer has created, with graphs for the outgoing (server-side) and incoming (client-side) connections and client wait times are also shown.

Setting Up The PgBouncer Integration

PgBouncer information is collected using pgmetrics and sent to the pgDash server using the pgdash command, similar to the collecting from PostgreSQL servers. One invocation of pgmetrics will connect to and collect from a single PgBouncer instance. Here is how to invoke pgmetrics to collect from a PgBouncer, and report it to pgDash:

pgmetrics -h PGBOUNCER-HOST -p PGBOUNCER-PORT -U ADMIN-USER -wfjson pgbouncer |
pgdash -a APIKEY report-pgbouncer POSTGRES-SERVER PGBOUNCER-HOST

Here the PgBouncer is running on PGBOUNCER-HOST, listening on port PGBOUNCER-PORT and ADMIN-USER is a PgBouncer user with admin privileges. The PostgreSQL server being monitored is already known to pgDash as POSTGRES-SERVER. Your pgDash API key is APIKEY.

If a password is needed, the usual methods (passfiles, certificates, environment variables) can be used. Here is how to supply the password as an environment variable:

PGPASSWORD=mysecretpass \
pgmetrics -h PGBOUNCER-HOST -p PGBOUNCER-PORT -U ADMIN-USER -wfjson pgbouncer |
pgdash -a APIKEY report-pgbouncer POSTGRES-SERVER PGBOUNCER-HOST

We recommend that you collect metrics one by one from each PgBouncer of a PostgreSQL server, right after collecting from the PostgreSQL server itself. Here is an example of a cron job script collecting from one PostgreSQL server with 2 PgBouncers:

# collect and report from PostgreSQL server on dbhost1
pgmetrics -h dbhost1 -wfjson mydb |
pgdash -a APIKEY report dbhost1
# collect and report from PgBouncer on pgbhost1
pgmetrics -h pgbhost1 -wfjson -U pgbounceradmin pgbouncer |
pgdash -a APIKEY report-pgbouncer dbhost1 pgbhost1
# collect and report from PgBouncer on pgbhost2
pgmetrics -h pgbhost2 -wfjson -U pgbounceradmin pgbouncer |
pgdash -a APIKEY report-pgbouncer dbhost1 pgbhost2

Questions? Feedback?

We'd love to hear your thoughts on this feature. Drop us a line at [email protected].