Integrations
Last updated
Last updated
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 three integrations: AWS CloudWatch, PgBouncer and Pgpool.
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:
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).
The information from the PostgreSQL logs are used to show query execution plans for individual queries and to show autovacuum run history.
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.
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.
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:
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:
The MYDBIDENTIFIER above should be replaced with the actual value.
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 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:
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:
The dashboard lists the details of each upstream database, along with graphs for the number of transactions and queries per hour.
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.
PgBouncer information is collected using pgmetrics and sent to the pgDash server using the pgdash command, similar to 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:
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:
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:
Pgpool is commonly used to load balance queries among replicated servers, to pool connections and cache query results. It is typically deployed as an add-on to a cluster of replicated PostgreSQL servers.
pgDash can monitor your Pgpool instance's in-memory query cache, per-backend performance, health check status and overall performance. Here is how the dashboard looks:
The per-backend information, health check status and time series graphs are also available:
Pgpool information is collected using pgmetrics and sent to the pgDash server using the pgdash command, similar to collecting from PostgreSQL servers. Note that you need at least version 1.15.2 of pgmetrics and version 1.10.3 of pgdash CLI. Pgpool versions 4.0 and above are supported.
Here is how to invoke pgmetrics to collect from a Pgpool, and report it to pgDash:
Here the Pgpool instance is running on PGPOOL-HOST, listening on port PGPOOL-PORT and PGPOOL-USER is a Pgpool username that clients can connect with. The postgres at the end of the pgmetrics command refers to the default database, you can change it if required. 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:
This command must be setup to run periodically -- typically as a cron job that is invoked every 5 minutes.
Once data has been successfully sent to pgDash, you can see the Pgpool hostname appear on the landing page under "Servers".
We'd love to hear your thoughts on this feature. Drop us a line at hello@rapidloop.com.
