I recently updated my Install docker and docker-compose using ansible post and wanted to test it against multiple target OSes and OS versions. Here's a way I found to do it easily using Vagrant.

Here's the Vagrantfile:

# -*- mode: ruby -*-
# vi: set ft=ruby :

targets = [
  "debian/bookworm64",
  "debian/bullseye64",
  "debian/buster64",
  "ubuntu/jammy64",
  "ubuntu/bionic64",
  "ubuntu/focal64"
]

Vagrant.configure("2") do |config|
  targets.each_with_index do |target, index|
    config.vm.define "machine#{index}" do |machine|
      machine.vm.hostname = "machine#{index}"
      machine.vm.box = target
      machine.vm.synced_folder ".", "/vagrant", disabled: true

      if index == targets.count - 1
        machine.vm.provision "ansible" do |ansible|
          ansible.playbook = "playbook.yml"
          ansible.limit = "all"
          ansible.compatibility_mode = "2.0"
          # ansible.verbose = "v"
        end
      end
    end
  end
end

The targets variable defines what Vagrant boxes to target. The possible list of boxes can be found here: https://app.vagrantup.com/boxes/search

In the Vagrant.configure section, I've defined a machine with an auto-generated machine ID for each target.

The machine.vm.synced_folder line disables the default vagrant share to keep things fast.

Then, I've run the ansible provisioning once at the end instead of for each box separately (from: https://developer.hashicorp.com/vagrant/docs/provisioning/ansible#tips-and-tricks).

The test can be run using:

$ vagrant up

If the boxes are already up, to re-run provisioning, run:

$ vagrant provision

This code can also be found on GitHub: https://github.com/srijan/ansible-install-docker

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.

Updated for 2023: I've updated this post with the following changes:

1. Added a top-level sample playbook
2. Used ansible apt_module's cache_time parameter to prevent repeated apt-get updates
3. Install docker-compose-plugin using apt (provides docker compose v2)
4. Make installing docker compose v1 optional
5. Various fixes as suggested in comments
6. Tested against Debian 10,11,12 and Ubuntu 18.04 (bionic), 20.04 (focal), 22.04 (jammy) using Vagrant.

I've published a new post on how I've done this testing.

I wanted a simple, but optimal (and fast) way to install docker and docker-compose using Ansible. I found a few ways online, but I was not satisfied.

My requirements were:

• Support Debian and Ubuntu
• Install docker and docker compose v2 using apt repositories
• Prevent unnecessary apt-get update if it has been run recently (to make it fast)
• Optionally install docker compose v1 by downloading from github releases
  • But, don’t download if current version >= the minimum version required

I feel trying to achieve these requirements gave me a very good idea of how powerful ansible can be.

The final role and vars files can be seen in this gist. But, I’ll go through each section below to explain what makes this better / faster.

File structure

playbook.yml
roles/
├── docker/
│    ├── defaults/
│    │   ├── main.yml
│    ├── tasks/
│    │   ├── main.yml
│    │   ├── docker_setup.yml

Playbook

This is the top-level playbook. Any default vars mentioned below can be overridden here.

---
- hosts: all
  vars:
    - docker_compose_install_v1: true
    - docker_compose_version_v1: "1.29.2"
  tasks:
    - name: Docker setup
      block:
        - import_role: name=docker

Variables

First, we’ve defined some variables in defaults/main.yml. These will control which release channel of docker will be used and whether to install docker compose v1.

---
docker_apt_release_channel: stable
docker_apt_arch: amd64
docker_apt_repository: "deb [arch={{ docker_apt_arch }}] https://download.docker.com/linux/{{ ansible_distribution | lower }} {{ ansible_distribution_release }} {{ docker_apt_release_channel }}"
docker_apt_gpg_key: https://download.docker.com/linux/{{ ansible_distribution | lower }}/gpg
docker_compose_install_v1: false
docker_compose_version_v1: "1.29.2"

Role main.yml

The tasks/main.yml file imports tasks from tasks/docker_setup.yml and turns on become for the whole task.

---
- import_tasks: docker_setup.yml
  become: true

Docker Setup

This task is divided into the following sections:

Install dependencies

- name: Install packages using apt
  apt:
    name: 
        - apt-transport-https
        - ca-certificates
        - curl
        - gnupg2
        - software-properties-common
    state: present
    cache_valid_time: 86400

Here the state: present makes sure that these packages are only installed if not already installed. I've set cache_valid_time to 1 day so that apt-get update is not run if it has already run recently.

Add docker repository

- name: Add Docker GPG apt Key
  apt_key:
    url: "{{ docker_apt_gpg_key }}"
    state: present

- name: Add Docker Repository
  apt_repository:
    repo: "{{ docker_apt_repository }}"
    state: present
    update_cache: true

Here, the state: present and update_cache: true make sure that the cache is only updated if this state was changed. So, apt-get update is not run if the docker repo is already present.

Install and enable docker and docker compose v2

- name: Install docker-ce
  apt:
    name: docker-ce
    state: present
    cache_valid_time: 86400

- name: Run and enable docker
  service:
    name: docker
    state: started
    enabled: true

- name: Install docker compose
  apt:
    name: docker-compose-plugin
    state: present
    cache_valid_time: 86400

Again, due to state: present and cache_valid_time: 86400, there are no extra cache fetches if docker and docker-compose-plugin are already installed.

Docker Compose V1 Setup

WARNING: docker-compose v1 is end-of-life, please keep that in mind and only install/use it if absolutely required.

This task is wrapped in an ansible block that checks if docker_compose_install_v1 is true.

- name: Install docker-compose v1
  when:
    - docker_compose_install_v1 is defined
    - docker_compose_install_v1
  block:

Inside the block, there are two sections:

Check if docker-compose is installed and it’s version

- name: Check current docker-compose version
  command: docker-compose --version
  register: docker_compose_vsn
  changed_when: false
  failed_when: false
  check_mode: no

- set_fact:
    docker_compose_current_version: "{{ docker_compose_vsn.stdout | regex_search('(\\d+(\\.\\d+)+)') }}"
  when:
    - docker_compose_vsn.stdout is defined

The first block saves the output of docker-compose --version into a variable docker_compose_vsn. The failed_when: false ensures that this does not call a failure even if the command fails to execute. (See error handling in ansible).

Sample output when docker-compose is installed: docker-compose version 1.26.0, build d4451659

The second block parses this output and extracts the version number using a regex (see ansible filters). There is a when condition which causes the second block to skip execution if the first block failed (See playbook conditionals).

Install or upgrade docker-compose if required

- name: Install or upgrade docker-compose
  get_url: 
    url : "https://github.com/docker/compose/releases/download/{{ docker_compose_version }}/docker-compose-Linux-x86_64"
    dest: /usr/local/bin/docker-compose
    mode: 'a+x'
    force: yes
  when: >
    docker_compose_current_version == ""
    or docker_compose_current_version is version(docker_compose_version, '<')

This just downloads the required docker-compose binary and saves it to /usr/local/bin/docker-compose, but it has a conditional that this will only be done if either docker-compose is not already installed, or if the installed version is less than the required version. To do version comparison, it uses ansible’s built-in version comparison function.

So, we used a few ansible features to achieve what we wanted. I’m sure there are a lot of other things we can do to make this even better and more fool-proof. Maybe a post for another day.