Introduction

This post will go through my experience with setting up some advanced monitoring for PostgreSQL database using Telegraf, InfluxDB, and Grafana (also known as the TIG stack), the problems I faced, and what I ended up doing at the end.

What do I mean by advanced? I liked this Datadog article about some key metrics for PostgreSQL monitoring. Also, this PostgreSQL monitoring template for Zabbix has some good pointers. I didn’t need everything mentioned in these links, but they acted as a good reference. I also prioritized monitoring for issues which I’ve myself faced in the past.

Some key things that I planned to monitor:

• Active (and idle) connections vs. max connections configured
• Size of databases and tables
• Read query throughput and performance (sequential vs. index scans, rows fetched vs. returned, temporary data written to disk)
• Write query throughput and performance (rows inserted/updated/deleted, locks, deadlocks, dead rows)

There are a lot of resources online about setting up the data collection pipeline from Telegraf to InfluxDB, and creating dashboards on Grafana. So, I’m not going into too much detail on this part. This is what the pipeline looks like:

And here’s what my final Grafana dashboard looks like

Research on existing solutions

I found several solutions and articles online about monitoring PostgreSQL using Telegraf:

1. Telegraf PostgreSQL input plugin

Telegraf has a PostgreSQL input plugin which provides some built-in metrics from the pg_stat_database and pg_stat_bgwriter views. But this plugin cannot be configured to run any custom SQL script to gather the data that we want. And the built-in metrics are a good starting point, but not enough. So, I rejected it.

2. Telegraf postgresql_extensible input plugin

Telegraf has another PostgreSQL input plugin called postgresql_extensible. At first glance, this looks promising: it can run any custom query, and multiple queries can be defined in its configuration file.

However, there is an open issue due to which this plugin does not run the specified query against all databases, but only against the database name specified in the connection string.

One way this can still work is to specify multiple input blocks in the Telegraf config file, one for each database.

[[inputs.postgresql_extensible]]
  address = "host=localhost user=postgres dbname=database1"
  [[inputs.postgresql_extensible.query]]
    script="db_stats.sql"

[[inputs.postgresql_extensible]]
  address = "host=localhost user=postgres dbname=database2"
  [[inputs.postgresql_extensible.query]]
    script="db_stats.sql"

But, configuring this does not scale, especially if the database names are dynamic or we don’t want to hardcode them in the config.

But I really liked the configuration method of this plugin, and I think this will work very well for my use case once the associated Telegraf issue gets resolved.

3. Using a monitoring package like pgwatch2

Another method I found was to use a package like pgwatch2. This is a self-contained solution for PostgreSQL monitoring and includes dashboards as well.

Its main components are

1. A metrics collector service. This can either be run centrally and “pull” metrics from one or more PostgreSQL instances, or alongside each PostgreSQL instance (like a sidecar) and “push” metrics to a metrics storage backend.
2. Metrics storage backend. pgwatch2 supports multiple metrics storage backends like bare PostgreSQL, TimescaleDB, InfluxDB, Prometheus, and Graphite.
3. Grafana dashboards
4. A configuration layer and associated UI to configure all of the above.

I really liked this tool as well, but felt like this might be too complex for my needs. For example, it monitors a lot more than what I want to monitor, and it has some complexity to handle multiple PostgreSQL versions and multiple deployment configurations.

But I will definitely keep this in mind for a more “batteries included” approach to PostgreSQL monitoring for future projects.

My solution: custom Telegraf plugin

Telegraf supports writing an external custom plugin, and running it via the execd plugin. The execd plugin runs an external program as a long-running daemon.

This approach enabled me to build the exact features I wanted, while also keeping things simple enough to someday revert to using the Telegraf built-in plugin for PostgreSQL.

The custom plugin code can be found at this Github repo. Note that I’ve also included the line_protocol.py file from influx python sdk so that I would not have to install the whole sdk just for line protocol encoding.

What this plugin (and included configuration) does:

1. Runs as a daemon using Telegraf execd plugin.
2. When Telegraf asks for data (by sending a newline on STDIN), it runs the queries defined in the plugin’s config file (against the configured databases), converts the results into Influx line format, and sends it to Telegraf.
3. Queries can be defined to run either on a single database, or on all databases that the configured pg user has access to.

This plugin solves the issue with Telegraf’s postgresql_extensible plugin for me—I don’t need to manually define the list of databases to be able to run queries against all of them.

This is what the custom plugin configuration looks like

[postgresql_custom]
address=""

[[postgresql_custom.query]]
sqlquery="select pg_database_size(current_database()) as size_b;"
per_db=true
measurement="pg_db_size"

[[postgresql_custom.query]]
script="queries/backends.sql"
per_db=true
measurement="pg_backends"

[[postgresql_custom.query]]
script="queries/db_stats.sql"
per_db=true
measurement="pg_db_stats"

[[postgresql_custom.query]]
script="queries/table_stats.sql"
per_db=true
tagvalue="table_name,schema"
measurement="pg_table_stats"

Any queries defined with per_db=true will be run against all databases. Queries can be specified either inline, or using a separate file.

The repository for this plugin has the exact queries configured above. It also has the Grafana dashboard JSON which can be imported to get the same dashboard as above.

Future optimizations

• Monitoring related to replication is not added yet, but can be added easily
• No need to use superuser account in PostgreSQL 10+
• This does not support running different queries depending on version of the target PostgreSQL system.

Let me know in the comments below if you have any doubts or suggestions to make this better.

There are many different ways to use replication in PostgreSQL, whether for high
availability (using a failover), or load balancing (for scaling), or just for
keeping a backup. Among the various tools I found online, I though bucardo is
the best for my use case - keeping a live backup of a few important tables.

I've assumed the following databases:

• Primary: Hostname = host_a, Database = btest
• Backup: Hostname = host_b, Database = btest

We will install bucardo in the primary database (it required it's own database
to keep track of things).

1. Install postgresql

    sudo apt-get install postgresql-9.4

2. Install dependencies on host_a

    sudo apt-get install libdbix-safe-perl libdbd-pg-perl libboolean-perl build-essential postgresql-plperl-9.4

3. On host_a, Download and extract bucardo source

    wget https://github.com/bucardo/bucardo/archive/5.4.0.tar.gz
    tar xvfz 5.4.0.tar.gz

4. On host_a, Build and Install

    perl Makefile.PL
    make
    sudo make install
    sudo mkdir /var/run/bucardo
    sudo mkdir /var/log/bucardo

5. Create bucardo user on all hosts

    CREATE USER bucardo SUPERUSER PASSWORD 'random_password';
    CREATE DATABASE bucardo;
    GRANT ALL ON DATABASE bucardo TO bucardo;

   Note: All commands from now on are to be run on host_a only.

6. On host_a, set a password for the postgres user:

    ALTER USER postgres PASSWORD 'random_password';

7. On host_a, add this to the installation user's ~/.pgpass file:

    host_a:5432:*:postgres:random_password
    host_a:5432:*:bucardo:random_password

   Also add entries for the other hosts for which users were created in step 5.

   Note: It is also a good idea to chmod the ~/.pgpass file to 0600.

8. Run the bucardo install command:

    bucardo -h host_a install

9. Copy schema from A to B:

    psql -h host_b -U bucardo template1 -c "drop database if exists btest;"
    psql -h host_b -U bucardo template1 -c "create database btest;"
    pg_dump -U bucardo --schema-only -h host_a btest | psql -U bucardo -h host_b btest

10. Add databases to bucardo config

     bucardo -h host_a -U bucardo add db main db=btest user=bucardo pass=host_a_pass host=host_a
     bucardo -h host_a -U bucardo add db bak1 db=btest user=bucardo pass=host_b_pass host=host_b

    This will save database details (host, port, user, password) to bucardo
    database.

11. Add tables to be synced

    To add all tables:

     bucardo -h host_a -U bucardo add all tables db=main relgroup=btest_relgroup

    To add one table:

     bucardo -h host_a -U bucardo add table table_name db=main relgroup=btest_relgroup

    Note: Only table which have a primary key can be added here. This is a
    limitation of bucardo.

12. Add db group

     bucardo -h host_a -U bucardo add dbgroup btest_dbgroup main:source bak1:target

13. Create sync

     bucardo -h host_a -U bucardo add sync btest_sync dbgroup=btest_dbgroup relgroup=btest_relgroup conflict_strategy=bucardo_source onetimecopy=2 autokick=0

14. Start the bucardo service

     sudo bucardo -h host_a -U bucardo -P random_password start

    Note that this command requires passing the password because it uses sudo,
    and root user's .pgpass file does not have the credentials saved for bucardo
    user.

15. Run sync once

     bucardo -h host_a -U bucardo kick btest_sync 0

16. Set auto-kick on any changes

     bucardo -h host_a -U bucardo update sync btest_sync autokick=1
     bucardo -h host_a -U bucardo reload config

That's it. Now, the tables specified in step 11 will be replicated from host_a
to host_b.

I also plan to write about other alternatives I've tried soon.