PGBouncer enhancement

This PR is for the addition of a PGBouncer connection pooler to the Zalando Postgres Operator. It creates a new PGBouncer deployment, service and configmap. PGBouncer then serves as the cluster's frontend and forwards queries to the master Postgres service.

This task was one of the milestones for the Postgres Operator's 1.4 release. Before I was able to submit this work to the project, the Zalando team had already implemented this feature themselves.

While this implementation performed robustly and correctly, it regrettably isn't as polished as I'd like to make it. I leave it here for posterity.

Design choices

One goal of this work was to contribute it upstream. As such it needed to both be useful to my specific use case and environment, but generic enough to be easily used by others.

PGBouncer is configurable per cluster

The PGBouncer config is done in the Postgres YAML manifest, not in the configuration for the operator itself. This supports the use case where you may have one operator deployment which manages multiple Postgres clusters and not all of them want a PGBouncer. It adds more flexibility.

Bring your own PGBouncer image - or use Spilo

You can specify a custom PGBouncer container image. The defaults are to use the Spilo image itself, so that the project remains self-contained for simplicity. If you prefer a smaller, more specialized image for PGBouncer it is simple to configure.

PGBouncer config with cluster config

Arbitrary config directives can be passed to PGBouncer via the Postgres cluster manifest. This makes the manifest the single place to configure the Postgres cluster and PGBouncer. The config is broken into three peices that correspond to the sections in the pgbouncer.ini file. This eliminates the need to create and maintain a map of all possible PGBouncer directives. The pgbouncer.ini file is assembled from these Postgres manifest parameters and combined with dynamic content (the name of the Postgres cluster's master service). This config and the userlist file are injected into the PGBouncer container as a volume mount.

Postgres superuser access

PGBouncer is configured to access Postgres via the superuser account to perform user authentication. This was quick to implement but it isn't very secure. The superuser should be used as little as possible. I had future plans to use a Security Definer function to look up user credentials so an unpriveleged Postgres user can be used. This is exactly what the Zalando implementation does.

Configuration

In the Postgres cluster manifest, there is now a new section called pgbouncer. These parameters are grouped directly under the spec key in the manifest.

image Optional PGBouncer container image path

pgbouncerIniSection The [pgbouncer] section of the pgbouncer.ini file usersIniSection The [users] section of the pgbouncer.ini file databasesIniSection The [databases] section of the pgbouncer.ini file

Example

spec:
  pgbouncer:
    image: pgbouncer/pgbouncer:1.12.0
    pgbouncerIniSection: |
      min_pool_size = 50
    usersIniSection: |
      # Existing users
      karl md5f47636673b14c54021a69dc06f6a19fb
    databasesIniSection: |
      # Our old DB
      legacydb = host=legacy.example.com port=5432

Thank you and kudos to the Zalando team and all the contributors for making such a great project!

For reference, the official/merged PR for the PGBouncer enhancement is PR #799

-Derek

---

Postgres Operator

The Postgres Operator enables highly-available PostgreSQL clusters on Kubernetes (K8s) powered by Patroni. It is configured only through manifests to ease integration into automated CI/CD pipelines with no access to Kubernetes directly.

Operator features

• Rolling updates on Postgres cluster changes
• Volume resize without Pod restarts
• Cloning Postgres clusters
• Logical Backups to S3 Bucket
• Standby cluster from S3 WAL archive
• Configurable for non-cloud environments
• UI to create and edit Postgres cluster manifests

PostgreSQL features

• Supports PostgreSQL 9.6+
• Streaming replication cluster via Patroni
• Point-In-Time-Recovery with pg_basebackup / WAL-E via Spilo
• Preload libraries: bg_mon, pg_stat_statements, pgextwlist, pg_auth_mon
• Incl. popular Postgres extensions such as decoderbufs, hypopg, pg_cron, pg_partman, pg_stat_kcache, pgq, plpgsql_check, postgis, set_user and timescaledb

The Postgres Operator has been developed at Zalando and is being used in production for over two years.

Getting started

For a quick first impression follow the instructions of this tutorial.

Documentation

There is a browser-friendly version of this documentation at postgres-operator.readthedocs.io

• How it works
• Installation
• The Postgres experience on K8s
• The Postgres Operator UI
• DBA options - from RBAC to backup
• Build, debug and extend the operator
• Configuration options
• Postgres manifest reference
• Command-line options and environment variables

Google Summer of Code

The Postgres Operator made it to the Google Summer of Code 2019! Check our ideas and start discussions in the issue tracker.

Community

There are two places to get in touch with the community:

1. The GitHub issue tracker
2. The #postgres-operator slack channel