the pgpool postgres proxy and load balancer

This chart uses the PGPool-II and an enormous amount of too-clever-by-half scripting to provide automatic load balancing across a Google CloudSQL Postgres database and one or more read replicas in the same region.

The primary moving parts are:

• PGPool itself. This runs in the pod's primary container, launched from the pgpool.sh wrapper script

• A discovery script that runs in a separate container. Once a minute, this script:

  • Uses the gcloud cli to list any primary dbs that match the PRIMARY_INSTANCE_PREFIX env variable as a prefix. (i.e. a prefix of metadata- matches metadata-4093504851)
  • Lists all currently active read replicas of the primary database
  • Uses the envtpl tool to fill out pgpool.conf.tmpl and copy the result into /etc/pgpool/pgpool.conf if it differs from what is already there.
  • If replicas have been added or removed, it runs pcp_reload_config which forces the pgpool process to re-read its configuration file.
  • If replicas have been added, it runs pcp_attach_node to tell pgpool to begin routing traffic to it

• The pgpool prometheus exporter, which exposes a Prometheus-compatible /metrics HTTP endpoint with statistics gleaned from PGPool's internal stats commands.

• The telegraf monitoring agent, configured to forward the metrics exposed by the exporter to Google Cloud Monitoring, formerly known as Stackdriver. (This portion is optional; if you have an existing apparatus for scraping prometheus metrics, you can point it directly at the exporter.)

In general you should expect that if you add a new replica, it should start taking 1/Nth of the select query load (where N is the number of replicas) within 5 to 15 minutes (beware of stackdriver reporting lag!).

By default as we've configured it, pgpool will send all mutating statements (UPDATE/INSERT/DELETE and all statements including SELECT inside a transaction with a mutating statement) to the primary instance, and load balance (on a per-statement level) all SELECTs evenly across the available read replicas.

The exception is that any statement containing the string DO NOT LOAD BALANCE (inside a comment is okay and in fact expected) will be routed to the primary instance no matter what. This is configureable at deploy time as .pgpool.primaryRoutingQueryPatternList in values.yaml.

Upgrade Guides

Installing the Chart

Step One: add our Helm repository to your client:

helm repo add pgpool-cloudsql https://odenio.github.io/pgpool-cloudsql && \
helm repo update

Step Two: install the chart

export RELEASE_NAME=my-pgpool-service # a name (you will need 1 installed chart for each primary DB)
export NAMESPACE=my-k8s-namespace     # a kubernetes namespace
export CHART_VERSION=1.1.4           # a chart version: https://github.com/odenio/pgpool-cloudsql/releases
export VALUES_FILE=./my_values.yaml   # your values file

helm install \
  "${RELEASE_NAME}" \
  pgpool-cloudsql/pgpool-cloudsql \
  --atomic \
  --timeout=5m0s \
  --namespace "${NAMESPACE}" \
  --version "${CHART_VERSION}" \
  --values "${VALUES_FILE}"

Please note that there are several Helm values that you are required to fill out; a bare-minimum local my_values.yaml would look like this:

discovery:
  primaryInstancePrefix: my-cloudsql-instance-name-prefix
pgpool:
  srCheckUsername: <postgres_username>
  srCheckPassword: <postgres_password>
  healthCheckUsername: <postgres_username>
  healthCheckPassword: <postgres_password>
exporter:
  postgresUsername: <postgres_username>
  postgresPassword: <postgres_password>

See below for the full list of possible values:

Chart Values

Kubernetes deployment options

PCP options

Discovery options

pgpool2_exporter options

telegraf options

pgpool options

Monitoring with Google Cloud Monitoring (AKA Stackdriver)

If the telegraf container is enabled, pgpool-cloudsql exports the following Google Cloud Monitoring metricDescriptors with the gke_container resource type and all resource labels automatically filled in.

An example Stackdriver dashboard definition can be found in monitoring/dashboard.json.

The full list of metricDescriptor definitions is in monitoring/metrics.json.

Monitoring with Prometheus directly

Using telegraf to forward prometheus metrics to Google Cloud Monitoring/Stackdriver is optional: the pgpool2_exporter container exposes a prometheus-style /metrics endpoint on port 9090/tcp (and named as the metrics port in the pod port configuration), and if you have an existing local Prometheus infrastructure or if you are using the Google Managed Service for Prometheus, you can scrape and ingest those metrics directly.

The details of this will be specific to your local Prometheus setup, but traditionally prometheus-on-kubernetes collection agents (whether the native prometheus one or the Opentelemetry Collector) use Kubernetes annotations to configure their scrape targets. To add annotations to your pods, use the .deploy.annotations value in your Helm values.yaml, for example:

deploy:
  annotations:
    prometheus.io/scrape: enabled
    prometheus.io/path: /metrics
    prometheus.io/port: metrics

background info: maybe the real friends were all the yaks we shaved along the way

A billion fussy details stood in between us and the simple-seeming state of affairs described above, and it's possible you may have to debug them in anger, so here are a few of them:

inter-container authentication

The updater container uses the pcp cli commands to talk to pgpool over its tcp control port (9898/tcp). The PCP protocol requires password auth; this is set up in the pcp.conf file that is in the shared volume mounted as /etc/pgpool in both containers. A random PCP password is set up at runtime if you do not set .pcp.password in values.yaml.

If you need to run any of the pcp_* commands yourself for interactive debugging, you will need to use the -h localhost -w flags. The -w flag forces the commands to use the /root/.pcppass file as the auth source so that the updater script (and you) can issue pcp commands without typing the password.

Note: the pcp.conf file is generated at runtime from the template.

config reloads don't do what you think

Adding a new replica to pgpool.conf and running pcp_reload_config does not commence sending traffic to that node! It only makes pgpool aware that the node exists -- you have to then run the pcp_attach_node command to tell pgpool that the node is able to receive traffic. The discovery script does this for you automatically.

node ID to hostname/ip bindings must be stable

Pgpool gets extremely unhappy if the hostname or IP address of a node changes out from underneath it; we've addressed this by adding an extremely sleazy persistence model: for every IP address we observe, we create a new node ID, and node IDs are never recycled for the lifetime of the pod.

The pgpool documentation is silent on the question of how many backend nodes it is capable of monitoring: if you delete and then re-create a large number of replicas for some reason, it might be advisable to slowly roll the pgpool pods to make sure they start from scratch.

Oh god, Stackdriver

Getting useful stackdriver metrics out of pgpool was easily 60% of the process of pulling this project together. By default, the only statistics interface that pgpool offers is a series of "SQL-like" commands that are run inside a psql session. Importantly, the tables returned by these commands are not actual tables and cannot be queried with SELECT or JOIN. (You can add the ability to do that with the pgpool_adm extension but of course there is no way to install that on a CloudSQL instance.)

But there was some good news: the pgpool2_exporter is a standalone binary that scrapes and parses the data returned by the "sql-like" commands and exports it as a prometheus-compatible /metrics endpoint.

Oh god, Telegraf

Lastly, it's worth calling out the telegraf config file; in order to correctly fill in the required attributes of a stackdriver gke_container resource, we run telegraf under a wrapper script that queries the GCP instance metadata API in order to fill out the relevant environment variables.