Monitor PostgreSQL With Telegraf and MetricFire

Monitor PostgreSQL With Telegraf and MetricFire

Table of Contents

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 datasource.

        

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)

Download Telegraf and unzip it (see the telegraf docs for up-to-date versions and installation commands for many operating systems). Packages and files are generally installed at /etc/telegraf/.
Ubuntu/Debian
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 own datasource, you can start a 14 day free trial with Hosted Graphite by MetricFire in order to follow along with these next steps.

A Hosted Graphite account will provide the datasource, includes Hosted Grafana as a visualization tool, and offers 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 servers line to:

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

Finally, uncomment and edit the prefix line to:

prefix = "<YOUR_API_KEY>.telegraf"
If you don't already have a Hosted Graphite account, sign up for a free trial here to obtain a Hosted Graphite API key.
Otherwise, you can configure a different telegraf output to forward metrics to another datasource.

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 and 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 datasource, 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 own 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:

Monitor PostgreSQL With Telegraf and MetricFire - 1

Then you can use the query UI to select a graphite metric path (the default datasource 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.

Monitor PostgreSQL With Telegraf and MetricFire - 2

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

Monitor PostgreSQL With Telegraf and MetricFire - 3

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:

Monitor PostgreSQL With Telegraf and MetricFire - 4

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

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

Monitor PostgreSQL With Telegraf and MetricFire - 5

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:

Monitor PostgreSQL With Telegraf and MetricFire - 6

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 signup 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:

Monitor PostgreSQL With Telegraf and MetricFire - 7

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.

You might also like other posts...
metricfire Jan 24, 2024 · 6 min read

Monitor Heroku Add-Ons Using Hosted Graphite

Monitoring your Heroku stack helps you understand the performance of your application and infrastructure.... Continue Reading

metricfire Jan 23, 2024 · 8 min read

Monitor Redis Using Telegraf and MetricFire

Monitoring Redis instances is essential for maintaining performance, reliability, and security. It allows you... Continue Reading

metricfire Jan 19, 2024 · 8 min read

Monitor Your Mailchimp Campaigns Using Telegraf

Monitoring your email campaigns helps you track key performance indicators (KPIs) such as open... Continue Reading

header image

We strive for
99.999% uptime

Because our system is your system.

14-day trial 14-day trial
No Credit Card Required No Credit Card Required