How to Monitor PostgreSQL With Telegraf and MetricFire

Introduction 

Monitoring your PostgreSQL instance is essential for maintaining performance, reliability, security, and compliance. It allows you to stay ahead of potential issues, optimize resource utilization, and ensure a smooth and efficient operation of your database system. Database monitoring helps you can pinpoint problematic queries, analyze execution plans, and make necessary adjustments to improve overall application responsiveness.

In this article, we'll detail how to use the Telegraf agent to collect performance metrics from your PostgreSQL instance and forward them to a data source.

Getting Started with the Telegraf Agent

Telegraf is a plugin-driven server agent built on InfluxDB and can be used for collecting and sending metrics/events from databases, systems, devices, and a range of popular technologies. Telegraf is written in Go and compiles into a single binary with no external dependencies, and requires a very minimal memory footprint. It is compatible with many operating systems and has many useful output plugins and input plugins for collecting and forwarding a wide variety of performance metrics. 

Install Telegraf (Linux/Redhat)

wget https://dl.influxdata.com/telegraf/releases/telegraf_1.21.2-1_amd64.deb

sudo dpkg -i telegraf_1.21.2-1_amd64.deb

RedHat/CentOS

wget https://dl.influxdata.com/telegraf/releases/telegraf-1.21.4-1.x86_64.rpm

sudo yum localinstall telegraf-1.21.4-1.x86_64.rpm

Configure an Output

You can configure telegraf to output to a variety of sources, like Kafka, Graphite, InfluxDB, Prometheus, SQL, NoSQL, and more.

In this example, we will configure telegraf with a Graphite output. If you're not currently hosting your data source, you can start a 14-day free trial with Hosted Graphite by MetricFire to follow along with these next steps.

A Hosted Graphite account will provide the data source, include Hosted Grafana as a visualization tool, and offer an alerting feature.

To configure the Graphite output, you need to locate the downloaded telegraf configuration file at /etc/telegraf/telegraf.conf and open it in your preferred text editor. Then you will need to make the following changes to the file:

Locate and comment out the line:

# [[outputs.influxdb]]

Then, uncomment the line:

[[outputs.graphite]]

Next, uncomment and edit the server line to:

servers = ["carbon.hostedgraphite.com:2003"]

Finally, uncomment and edit the prefix line to:

prefix = "<YOUR_API_KEY>.telegraf"

Configure the PostgreSQL Input Plugin:

Telegraf has many input plugins that can collect a wide range of data from many popular technologies and 3rd party sources. In this example, we'll demonstrate how to configure the Postgresql plugin.

All you need to do is search for the inputs.postgresql section in your telegraf.conf file, and uncomment the [[inputs.postgresql]] line:

[[inputs.postgresql]]

Then you can specify an address URL, or uncomment the 'address' line for localhost:

address = "host=localhost user=postgres sslmode=disable"

Save your changes, and then run Telegraf using the following command, to see if there are any configuration errors in the output:

telegraf --config telegraf.conf

NOTE: if your Telegraf config contains the default user=postgres, you should create this same user in your DB:

createuser -s postgres

Telegraf will now be forwarding roughly 27 metrics per DB/user to your data source, this is what they will look like in the Graphite format:

telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.blk_read_time
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.blk_write_time
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.blks_hit
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.blks_read
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.buffers_alloc
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.buffers_backend
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.buffers_backend_fsync
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.buffers_checkpoint
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.buffers_clean
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.checkpoint_sync_time
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.checkpoint_write_time
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.checkpoints_req
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.checkpoints_timed
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.conflicts
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.datid
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.deadlocks
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.maxwritten_clean
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.numbackends
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.temp_bytes
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.temp_files
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.tup_deleted
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.tup_fetched
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.tup_inserted
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.tup_returned
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.tup_updated
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.xact_commit
telegraf.<host>.postgres.host=localhost_user=postgres_.postgresql.xact_rollback

See the official GitHub repository for more information and configuration options for the Postgresql input plugin.

Use Hosted Graphite by MetricFire to Create Custom Dashboards and Alerts

MetricFire is a monitoring platform that enables you to gather, visualize, analyze, and alert on metrics from sources such as servers, databases, networks, devices, and applications. By utilizing MetricFire, you can effortlessly identify problems and optimize resources from within your infrastructure. Hosted Graphite by MetricFire takes away the burden of self-hosting your monitoring solution, allowing you more time and freedom to work on your most important tasks.

1. Once you have signed up for a Hosted Graphite account and used the above steps to configure your server with the Telegraf Agent, metrics will be forwarded, timestamped, ingested, and aggregated into the Hosted Graphite backend.
2. They will be sent and stored in the Graphite format of metric.name.path <numeric-value> <unix-timestamp>, which provides a tree-like data structure and makes them easy to query.
3. You can locate these metrics in your Hosted Graphite account, and use them to build custom Alerts and Grafana dashboards.

Create Dashboards in Hosted Graphite's Hosted Grafana

In the Hosted Graphite UI, navigate to Dashboards => Primary Dashboards and select the + button to build a new panel:

Then you can use the query UI to select a graphite metric path (the default data source will be the hosted graphite backend if you are accessing Grafana through your Hosted Graphite account):

The Hosted Graphite datasource also supports wildcard (*) searches to grab all metrics that match a specified path.

Now you can apply Graphite functions to these metrics, like aliasByNode() to reformat the metric names on the graph:

Grafana has many additional options like configuring dashboard variables and annotations. You can also use different visualizations, modify the display, set the units of measurement, and much more.

Hosted Graphite also has a pre-configured dashboard for Telegraf metrics located in their Dashboard Library:

Once this dashboard is generated in your account, you can locate it in your Primary Dashboards to see system metrics (CPU, mem, disk) displayed.

These system performance metrics come standard with a Telegraf => Graphite configuration:

See the Hosted Graphite dashboard docs for more details.

Creating Graphite Alerts

In the Hosted Graphite UI, navigate to Alerts => Graphite Alerts to create a new alert. Name the alert, add one of your graphite metrics to the alerting metric field, and add a description of what this alert is:

Then select the Criteria tab which will set the threshold, and select a notification channel. The default notification channel is the email you used to sign up for the Hosted Graphite account, but you can easily configure a channel for Slack, PagerDuty, Microsoft Teams, and more. See the Hosted Graphite docs for more details on notification channels:

Conclusion

Database monitoring is crucial for businesses as it ensures the optimal performance, reliability, and security of critical data infrastructure. By continuously tracking key metrics such as resource utilization, query performance, and system health, businesses can proactively identify and address potential issues before they impact operations. Monitoring facilitates efficient capacity planning, enabling businesses to scale resources appropriately and avoid downtime due to resource shortages. It plays a pivotal role in ensuring data integrity, security compliance, and user satisfaction. Additionally, by analyzing patterns and trends over time, businesses can make informed decisions for database optimization, cost management, and strategic planning, ultimately contributing to the overall stability and success of their operations.

DB performance monitoring provides valuable data, and using tools like dashboards and alerts complements this monitoring by providing real-time visualization, proactive identification of issues, historical trend analysis, and facilitating informed decision-making, all of which are essential for maintaining a robust and efficient network infrastructure.

Sign up for a free trial, and experiment with monitoring your PostgreSQL instances today! You can also book a demo and talk to the MetricFire team directly about your monitoring needs.