SITE RELIABILITY ENGINEERING

Monitor Postgres Performance Using Custom Queries

Use custom query for collecting Postgres metrics

I came across several articles on the internet suggesting custom queries to monitor Postgres performance.

For instance, in this AWS blog post, they recommend using the following query for monitoring the Postgres dead-tuples.

SELECT 
  relname AS TableName ,
  n_live_tup AS LiveTuples,
  n_dead_tup AS DeadTuples ,
  last_autovacuum AS Autovacuum ,
  last_autoanalyze AS Autoanalyze
FROM pg_stat_user_tables;

Do you know how to use these custom queries as metrics for monitoring purpose?

Are you aware of some system that supports it out-of-box?

To answer these questions, I come up with a solution. This post shows you

how to monitor Postgres performance using any self-defined query.

Stack Used

I used the following stack

Filebeat + ElasticSearch + Kibana

But you can customize it as per your monitoring stack.

Let me know if you need help with that.

PostgreSQL — Monitor Continuous Archiving and Point-in-Time Recovery

Basic workflow

The basic workflow for collecting Postgres metrics

• A custom query defined in a script is executed on a Postgres DB.
• Returned results are stored in a file as a JSON.
• filebeat ships these metrics to elasticsearch.
• You can visualize these metrics using kibana.
• Add watcher(s) to inform your team in case of any failures.

Proof of concept

Source code: monitor Postgres performance

Metric collector script

A query of interest is defined inside a script.

# replace it with your desired query for monitoring
query="SELECT
       relname AS TableName,
       n_live_tup AS LiveTuples,
       n_dead_tup AS DeadTuples
       FROM pg_stat_user_tables"

This query is executed on the specified database using psql inside a docker container. Upon successful execution, results (or metrics) are stored in a file /metrics/query-output.log

/usr/bin/docker run --name "$name" --rm --memory="64m" \
  --network demo \
  -e PGPASSWORD=password \
  postgres:14.5-alpine \
  psql -h "$host" -U postgres -d demo -c "\copy (SELECT row_to_json(t) FROM ( $query ) t) To STDOUT" >> metrics/query-output.log

Shipping metrics to elastic search

Filebeat ships these metrics to an elasticsearch using the following configuration.

filebeat.inputs:
- type: log
  enabled: true
  paths:
  - /metrics/query-output.log
  json.add_error_key: true
  fields:
    type: "db.metrics"

output.elasticsearch:
  hosts: ["http://elasticsearch:9200"]

One could now visualize this information via kibana.

Postgres metrics in kibana

You can now inform your team by adding watchers in case of any problems.

How to use it in a production system?

You might be using AWS RDS for managing your Postgres database. You need the following.

• AWS RDS Postgres endpoint
• Read-only user for collecting metrics

Use this script as a cron job on an instance (of your choice). Ship these metrics via filebeat (running as a docker container) on the same instance.

Thanks for reading.

If you enjoy this post, you might also like the following post.

Monitor Your System — Practical Guide

Anas Anjaria

Programming

View list

20 stories

Want to connect?
Facebook | LinkedIn | Newsletter | Twitter