patroni/debian/design.md

Integrating Patroni with Debian

Introduction

Patroni manages PostgreSQL instances and mostly expects a blank sheet, i.e prefers to initialize and insists to start and stop the database itself. Debian on the other hand includes the postgresql-common (called pg-common in the following) framework that manages concurrent major versions of PostgreSQL and possibly database instances for each of them. This document details a design on how to integrate Patroni (in form of the Debian patroni package, preferable provided by PGDG's apt repository) with Debian's pg-common framework and policies.

pg-common nomenclature and directory layout

pg-common enhances the usual libpq environment variables like PGHOST and PGDATABASE with PGCLUSTER, that is the Debian standard way of addressing a specific version/instance combination. The PGCLUSTER environment variable consists of two parts, the PostgreSQL major version and the instance name (main being the default instance name), serarated by a '/', e.g. '10/main'. The two components are usually abbreviated or referred to as '%v' and '%c', respectively.

The versioned binaries and libraries are located in /usr/lib/postgresql/%v/{bin,lib}. The configuration files usually reside in /etc/postgresql/%v/%c/ and the default log file (set via the pg_ctlcluster wrapper) is /var/log/postgresql/postgresql-%v-%c.log. The default data directory is /var/lib/postgresql/%v/%c and the listening sockets are located in /var/run/postgresql/.

Patroni configuration

The Patroni configuration is in YAML format. Different Patroni instances or installations are identified by the scope configuration option, which mostly maps to an instance name.

As Patroni is usually started via Docker or another container runtime, there is no opinioated default file system layout or even configuratin file location.

It would be desirable to create/maintain a Patroni configuration for each pg-common instance as /etc/patroni/%v-%c.yml, i.e. e.g. /etc/patroni/10-main.yml. Another possibility would be to prefix the configuration file name with patroni-.

Automatic generation of Patroni configuration

Currently, the pg-common instance specific configuration file /etc/patroni/%v-%c.yml needs to be deployed/adopted manually as the pg-common framework and pg_createcluster have no possibilty to run external programs as hooks after instance creation.

Another possibilty is to create the configuration file via an external config management program like Ansible or Puppet.

Regardless of this, a simple program that creates the instance-specific configuration from a template (e.g. /etc/patroni/patroni.yml.in) would be desirable and could be shipped in the patroni Debian package.

Debian-adopted Patroni configuration

it is possble to mimick the default Debian layout via the following configuration parameters:

data_dir = "/var/lib/postgresql/%v/%c"
bin_dir = "/usr/lib/postgresql/%v/bin"
config_dir: "/etc/postgresql/%v/%c"

The logfile location and filename as well as the socket directory have to be explicitly set via the configuration file:

unix_socket_directories = '/var/run/postgresql/'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%v-%c.log'

The postgresql.conf configuration file

Patroni expects to deploy a configuration file and to be able to change it and keep it in sync across nodes. If a configuration file postgresql.conf is already present in the config_dir, it renames it to postgresql.base.conf and includes it at the top of the postgresql.conf it writes instead.

Initialization of the first and standby instances

Patroni by default runs initdb on the data directory during bootrap while pg-common provides the pg_createcluster command for this purpose.

It is possible to tell Patroni to run an external bootstrap command which is passed the --scope and --datadir command-line options. This makes it possible to have a small wrapper script like the following that runs pg_createcluster instead:

#!/bin/sh

for i in "$@"
do
case $i in
    --scope=*)
    SCOPE="${i#*=}"
    shift # past argument=value
    ;;
    --datadir=*)
    DATADIR="${i#*=}"
    shift # past argument=value
    ;;
    *)
       # unknown option
    ;;
esac
done

VERSION=$(echo $SCOPE | sed -e 's/\/.*//')
CLUSTER=$(echo $SCOPE | sed -e 's/.*\///')

pg_createcluster $VERSION $CLUSTER
exit $?

This requires the following in the Patroni YAML configuratio:

bootstrap:
  # Custom bootstrap method
  method: pg_createcluster
  pg_createcluster:
    command: <custom script above>

Subsequent nodes are boostrapped from the primary via base backups. Just running pg_createcluster on them is not possible as then the cluster IDs (the 'Database system identifier' in the pg_controldata output) would differ.

Again, it is possible to provide an external clone program, which can run pg_basebackup itself. The pg-common framework currently does not provide for this, but it is possible to run pg_basebackup after pg_createcluster (which creates the configuration directory) and purging the data directory:

#!/bin/sh

for i in "$@"
do
case $i in
    --scope=*)
    SCOPE="${i#*=}"
    shift # past argument=value
    ;;
    --role=*)
    ROLE="${i#*=}"
    shift # past argument=value
    ;;
    --datadir=*)
    DATADIR="${i#*=}"
    shift # past argument=value
    ;;
    --connstring=*)
    CONNSTR="${i#*=}"
    shift # past argument=value
    ;;
    *)
       # unknown option
    ;;
esac
done

VERSION=$(echo $SCOPE | sed -e 's/\/.*//')
CLUSTER=$(echo $SCOPE | sed -e 's/.*\///')

if [ -f /etc/postgresql/$VERSION/$CLUSTER/postgresql.conf ]
then
    pg_dropcluster $VERSION $CLUSTER
fi

pg_createcluster $VERSION $CLUSTER && rm -rf $DATADIR && pg_basebackup --pgdata $DATADIR -X stream --dbname=$CONNSTR
exit $?

Those two scripts could be shipped in the patroni Debian package e.g. as /usr/share/patroni/pg_createcluster_patroni and /usr/share/patroni/pg_clonecluster_patroni.

Systemd services

The Patroni daemon/agent needs to be started for each configuration file in /etc/patroni, i.e. a systemd service unit is required for each. This can be facilitated via the patroni@.service that acts as a wild card and could have the following pg-common specific content:

[Unit]
ConditionPathExists=/etc/patroni/%i.yml

[Service]
ExecStart=/usr/bin/patroni /etc/patroni/%i.yml

This makes it possible to e.g. start Patroni for the '10/main' instance with systemctl start patroni@10/main.

DCS Debian availability

The only DCS server available in Debian 9 (stretch) is zookeeper. Both etcd and consul were blacklisted before release by the Debian security team due to being written in Go.

Current testing (buster) also includes etcd and consul.

The latest Patroni Debian package is available for both Debian stable and testing/unstable via the PGDG APT repository.

DCS setup

One outstanding issue with providing a general Patroni configuration template and possibly a program that creates a Patroni configuration file per pg-common instance is the distributed consensus store, which also needs to be configured in the Patroni YAML file.

One possibility would be to introduce a file /etc/default/patroni where the DCS and it's ip-address(es) could be configured which could be sourced by the configuration file generator.

Security

The proposed/current setup should be considered a Proof of Concept. In particular, care must be taken that the postgres db/system user password is not leaked and that the DCS access is secured/hardened.

Making patronictl pg-common aware

The patronictl CLI currently looks for a configuration file ~/.config/patronictl.yml. This could potentially be a symlink to the configuration under /etc/patroni/. In the pg-common scope, it could addtionally either display all pg-common instances and/or accept the '%v/%c' instance notation in order to select a specific instance to display or act on.

Current status

As of the postgresql-common_194 and patroni_1.5.0-9 packages, the following steps are still needed for each pg-common instance %v/%c:

1. Setup DCS and possibly install its client python packages
2. Install postgresql-common package
3. Disable automatic instance creation in /etc/postgresql-common/createcluster.conf
4. Install postgresql-%v package
5. Setup authentication via pgpass or otherwise
6. Create a /etc/patroni/%v-%c.yml configuration file
7. Start the patroni systemd service for the instance

In particular, no changes are required to the Patroni or postgresql-common upstream codebases.