Monitoring Your PostgreSQL Database With Telegraf and InfluxDB

This tutorial will specifically cover the process of setting up Telegraf and InfluxDB to monitor PostgreSQL. For any newcomers to the scene, PostgreSQL (or just Postgres for short) is a really popular open-source, object-relational database system that was originally spearheaded by developers at UC Berkeley back in 1986. It has important features like multi-version concurrency control and write-ahead logging that help to ensure data reliability. If you're not too familiar with PostgreSQL, I'd recommend starting with their beginner's tutorial.

Recognizing the importance of tracking and monitoring performance and throughput of databases, the makers of PostgreSQL added a statistics collector that automatically amasses information about its own database activity. You essentially have all these great metrics right out of the box. So let's capitalize on that, expose all those metrics to Telegraf, and send them on over to InfluxDB.

What You'll Need

I'm using a local installation of InfluxDB, Telegraf, and Chronograf for this tutorial; the "Getting Started" guides for each of those projects are great and easy to walk through. You'll also need PostgreSQL on your machine, and if you don't happen to have any sample applications and databases lying around, you can fork/clone this repo down to follow along — it's just a small Node/Express app that stores color palettes in PostgreSQL — be sure to follow the README on how to get the app working.

Editing Your Telegraf Config

To start with, the Telegraf GitHub page offers a number of input and output plugins to suit a variety of use cases-one of those includes the PostgreSQL input plugin. If we configure this plugin correctly in our Telegraf configuration file, we should automatically start seeing metrics being sent over to our default telegraf.autogen database within InfluxDB.

Let's try it out.

Navigate to your Telegraf config file and find the [[inputs.postgresql]] section. If you're using a Mac OS and used Homebrew to install InfluxDB and Telegraf, this path /usr/local/etc/telegraf.conf should get you to the default config file. Otherwise, feel free to refer to the Telegraf docs for further reference.

# # Read metrics from one or many postgresql servers
# [[inputs.postgresql]]
#   ## specify address via a url matching:
#   ##   postgres://[pqgotest[:password]]@localhost[/dbname]\
#   ##       ?sslmode=[disable|verify-ca|verify-full]
#   ## or a simple string:
#   ##   host=localhost user=pqotest password=... sslmode=... dbname=app_production
#   ##
#   ## All connection parameters are optional.
#   ##
#   ## Without the dbname parameter, the driver will default to a database
#   ## with the same name as the user. This dbname is just for instantiating a
#   ## connection with the server and doesn't restrict the databases we are trying
#   ## to grab metrics for.
#   ##
#   address = "host=localhost user=postgres sslmode=disable"
#   ## A custom name for the database that will be used as the "server" tag in the
#   ## measurement output. If not specified, a default one generated from
#   ## the connection address is used.
#   # outputaddress = "db01"
#
#   ## connection configuration.
#   ## maxlifetime - specify the maximum lifetime of a connection.
#   ## default is forever (0s)
#   max_lifetime = "0s"
#
#   ## A  list of databases to explicitly ignore.  If not specified, metrics for all
#   ## databases are gathered.  Do NOT use with the 'databases' option.
#   # ignored_databases = ["postgres", "template0", "template1"]
#
#   ## A list of databases to pull metrics about. If not specified, metrics for all
#   ## databases are gathered.  Do NOT use with the 'ignored_databases' option.
#   # databases = ["app_production", "testing"]

This is what the config file looks like out of the box. As you can see, the instructions to follow are fairly simple. You definitely need to specify the address to connect to so Telegraf can talk to your PostgreSQL server. You can optionally specify other parameters such as a username, password, enable or disable ssl-mode, and connect to a specific database if you wish.

If you want to create a custom name for the server tag in your InfluxDB database, you can specify that in outputaddress. Connection lifetime dictates the duration you'd like the connection to remain open. Finally, you can list arrays of databases to either ignore or to collect metrics specifically for those listed. For this option, you can only do one or the other, not both.

This plugin makes it easy to pull metrics from the already built-in pg_stat_database and pg_stat_bgwriter views within PostgreSQL. Check out the docs to see exactly what metrics are pulled. Let's change the address value to a string listing our host as localhost, like so:

address = "host=localhost"

The only other thing to ensure is that your data output will be sent to InfluxDB. If you scroll down to the outputs.influxdb section, you can edit the URL to include InfluxDB's default port 8086:

# Configuration for influxdb server to send metrics to
[[outputs.influxdb]]
  ## The full HTTP or UDP URL for your InfluxDB instance.
  ##
  ## Multiple urls can be specified as part of the same cluster,
  ## this means that only ONE of the urls will be written to each interval.
  # urls = ["udp://localhost:8089"] # UDP endpoint example
  urls = ["http://localhost:8086"] # required
  ## The target database for metrics (telegraf will create it if not exists).
  database = "telegraf" # required

  ## Name of existing retention policy to write to.  Empty string writes to
  ## the default retention policy.
  retention_policy = ""
  ## Write consistency (clusters only), can be: "any", "one", "quorum", "all"
  write_consistency = "any"

  ## Write timeout (for the InfluxDB client), formatted as a string.
  ## If not provided, will default to 5s. 0s means no timeout (not recommended).
  timeout = "5s"
  # username = "telegraf"
  # password = "metricsmetricsmetricsmetrics"
  ## Set the user agent for HTTP POSTs (can be useful for log differentiation)
  # user_agent = "telegraf"
  ## Set UDP payload size, defaults to InfluxDB UDP Client default (512 bytes)
  # udp_payload = 512

  ## Optional SSL Config
  # ssl_ca = "/etc/telegraf/ca.pem"
  # ssl_cert = "/etc/telegraf/cert.pem"
  # ssl_key = "/etc/telegraf/key.pem"
  ## Use SSL but skip chain & host verification
  # insecure_skip_verify = false

  ## HTTP Proxy Config
  # http_proxy = "http://corporate.proxy:3128"

  ## Optional HTTP headers
  # http_headers = {"X-Special-Header" = "Special-Value"}

  ## Compress each HTTP request payload using GZIP.
  # content_encoding = "gzip"

Restart Telegraf and Chronograf, navigate to Chronograf's default port (8888), and in the Data Explorer section of the menu, you should see a measurement called postgresql under the default telegraf.autogen database. You should also see a plethora of metrics in the field column, including blk_read_time, blk_write_time, buffers_clean, datid, deadlocks, tup_inserted, and tup_deleted, just to name a few. To read up on what each of those fields means exactly, check out this reference page.

Alternatively, you can query the data from InfluxDB using the CLI. In your terminal, type influx to access the Influx shell. The command, SHOW DATABASES will list the databases out for you, USE [databasename] and then SHOW MEASUREMENTS will list out the measurement names associated with that particular database. Then you can run various query statements, such as

SELECT mean("xact_commit") AS "mean_xact_commit" FROM "telegraf"."autogen"."postgresql" WHERE time > now() - 5m AND "db"='palette_picker'

or

SELECT * FROM "telegraf"."autogen"."postgresql" WHERE time > now() - 1m AND "db"='palette_picker'

Try it out and see for yourself! If you get too query-happy and need to kill a query at any time, just run KILL QUERY [qid], which can be found using the SHOW QUERIES command.

Monitoring PostgreSQL in Production

If you want to keep tabs on your PostgreSQL databases while in production, it's easy-peasy. Just update the telegraf config file with the correct address information. I've updated the address in my Telegraf config file below to monitor PostgreSQL from my Heroku instance of this same sample app (Palette Picker). I was able to find all these credentials on my Heroku dashboard page. Check it out:

address = "host=ec2-204-236-239-225.compute-1.amazonaws.com user=username password=password dbname=databasename"

(The username, password, and dbname have been changed here for security purposes)

Pretty simple, right?

Next Steps

Hopefully, this guide has helped show just how easy it is to monitor your PostgreSQL databases using Telegraf and InfluxDB. In the next post, we'll talk about some of the key metrics to keep an eye on when evaluating the health of your Postgres database. Feel free to comment below or reach out to us on Twitter @influxDB and @mschae16 with any questions or comments!