How to Monitor PostgreSQL With Telegraf and MetricFire

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

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

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

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

[[outputs.graphite]]

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

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. Using MetricFire, you can effortlessly identify problems and optimize resources within your infrastructure. Hosted Graphite by MetricFire removes 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> provides a tree-like data structure and makes them easy to query.
3. These metrics can be found in your Hosted Graphite account, where you can 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. Still, 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 critical data infrastructure's optimal performance, reliability, and security. Businesses can proactively identify and address potential issues before they impact operations by tracking key metrics such as resource utilization, query performance, and system health. Monitoring facilitates efficient capacity planning, enabling businesses to scale resources appropriately and avoid downtime due to resource shortages. It is pivotal 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 their operations' overall stability and success.

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.