# pgDash Documentation Below is an overview of pgDash followed by a set of articles to help you get up and running with pgDash quickly. If you have any questions, feel free to contact us at hello\@rapidloop.com. pgDash is a modern, comprehensive monitoring solution designed specifically for PostgeSQL deployments. pgDash lets you observe and track every aspect of your PostgreSQL database servers. pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs and detailed reports as well as alerting. pgDash relies on [pgmetrics](https://pgmetrics.io/) for collecting metrics from Postgres servers. pgmetrics is an open-source, standalone utility. pgmetrics is a single, zero-dependency binary with a command-line syntax similar to psql. It can emit the data it collects either as a textual report, or as JSON. The JSON output can be used for scripting, and in this case, can be sent to pgDash. See [pgmetrics.io](https://pgmetrics.io/) for more information. pgDash is available both as a SaaS product, at [https://pgdash.io](https://pgdash.io/), and as a self-hosted (on-premise) version that you can host yourself. For more details about a Free Trial of the self-hosted version, please contact us at . pgDash is a product from [RapidLoop](https://www.rapidloop.com/), the makers of [OpsDash](https://www.opsdash.com/). # Getting Started View a quick screencast at to get an overview of the steps detailed below to get pgDash setup:
## 1. Get pgmetrics ``` curl -O -L https://github.com/rapidloop/pgmetrics/releases/download/v1.19.0/pgmetrics_1.19.0_linux_amd64.tar.gz tar xvf pgmetrics_1.19.0_linux_amd64.tar.gz ``` [pgmetrics](https://pgmetrics.io/) is the open-source collection agent for pgDash. This is available as a single, zero-dependency, statically-linked binary executable that you can copy to your `$PATH`. You can download this from [GitHub here](https://github.com/rapidloop/pgmetrics/releases), or see other [install options here](https://pgmetrics.io/docs/install.html). Once downloaded, try invoking it and seeing a report for your database: ``` ./pgmetrics_1.19.0_linux_amd64/pgmetrics -h myhost -U myuser mydb ``` The command-line syntax is almost exactly the same as for `psql` and other utilities. Environment variables (like `PGHOST`, `PGPORT`, `PGSSLMODE`, `PGPASSFILE` etc) and files like `~/.pgpass`, `~/.postgresql/postgresql.crt` are also understood by pgmetrics. You should see an output similar to this: ``` pgmetrics run at: 28 Mar 2019 11:03:50 AM (now) PostgreSQL Cluster: Name: 10/main Server Version: 10.7 (Debian 10.7-1.pgdg90+1) Server Started: 27 Mar 2019 11:24:31 AM (23 hours ago) System Identifier: 6547510021216283340 Timeline: 1 Last Checkpoint: 27 Mar 2019 1:44:52 PM (21 hours ago) Prior LSN: 2/7E30D140 REDO LSN: 2/7E321370 (80 KiB since Prior) Checkpoint LSN: 2/7E3213A8 (56 B since REDO) Transaction IDs: 548 to 1150825 (diff = 1150277) Notification Queue: 0.0% used Active Backends: 1 (max 200) Recovery Mode? no ``` The pgmetrics binary is also available for Windows and macOS. ## 2. Get pgdash CLI ``` curl -O -L https://github.com/rapidloop/pgdash/releases/download/v1.14.0/pgdash_1.14.0_linux_amd64.tar.gz tar xvf pgdash_1.14.0_linux_amd64.tar.gz ``` The command-line tool for uploading reports to the pgDash website is also available from [GitHub here](https://github.com/rapidloop/pgdash/releases). This too is a single binary that can be placed anywhere. ## 3. Get Your API Key Once you [signup](https://app.pgdash.io/signup) for pgDash, you can copy the API key from your [profile page](https://app.pgdash.io/profile). ## 4. Create and Upload a Report ``` ./pgmetrics_1.19.0_linux_amd64/pgmetrics --no-password -f json bench | ./pgdash_1.14.0_linux_amd64/pgdash -a YOUR-API-KEY report myserver1 ``` The `pgdash report` command will upload the report in JSON format, piped to it from pgmetrics into . “myserver1” is a friendly name to identify your database server -- we recommend the hostname of the server you're connecting to. For this to work successfully, pgmetrics should be able to complete without asking for a password (this step is meant to be automated and run as a cron job). You can use the usual techniques (.pgpass file, client certificates, trust/peer authentication in pg\_hba.conf), or even supply the password using the environment variable PGPASSWORD (although this is not recommended): ``` PGPASSWORD=mypass pgmetrics -h host {args} | pgdash {args} ``` Tips: 1. If the command appears to hang, it's probably waiting for you enter the password. 2. If you get a 429 error, it means you've hit a rate limit. Wait for 60 seconds before trying again. ## 5. That’s It! You should now be able to see the report in pgDash after logging in. You can repeat this command say, every 10 minutes or so, and see the timeseries graphs populating under “myserver1”. Learn more about [automating](https://pgdash.io/docs/automation) this operation. # AWS Getting Started Getting Started with AWS RDS PostgreSQL and AWS Aurora You can use pgDash with AWS RDS and Aurora, just like with any other PostgreSQL database server. pgDash supports AWS RDS PostgreSQL and AWS Aurora with a PostgreSQL engine. For AWS Aurora, there is no difference between a reader and writer instance as far as pgmetrics and pgDash are concerned. Serverless and Global versions of Aurora are also supported. The steps for using pgDash with AWS RDS PostgreSQL and with an Aurora reader/writer instance are the same. The following steps should help you get up and running with monitoring your RDS instance with pgDash in just a few minutes. In a nutshell, it involves setting up a periodic (cron) job on a system with access to the database (typically an EC2 instance in the same subnet) that connects to the RDS instance, collects metrics, and sends them to pgDash. ### 1. Get pgmetrics [pgmetrics](https://pgmetrics.io) is an open-source, command-line tool that collects metrics from your RDS instance. It can display the results in a easy-to-read text format, or as JSON for sending it to other tools. pgmetrics is a single, statically-linked binary, which makes it easy to deploy. It also uses, by design, almost exactly the same arguments as the standard `psql` command. It also understands the same environment variables and `.pgpass` file that other PostgreSQL CLI tools do. You can download the latest release of pgmetrics from GitHub here: Unpack the release and copy the `pgmetrics` binary to an EC2 instance with access to the database. ### 2. Get pgdash CLI The pgdash CLI tool is a simple command-line tool to send the output of pgmetrics to pgDash. You can download the latest release of pgdash CLI from GitHub here: Unpack the release and copy the `pgdash` binary to the same EC2 instance as before. ### 3. Run pgmetrics Assume you're able to connect to your database using `psql` like this: ``` ~$ psql -h mydbinstance.cipenhlkhjrl.us-east-1.rds.amazonaws.com -U wheel mydb Password for user wheel: Timing is on. Null display is "~". psql (10.6 (Ubuntu 10.6-0ubuntu0.18.10.1), server 11.1) WARNING: psql major version 10, server major version 11. Some psql features might not work. SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. mydb=> ``` You can then run `pgmetrics` using the same arguments: ``` ~$ pgmetrics -h mydbinstance.cipenhlkhjrl.us-east-1.rds.amazonaws.com -U wheel mydb Password: pgmetrics run at: 24 Mar 2019 7:48:19 AM (14 seconds ago) PostgreSQL Cluster: Name: Server Version: 11.1 Server Started: 24 Mar 2019 7:41:33 AM (7 minutes ago) System Identifier: 6649771538356156598 Timeline: 1 Last Checkpoint: 24 Mar 2019 7:47:07 AM (1 minute ago) REDO LSN: 0/C000D18 Checkpoint LSN: 0/C000D50 (56 B since REDO) Transaction IDs: 562 to 620 (diff = 58) Notification Queue: 0.0% used Active Backends: 3 (max 87) Recovery Mode? no [..snip..] ``` Here `wheel` is a user with superuser privileges, but you can use any credentials that have access to the `pg_catalog` schema. You'll need to invoke pgmetrics without it prompting for a password, so that you can automate the collection process. The standard ways all work -- `.pgpass` files, using `trust` authentication and client certificates. You can even directly supply the password in plain-text, like this: ``` ~$ PGPASSWORD='MY-PASSWORD' pgmetrics -h mydbinstance.cipenhlkhjrl.us-east-1.rds.amazonaws.com -U wheel mydb pgmetrics run at: 24 Mar 2019 7:54:43 AM (14 seconds ago) PostgreSQL Cluster: Name: Server Version: 11.1 Server Started: 24 Mar 2019 7:41:33 AM (13 minutes ago) System Identifier: 6649771538356156598 Timeline: 1 Last Checkpoint: 24 Mar 2019 7:52:07 AM (2 minutes ago) REDO LSN: 0/10001230 Checkpoint LSN: 0/10001268 (56 B since REDO) Transaction IDs: 562 to 621 (diff = 59) Notification Queue: 0.0% used Active Backends: 3 (max 87) Recovery Mode? no [..snip..] ``` You should now be able to invoke pgmetrics without a password and see the text output. pgmetrics can generate the report in JSON format also, which we need so that we can send it to pgdash. Use the `-f json` option for this: ``` ~$ PGPASSWORD='MY-PASSWORD' pgmetrics -h mydbinstance.cipenhlkhjrl.us-east-1.rds.amazonaws.com -U wheel -f json mydb { "meta": { "version": "1.5", "at": 1553394427, "collected_dbs": [ "mydb" ], "local": false }, "start_time": 1553393493, "system_identifier": "6649771538356156598", "checkpoint_lsn": "0/140001D0", "prior_lsn": "", "redo_lsn": "0/14000198", "timeline_id": 1, [..snip..] ``` ### 4. Send pgmetrics Report to pgDash To send the report generated by pgmetrics to pgDash, you'll need an API key. If you haven't signed up for pgDash yet, you can do that [here](https://app.pgdash.io/signup). The API key can be found in [your profile page](https://app.pgdash.io/profile). To actually send the report, simply pipe the output of pgmetrics to the pgdash CLI tool: ``` pgmetrics -f json {args} | pgdash -a YOUR-API-KEY report RDS-INSTANCE-NAME ``` for example: ``` ~$ PGPASSWORD='MY-PASSWORD' pgmetrics -h mydbinstance.cipenhlkhjrl.us-east-1.rds.amazonaws.com -U wheel -f json mydb | pgdash -a hkBjMH5qgtzTpPj8LpYQSM report mydbinstance ``` Note that you should be able to let pgmetrics execute without prompting for a password, otherwise this command will appear to hang (it's actually waiting for a password). We recommend using the RDS instance name so that it is easy to identify and relate to in the pgDash UI. You should now see this in the pgDash UI at : ![](/files/-LahaQtetlf55jR-52zK) Tip: If you get a 429 error, it means you're getting rate limited. You'll need to wait 60 seconds at least before trying to send another report. ### 5. (Optional) Setup the pg\_stat\_statements Extension pg\_stat\_statements is a very useful extension that is bundled by default in RDS. It provides information about query performance that pgmetrics can collect and report. You can find more information about pg\_stat\_statements [here in the PostgreSQL docs](https://www.postgresql.org/docs/current/pgstatstatements.html). #### Locate the Parameter Group Locate and open the parameter group for your RDS instance: ![](/files/-Lahbj0LmdWaLXDzC9ua) In this case, the group is called "default.postgres11". #### Ensure shared\_preload\_libraries is present ![](/files/-Lahbwc6zmRc0NoPnVY7) Search for shared\_preload\_libraries and ensure that it has contains the value pg\_stat\_statements. This might already be enabled in your case. Note that if you had to add it, you'll need to restart your RDS instance because PostgreSQL loads shared libraries only at startup. #### Create the extension Run the "CREATE EXTENSION" command to create the extension in the database. You must do this in each database you need to monitor. ``` ~$ psql -h mydbinstance.cipenhlkhjrl.us-east-1.rds.amazonaws.com -U wheel mydb Password for user wheel: Timing is on. Null display is "~". psql (10.6 (Ubuntu 10.6-0ubuntu0.18.10.1), server 11.1) WARNING: psql major version 10, server major version 11. Some psql features might not work. SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. mydb=> CREATE EXTENSION pg_stat_statements; CREATE EXTENSION Time: 381.516 ms mydb=> ``` #### Check if a SELECT works for the pgmetrics user Finally, you should check if the user which pgmetrics is connecting as has permissions to do a SELECT from the pg\_stat\_statements view: ``` mydb=> SELECT * FROM pg_stat_statements LIMIT 1; userid | dbid | queryid | query --------+-------+----------------------+----------------------------------------------------------------------------------------------- 10 | 16384 | -3405321599851175834 | select coalesce(max(pg_current_wal_lsn()::pg_lsn - slots.restart_lsn), $1) as slot_byte_lag fr (1 row) ``` That's it! pgmetrics will automatically collect SQL query metrics if this extension is present. ### 6. (Optional) Setup the auto\_explain Extension The auto\_explain extension can capture the execution plans of slow queries and log them into CloudWatch Logs. To view these execution plans in pgDash, first setup auto\_explain (this step), then let pgmetrics read the logs via the CloudWatch Logs (next step, see below). To setup auto\_explain, edit the parameter group of the database instance and ensure that the value of the configuration setting `shared_preload_libraries` includes `auto_explain`. You'll also need to configure the settings of the auto\_explain extension. These settings are documented extensively [here](https://www.postgresql.org/docs/current/auto-explain.html). Here is a sample configuration: * auto\_explain.log\_min\_duration = 2000 * auto\_explain.log\_analyze = 1 * auto\_explain.log\_timing = 0 * auto\_explain.log\_format = json * With the settings above, any SQL statement that executes for more than 2 seconds will have it's execution plan logged into the PostgreSQL log file in JSON format. Be sure to go through all available settings [in the documentation](https://www.postgresql.org/docs/current/auto-explain.html) and tweak them to suit your deployment. Note that if you had to change the value of `shared_preload_libraries`, a reboot of the database instance will be required. ### 7. (Optional) Collect AWS CloudWatch Metrics pgmetrics can also collect metrics from AWS CloudWatch, for RDS Postgres and Aurora (with Postgres engine) services. This provides information about the VM instance where the database engine is running, including memory and disk usage. pgmetrics will also collect [Enhanced Monitoring](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html) metrics if you've enabled that in your AWS setup. It will also collect PostgreSQL logs via AWS CloudWatch Logs. To let pgmetrics collect CloudWatch metrics, also pass the **database instance ID** of the database being monitored as the **--aws-rds-dbid** option to pgmetrics: ``` $ pgmetrics --aws-rds-dbid=mydbinstance-id {args} | pgdash {args} ``` In case of AWS Aurora, use the database instance ID of the reader or writer instance. pgmetrics supports standard methods of accessing the credentials required for calling the AWS APIs. 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) * Use a shared credentials file (\~/.aws/credentials) * Use environment variables (AWS\_REGION, AWS\_ACCESS\_KEY\_ID, AWS\_SECRET\_ACCESS\_KEY and others) This [AWS documentation page](https://docs.aws.amazon.com/sdk-for-go/v1/developer-guide/configuring-sdk.html#specifying-credentials) has more details. More information about AWS support can be found in the [Integrations](/integrations) page. Finally, note that **AWS will charge you** for the API calls made by pgmetrics to collect this information. See the [AWS CloudWatch pricing page](https://aws.amazon.com/cloudwatch/pricing/) for more details. # Automation You can send pgmetrics reports to pgDash regularly, to let pgDash extract and store metrics from those reports. Typically, you’ll collect the metrics from all important/interesting/related databases from one server in one go, storing it under one name: ``` pgmetrics {options..} {dbs..} | pgdash prod-23 ``` #### **Avoiding Password Prompt** Running in an automated manner naturally requires avoiding the password prompt that pgmetrics brings up by default. You can use the `--no-password` option of pgmetrics to suppress this prompt, then setup alternate ways to authenticate. Using [.pgpass files](https://www.postgresql.org/docs/current/static/libpq-pgpass.html) to supply the password and/or configuring your [pg\_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) file to not require password for a particular host/user/database combination are common options. For more information see [pgmetrics invocation options](https://pgmetrics.io/docs/invoke.html) and [client autentication chapter](https://www.postgresql.org/docs/current/static/client-authentication.html) in the Postgres docs. #### **Collecting and Reporting Metrics Periodically** How often you collect and report metrics depends on your database activity. We recommend a frequency of 5 minutes or so. The pgDash API is rate limited so that you have to wait a minimum of 60 seconds before reporting again -- the pgdash CLI will report an error code 429 if your request was rejected because of rate limit. You can run the command above (“pgmetrics | pgdash”) as a cron job, or use a simple script: ``` #!/bin/sh while true do pgmetrics {options..} {dbs..} | pgdash -a APIKEY report NAME sleep 300 done ``` If you're using a cron job with an interval of one minute, remember that it may a few seconds for the job to complete. This might result in the job being invoked again before 60 seconds are up and may result in a rate-limit. # Teams ## Overview pgDash lets you share all the data for a server with your team members. The team member you're sharing it with can view all information, metrics and graphs, but will not be able to delete any server data. They can also modify the alert rules and notification settings for the server. The Teams feature is available in the Pro and Enterprise versions of pgDash SaaS and self-hosted / on-premise. ## Inviting a Team Member To get started, navigate to the server you'd like to grant access and click on the "Teams..." menu item from the gear icon dropdown to the right. In the dialog that pops up, enter the email address of the team member you want to share the data with. pgDash will send an email with an invite link for the user to create a team member account in pgDash. The mail will also be copied to you. ![](/files/-LKtouh6lr6OBbVv7U1q) In case the email address you enter refers to an already existing, full-fledged account on pgDash, access to the server will be given immediately, and a notification mail will be sent to the user. ## Team Member Accounts Team member accounts can only work with data that have been shared with that account. There is no associated API key or billing plan for such accounts. They have their own username (the email) and password. Team members can delete their own accounts; this does not affect the data shared with them. Team members can view all data associated with a server. They can modify the alert rules and notification settings. They **cannot**, however, delete the report/metric data for the server. ![](/files/-LKtqkCbb7Dk60rXNDfJ) The Settings -> Teams... dialog will show information about who is sharing the server with the team member. Only the owner of the server will be able to share the servers with a team member, the team members themselves are not allowed to share access. ## Revoking Access Owners can revoke access from team members using the same dialog. Use the trash icon ("Revoke Access") next to the email address of the team member you want to revoke access from: ![](/files/-LKtsBTt0HFfiSHDLQop) Access will be revoked immediately upon clicking the icon. # 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 three integrations: AWS CloudWatch, PgBouncer and Pgpool. ### 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](/files/-MQfc_LvX1N5hxcysYed) 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](/files/-MQfc_M0Durol5ghi4cQ) ![Process List section of AWS CloudWatch Dashboard](/files/-MQfc_Lx82na-xeBOHOf) 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html)" 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](/files/-MQfc_M-pNxz2pVG1-T3) ![The Database Identifier of an Aurora Replica](/files/-MQfc_Lz_MYHtzoUWVKL) 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](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon-ec2.html). * Use a shared credentials file (`~/.aws/credentials`). See [AWS docs here](https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-files.html). * 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](https://www.pgbouncer.org/) 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](/files/-MQew8m2C1NyiNyxfWcq) 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](/files/-MQexmmnw-Ba8PltzZGK) 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](/files/-MQexmmm5mTi5LrmgOoM) 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 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: ```bash 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: ```bash 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: ```bash # 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 ``` ### Pgpool [Pgpool](https://www.pgpool.net/) 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:

Pgpool dashboard

The per-backend information, health check status and time series graphs are also available:

Per-backend information and graphs

#### Setting Up The Pgpool Integration 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: ```bash pgmetrics -h PGPOOL-HOST -p PGPOOL-PORT -U PGPOOL-USER -wfjson postgres | pgdash -a APIKEY report-pgpool PGPOOL-HOST ``` 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: ```bash PGPASSWORD=mysecretpass \ pgmetrics -h PGPOOL-HOST -p PGPOOL-PORT -U PGPOOL-USER -wfjson postgres | pgdash -a APIKEY report-pgpool PGPOOL-HOST ``` 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". ### Questions? Feedback? We'd love to hear your thoughts on this feature. Drop us a line at . # Alerts pgDash Alerts allows you to quickly set smart alerts for your database. Here is an overview the alerts that are available: #### Server Level At the server-level (or more correctly at the database cluster level), you can set alerts for: * Transaction ID range getting closer to 2 billion * Time since last checkpoint * Replication: number of inactive replication slots * Replication at primaries: write/flush/replay lag in bytes * Replication at standbys: replay lag in units of bytes * Replication at standbys: replay lag in units of time * WAL files: number of wal files in pg\_wal (earlier pg\_xlog) directory * WAL archiving: number of wal files ready for archiving * Number of backends waiting for locks * Number of backends idling in transaction * Number of backends with transactions open for more than a certain time Here is the UI to set server-level alerts. You can add the new rule either as a *warning* or a *critical* severity alert ![Setting server-level alerts](/files/-LVqxeZwZKGssbov4Qmk) #### Database Level At database-level, you can set these alerts: * Number of backends, as an absolute number * Number of backends, as a percentage of the maximum connections allowed to the database * Commit ratio, as a percentage * Transaction ID age (value of *age(datfrozenxid)*) as as a percentage of autovacuum\_freeze\_max\_age setting (usually 2 billion) * Database size * Count of disabled triggers * Cache hit ratio, as a percentage You can choose which databases to apply the rule to. You can either apply the to rule to all databases, or restrict to databases whose name contains/does not contain/starts with/ends with/is exactly a certain texts ![Setting database-level alerts](/files/-LVqxzTTrcSoMwGf_G35) #### Table Level Table-level alerts include size and bloat. The full list is: * The time since the last auto/manual vacuum/analyze happened * The size of the table in units of bytes * The bloat, in units of bytes * The bloat, as a percentage of the table size The tables the rule applies to can be chosen like with database-level rules. Additionally, you can also restrict the rule to all the tables within a certain database ![Setting table-level alerts](/files/-LVqxzTZ5UycKC-YT3Cn) #### Tablespace Level Disk space rules are set at the tablespace level. * The size of the tablespace, in units of bytes. * The free disk space, as a percentage of the total disk space. * The free inode count, as a percentage of the total available inode count. #### Alerting Status Alerting rules are evaluated whenever you send in a pgmetrics report. These are displayed in the UI, and can also be sent out as email notifications. Here is the full UI, with a few triggered alerts: ![pgDash Alert UI](/files/-LVqylvxHrKhbFyK-Z0N) #### Notifications Alerts can be notified via email, to one or more email addresses. They can also be sent out to Slack channels. PagerDuty events can also be triggered. You can also create alerts in VictorOps. Here is a what an email looks like: ![pgDash Alert Email](/files/-LVqyt8KcvhcFgWsysq_) # Change Alerts ## Overview Change alerts allow you to stay informed about important changes to your PostgreSQL databases, like addition or deletion of users, tables, indexes, or abrupt increases or decreases in table size, and more. While regular alerts are based on thresholds (like table size exceeding a certain limit), change alerts are based on the difference between two consecutive reports. When change alert rules are set, pgDash will check every incoming report against the previous one. Change alerts are designed to keep you informed of any major change happening to your fleet of databases. They can catch unplanned upgrades, restores, schema modifications and rogue scripts inserting or deleting data. They can also be used to detect security concerns like creation of new users or modification of users (change of privileges, group membership). Change alerts can be set from the pgDash UI. Notifications can be sent to email addresses, Slack channels and PagerDuty. The Change Alerts feature is available in the Pro and Enterprise versions of pgDash SaaS and self-hosted / on-premise. ## Setting Change Alerts Select the changes you would like to be alerted about from the pgDash UI. ![The UI in pgDash for setting change alerts](/files/-LM5puYdl5myT-OsMHod) Send notifications via email, Slack channels and PagerDuty. Use the panel at the bottom of the same Change Alerts page to set the destinations: ![Setting notification destinations for change alerts](/files/-LM5puYfic_pvsLuJJ58) ## Notifications When an alert has been triggered, you will get notifications that look something like this: ![A change alert email notification](/files/-LM5puYgSv5Q_NxcokoT) ![A change alert Slack notification](/files/-LM5puYigoYfWN4Z02yM) # Self-hosted pgDash is available as a self-hosted package that can be installed within your datacenter or cloud instances. The self-hosted version of pgDash is entirely self-contained and does not require internet access for its operation. It requires a PostgreSQL database, which has to be provisioned and managed by you. Self-hosted pgDash has all the same features as the SaaS version of pgDash hosted at . ## Availability The pgDash server binary package is available in **.deb** and **.rpm** installer packages and **.tar.gz** tarball format. These packages can be downloaded from the RapidLoop download server at as well as the RapidLoop APT/YUM repo. Instructions below. Currently we support running pgDash on **Debian 11, Debian 12, Ubuntu Server 20.04 LTS, 22.04 LTS, 24.04 LTS** and **RHEL/CentOS/RockyLinux 8 and 9** distros. It is possible to run pgDash as a Docker container, see instructions below. pgDash needs **PostgreSQL 13** or above. We recommend a dedicated database and a dedicated PostgreSQL user for pgDash. You will be able to setup the pgDash server by following the instructions on this page. However, a license file is needed for the server to start. Please contact us at and we'd be happy to provide you with a license file for a Free Trial to take pgDash on a test run! The current version of pgDash self-hosted is **3.5.30**. ## Setup You can install the pgDash server from the RapidLoop APT/YUM repo (recommended), or by downloading and installing the .deb/.rpm installer, or directly using the tarball. ### Using the RapidLoop APT repository 1\. Import the RapidLoop signing key: ``` curl -fsSL https://packages.rapidloop.com/gpg-pubkey-rapidloop.asc | sudo tee /etc/apt/keyrings/gpg-pubkey-rapidloop.asc > /dev/null ``` 2\. Add the RapidLoop repo: ``` echo "deb [signed-by=/etc/apt/keyrings/gpg-pubkey-rapidloop.asc] https://packages.rapidloop.com/debian stable main" | sudo tee /etc/apt/sources.list.d/rapidloop.list ``` 3\. Fetch the RapidLoop package information: ``` sudo apt-get update ``` 4\. Install the pgDash server package: ``` sudo apt-get install pgdash-server ``` ### Using the .deb installer 1\. Download the .deb file: ``` wget https://packages.rapidloop.com/downloads/pgdash-server_3.5.30_amd64.deb ``` 2\. Install the package using the `dpkg` command: ``` sudo dpkg -i pgdash-server_3.5.30_amd64.deb ``` ### Using the RapidLoop YUM repository 1\. Add the RapidLoop YUM repo: ``` sudo dnf config-manager --add-repo https://packages.rapidloop.com/rapidloop.repo ``` 2\. Install the pgDash server package: ``` sudo dnf install -y pgdash-server ``` ### Using the .rpm installer 1\. Download the .rpm file: ``` wget http://packages.rapidloop.com/downloads/pgdash-server-3.5.30-1.x86_64.rpm ``` 2\. Install using the rpm command ``` sudo dnf install -y pgdash-server-3.5.30-1.x86_64.rpm ``` ### Using the .tar.gz tarball Download the .tar.gz tarball: ``` wget https://packages.rapidloop.com/downloads/pgdash-server-3.5.30.amd64.tar.gz ``` 2\. Unpack the tarball ``` tar xvf pgdash-server-3.5.30.amd64.tar.gz ``` 3\. The pgDash server can be started with the current working directory as the root of the unpacked tarball. The server will look for all files (like /etc/pgdash/pgdash.cfg, /var/log/pgdash/pgdash.log) relative to this directory. ``` cd pgdash-server-3.5.30-amd64 usr/sbin/pgdashd ``` The pgDash server will run in the foreground until interrupted by ^C. ### Running as a Docker Container The pgDash server itself is not available as a Docker container, because you'll typically need to edit the configuration file and supply a license file before it can start. We recommend building your own container using this Dockerfile as a starting point: {% tabs %} {% tab title="Dockerfile" %} ``` FROM debian:12 ENV PGDASH_VERSION 3.5.30 RUN apt-get update RUN apt-get -yq install wget RUN wget https://packages.rapidloop.com/downloads/pgdash-server_${PGDASH_VERSION}_amd64.deb RUN dpkg -i pgdash-server_${PGDASH_VERSION}_amd64.deb COPY pgdash.cfg /etc/pgdash COPY ABCDE-FGHIJ-KLMNO-PQRST.lic /etc/pgdash VOLUME /var/lib/pgdash ENTRYPOINT ["/usr/sbin/pgdashd"] ``` {% endtab %} {% endtabs %} Place your custom pgdash.cfg and the license file in the same directory as this Dockerfile and use the `docker build` command to build your container. The timeseries and other metrics data is stored under `/var/lib/pgdash`, which should be persisted into a Docker volume. Log files created under `/var/log/pgdash` , as well as configuration files in `/etc/pgdash` can also be stored on volumes if desired. ### System Requirements The main pgDash application is a single, native binary that runs as a daemon. As such, it has low memory and CPU requirements. It does not store any state on-disk, and relies on the PostgreSQL database for storage of all persistent data, including timeseries data. #### System Clock The system on which pgDash is running must have it's clock synchronized via NTP or similar. An incorrect clock may result in unexpected behavior. #### System Time Zone We highly recommend that the time zone of the system on which pgDash is running is set to UTC. Note that the timestamps shown in the browser when you access pgDash will still appear in the time zone of the browser's machine. #### File System pgDash stores timeseries data (metrics, and parts of pgmetrics reports) in a compressed format under `/var/lib/pgdash`. There should be sufficient storage and IOPS for the filesystem where this directory will be located. ### Reverse Proxy It is possible to run pgDash behind a reverse proxy. To host pgDash at a location like `https://internal.company.com/pgdash`, and assuming the fronting server is nginx, you can configure nginx like: ``` location /pgdash/ { proxy_http_version 1.1; proxy_pass http://internal.pgdash.server:8080/; } ``` and also add the following in the pgDash configuration file (`/etc/pgdash/pgdash.cfg`): ``` exturl = 'https://internal.company.com/pgdash' ``` ## Configuration The pgDash daemon (`/usr/sbin/pgdashd`) is installed as a systemd service (*pgdashd.service*). On startup, it reads the configuration file `/etc/pgdash/pgdash.cfg`. Please refer the comments in that file to edit it to suit your needs. You'll need to provision a PostgreSQL database for pgDash's use. We recommend a dedicated database, and a dedicated PostgreSQL user `pgdash`. You'll need to specify the connection parameters to this database in `/etc/pgdash/pgdash.cfg`, like so: ``` dsn = 'host=my.db.host user=pgdash dbname=pgdash' ``` pgDash will create and manage the schema in the database without any extra steps. ### Email Settings For the pgDash server to send emails (team invite, alerts etc), you'll need to configure an SMTP server in the configuration file. The following entries need to be set: ``` [smtp] server = "smtp.example.com:587" from = "pgdash@example.com" username = "user" password = "pass" ``` After changing the configuration file, use `sudo systemctl restart pgdashd` for changes to take effect. Note that your SMTP provider might require a pre-validated *from* address, and that there might be default firewall rules on outbound connections to ports 25, 465 or 587. Once configured, you can use the following command to send a test mail to check if the settings are good: ``` /usr/sbin/pgdash-admin email test your.email@example.com ``` ### External URL For pgDash to include the proper URL in emails, alert texts etc., set the external URL by which users will access the pgDash UI in the configuration file: ``` exturl = 'http://10.1.2.3:8080' ``` If you are hosting pgDash behind a reverse proxy ([see above](#reverse-proxy)) to let your users access pgDash with a URL like `https://pgdash.example.com`, then set that instead: ``` exturl = 'https://pgdash.example.com' ``` After changing the configuration file, use `sudo systemctl restart pgdashd` for changes to take effect. ### License File The license file that you receive should be placed in `/etc/pgdash`, and must be readable at least by the system user "pgdash" or the system group "pgdash". Do not rename the license file that you receive. The pgDash service must be restarted for changes to take effect. The following steps are recommended: ``` 1. Ensure that the license file is present in the /etc/pgdash directory 2. sudo chmod 0400 /etc/pgdash/*.lic 3. sudo chown pgdash:pgdash /etc/pgdash/*.lic 4. sudo systemctl restart pgdashd ``` The files in `/etc/pgdash` should look like: ``` $ ls -l /etc/pgdash total 12 -r-------- 1 pgdash pgdash 120 Mar 15 10:30 J9NYY-KMSR6-IXJ3M-OQHZR-RDKTK.lic -rw-r----- 1 pgdash root 3886 Mar 27 08:23 pgdash.cfg ``` ## Getting Started Once you have pgDash up and running, you'll be able to browse to (or whichever URL you configured) and see the login page. New users can signup to your instance of pgDash. You can use the same steps as the SaaS version for [Getting Started](/getting-started), with one change -- the pgdash command-line tool must be instructed to send the data to your instance rather than the SaaS instance. This is done by using the "--base-url" argument for pgdash: ``` pgmetrics --no-password -f json -h server.name mydb1 | pgdash --base-url=http://pgdash:8080/api/v1 -a MYAPIKEY report server.name ``` All other aspects of using pgDash remain the same, and further documentation can be found in other sections of this manual. ## Administration pgDash comes with a command line tool pgdash-admin to perform administrative tasks. Use the --help option to see what commands are available: ``` pgdash-admin --help ``` Log files will be present in /var/log/pgdash. ## Support Please contact us at for any help with your self-hosted pgDash instance. ## Free Trial If you'd like to take pgDash for a test-drive, email us at and we'll get you setup with a self-hosted Free Trial. # Self-hosted FAQ ### Getting Started #### Can I try the self-hosted version of pgDash to see if it meets my needs? Yes. Please contact us at for a 14-day Free Trial license. Be sure to mention the number of servers you want to monitor. You can find detailed technical instructions for getting started with the self-hosted version of pgDash here: The feature set of the on-premise version is same as what you see in the SaaS instance at [app.pgdash.io](https://app.pgdash.io/), so you can also check out pgDash by signing up for the 14-day Free Trial of the SaaS version of pgDash. #### How do I get started with self-hosted / on-premise pgDash? You can find detailed technical instructions for getting started with the self-hosted version of pgDash here: Please note: the SaaS and self-hosted versions of pgDash are entirely separate, so please be sure to login to your instance only when setting up and using the self-hosted version. You will not be able to login via app.pgdash.io to access your self-hosted instance of pgDash. #### What is the recommended hardware configuration for running the pgDash server? The minimum hardware configuration depends on the number of Postgres databases you'll be monitoring. Please contact us as and let us know: Number of servers: Number of Postgres clusters per server: 1 Number of databases per cluster (average): Number of tables per database (average): Number of indexes per database (average): With these details, we can provide you a recommended minimum hardware configuration for your deployment. #### What dependencies are there / what other software do I need to run my own pgDash server instance? The pgDash server can run on Debian 9+, Ubuntu 18.04 LTS, Centos/RHEL 7+ and similar distros. It needs a PostgreSQL v10+ database to store non-timeseries data. We recommend creating a dedicated database and user on your PostgreSQL server for pgDash's use. #### What features does the on-premise version have? The feature set of the on-premise version is same as what you see in the SaaS instance at [app.pgdash.io](https://app.pgdash.io/), so you can also check out pgDash by signing up for the 14-day Free Trial of the SaaS version of pgDash. #### How much does it cost? The self-hosted version of pgDash is priced similarly to the SaaS version, but is licensed on an annual rather than a monthly basis. License fees are payable, annually, in advance, and include a 10% discount off list prices. Total price depends on the number of servers you want to monitor. For licensing purposes, a "server" is technically a Postgres cluster -- a single postgres server process (postmaster/daemon) managing a set of databases. Please write to us at with this information and we'll get back with a quote. #### What payment options do you accept for the self-hosted version of pgDash? Annual license fees can be paid by credit card, wire transfer or ACH (US only). ### Running pgDash #### How do I get an API key on the on-premise version? Signup at the signup page on your pgDash server (). After you are logged in, you can copy the key from the profile page. Note that the SaaS and self-hosted versions of pgDash are entirely separate, so please be sure to login to your instance only. You will not be able to login via app.pgdash.io to access your self-hosted instance of pgDash. #### How do I send in the pgmetrics reports? After logging in to your pgDash server, copy out the API key from the profile page. Use this key, along with the URL of your pgDash server, in the command line: ``` pgmetrics {args} | pgdash -a APIKEY --base-url=http://your-server:8080/api/v1 \ report your-db-server-name ``` Note that the right API key and the "--base-url" parameter **must** be specified for this to work correctly. The base URL instructs the pgdash CLI command to send the data to your pgDash server and not the SaaS instance. #### How do I share access with my team? You can share access on a per-dbserver basis using the Team Sharing feature. See the [Teams ](/teams)page for more information. Note that you must configure an SMTP server in `/etc/pgdash/pgdash.cfg` so that your pgDash server is able to send invite mails for team sharing. More details [here](/self-hosted#configuration). #### There are no queries listed in the "Queries" page. Please see the [Working With Queries](/queries) page for information on how to collect query information from PostgreSQL. #### Where does pgDash store it's data? How should I manage it? * pgDash stores most of it's data in a PostgreSQL database that you'll have to provision and manage. * Timeseries metrics and related data are stored on the local filesystem under `/var/lib/pgdash`. This directory maybe symlinked to another disk (NFS volumes are also OK). The pgDash daemon internally prunes out old timeseries data (by default data older than 3 months) automatically. * Log files are present under `/var/log/pgdash`. * Configuration and license files are present under `/etc/pgdash` . # AI Integration for Self-Hosted To use AI-powered Chat on your self-hosted installation, follow these steps: * Ensure you have pgDash version 3.5.27 or above installed. If you need to upgrade, follow the instructions in the [Self-hosted](/self-hosted) page. * Currently, OpenAI and Google Gemini are supported as AI providers. Obtain an API key from either of these providers. pgDash uses the chat completion / text generation APIs fot the AI-powered Chat feature. * Edit `/etc/pgdash/pgdash.cfg` and locate the `[ai]` section. Set the keys appropriately: * `provider` to either `openai` or `gemini` * `apikey` to the API key from OpenAI or Gemini * `model` to any model from the provider that supports chat completion / text generation. pgDash benefits a lot from larger context windows. As of now, we recommend using Google Gemini with `gemini-2.5-pro` model. All responses of AI must be verified before use. Finally, note that OpenAI or Google Gemini will charge you for the API calls made to enable the AI responses. # Webhooks pgDash supports notification of alerts and change alerts via Webhooks since version 2.4.11. This document describes the technical details required to write your own Webhook to process these notifications. #### General Requirements: * Webhooks must be provided as a `http` or `https` URL. For `https` URLs the certificate must be valid. * Notifications are delivered as an HTTP POST request with a content type of `application/json`. The *User-Agent* header will begin with `pgDash-Webhook`. The connection will be closed after a successful request. * The webhook must process the notification within *3 seconds*. If it fails to send a response within this time, pgDash will wait for *5 seconds* and retry the request. No more than *3* retries will be attempted. Note that retries are attempted only on timeout, not on the response's HTTP status code. * The webhook must return a status code of 200 if it receives and processes the request. * pgDash will discard any response body from the webhook. No response body is required. #### Request Body for Alerts: * Webhook URLs set on the "Alerts" page in pgDash will be invoked whenever there is a change in the alert status of *any* rule that has been set. * Notifications are sent only when the status changes, that is, the mechanism is "edge-triggered" and not "level-triggered". In the absence of any notifications it must be assumed that the state indicated by the last-sent notification continues to persist. * When all alerts clear, a notification is sent without any alert items (the `alerts` array described below is empty). As per the previous rule, another notification will be sent only when this state changes, that is, when an alert rule is triggered. * The request body is a JSON object with the following properties: * `version` - integer - the version of the JSON object's schema. Currently always 1. Note that backwards-compatible changes, like adding a new property might be done without incrementing this version number. * `server` - string - the name of the server whose alert status has changed. * `reported` - integer - the timestamp of the pgmetrics report which caused the change in the alert status. The timestamp is represented as the number of seconds since epoch (1 Jan 1970). * `alerts` - array of objects - the list of warning or critical alerts, each as an object with properties as below. Note that if the alerts just cleared and the state went back to normal, this array will be empty. For each separate object that triggered a rule, there will be a separate entry in this array (example if the rule is "Table size is greater than 100 MiB" and two tables qualify, there will be two entries in this array that have the same "text" property.) * `type` - string - one of `warn` or `crit` indicating a rule type of warning or critical respectively. * `text` - string - a description of the alert rule, example "Number of backends is greater than 40", "Database size is greater than 1 GiB". * `value` - string - the current value which triggered the rule, example "43", "1.4 GiB" * `objname` - string - the name of the object involved in the alert * `objlink` - string - a URL to the object involved in the alert * `query` - string, optional - if this is a query-level alert, the full SQL query text Here is an example of the request body: ``` { "version": 1, "server": "prod-42", "reported": 1567428364, "alerts": [ { "type": "warn", "text": "Number of backends is greater than 40", "value": "43", "objname": "inventorydb", "objlink": "https://app.pgdash.io/server/prod-42/db/inventorydb" }, { "type": "warn", "text": "Max time taken by query is greater than 3 minutes", "value": "4m", "objname": "/*\"\"*/ select *, pg_", "objlink": "https://app.pgdash.io/server/prod-42/alerts", "query": "/*\"\"*/ select *, pg_sleep($) from testtable" }, { "type": "crit", "text": "Database size is greater than 1 GiB", "value": "1.4 GiB", "objname": "inventorydb", "objlink": "https://app.pgdash.io/server/prod-42/db/inventorydb" } ] } ``` #### Request Body for Change Alerts: * Webhook URLs set on the "Change Alerts" page will be invoked whenever changes between consecutive reports (as set by the change alert rules) are detected by pgDash. * Notifications are sent whenever changes are detected. Each notification is independent, and there is no "state" as for the regular alerts. * The request body is a JSON object with the following properties: * `version` - integer - the version of the JSON object's schema. Currently always 1. Note that backwards-compatible changes, like adding a new property might be done without incrementing this version number. * `server` - string - the name of the server involved. * `prevreport` - integer - the timestamp (as seconds since epoch 1 Jan 1970) of the previous report which was the basis for the change. * `currreport` - integer - the timestamp (as seconds since epoch 1 Jan 1970) of the current report which has changed since the previous one. * `changes` - array of objects - the list of changes. Each entry is a JSON object with the following properties: * `item` - string - a description of the change * `prev` - string, optional - the previous value, if applicable * `curr` - string, optional - the current value, if applicable Here is an example of the request body: ``` { "version": 1, "server": "prod-42", "prevreport": 1567432246, "currreport": 1567432291, "changes": [ { "item": "New Table Added", "curr": "inventorydb.public.dispatch201909" }, { "item": "Size of Tablespace \"pg_default\" Changed", "prev": "53 GiB", "curr": "54 GiB" } ] } ``` # Working With Queries With pgDash, you can examine how your SQL queries are performing, identify reasons for slow queries, track query stats over time and examine query execution plans. ### Setting Up pgDash relies on the popular extensions `pg_stat_statements` and `auto_explain` to collect query and plan information. These extensions are part of the core PostgreSQL distribution, and are typically available already in your deployment (including AWS RDS and Aurora). Documentation for the extensions can be found in the PostgreSQL docs [here](https://www.postgresql.org/docs/current/pgstatstatements.html) and [here](https://www.postgresql.org/docs/current/auto-explain.html). Tip: You can use the psql command `\dx` to list all the currently installed extensions in a database. #### pg\_stat\_statements Follow these steps to install the pg\_stat\_statements extension: * Update the configuration setting `shared_preload_libraries` ([docs](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES)) to include "pg\_stat\_statements". * The PostgreSQL server needs to be restarted for this change to take affect. (Yes, this is required and cannot be avoided. This is the case for AWS RDS also.) * Connect to the database and run `CREATE EXTENSION pg_stat_statements;` You'll need to connect as a superuser or at least the database owner. * Verify that regular users are able to run `SELECT * FROM pg_stat_statements LIMIT 1` and see the results without any errors. In particular, the "query" column should not say "\". Connect in turn to each database you want to monitor, and repeat the last two steps. #### auto\_explain Follow these steps to install the auto\_explain extension: * Update the configuration setting `shared_preload_libraries` ([docs](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES)) to include "auto\_explain". * Add auto\_explain related settings to your PostgreSQL configuration. Refer to the [auto\_explain documentation](https://www.postgresql.org/docs/current/static/auto-explain.html) for the full list. Here is a starting point: * `auto_explain.log_min_duration = 2000` * `auto_explain.log_analyze = 1` * `auto_explain.log_timing = 0` * `auto_explain.log_format = json` * With the settings above, any SQL statement that executes for more than 2 seconds will have it's execution plan logged into the PostgreSQL log file in JSON format. Be sure to go through all available settings [in the documentation](https://www.postgresql.org/docs/current/auto-explain.html) and tweak them to suit your deployment. * For the changes to take effect, manually run `LOAD auto_explain;` to load the extension and `ALTER SYSTEM SET` to change the value of the configuration settings. (You can also *reload* PostgreSQL for the configuration changes to take effect). * The auto\_explain extension is independent of any database, and the above steps need to be done only once for the entire server. ### Using pgmetrics to collect query and plan information #### Query information (via pg\_stat\_statements) The query information is collected by connecting to PostgreSQL and querying the pg\_stat\_statements view. By default, pgmetrics will attempt to collect query information from pg\_stat\_statements without requiring any explicit actions. There are a few command-line parameters you can tweak to ensure it does what you want it to: * `--sql-limit=500` By default, pgmetrics collects only the first 500 characters of SQL queries, which is hopefully enough to identify the query which you can then locate in your code. If 500 is too few for you, you can increase the value by using this option, like so: `pgmetrics --sql-limit=1000 {other.args}`. Also note that pg\_stat\_statements itself has a similar limit (by default 5000). If you need to go above this limit, you'll need to change the setting `pg_stat_statements.max` (more details [here](https://www.postgresql.org/docs/current/pgstatstatements.html)).
* `--statements-limit=100` By default, pgmetrics collects the 100 slowest queries (as ordered by total\_time). You can change this number using this parameter, like so: `pgmetrics --statements-limit=200 {other.args}`. #### Plan information (via PostgreSQL log files) The plan information is collected from PostgreSQL log files. pgmetrics has to be run on the same system as the PostgreSQL server so that it can read and parse the PostgreSQL log files. In case of AWS RDS Postgres and AWS Aurora, pgmetrics can connect via the AWS CloudWatch Logs API to fetch and parse PostgreSQL log files. When pgmetrics is run from the machine that hosts both the Postgres server and its log files, pgmetrics will attempt to automatically locate and read the last few minutes' worth of log files to collect auto\_explain output. The following command-line arguments can help tweak the exact behavior: * `--log-file=path/to/log/file` If the default logic does not locate the correct log file, use this option to specify the exact location of the log file.
* `--log-span=MINUTES` By default pgmetrics scans only the log file entries made during last 5 minutes of the log file. (Note that pgmetrics is meant to be run periodically, like every 5 minutes). You can change the default value of 5 using this option.
* (AWS RDS and Aurora only) The `--aws-rds-dbid` option, as well credentials to access the AWS APIs must be set so that pgmetrics can fetch the Postgres logs via CloudWatch. See [this page](https://docs.pgdash.io/pages/-LahNvPTUc9ugGbAW0m7#7.-optional-collect-aws-cloudwatch-metrics) for more info. ### Collecting Disk I/O Times PostgreSQL can track the amount of time it spends doing disk I/O, but this feature is turned off by default. The configuration setting "track\_io\_timing" ([docs](https://www.postgresql.org/docs/current/runtime-config-statistics.html)) controls this setting. It is off by default because the measurement may incur an overhead. However, it should be safe to turn it on in nearly any modern Linux system. You can check the actual overhead using the [pg\_test\_timing](https://www.postgresql.org/docs/current/pgtesttiming.html) tool. When track\_io\_timing is enabled, pg\_stat\_statements can report the time spent by each query in performing disk reads and writes. In practice, this information can be quite useful in analyzing query performance. ### Resetting The Statistics pg\_stat\_statements reports aggregate values (average, standard deviation, sum) of the actual query execution statistics. These values are accumulated permanently (even across server restarts) until reset manually. Because of this, you may find that it takes time for the stats to reflect the current numbers. Resetting is especially helpful after an earnest round of query optimization or a significant change in workload patterns. You can use the function `pg_stat_statements_reset()` to clear accumulated statistics. Use it like so: `SELECT pg_stat_statements_reset();` You'll need superuser privileges to run this function. Postgres 12 is more flexible and can discard only a subset of the accumulated statistics. For more information see the [docs](https://www.postgresql.org/docs/current/pgstatstatements.html).