The PostgreSQL application controller

The postgresql controller type manages a PostgreSQL primary and its streaming standbys. qfence’s orchestrators at a server’s site reach it over SQL, with TLS and the server’s certificate pinned, and nothing else: no SSH, OS account or agent. Nothing is installed on the server apart from a role and its grants. ADR 0013 sets out the design.

One controller is one server. A plan usually has one controller per site: the primary at the owner site and a standby at each other site. Use it to:

• read each standby’s lag, as the standby measures it from what the primary last reported, as readiness;
• make the old primary read-only the moment qfence decides to give its site up;
• promote the standby, and move a primary from one site to another without losing a committed transaction.

Stopping the server is not this type’s job. The plan’s other fences do it: the platform the server runs on (a VM stop, a container shape, a BMC), as for any application.

1. What it can do

A binding names a database on the server. db.health runs its query there. The other capabilities act on the whole server, and name a database only so that a site instance’s resource stays one value.

db.readonly is an application-level fence. It never proves that a site has stopped: the plan still needs a fence that does, for each site (§6). It is soft. A session can still run BEGIN READ WRITE until the server stops, which is why that other fence matters.

The type has no recovery capability: SQL cannot restart a server. A plan’s recovery binds the controller that runs the server, such as systemd’s unit.restart or the container platform’s.

2. What the server needs

• PostgreSQL 15, 16, 17 or 18. Version 14 is refused: it lacks the parameter grants that let qfence’s role do without superuser. A version newer than 18 works, and the connectivity test notes that it is untested.
• TLS on (ssl = on), with a certificate. qfence pins it by its SHA-256 fingerprint, so a self-signed one is fine.
• A role for qfence that is not a superuser, with the grants in §3, and a pg_hba.conf line that lets it in over TLS from the site’s orchestrators.
• For readiness, streaming replication with a WAL receiver: primary_conninfo on each standby, as pg_basebackup -R writes it.
• Recommended: wal_receiver_timeout well under max_replication_lag (§6), and wal_log_hints = on or data checksums, so an old primary can be rejoined with pg_rewind (§9).

3. Set up the server

Run this as a superuser on the primary. Grants are catalog changes, so they reach every standby:

CREATE ROLE qfence WITH LOGIN PASSWORD '…';
-- Read the WAL receiver's state and every session's.
GRANT pg_monitor TO qfence;
-- End application sessions when it fences (never a superuser's, never replication).
GRANT pg_signal_backend TO qfence;
GRANT EXECUTE ON FUNCTION pg_reload_conf() TO qfence;
GRANT EXECUTE ON FUNCTION pg_promote(boolean, integer) TO qfence;
-- The fence's setting.
GRANT ALTER SYSTEM ON PARAMETER default_transaction_read_only TO qfence;
-- Each plan's generation (§7): one parameter for every plan, and reading it back.
GRANT ALTER SYSTEM ON PARAMETER qfence.generations TO qfence;
GRANT SELECT ON pg_file_settings TO qfence;
GRANT EXECUTE ON FUNCTION pg_show_all_file_settings() TO qfence;
-- db.health's database, if PUBLIC may not connect to it.
GRANT CONNECT ON DATABASE erp TO qfence;

A role that only observes (db.health, replica.lag, databases.list) needs only pg_monitor. Validation names each grant a capability lacks, with its statement.

In pg_hba.conf, ahead of any broader line:

hostssl  all  qfence  10.20.0.0/24  scram-sha-256

Use the method cert instead for client certificates (§4). Reload the server afterwards.

On each standby, so that the lag stays fresh while the primary writes nothing:

wal_receiver_timeout = 10s

4. Store the password on the orchestrators

The password is a secret, so configuration only names it. Put it in the encrypted secrets store of every orchestrator at the controller’s site and reach, as a JSON object of the basic method’s secret fields:

jq -n --arg p "$PASSWORD" '{password: $p}' |
  qfence-orchestrator secret put -server https://orchestrator-dc1:8443 -token-file admin.token -ca-file ca.pem -ref pg-dc1 \
    -scope-type postgresql -scope-host db-dc1.example.com

-scope-type and -scope-host are the controller types and the addresses that may use the secret: the controller’s host, which allows any port of it, or db-dc1.example.com:5432, its port, which allows that port only (ADR 0016 §6). Any other controller that names it is refused it (ADR 0011 §8).

Run it against each of those orchestrators while it runs (writing a secret): the controllers that use the password pick it up at once.

The role’s name is not secret. It goes in the controller’s auth.config. For mtls, store tls_cert and tls_key (PEM) instead, and log the role in with pg_hba.conf method cert; its certificate’s CN must be the role’s name.

5. Register the controller

In the console: go to Controllers, then Application, then Register. Choose PostgreSQL, enter the host, and pick the auth method and the secret reference. The first connectivity test is refused, and shows the certificate the server presents. Compare it with the server’s own:

openssl x509 -noout -fingerprint -sha256 -in /var/lib/postgresql/data/server.crt

Then choose Pin this certificate and test again. The identity is postgres:<system identifier>@<host>:<port>. A primary and its standbys share the system identifier, which names the cluster.

In a configuration file (ADR 0001):

controllers:
  application:
    - id: pg-dc1
      type: postgresql
      site: dc1
      connection:
        host: db1.dc1.example.com
        server_certificate_sha256: "a3f1c2…"
      auth: {method: basic, credentials_ref: "local:pg-dc1", config: {username: qfence}}
      capabilities: [db.health, replica.lag, db.readonly, db.promote, databases.list]

The PG* environment variables of the orchestrator never change where it connects, how it logs in or whether it uses TLS.

Renewing the certificate. The controller’s Overview shows its pins and the certificate each orchestrator is presented, and warns 14 days before that one expires. Pin the renewed certificate as the next one before the server changes to it, then make it the pinned one.

Validation. At registration:

For each binding: same-cluster compares the cluster the controller reaches with the clusters the plan’s other sites reach with controllers of this type, and fails a site that binds another cluster. wal-receiver-timeout warns when the standby’s ping, half of wal_receiver_timeout, is more than half of the binding’s max_replication_lag, or when the timeout is 0. db.health’s binding proves CONNECT on its database.

6. Bind it to a plan

Each site’s server has its own controller (pg-dc1, pg-dc2). Each site also needs a fence that proves it stopped, which is the platform’s: here, the server’s VM. That fence must leave a standby able to stream. qfence fences every site it does not promote, so a fence that switched a standby off would stop its replication and, with it, every later failover. A VM fence suits a standby that is off while its site is fenced: it is powered on, and its readiness read, only when its site is promoted. A shape that runs the server as a standby, as the swarm lab’s erp-db does, keeps it streaming instead.

availability_plans:
  - id: erp-db
    sites: [dc1, dc2]
    fencing: {success: ANY, unfence_on_promote: true, fence_timeout_seconds: 90}
    instances:
      - id: erp-db-dc1
        site: dc1
        resource: {controller: pg-dc1, resource: erp}
        bindings:
          - {id: vm-db1, controller: pve-dc1, capability: vm.stop, resource: "101", role: fence, required: true}
          - {id: erp-ro-dc1, controller: pg-dc1, capability: db.readonly, resource: erp, role: fence}
          - {id: erp-lag-dc1, controller: pg-dc1, capability: replica.lag, resource: erp, role: readiness,
             params: {max_replication_lag: 30s}}
          - {id: erp-promote-dc1, controller: pg-dc1, capability: db.promote, resource: erp, role: switchover,
             params: {strategy: graceful, max_replication_lag: 30s, readiness_timeout: 2m}}
          - {id: erp-health-dc1, controller: pg-dc1, capability: db.health, resource: erp, role: health}
      # erp-db-dc2: the same, with pg-dc2 and its own VM.

Where the read-only fence runs. Keep db.readonly secondary (required: false, the default). A site’s secondary application fences run before its required ones, for at most fencing.secondary_application_timeout_seconds (default 10 s; ADR 0009 §2). It makes the server read-only, and ends its application sessions, while the server is still up and before the platform’s fence stops it. When the server cannot be reached, as when its site is lost, it is cut short at that limit and reads UNKNOWN, and the required fence proves the site without it.

Readiness. The lag is what a standby could lose if promoted now:

\text{lag} = (t_{\text{send}} - t_{\text{last replayed commit}}) + (t_{\text{now}} - t_{\text{receipt}})

Here send is when the primary sent its last message (by the primary’s clock), receipt is when the standby received it (by the standby’s clock), and the first term is 0 once the standby has replayed everything that message reported. Each term reads one clock, so the sites’ clocks need not agree. Once the primary is gone, the lag keeps growing from its last message. A site-loss failover therefore needs no override while that bound is within max_replication_lag; past it, promotion needs ADR 0006’s recorded acceptance of the data loss. When the standby’s WAL receiver has stopped, the lag is counted from the last reading, which each orchestrator keeps in its node state across a restart. An orchestrator with no reading cannot measure the lag, and reads the standby not ready.

A primary’s readiness. A primary has nothing to replay. It is ready, with no lag, at the site where the plan was last ACTIVE, or at any site while the plan has never been ACTIVE. That covers a plan’s first activation where its primary runs, and a retry there after a switchover that blocked. Anywhere else, a primary is an old primary not yet rejoined, or a standby promoted outside qfence. Its data may have diverged from what was served since, so it reads not ready until it is rejoined as a standby (§9).

A graceful switchover (POST /api/v1/availability-plans/erp-db/switchover, or the plan page’s Switchover):

1. makes the old primary read-only, ends its sessions, and verifies it;
2. waits, up to readiness_timeout, until the standby has replayed every byte of the old primary’s WAL. Each round verifies the demotion again and reads where the old primary’s WAL ends, then reads how far the standby has replayed. Zero lag is measured, not assumed;
3. applies the plan’s required fences, as any failover does;
4. promotes the standby through db.promote.

No committed transaction is lost. If the old primary keeps a read-write transaction open, step 2 never ends: the plan BLOCKS. A session that commits short read-write transactions between rounds can still write after the fence, until the required fence stops the server. Those writes are lost: the soft fence’s limit (§11).

fence-then-promote is a failover, run by hand: no demotion and no wait.

A server that is restarting. A fence, an unfence and a promotion each wait, within their own deadline, for a server that is starting up, shutting down, not listening yet, or behind a proxy or relay that closes the connection or does not answer while it is down; connect_timeout bounds each attempt. A promotion often follows the platform restarting the server as it unfences its site, as the swarm lab’s erp-db does twice.

Accepted data loss. A failover whose readiness an operator’s accepted loss covers (ADR 0006) tells db.promote what was accepted, so it does not refuse a lag beyond its own max_replication_lag.

7. Stale commands

Every fence, unfence and promotion carries the plan’s generation (§40). The server keeps each plan’s newest generation in its own configuration, in one parameter, qfence.generations (erp-db=7,crm=3), written with ALTER SYSTEM. That works on a standby, and it never replicates. Each command takes a session advisory lock in the controller’s database, reads the parameter from pg_file_settings, which parses the configuration files at each read, refuses a generation older than the plan’s, records a newer one, then acts, and closes its connection, which releases the lock. A command a deposed leader sent late therefore cannot undo what a newer leader did.

Give every controller of one server the same database: advisory locks are per database.

If the parameter holds anything qfence did not write, every fence, unfence and promotion through that server is refused until it is reset:

ALTER SYSTEM RESET qfence.generations;
SELECT pg_reload_conf();

A plan deleted and created again with the same id keeps its generations in raft, so the server’s records stay valid.

8. Cluster managers

Where Patroni or repmgr runs a server, qfence only observes it. A manager that promotes, restarts or reconfigures PostgreSQL on its own would undo a fence or race a promotion. db.health, replica.lag and databases.list work. db.readonly and db.promote fail their no-cluster-manager check, so they are not advertised and cannot be bound:

• declare the manager in cluster_manager, or
• validation finds a sign of one over SQL: the repmgr extension, a database named repmgr, or a session whose application_name starts with Patroni or repmgr.

Finding no sign proves nothing about a manager that left none, which is why the check is only inferred. Declare the manager. Such a site gets health and readiness from qfence, not switchover. Driving a manager’s own API would be a separate controller type.

9. Rejoining an old primary by hand

After a failover, the old primary keeps whatever it had that its standby did not, until you decide. qfence never rejoins it: rejoining discards those transactions, needs host access, and can leave the data directory unusable if it fails partway (ADR 0013 §1). Until it is rejoined, its replica.lag reads not ready, so the plan cannot fail over back to it.

First, save anything you want from the old primary, while it is read-only and stopped.

With pg_rewind (needs wal_log_hints = on or data checksums, and the new primary’s WAL back to where they diverged):

# On the old primary's host, as the postgres OS user, with the server stopped:
pg_rewind --target-pgdata="$PGDATA" \
  --source-server="host=db2.dc2.example.com user=rewind dbname=postgres sslmode=require" \
  --write-recovery-conf --progress
# --write-recovery-conf creates standby.signal and primary_conninfo.
pg_ctl -D "$PGDATA" start

Or re-seed it with a full copy, which deletes the data directory:

pg_ctl -D "$PGDATA" stop -m fast
rm -rf "$PGDATA"/*
pg_basebackup -h db2.dc2.example.com -U replicator -D "$PGDATA" -R -X stream -c fast
pg_ctl -D "$PGDATA" start

Either way, the copy is a standby once it starts, and it comes up read-only if the new primary’s configuration was copied with qfence’s fence still set. replica.lag confirms the result: return its site to standby (ADR 0009 §6) and the plan page shows it ready once it streams.

10. Troubleshooting

11. Limits

• An application fence is not a stop. db.readonly refuses new writes by default, but a session that asks for BEGIN READ WRITE can still write until the server stops. The plan’s required fence is what makes a failover safe.
• Rejoining is manual (§9).
• Finding no cluster manager is inferred (§8).
• The lag bound is optimistic by one network trip. The time a message spent between the primary and the standby is not counted.
• The generation records live on the server. A superuser can change them, as they can change anything qfence manages there.
• Tested against PostgreSQL 15 and 18 in containers, a primary and a streaming standby, by the conformance kit and the acceptance and chaos slice, on every push in CI (QFENCE_POSTGRES_IT=1 go test ./internal/controllers/application/postgresql/; the local Docker daemon, or the one DOCKER_HOST names).