AWS RDS Getting Started

You can use pgDash with AWS RDS, just like with any other PostgreSQL database server. 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 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: https://github.com/rapidloop/pgmetrics/releases​

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: https://github.com/rapidloop/pgdash/releases​

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. The API key can be found in your profile page.

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 https://app.pgdash.io/servers:

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.

Locate the Parameter Group

Locate and open the parameter group for your RDS instance:

In this case, the group is called "default.postgres11".

Ensure shared_preload_libraries is present

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.

​