PgDoorman

A multi-threaded PostgreSQL connection pooler written in Rust. Drop-in replacement for PgBouncer and Odyssey, and an alternative to PgCat. Three years in production at Ozon under Go (pgx), .NET (Npgsql), Python (asyncpg, SQLAlchemy), and Node.js workloads.

Get PgDoorman 3.11.0 · Comparison · Benchmarks

Headline features

Why PgDoorman

• Caches Parse on hot query paths. Prepared backend state is reused between clients sharing a pool, including the anonymous Parse most drivers send for short parameterised queries. That cuts PostgreSQL planner CPU on repeated OLTP queries; SHOW INTERNER shows query-text memory, while Prometheus metrics show cache hits, misses, and evictions.
• Multi-threaded, single shared pool. All worker threads share one pool. PgBouncer is single-threaded; the recommended scale-out — several instances behind so_reuseport — gives each instance its own pool, and idle counts can drift between processes for the same database.
• Thundering herd suppression. When 200 clients race for 4 idle connections, PgDoorman caps concurrent backend creates (scaling_max_parallel_creates) and routes returning servers straight to the longest-waiting client through an in-process oneshot channel — no requeue through the idle pool.
• Bounded tail latency. Waiters are served strict FIFO so the worst-case wait can't be overtaken by latecomers. Pre-replacement of expiring backends — at 95% of server_lifetime, up to 3 in parallel — keeps the pool warm, so there is no checkout spike when a generation of connections rotates out.
• Dead backend detection inside transactions. If the backend dies mid-transaction (failover, OOM, network partition), PgDoorman returns SQLSTATE 08006 immediately by racing the client read against backend readability with a 100 ms tick. Without this, the client would block until TCP keepalive fires — on Linux defaults that is about two hours plus 9×75 s probes.
• Built for operations. YAML or TOML config with human-readable durations (30s, 5m). pg_doorman generate --host … introspects an existing PostgreSQL and emits a starter config. pg_doorman -t validates the config without starting the server. A Prometheus /metrics endpoint is built-in.

Comparison

Full feature matrix →

Benchmarks

AWS Fargate (16 vCPU), pool size 40, pgbench 30 s per test:

Full results →

Quick start

Install via your distro package manager:

# Ubuntu / Debian
sudo add-apt-repository ppa:vadv/pg-doorman
sudo apt update
sudo apt install pg-doorman

# Fedora / RHEL family
sudo dnf copr enable @pg-doorman/pg-doorman
sudo dnf install pg_doorman

Distro packages and the Docker image are built without the tls-migration and pam features. See Installation for the TLS feature matrix and how to build with them.

Or run via Docker:

docker run -p 6432:6432 \
  -v $(pwd)/pg_doorman.yaml:/etc/pg_doorman/pg_doorman.yaml \
  ghcr.io/ozontech/pg_doorman \
  pg_doorman /etc/pg_doorman/pg_doorman.yaml

Minimal config (pg_doorman.yaml):

general:
  host: "0.0.0.0"
  port: 6432
  admin_username: "admin"
  admin_password: "change_me"

pools:
  mydb:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    users:
      - username: "app"
        password: "md5..."   # hash from pg_shadow / pg_authid
        pool_size: 40

server_username and server_password are omitted on purpose: PgDoorman re-uses the client's MD5 hash or SCRAM ClientKey to authenticate against PostgreSQL. No plaintext passwords in the config.

Installation guide → · Configuration reference →

Where to next

• New to PgDoorman? Start with Overview, then Installation and Basic usage.
• Migrating from PgBouncer or Odyssey? Read Comparison and Authentication.
• Running Patroni? See Patroni-assisted fallback and patroni_proxy.
• Production sizing? Read Pool pressure and Pool Coordinator.
• Operating PgDoorman? See Binary upgrade, Signals, Troubleshooting.

PgDoorman vs PgBouncer vs Odyssey

Side-by-side feature matrix for choosing a PostgreSQL connection pooler. Every PgBouncer claim is anchored to its config reference and changelog; every Odyssey claim is anchored to the project's docs.

PgCat is intentionally omitted: its design centre is sharding/load-balancing rather than drop-in replacement of PgBouncer, so a row-by-row comparison is misleading. See the PgCat repo if you need horizontal sharding.

For benchmark numbers, see Benchmarks.

Authentication

See Authentication.

TLS

See TLS.

Routing and high availability

See Patroni-assisted fallback, patroni_proxy.

Pooling

See Pool Coordinator, Pool pressure.

Limits and timeouts

See General settings reference, Pool settings reference.

Observability

See Prometheus metrics reference, Admin commands.

Operations

See Binary upgrade, Signals.

Protocol

When PgDoorman is not the right fit

• You need LDAP authentication. Use Odyssey or PgBouncer 1.25+.
• You need replication passthrough for logical replication tools. Use PgBouncer 1.23+.
• You need transaction_timeout enforced by the pooler. Use PgBouncer 1.25+.
• You need horizontal sharding inside the pooler. Use PgCat.

For prepared statements in transaction mode, Patroni HA without external proxies, multi-threaded throughput in a single shared pool, and binary upgrades that migrate live sessions, PgDoorman is the closer fit.

Overview

What PgDoorman does

PgDoorman sits between your applications and PostgreSQL. To the application it looks like a PostgreSQL server (same wire protocol, same psql connect string); under the hood it multiplexes many client sessions onto a much smaller set of real backend connections.

graph LR
    App1[Application A] --> Pooler(PgDoorman)
    App2[Application B] --> Pooler
    App3[Application C] --> Pooler
    Pooler --> DB[(PostgreSQL)]

PgDoorman was originally forked from PgCat but has since been rewritten around different goals: prepared statements in transaction mode, multi-threaded shared pools, Patroni integration, and binary upgrades that migrate live sessions. It is now a separate codebase.

Why a pooler at all

Each PostgreSQL connection costs the server roughly 10 MB of RAM, a process, and time on every handshake (auth, SCRAM, search_path resolution). Without a pooler, an application that opens N short-lived connections per second pays N×handshake-time. A pooler lets the same N clients reuse a small set of long-lived backend connections, so the handshake cost is paid once per backend instead of once per client.

Concrete impact:

• A pool_size of 40 typically serves several thousand client sessions for short OLTP transactions.
• PostgreSQL avoids the per-process memory overhead of the connections it would otherwise have to keep open.
• Failover, restart, or rolling deployments don't translate into a thundering herd of fresh handshakes.

Pool modes

PgDoorman does not implement statement mode. See Pool Modes for the exact contract of each mode and what works in transaction mode that doesn't work in other poolers.

Operations surface

• Admin console — a PostgreSQL-compatible endpoint for SHOW POOLS, SHOW CLIENTS, RELOAD, PAUSE, UPGRADE, etc.
• Prometheus /metrics — built-in HTTP endpoint with per-pool latency percentiles, prepared-statement counters, fallback state, and TLS metrics.
• Prepared-statement cache visibility — SHOW INTERNER and SHOW POOLS_MEMORY expose interner footprint and per-client Named / Anonymous counts, with matching Prometheus gauges.
• pg_doorman -t — validate the config without starting the server.
• pg_doorman generate --host … — emit a starter config by introspecting an existing PostgreSQL.

See Admin commands and Prometheus reference.

Where to go next

• Installation — install pg_doorman from packages, source, or Docker.
• Basic usage — minimal config, first connection, common gotchas.
• Pool Coordinator — when one database is shared between several user-pools.
• Binary upgrade — replace the binary in production without dropping live sessions.

Installing PgDoorman

PgDoorman runs on Linux and macOS. The recommended path for production is to build from source against the Rust toolchain you control. Pre-built distribution packages and binaries are also available; Docker is intended for testing.

System requirements

• Linux (recommended) or macOS
• PostgreSQL 10 or newer (any supported version)
• Memory budget proportional to pool size (a few MB per pool plus prepared statement cache)
• Rust 1.87 or newer if building from source

Build from source (recommended)

Build against your own toolchain so you control compiler version, target platform, and dependencies:

git clone https://github.com/ozontech/pg_doorman.git
cd pg_doorman
cargo build --release
sudo install -m 0755 target/release/pg_doorman /usr/local/bin/pg_doorman

cargo build --release produces an optimized binary at target/release/pg_doorman. Build prerequisites and the development workflow are in Contributing.

Cargo features

Building with TLS client migration

By default, TLS clients cannot migrate to the new process during binary upgrade — they disconnect with 58006 and reconnect. Enable seamless migration with the tls-migration feature:

cargo build --release --features tls-migration

This compiles a vendored OpenSSL 3.5.5 with a custom patch that exports and re-imports TLS cipher state (keys, IVs, sequence numbers, TLS 1.3 traffic secrets) across the binary handover. Encrypted clients keep the same TCP connection without re-handshaking.

Requirements:

• Linux only (macOS and Windows use platform-native TLS, not OpenSSL).
• perl and patch utilities in PATH.
• Roughly 5 minutes of additional build time for OpenSSL compilation.

Offline / air-gapped builds:

curl -fLO https://github.com/openssl/openssl/releases/download/openssl-3.5.5/openssl-3.5.5.tar.gz
OPENSSL_SOURCE_TARBALL=$(pwd)/openssl-3.5.5.tar.gz \
  cargo build --release --features tls-migration

Both the old and the new process must use identical tls_certificate and tls_private_key files. For the full upgrade flow, monitoring, and troubleshooting, see Binary Upgrade → TLS migration.

For deb/rpm packaging see debian/ and pkg/ in the repository.

Distribution packages

Pre-built deb and rpm packages are published from the same release tags. Use these when you cannot or do not want to build from source.

Ubuntu / Debian (PPA)

sudo add-apt-repository ppa:vadv/pg-doorman
sudo apt update
sudo apt install pg-doorman

Supported releases: jammy (22.04 LTS), noble (24.04 LTS), questing (25.10), resolute (26.04 LTS).

Fedora / RHEL / CentOS / Rocky / AlmaLinux (COPR)

sudo dnf copr enable @pg-doorman/pg-doorman
sudo dnf install pg_doorman

Supported targets: Fedora 39, 40, 41; EPEL 8 and 9 for RHEL-family distributions.

The systemd unit, default config layout, and pg_doorman user are set up by the package.

Pre-built binaries from GitHub Releases

If neither building from source nor distribution packages fit, download a static binary from the releases page:

# Replace VERSION and TARGET with the desired values from the releases page.
curl -L -o pg_doorman \
  "https://github.com/ozontech/pg_doorman/releases/download/VERSION/pg_doorman-TARGET"
curl -L -o pg_doorman.sha256 \
  "https://github.com/ozontech/pg_doorman/releases/download/VERSION/pg_doorman-TARGET.sha256"
sha256sum -c pg_doorman.sha256                    # must print "OK"
chmod +x pg_doorman
sudo mv pg_doorman /usr/local/bin/

Skipping the checksum step means trusting the network path between you and objects.githubusercontent.com. Don't.

Docker (testing only)

Docker is supported for development, CI, and quick demos. We do not recommend it for production — packaging and lifecycle management are simpler with the system packages above.

docker run -p 6432:6432 \
  -v $(pwd)/pg_doorman.yaml:/etc/pg_doorman/pg_doorman.yaml \
  ghcr.io/ozontech/pg_doorman \
  pg_doorman /etc/pg_doorman/pg_doorman.yaml

The image's default CMD runs pg_doorman without arguments. With WORKDIR /etc/pg_doorman, that means /etc/pg_doorman/pg_doorman.toml. If you mount a YAML config, pass the path explicitly as shown above.

Publish 6432 for PostgreSQL protocol traffic. If your config enables web.enabled, also publish 9127 for /metrics and the web console; without that flag the listener is not started. The config path can be passed as a positional argument or through CONFIG_FILE; the image also accepts LOG_LEVEL (default info), LOG_FORMAT (text, structured, or debug), and NO_COLOR.

The Dockerfile sets STOPSIGNAL SIGTERM, so docker stop sends pg_doorman the normal container stop signal. Do not use SIGINT to stop the container: outside a TTY, that signal starts binary upgrade, which normally exits PID 1 in a container run.

The public image is built without the tls-migration and pam features. Regular TLS for client and backend connections does not depend on tls-migration; that feature is only needed to migrate TLS sessions across a binary upgrade. For TLS migration or PAM, build your own image from the public Dockerfile and add --features tls-migration and/or pam to the cargo build --release step.

A docker-compose.yaml with a sidecar PostgreSQL is in example/ for end-to-end smoke tests.

Verifying the installation

pg_doorman --version
pg_doorman -t /etc/pg_doorman/pg_doorman.yaml   # validates config
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c "SHOW VERSION;"

pg_doorman -t validates the config file before deploy — PgBouncer and Odyssey lack this.

Where to next

• Basic Usage — first config, admin console, monitoring.
• Authentication — pick the right auth method.
• Operations — signals, reload, systemd integration.
• Binary Upgrade — replacing the binary without dropping clients.

PgDoorman Basic Usage Guide

End-to-end walkthrough: command-line flags, minimal config, the admin console, and operational commands (PAUSE, RESUME, RECONNECT, RELOAD). Intended as the second page you read after Overview and Installation.

Command-line options

$ pg_doorman --help

PgDoorman: Nextgen PostgreSQL Pooler (based on PgCat)

Usage: pg_doorman [OPTIONS] [CONFIG_FILE] [COMMAND]

Commands:
  generate  Generate configuration for pg_doorman by connecting to PostgreSQL and auto-detecting databases and users
  help      Print this message or the help of the given subcommand(s)

Arguments:
  [CONFIG_FILE]  [env: CONFIG_FILE=] [default: pg_doorman.toml]

Options:
  -l, --log-level <LOG_LEVEL>    [env: LOG_LEVEL=] [default: INFO]
  -F, --log-format <LOG_FORMAT>  [env: LOG_FORMAT=] [default: text] [possible values: text, structured, debug]
  -n, --no-color                 force colors off in the log output
  -d, --daemon                   run as daemon [env: DAEMON=]
  -h, --help                     Print help
  -V, --version                  Print version

Available Options

Setup and Configuration

Configuration File Structure

PgDoorman supports both YAML and TOML configuration formats. YAML is recommended for new setups. The configuration is organized into several sections:

general:        # Global settings for the PgDoorman service
pools:
  <name>:       # Settings for a specific database pool
    users:
      - ...     # User settings for this pool

Minimal Configuration Example

Here's a minimal configuration example to get you started:

YAML (recommended)

general:
  host: "0.0.0.0"         # Listen on all interfaces
  port: 6432               # Port for client connections
  admin_username: "admin"
  admin_password: "admin"  # Change this in production!

pools:
  exampledb:
    server_host: "127.0.0.1"  # PostgreSQL server address
    server_port: 5432          # PostgreSQL server port
    pool_mode: "transaction"   # Connection pooling mode
    users:
      - pool_size: 40
        username: "doorman"
        password: "SCRAM-SHA-256$4096:6nD+Ppi9rgaNyP7...MBiTld7xJipwG/X4="

TOML

[general]
host = "0.0.0.0"
port = 6432
admin_username = "admin"
admin_password = "admin"

[pools.exampledb]
server_host = "127.0.0.1"
server_port = 5432
pool_mode = "transaction"

[pools.exampledb.users.0]
pool_size = 40
username = "doorman"
password = "SCRAM-SHA-256$4096:6nD+Ppi9rgaNyP7...MBiTld7xJipwG/X4="

For a complete list of configuration options, run pg_doorman generate --reference --output ref.yaml to get an annotated config with all parameters and defaults.

Automatic Configuration Generation

The generate command creates a configuration file by connecting to your PostgreSQL server and detecting databases and users. By default, the generated config includes inline comments explaining every parameter.

# View all available options
pg_doorman generate --help

# Generate a YAML configuration file (recommended)
pg_doorman generate --output pg_doorman.yaml

# Generate a TOML configuration file (for backward compatibility)
pg_doorman generate --output pg_doorman.toml

# Generate a reference config with all settings (no PG connection needed)
pg_doorman generate --reference --output pg_doorman.yaml

# Generate a reference config with Russian comments for quick start
pg_doorman generate --reference --ru --output pg_doorman.yaml

# Generate a config without comments (plain serialization)
pg_doorman generate --no-comments --output pg_doorman.yaml

The generate command supports several options:

The command connects to PostgreSQL, detects databases and users, and creates a documented configuration file.

users:
  - username: "app_user"              # client-facing name
    password: "md5..."                # hash for client authentication
    server_username: "pg_app_user"    # different backend PostgreSQL user
    server_password: "real_password"  # plaintext password for that user

Client access control (pg_hba)

PgDoorman can enforce client access rules using PostgreSQL-style pg_hba.conf semantics via the general.pg_hba parameter. You can embed rules directly in the config or reference a file path. See the reference section for full examples.

Trust mode: when a matching rule uses trust, PgDoorman will accept connections without prompting the client for a password, mirroring PostgreSQL behavior. TLS-related rule types are honored: hostssl requires TLS, hostnossl forbids TLS.

Running PgDoorman

After creating your configuration file, you can run PgDoorman from the command line:

$ pg_doorman pg_doorman.toml

If you don't specify a configuration file, PgDoorman will look for pg_doorman.toml in the current directory.

Connecting to PostgreSQL via PgDoorman

Once PgDoorman is running, connect to it instead of connecting directly to your PostgreSQL database:

$ psql -h localhost -p 6432 -U doorman exampledb

Your application's connection string should be updated to point to PgDoorman instead of directly to PostgreSQL:

postgresql://doorman:password@localhost:6432/exampledb

The application talks PostgreSQL wire protocol; the connection-pooling layer is transparent to it.

Administration

Admin Console

PgDoorman exposes an administrative interface through the special database pgdoorman (or pgbouncer for backward compatibility):

$ psql -h localhost -p 6432 -U admin pgdoorman

Once connected, you can view available commands:

pgdoorman=> SHOW HELP;
NOTICE:  Console usage
DETAIL:
	SHOW HELP|CONFIG|DATABASES|POOLS|POOLS_EXTENDED|POOLS_MEMORY|POOL_COORDINATOR|POOL_SCALING
	SHOW CLIENTS|SERVERS|USERS|CONNECTIONS|STATS|PREPARED_STATEMENTS|AUTH_QUERY
	SHOW LISTS|SOCKETS|LOG_LEVEL|VERSION
	SET log_level = '<filter>'
	RELOAD
	SHUTDOWN
	UPGRADE
	PAUSE [db]
	RESUME [db]
	RECONNECT [db]

# Allow only local admin to access the admin DB without a password
host  pgdoorman  admin  127.0.0.1/32  trust

Monitoring PgDoorman

The admin console provides several commands to monitor the current state of PgDoorman:

• SHOW STATS - View performance statistics
• SHOW CLIENTS - List current client connections
• SHOW SERVERS - List current server connections
• SHOW POOLS - View connection pool status
• SHOW DATABASES - List configured databases
• SHOW USERS - List configured users

These commands are described in detail in the Admin Console Commands section below.

Reloading Configuration

If you make changes to the pg_doorman.toml file, you can apply them without restarting the service:

pgdoorman=# RELOAD;

When you reload the configuration:

1. PgDoorman reads the updated configuration file
2. Changes to database connection parameters are detected
3. Existing server connections are closed when they're next released (according to the pooling mode)
4. New server connections immediately use the updated parameters

This allows you to make configuration changes with minimal disruption to your applications.

Admin Console Commands

The admin console provides a set of commands to monitor and manage PgDoorman. These commands follow a SQL-like syntax and can be executed from any PostgreSQL client connected to the admin console.

Show Commands

The SHOW commands display information about PgDoorman's operation. Each command provides different insights into the pooler's performance and current state.

SHOW STATS

The SHOW STATS command displays comprehensive statistics about PgDoorman's operation:

pgdoorman=> SHOW STATS;

Statistics are presented per (database, user) pair:

SHOW SERVERS

The SHOW SERVERS command displays detailed information about all server connections:

pgdoorman=> SHOW SERVERS;

SHOW CLIENTS

The SHOW CLIENTS command displays information about all client connections to PgDoorman:

pgdoorman=> SHOW CLIENTS;

SHOW POOLS

The SHOW POOLS command displays information about connection pools. A new pool entry is created for each (database, user) pair:

pgdoorman=> SHOW POOLS;

SHOW USERS

The SHOW USERS command displays information about all configured users:

pgdoorman=> SHOW USERS;

SHOW DATABASES

The SHOW DATABASES command displays information about all configured database pools:

pgdoorman=> SHOW DATABASES;

SHOW SOCKETS

The SHOW SOCKETS command displays TCP/TCP6/Unix socket state counts (Linux only):

pgdoorman=> SHOW SOCKETS;

Shows aggregated counts of socket states (ESTABLISHED, SYN_SENT, etc.) parsed from /proc/net/tcp, /proc/net/tcp6, and /proc/net/unix.

SHOW VERSION

The SHOW VERSION command displays the PgDoorman version information:

pgdoorman=> SHOW VERSION;

This is useful for verifying which version you're running, especially after upgrades.

Control Commands

PgDoorman provides control commands that allow you to manage the service operation directly from the admin console.

SHUTDOWN

The SHUTDOWN command gracefully terminates the PgDoorman process:

pgdoorman=> SHUTDOWN;

When executed:

1. PgDoorman stops accepting new client connections
2. Existing transactions are allowed to complete (within the configured timeout)
3. All connections are closed
4. The process exits

SET log_level

Change the log level at runtime without restarting the pooler:

-- Global level
pgdoorman=> SET log_level = 'debug';

-- Per-module (RUST_LOG syntax)
pgdoorman=> SET log_level = 'warn,pg_doorman::pool::pool_coordinator=debug';

-- View current level
pgdoorman=> SHOW LOG_LEVEL;

-- Reset to startup default
pgdoorman=> SET log_level = 'default';

Changes are ephemeral — lost on restart. Valid levels: error, warn, info, debug, trace, off.

RELOAD

The RELOAD command refreshes PgDoorman's configuration without restarting the service:

pgdoorman=> RELOAD;

This command:

1. Rereads the configuration file
2. Updates all changeable settings
3. Applies changes to connection parameters for new connections
4. Maintains existing connections until they're released back to the pool

PAUSE

The PAUSE [db] command blocks new backend connection acquisition for the specified database (or all databases if no argument is given). Active transactions continue to work — only new connection requests are blocked.

-- Pause all pools
pgdoorman=> PAUSE;

-- Pause only pools for a specific database
pgdoorman=> PAUSE mydb;

Clients that request a new backend connection while the pool is paused will wait until RESUME is issued or until query_wait_timeout expires (whichever comes first). If the timeout expires, the client receives a timeout error.

Use SHOW POOLS to verify pause state — the paused column will show 1 for paused pools.

RESUME

The RESUME [db] command lifts a PAUSE and immediately unblocks all waiting clients:

-- Resume all pools
pgdoorman=> RESUME;

-- Resume only pools for a specific database
pgdoorman=> RESUME mydb;

Clients that were waiting due to PAUSE will immediately proceed to acquire a backend connection.

RECONNECT

The RECONNECT [db] command forces all backend connections to be recreated:

-- Reconnect all pools
pgdoorman=> RECONNECT;

-- Reconnect only pools for a specific database
pgdoorman=> RECONNECT mydb;

When executed:

1. The pool's internal epoch counter is incremented
2. All idle connections are immediately closed
3. Active connections (currently serving a transaction) continue working but are discarded when returned to the pool — they will not be reused

This means RECONNECT does not interrupt active transactions. New connections are created on demand with the current epoch, so they will be accepted by recycle().

Edge Cases and Behavior

The following table describes behavior in edge cases for PAUSE, RESUME, and RECONNECT:

Signal Handling

PgDoorman responds to standard Unix signals for control and management. Send signals using kill (e.g., kill -HUP <pid>).

Authentication

PgDoorman authenticates clients before forwarding them to PostgreSQL. It supports six methods, dispatched in priority order based on what the client sends and what the pool config defines.

This page explains how PgDoorman picks an authentication method. For setup details, follow the per-method links below.

Methods at a glance

LDAP, Kerberos GSSAPI, certificate-based auth, and SCRAM channel binding (scram-sha-256-plus) are not supported. See Comparison.

Dispatch order

pg_hba.conf is evaluated first, before any credential check. A reject rule terminates the connection; a trust rule skips the credential check entirely.

After HBA, PgDoorman picks a credential method in this order:

1. Talos. Activated when the client connects with username talos. The client's password is parsed as a JWT, the role (owner / read_write / read_only) is extracted, and the connection continues under that derived identity.
2. HBA Trust. If pg_hba.conf matched a trust rule, no credential check happens.
3. PAM. If the matched user has auth_pam_service set, credentials go to PAM (Linux only). PAM wins over a static password.
4. SCRAM static. If the user's password in config starts with SCRAM-SHA-256$, PgDoorman runs SCRAM authentication.
5. MD5 static. If the user's password starts with md5, PgDoorman runs MD5 authentication.
6. JWT. If the user's password starts with jwt-pkey-fpath:, the client's password is verified as a JWT against the public key on disk.

auth_query is not in this dispatch list — it runs before the dispatch to populate the pool's user list with hashes pulled from PostgreSQL. After auth_query returns a passwd value, dispatch picks the right method based on that value's prefix (SCRAM-SHA-256$ or md5).

If none of the methods matches the password format, PgDoorman returns "Authentication method not supported" and closes the connection.

Talking to PostgreSQL: passthrough vs configured

PgDoorman has to authenticate twice: once as the gateway (client → PgDoorman) and once as the backend (PgDoorman → PostgreSQL). Three patterns:

• Passthrough (default). The client's MD5 hash or SCRAM ClientKey is reused to authenticate to PostgreSQL. No plaintext password in config. Requires server_username to be unset (or equal to the client username).
• Configured backend user. Set server_username and server_password in the user block. PgDoorman authenticates to PostgreSQL with these instead. Use this when the pool username is decoupled from the database user (Talos, JWT, name remapping).
• auth_query in dedicated mode. Set server_user inside the auth_query block. All dynamically-discovered users share one backend pool authenticated as server_user. Trades per-user backend identity for pool reuse efficiency.

See Passthrough for details and auth_query for dedicated mode.

Restricting connections

pg_hba.conf is enforced before credentials are checked. Common patterns:

• Reject everything except localhost: host all all 0.0.0.0/0 reject followed by host all all 127.0.0.1/32 trust.
• Require TLS for non-local connections: hostssl all all 0.0.0.0/0 scram-sha-256 and hostnossl all all 127.0.0.1/32 trust.
• Per-database ACL: host mydb appuser 10.0.0.0/8 scram-sha-256.

See pg_hba.conf.

Where to next

• New deployment? Read Passthrough and Basic usage.
• Many users with rotating credentials? Use auth_query.
• Token-based service identity? Use JWT.
• OS-integrated authentication? Use PAM.
• Network-level restrictions? Configure pg_hba.conf.

Passthrough Authentication (Default)

PgDoorman reuses the client's cryptographic proof — MD5 hash or SCRAM ClientKey — to authenticate to PostgreSQL. The plaintext password never leaves the client and is never stored in the pool config.

This is the recommended setup when the pool username matches the PostgreSQL user.

How it works

MD5

PostgreSQL's MD5 password protocol stores md5(password + username) server-side. The client also hashes the password the same way and sends md5(stored_hash + salt). PgDoorman:

1. Receives the client's hashed response.
2. Looks up the stored MD5 hash in its config (or via auth_query).
3. Verifies the client response matches.
4. Forwards the stored hash to PostgreSQL as the password during backend authentication. PostgreSQL accepts it because the hash is what pg_authid actually stores.

The password field in the pool config holds the stored hash, formatted as md5XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX (the 32-character MD5 of password + username, prefixed with literal md5).

SCRAM-SHA-256

SCRAM verifies the client without sending any password-equivalent material. PgDoorman:

1. Performs SCRAM handshake with the client, validating the ClientProof.
2. Extracts the ClientKey from a successful exchange.
3. Performs SCRAM handshake with PostgreSQL, replaying the same ClientKey to compute a fresh ClientProof for the backend nonce.

The password field in the pool config holds the SCRAM verifier from pg_authid.rolpassword, formatted as SCRAM-SHA-256$<iterations>:<salt>$<StoredKey>:<ServerKey>.

PgDoorman does not support SCRAM channel binding (scram-sha-256-plus).

Configuration

pools:
  mydb:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    users:
      - username: "app"
        password: "md5d41d8cd98f00b204e9800998ecf8427e"
        pool_size: 40

Note what is not there: no server_username, no server_password. PgDoorman infers passthrough from the absence of these fields.

For SCRAM, the password field looks like:

password: "SCRAM-SHA-256$4096:random_salt$stored_key:server_key"

Getting the hash

Connect as a superuser to PostgreSQL and read pg_shadow (or pg_authid):

SELECT usename, passwd FROM pg_shadow WHERE usename = 'app';

The passwd column contains either an MD5 hash (md5...) or a SCRAM verifier (SCRAM-SHA-256$...), depending on password_encryption setting at the time the password was set.

To force MD5 storage: SET password_encryption = 'md5'; ALTER ROLE app PASSWORD 'plaintext'; To force SCRAM: SET password_encryption = 'scram-sha-256'; ALTER ROLE app PASSWORD 'plaintext';

When passthrough is not enough

Set server_username and server_password explicitly when:

• The pool user differs from the backend user (username remapping).
• The client authenticates with JWT — there is no MD5 hash or SCRAM key to pass through.
• The client authenticates with Talos and you want a fixed backend identity per role.
• You use auth_query in dedicated mode.

users:
  - username: "external_app"
    password: "jwt-pkey-fpath:/etc/pg_doorman/jwt.pub"
    server_username: "app"
    server_password: "md5..."
    pool_size: 40

Auto-generated config

pg_doorman generate --host your-pg-host --user your-admin-user introspects PostgreSQL and produces a config with hashes from pg_shadow filled in automatically. Use this for new deployments to avoid copy-paste mistakes.

pg_doorman generate --host db.example.com --user postgres --output pg_doorman.yaml

See Basic Usage for the full generate flow.

auth_query

Look up user credentials from PostgreSQL itself instead of listing every user in the pool config. Useful when users are provisioned dynamically or rotated frequently.

Two modes

PgDoorman supports two modes; both are configured in the same auth_query block. Choose by whether you set server_user:

• Passthrough mode (no server_user): each authenticated user gets its own backend pool, authenticated as that user. Preserves per-user backend identity for current_user, row-level security, and audit logs.
• Dedicated mode (with server_user): all dynamic users share a single backend pool authenticated as server_user. Trades per-user identity for higher pool reuse and lower connection count.

PgBouncer-style auth_query is dedicated mode. Odyssey supports both. PgDoorman's passthrough mode is the default.

Passthrough mode

pools:
  mydb:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    auth_query:
      query: "SELECT passwd FROM pg_shadow WHERE usename = $1"
      user: "postgres"
      password: "md5..."
      database: "postgres"
      cache_ttl: "1h"
      cache_failure_ttl: "30s"

The query must return a column named passwd or password containing the MD5 or SCRAM hash. Extra columns are ignored except for startup_parameters. In passthrough mode, pg_doorman reads that column as a text JSON object with per-user PostgreSQL startup parameters. Dedicated mode ignores the column and logs a warning.

user and password are the credentials PgDoorman uses to run the lookup query. They must have permission to read the credential column. Either grant access to a custom view (recommended) or use a user in pg_read_server_files group.

When a client connects as alice:

1. PgDoorman runs the query with $1 = 'alice' and gets her hash.
2. Caches the hash in memory for cache_ttl seconds.
3. Performs MD5 or SCRAM passthrough authentication (see Passthrough).
4. Opens a backend connection authenticated as alice with the same hash.

Dedicated mode

pools:
  mydb:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    auth_query:
      query: "SELECT passwd FROM pg_shadow WHERE usename = $1"
      user: "auth_lookup"
      password: "md5..."
      database: "postgres"
      server_user: "app"
      server_password: "md5..."
      pool_size: 40
      min_pool_size: 5
      cache_ttl: "1h"

Setting server_user switches the mode. Now:

1. The client authenticates as alice against the hash returned by the query.
2. The backend pool is authenticated as app (the server_user), and is shared across all dynamic users.
3. current_user in PostgreSQL will always be app, regardless of which client connected.

Use this when you have many users (thousands) and per-user backend pools would exhaust PostgreSQL's connection slots.

Recommended PostgreSQL setup

Avoid using a superuser for the lookup. Create a dedicated function with SECURITY DEFINER:

CREATE OR REPLACE FUNCTION pg_doorman_lookup(uname text)
RETURNS TABLE(passwd text)
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
  SELECT passwd FROM pg_shadow WHERE usename = uname;
$$;

REVOKE ALL ON FUNCTION pg_doorman_lookup(text) FROM public;
GRANT EXECUTE ON FUNCTION pg_doorman_lookup(text) TO auth_lookup;

Then in the pool config:

auth_query:
  query: "SELECT passwd FROM pg_doorman_lookup($1)"
  user: "auth_lookup"
  password: "md5..."

Caching

Duration values are quoted strings: "1h", "30m", "300s". A bare integer is interpreted as milliseconds — cache_ttl: 3600 would cache for 3.6 seconds, not one hour.

Cache is per-pool, in-memory, evicted on RELOAD. Restart or RELOAD after rotating a user's password.

Observability

SHOW AUTH_QUERY exposes per-database stats:

database | cache_entries | cache_hits | cache_misses | cache_refetches | rate_limited | auth_success | auth_failure | executor_queries | executor_errors

Prometheus metrics: pg_doorman_auth_query_cache, pg_doorman_auth_query_auth, pg_doorman_auth_query_executor, pg_doorman_auth_query_dynamic_pools. See Admin commands.

PAM Authentication

PgDoorman delegates client authentication to a PAM service on the host. Use this for OS-integrated authentication (LDAP via pam_ldap, Kerberos, local PAM modules) without putting per-user credentials in the pool config.

PAM is Linux-only. The pre-built binaries ship with PAM support enabled.

Configuration

pools:
  mydb:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    users:
      - username: "alice"
        auth_pam_service: "pg_doorman"
        server_username: "alice"
        server_password: "md5..."
        pool_size: 20

auth_pam_service is the name of the PAM service file under /etc/pam.d/. PgDoorman does not validate the service name at startup — make sure the file exists.

The password field is omitted because PAM does the verification. server_username and server_password are required: PAM only authenticates the client to PgDoorman; PgDoorman still needs credentials for the backend connection.

Example PAM service

/etc/pam.d/pg_doorman:

auth     required pam_unix.so
account  required pam_unix.so

For LDAP-backed authentication:

auth     required pam_ldap.so
account  required pam_ldap.so

Configure pam_ldap in /etc/ldap.conf (or /etc/nslcd.conf) per your environment.

Dispatch order

PAM is checked after Talos and HBA Trust, but before any password-based method. If a user has both auth_pam_service and a static password (MD5, SCRAM, or JWT prefix), PAM wins.

See Overview.

Caveats

• PAM blocks the worker thread during the authentication call. If your PAM stack does network calls (LDAP, Kerberos), expect occasional latency spikes.
• pam_unix.so requires read access to /etc/shadow — usually only root. Run PgDoorman as a user with the right group membership, or use a different PAM module.
• PAM does not support SCRAM passthrough. The backend connection always uses server_username and server_password.
• For LDAP without PAM machinery, PgDoorman has no native LDAP support. Use Odyssey or PgBouncer 1.25+ for that.

JWT Authentication

Authenticate clients with a JSON Web Token signed by an external identity provider. PgDoorman verifies the token's RSA-SHA256 signature using a public key from disk, checks the preferred_username claim, and forwards the connection to PostgreSQL with a configured backend identity.

This fits service-to-database access where short-lived tokens are issued by an OIDC provider, Vault, or an internal token service.

Configuration

Generate (or obtain) an RSA public key and reference it in the user's password field with the jwt-pkey-fpath: prefix:

pools:
  mydb:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    users:
      - username: "billing-service"
        password: "jwt-pkey-fpath:/etc/pg_doorman/jwt-public.pem"
        server_username: "billing"
        server_password: "md5..."
        pool_size: 40

Whatever the client sends as a password is treated as a JWT and verified against /etc/pg_doorman/jwt-public.pem. The token must:

• Be signed with RS256 (RSA-SHA256). HS256 and EC variants are not supported.
• Have a preferred_username claim equal to the configured username (billing-service in the example above).
• Pass standard exp and nbf validation.

The backend connection is opened as billing with the server_password hash. The client's identity (billing-service) is decoupled from the database identity (billing).

Generating a key pair

openssl genrsa -out jwt-private.pem 2048
openssl rsa -in jwt-private.pem -pubout -out jwt-public.pem

Keep jwt-private.pem on the token issuer. Distribute jwt-public.pem to PgDoorman.

Issuing a token

Any RS256 JWT library works. Example with Python (PyJWT):

import jwt
import time

private_key = open("jwt-private.pem").read()

token = jwt.encode(
    {
        "preferred_username": "billing-service",
        "iat": int(time.time()),
        "exp": int(time.time()) + 300,  # 5 minutes
    },
    private_key,
    algorithm="RS256",
)

The client connects to PgDoorman with user=billing-service and password=<token>. Most PostgreSQL drivers accept any string in the password field.

Token rotation

PgDoorman reads the public key file once at startup and on SIGHUP. Rotate the key by:

1. Add the new public key to a second user entry with a parallel name.
2. Reload (kill -HUP).
3. Switch the issuer to the new key.
4. Remove the old user entry after the grace period.

Or, simpler, replace the file in place and SIGHUP. There is no support for multiple keys per user.

Dispatch order

JWT is the lowest-priority password format: PgDoorman checks SCRAM-SHA-256$ and md5 prefixes first, then jwt-pkey-fpath:. In practice this only matters if you use a placeholder password — set auth_pam_service for PAM, or use the jwt-pkey-fpath: prefix exclusively for JWT users.

If the same user has both auth_pam_service and a jwt-pkey-fpath: password, PAM wins.

See Overview.

Caveats

• The preferred_username claim must match exactly. There is no claim mapping or aliasing.
• No JWKS endpoint support: the public key must be on disk.
• No issuer (iss) or audience (aud) checks. If you need them, terminate JWT at a sidecar and translate to passthrough.
• For client identity carrying database role information (e.g., read_only vs read_write), see Talos.

Talos Authentication

Talos is a JWT-based authentication scheme developed at Ozon. The token carries a role assignment per database in its resource_access claim, and PgDoorman extracts the highest role to pick the backend identity. Multiple signing keys are supported via the kid header.

If you operate inside Ozon's Talos identity stack, this is the integration. Outside, prefer plain JWT.

How it works

1. A client connects with username talos and a JWT as the password.
2. PgDoorman reads the kid field from the JWT header and looks up the matching public key in general.talos.keys.
3. The token is verified (RS256, exp, nbf).
4. PgDoorman walks resource_access keys, splits each on :, and matches the part after the colon against general.talos.databases. So a key like "postgres.stg:billing" matches the billing database. The roles from every matching entry are collected; the highest wins (owner > read_write > read_only).
5. The connection is authenticated against a pool user named after the role: owner, read_write, or read_only. That user must exist in the pool with server_username and server_password configured.

The client identity (clientId from the token) is preserved in application_name and audit logs.

Configuration

general:
  host: "0.0.0.0"
  port: 6432
  talos:
    keys:
      - "/etc/pg_doorman/talos/keys/abc123.pem"
      - "/etc/pg_doorman/talos/keys/def456.pem"
    databases:
      - "billing"
      - "inventory"

pools:
  billing:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"
    users:
      - username: "owner"
        server_username: "billing_owner"
        server_password: "md5..."
        pool_size: 20
      - username: "read_write"
        server_username: "billing_app"
        server_password: "md5..."
        pool_size: 40
      - username: "read_only"
        server_username: "billing_ro"
        server_password: "md5..."
        pool_size: 60

The file stem of each key (abc123, def456) is the kid matched against the JWT header.

databases is a filter: only listed databases are eligible for Talos. A token without an entry for the requested database is rejected.

Token shape

{
  "kid": "abc123",
  "alg": "RS256"
}
.
{
  "exp": 1714500000,
  "nbf": 1714400000,
  "clientId": "billing-service",
  "resource_access": {
    "postgres.stg:billing": { "roles": ["read_write"] },
    "postgres.stg:inventory": { "roles": ["read_only", "read_write"] }
  }
}

resource_access keys must include a colon. PgDoorman ignores everything before it and matches the suffix against general.talos.databases. A token built without the colon prefix will produce no role and authentication will fail with "Token may not contain valid roles for the requested databases".

A client connecting to inventory with this token lands in the read_write user (max of the two listed roles).

Dispatch order

Talos has highest priority. If a client connects with username talos and general.talos.keys is non-empty, no other authentication method is tried.

See Overview.

Caveats

• Talos requires the special talos username. Non-Talos clients use other authentication methods normally.
• The role-to-user mapping is fixed: owner, read_write, read_only. Custom role names need code changes.
• Multiple roles in the same resource_access entry collapse to the maximum. There is no "deny" semantics.
• Public keys are loaded once at startup and reloaded on SIGHUP.

pg_hba.conf

Restrict who can connect to PgDoorman based on source address, database, user, and connection type. Uses the same rule format as PostgreSQL's pg_hba.conf.

This is a network-level access control layer that runs before credential authentication. A connection rejected by pg_hba never gets to the password check.

Configuration

Three formats. Pick whichever fits your deployment.

Inline string

general:
  hba: |
    hostssl all all 0.0.0.0/0 scram-sha-256
    host    all all 127.0.0.1/32 trust
    local   all all              trust
    host    all all 0.0.0.0/0    reject

From file

general:
  hba:
    path: "/etc/pg_doorman/pg_hba.conf"

The file is read on startup and on SIGHUP.

Inline content under structured key

general:
  hba:
    content: |
      hostssl all all 0.0.0.0/0 scram-sha-256
      host    all all 127.0.0.1/32 trust

Same as the inline string, useful when you generate the config from templating tools.

Rule format

Each line:

<connection_type> <database> <user> [<source_cidr>] <method>

connection_type — one of:

database — all, a specific database name, or a comma-separated list. replication is not handled (PgDoorman doesn't support replication passthrough).

user — all, a specific user, or a comma-separated list. +groupname (PostgreSQL role membership) is not supported.

source_cidr — IPv4 or IPv6 CIDR. Required for host, hostssl, hostnossl. Not applicable to local.

method — one of:

Rules are evaluated top to bottom. The first match wins.

Examples

Require TLS from the network, allow plain local

hostssl   all all 10.0.0.0/8     scram-sha-256
hostnossl all all 10.0.0.0/8     reject
host      all all 127.0.0.1/32   trust
local     all all                trust

Per-database ACL

host billing  app_billing  10.0.0.0/8 scram-sha-256
host billing  all          0.0.0.0/0  reject
host inventory app_inv     10.0.0.0/8 scram-sha-256
host all       admin       10.1.1.0/24 scram-sha-256
host all       all         0.0.0.0/0  reject

Block legacy MD5 from the open internet

hostssl all all 0.0.0.0/0 scram-sha-256
host    all all 0.0.0.0/0 reject

If your database stores MD5 hashes only and a client requests SCRAM, authentication fails with a clear error. Switch the database to SCRAM-SHA-256 (ALTER ROLE ... PASSWORD) before tightening rules.

Differences from PostgreSQL's pg_hba.conf

• No replication keyword (PgDoorman does not pass replication connections).
• No peer, ident, cert, gss, sspi, or pam methods. PAM is configured per-user with auth_pam_service, not via HBA.
• No +groupname user prefix.
• No regex (/regex syntax).
• IPv6 CIDR is supported. IPv4-mapped IPv6 (::ffff:1.2.3.4) is matched against IPv4 rules.

Reload

kill -HUP $(pidof pg_doorman)

Existing connections are not re-evaluated. New connections use the new rules.

Caveats

• Rules apply to clients connecting to PgDoorman, not to PostgreSQL. PostgreSQL's own pg_hba.conf still matters for the backend connection.
• trust admits the client without any credential check. The backend still has to authenticate as the pool user — but the client side is unverified. Use trust only on networks where the source address is trustworthy (loopback, restricted Unix socket).
• For LDAP, Kerberos, or peer authentication, see Comparison — these are not supported.

TLS

PgDoorman terminates TLS on the client side (clients → PgDoorman) and originates TLS on the server side (PgDoorman → PostgreSQL). The two sides are configured independently.

Client-side TLS

Encrypt connections between client applications and PgDoorman.

Modes

verify-full is mTLS — the server verifies the client's certificate. Set up a client CA bundle with tls_ca_cert.

Configuration

general:
  tls_mode: "require"
  tls_certificate: "/etc/pg_doorman/tls/server.crt"
  tls_private_key: "/etc/pg_doorman/tls/server.key"
  tls_ca_cert: "/etc/pg_doorman/tls/client_ca.pem"   # only for verify-full
  tls_rate_limit_per_second: 100                       # optional handshake throttle

The certificate may be self-signed for development; production deployments typically use Let's Encrypt or an internal CA.

Reload (client side)

Client-side certificates are loaded at startup. Changing them requires a process restart. There is no SIGHUP reload for client-side TLS.

Hot process handoff can load a new certificate and key for new inbound TLS connections, but it is not seamless rotation for sessions that are already open. To migrate TLS sessions, both processes must use the same tls_certificate and tls_private_key, and PgDoorman must run as a Linux build with tls-migration; if those files change, TLS clients drain and reconnect.

Cipher policy

Minimum TLS 1.2 enforced in the handshake. PgDoorman does not set an explicit cipher list — the effective ciphers come from the system OpenSSL build. If you need a hardened cipher list, configure it system-wide (/etc/ssl/openssl.cnf) or build OpenSSL with the policy you want.

Direct TLS handshake (PG17, no SSLRequest) is not supported. For TLS 1.3 cipher control or PG17 direct TLS, use PgBouncer 1.25+.

Server-side TLS

Encrypt connections between PgDoorman and PostgreSQL backends. Added in 3.6.0.

Modes

allow is the default to keep backward compatibility — existing deployments where PostgreSQL has TLS configured automatically upgrade without config changes. New deployments wanting explicit guarantees should use require or verify-full.

Configuration

general:
  server_tls_mode: "verify-full"
  server_tls_ca_cert: "/etc/pg_doorman/tls/pg_ca_bundle.pem"

# Optional: client certificate for mTLS to PostgreSQL
  server_tls_certificate: "/etc/pg_doorman/tls/pg_client.crt"
  server_tls_private_key: "/etc/pg_doorman/tls/pg_client.key"

server_tls_ca_cert accepts a PEM bundle (multiple CA certificates concatenated). All are loaded.

Hot reload

On SIGHUP, server-side certificates are re-read from disk. Existing connections keep using their original TLS context; new connections use the reloaded certificates. The reload is lock-free via Arc<ArcSwap<...>> — no connection drop, no handshake stall.

kill -HUP $(pidof pg_doorman)

This is the only TLS reload path. Client-side certificates do not reload on SIGHUP.

mTLS to PostgreSQL

Set server_tls_certificate and server_tls_private_key. PostgreSQL must be configured with ssl_ca_file matching the client cert's signer, and the role must have clientcert=verify-ca (or verify-full) in pg_hba.conf on the PostgreSQL side.

Observability

Three Prometheus series cover server-side TLS:

See Prometheus reference.

Known limitations

• The COPY protocol over server TLS is not exercised by the BDD test suite. Behavior is expected to work but unverified.
• Cancel requests to the backend bypass server TLS — they use a fresh plain TCP connection. This matches PostgreSQL's protocol design (cancel is sent on a separate socket).
• Direct TLS handshake (PG17 fast handshake without SSLRequest) is not supported on either side.

Where to next

• New cluster setup? See Installation.
• Rotating certificates? See Binary Upgrade and Signals. Client-facing TLS certificate rotation is not seamless for already-open TLS sessions.
• Hardening an existing deployment? Combine with pg_hba.conf: force hostssl for non-local connections.

Pool Modes

PgDoorman supports two pool modes: transaction and session. Set per pool, with optional per-user override.

There is no statement mode. Statement pooling rotates the backend after every statement, which forces clients to give up multi-statement transactions and breaks the prepared-statement protocol entirely; PgDoorman invests its tuning (prepared-statement cache, direct handoff, strict-FIFO scheduling) in transaction mode instead. PgBouncer keeps statement mode for backward compatibility; Odyssey omits it.

Transaction mode (recommended)

pools:
  mydb:
    pool_mode: "transaction"

A backend connection is held for the duration of a transaction, then returned to the pool on COMMIT, ROLLBACK, or implicit completion.

This is the mode that delivers PgDoorman's connection efficiency: a pool_size of 40 can serve thousands of clients as long as transactions are short.

What works in transaction mode (where most poolers fail):

• Prepared statements. PgDoorman caches them per-pool, remaps statement names across backend connections, and replays preparation transparently. Drivers that pin to unnamed statement (Go pgx, .NET Npgsql, Python asyncpg) work without configuration.
• Pipelined batches and async Flush flow.
• Cancel requests over TLS.
• LISTEN / NOTIFY — but only inside a transaction. A LISTEN issued and then committed releases the backend, and any notifications delivered to it after that go to whichever client checks it out next, not to the original LISTEN-er. PgBouncer behaves the same way; if you need cross-transaction LISTEN, use session mode for that client.

What does not work in transaction mode:

• SET and RESET outside a transaction. Use session mode for clients that rely on session-level GUC changes (SET TIME ZONE, SET search_path once per connection).
• Advisory locks held across transactions. Use session mode.
• Cursors held outside transactions (WITH HOLD). Use session mode.
• SET LOCAL works as expected — it is transaction-scoped.

Session mode

pools:
  legacy_app:
    pool_mode: "session"

A backend connection is held for the duration of the client session. Returned to the pool only when the client disconnects.

Use this when:

• The application uses session-scoped state (SET search_path, SET TIME ZONE).
• The application uses WITH HOLD cursors.
• The application uses advisory locks across transactions.
• You are migrating an unmodified PgBouncer deployment that was using session mode and you want a like-for-like swap.

In session mode, pool_size is effectively the maximum number of concurrent clients. Sizing matches PostgreSQL's max_connections minus reserves.

Per-user override

A pool's mode can be overridden per user:

pools:
  mydb:
    pool_mode: "transaction"
    users:
      - username: "app"
        password: "md5..."
        pool_size: 40
      - username: "admin_tools"
        password: "md5..."
        pool_size: 4
        pool_mode: "session"

Useful when one user (operations tooling, migrations) needs session semantics but the main application stays in transaction mode.

Cleanup on checkin

Cleanup in transaction mode is mutation-tracked, not unconditional. PgDoorman watches each transaction for SET, PREPARE, and DECLARE CURSOR, and only when the backend returns to the pool with one of those flags set does it issue RESET ALL, DEALLOCATE ALL, or CLOSE ALL respectively. A read-only transaction skips cleanup entirely — that's a measurable win on hot OLTP paths.

What gets reset when a flag fires:

• SET flag → RESET ALL drops session-level GUCs and runs pg_advisory_unlock_all implicitly.
• PREPARE flag → DEALLOCATE ALL drops PostgreSQL-side prepared statements that the driver named explicitly. PgDoorman's own prepared-statement cache survives the reset because it is keyed by query text, not by backend name.
• DECLARE CURSOR flag → CLOSE ALL drops cursors.

DEALLOCATE ALL and DISCARD ALL issued by the client clear that client's prepared-statement cache (so the next Parse registers anew). The pool-level shared cache is not affected; other clients keep their entries.

To opt out of cleanup entirely (for performance, in tightly-controlled deployments):

pools:
  mydb:
    pool_mode: "transaction"
    cleanup_server_connections: false

Only do this if you are sure your application never leaks session state. The mutation-tracked default is already cheap when no mutation happened, so the opt-out is rarely worth the risk.

Reference

• pool_mode parameter: Pool Settings.
• cleanup_server_connections: Pool Settings.
• Pool sizing: Pool Coordinator, Pool Pressure.

Pool Coordinator

The Pool Coordinator caps total backend connections per database across all users in that pool, with priority eviction when the cap is reached. It is what PgBouncer's max_db_connections should have been: enforced fairly, with a reserve for short bursts, and per-user minimums to protect critical workloads.

This page explains the concept and when to use it. For tuning recipes and read-out from SHOW POOL_COORDINATOR, see Pool Pressure.

What problem it solves

Without a coordinator, every user-pool is independent. A pool_size of 40 across 5 users means up to 200 backend connections — and PostgreSQL fights to maintain its own limits.

max_db_connections in PgBouncer caps the total, but once the cap is reached new clients simply queue. Connections only free up when their current owner closes them naturally on server_idle_timeout. Whoever grabbed connections first keeps them regardless of how heavily they use them, and slow workloads never yield to fast ones.

PgDoorman's Pool Coordinator caps the total and:

• Evicts idle connections from over-allocated users when another user needs to grow.
• Ranks users by p95 transaction time so the slowest pools yield first. Pools running fast transactions keep their reuse advantage; pools running long transactions sit idle a larger fraction of the time, so taking from them costs less.
• Reserves a small overflow for short bursts. Configured separately from the main cap.
• Guarantees a per-user minimum that is never evicted. Critical workloads keep their footing during contention.

When to use it

Turn on the coordinator when:

• Multiple distinct workloads share the same database and you need an upper bound on backend connection count (PostgreSQL max_connections, RAM, file descriptors).
• One workload has bursty demand and you want it to absorb idle slots from others without crowding them out permanently.
• You operate near the PostgreSQL connection ceiling and need fair degradation rather than first-come-first-served.

You do not need it when:

• Each user's pool_size is small enough that the sum is comfortably below PostgreSQL's max_connections.
• Workloads are predictable and pre-sized.
• You want PgBouncer-level simplicity. max_db_connections without eviction is supported but discouraged for shared databases.

Configuration

pools:
  shared_db:
    server_host: "127.0.0.1"
    server_port: 5432
    pool_mode: "transaction"

    # Total cap across all users in this pool.
    max_db_connections: 80

    # Reserve overflow above max_db_connections for short bursts.
    # Acquired only when no idle connection is available within reserve_pool_timeout.
    reserve_pool_size: 16
    reserve_pool_timeout: "3s"

    # Per-user safety net: connections never evicted from a user, even under pressure.
    # Sum across users should be ≤ max_db_connections.
    min_guaranteed_pool_size: 5

    # Eviction grace period: connections younger than this are not evicted.
    # Prevents thrashing when a workload briefly idles.
    min_connection_lifetime: "30s"

    users:
      - username: "fast_app"
        password: "md5..."
        pool_size: 40

      - username: "batch_job"
        password: "md5..."
        pool_size: 60

Effective ceiling: max_db_connections + reserve_pool_size = 96. The reserve absorbs sub-second spikes; if the spike persists, eviction kicks in.

How it picks who donates

When a user requests a new backend and the cap is reached:

1. Find candidates with idle connections. A user holding only active connections cannot donate — its work is in flight.
2. Skip protected users. A user below min_guaranteed_pool_size is excluded.
3. Skip recently-created connections. Connections younger than min_connection_lifetime are not evicted (avoids churn during minor idle gaps).
4. Rank by surplus. Users with the most idle connections above their min_guaranteed_pool_size rank highest.
5. Tiebreak by p95 transaction time. Among equally-idle users, the pool with the higher p95 yields first. Higher p95 means each transaction holds the connection longer; the same user therefore reuses each connection less often, so a single eviction translates into fewer reused checkouts lost.

The chosen idle connection is closed; the requesting user receives a fresh connection from PostgreSQL.

Observability

SHOW POOL_COORDINATOR shows current state per database:

database    | max_db_conn | current | reserve_size | reserve_used | evictions | reserve_acq | exhaustions
shared_db   | 80          | 78      | 16           | 2            | 142       | 18          | 0

• evictions rising fast — one user is starved repeatedly. Either raise max_db_connections or set min_guaranteed_pool_size for that user.
• reserve_acq high — bursts are normal but you might be undersized; consider raising max_db_connections instead of relying on reserve.
• exhaustions non-zero — even reserve was full. Clients hit query_wait_timeout waiting for a backend. Raise the cap.

Prometheus: pg_doorman_pool_coordinator{type="..."} (gauges) and pg_doorman_pool_coordinator_total{type="evictions|reserve_acquisitions|exhaustions"} (counters). See Admin commands and Prometheus reference.

Caveats

• The coordinator only operates within one pool (one database). Cross-pool / cross-database limits are not supported.
• Eviction picks idle connections; a user holding all connections in long transactions cannot donate, so other users may starve. If this is your shape, raise max_db_connections or split the workload.
• min_guaranteed_pool_size is a floor for eviction, not a min_pool_size for warm-up. The pool still has to create those connections on demand.
• Setting max_db_connections without min_guaranteed_pool_size is the PgBouncer mode — works, but starves smaller users under pressure. Always set both for shared databases.

Where to next

• Sizing recipe with worked examples: Pool Pressure → Sizing the cap.
• Tuning under load: Pool Pressure → Tuning parameters.
• Reading admin output: Admin Commands → SHOW POOL_COORDINATOR.
• Pool modes (transaction vs session): Pool Modes.

Anonymous Parse caching

PgDoorman caches anonymous Parse messages for transaction pooling. Many drivers send short parameterised queries as Parse with an empty statement name. Without a remap, PostgreSQL plans the query again on every Bind, so hot OLTP paths pay planner CPU on every call.

PgDoorman transparently remaps every anonymous Parse to an internal DOORMAN_<N> name on the backend. The plan lands in the backend's named prepared statement registry and gets reused across Binds of one client and across clients sharing the same pool. The main effect is less planner CPU and fewer repeated backend Parses for the same query shape.

The remap is transparent to the driver: clients send and receive empty statement names just as they would against a vanilla PostgreSQL.

PgBouncer (1.21+) and Odyssey support prepared statements in transaction mode, but only for named statements. They forward anonymous Parse unchanged. PgDoorman's extra behaviour is the anonymous remap. Cache bounds, LRU, TTL, and observability keep that remap controlled under dynamic SQL.

What gets faster

Anonymous Parse caching removes repeated work from hot parameterised query paths:

• the backend does not receive the same Parse on every reuse of an already known query shape;
• PostgreSQL can use a prepared statement already created on that backend connection;
• different clients in the same pool share one pool-level entry instead of warming the same query independently;
• on a server-cache hit, PgDoorman synthesizes ParseComplete without a PostgreSQL round-trip.

This is primarily a performance optimization for OLTP workloads with repeated query shapes. Cache caps, TTL, and the interner exist so the speedup does not become unbounded memory growth in the pooler or on PostgreSQL backends.

The PostgreSQL baseline

A Parse message carries a statement name. An empty name means anonymous, anything else means named:

                          Lifetime in PG          Plan caching
  ─────────────────────   ─────────────────       ─────────────────────
  Anonymous (name="")     Until next anonymous    No named registry
                          Parse or session end    entry; each new
                                                   unnamed Parse plans
  Named (name="stmt_42")  Until Close /           Starts with custom
                          DEALLOCATE /            plans; may switch to
                          session end             a generic plan after
                                                   5 custom executions

Most modern drivers default to anonymous for one-shot parameterised queries: lib/pq (Go), libpq PQexecParams (C), some flows in pgjdbc and psycopg. The application code looks identical to a parameterised named-statement query, but the wire protocol carries an empty name.

Why this is a problem for transaction-mode pooling

Transaction pooling rotates a backend among many clients. If the pooler forwards the empty Parse name as-is, every client's Bind runs against a backend that has no plan cached for that query. Hot OLTP paths pay the planner cost on every call.

Named prepared statements solve planner performance, but they push the bookkeeping problem onto the pooler:

• The pooler must remember each client's named statements until the client disconnects, even if the pool-level shared cache evicts the entry.
• On every Bind, it must verify the current backend knows the name and re-Parse otherwise.
• On client disconnect, it must issue Close or DEALLOCATE to the right backend.
• Drivers that mint per-query names (stmt_<seq>) compound the per-client cache size: hundreds of entries per client times tens of thousands of clients.

So the choice is: give up plan caching for anonymous traffic, or inherit the full cost of named-statement bookkeeping. PgDoorman takes a third option.

What PgDoorman does

On every anonymous Parse from the client, PgDoorman:

1. Hashes the query text, parameter type OIDs, and a digest of the planner GUCs pinned by the client's StartupMessage (search_path, default_transaction_isolation, default_transaction_read_only, default_text_search_config, role). Two clients that send the same query and parameter OIDs but pin different search_path values therefore get separate cache entries and separate PostgreSQL plans. Planner GUCs outside this list (TimeZone, DateStyle, plan_cache_mode, enable_*, JIT cost knobs) are not part of the key. See the sync_server_parameters reference before mixing the same prepared query across different values of those GUCs.
2. Looks up the hash in the pool-level cache (shared across all clients of this pool). On miss, it allocates a fresh DOORMAN_<counter> name and registers an Arc<Parse> entry.
3. Stores a per-client cache entry keyed by Anonymous(hash) so the following Bind can locate the same DOORMAN_<N>.
4. Forwards Parse to the backend with the rewritten name.
5. On the matching Bind (with empty name), rewrites the statement name to DOORMAN_<N> and ensures the current backend already holds the named statement; sends a fresh Parse if not.

The client never sees DOORMAN_<N>: the rewrite lives only on the leg between PgDoorman and the backend. When the backend already holds the name, PgDoorman synthesises ParseComplete itself and skips the round-trip.

Wire-protocol example

A Go application running

db.Query("SELECT * FROM t WHERE name = $1", "vasya")

through lib/pq produces this exchange:

  Client                   PgDoorman                  Backend
  ──────                   ─────────                  ───────
  Parse("", q)        ────►│ hash, miss → DOORMAN_42
                            │ pool_cache[hash] = Arc<Parse>
                            │ client_cache[Anon(hash)] = ...
                            │             Parse("DOORMAN_42") ────►
                            │                    ◄── ParseComplete
                       ◄────│ ParseComplete
  Bind("", "vasya")   ────►│ rewrite "" → "DOORMAN_42"
                            │             Bind("DOORMAN_42") ─────►
                            │             Execute, Sync ──────────►
                            │                ◄── BindComplete, ...
                            │                ◄── ReadyForQuery
                       ◄────│ BindComplete, ...

A second client running the same query in the same pool hits the pool cache and skips the backend Parse entirely:

  Client B           PgDoorman                       Backend (same)
  ────────           ─────────                       ──────────────
  Parse("", q)  ───►│ hash hit → DOORMAN_42
                     │ server_cache contains "DOORMAN_42"
                ◄────│ synthetic ParseComplete       (no message sent)
  Bind("", v)   ───►│ rewrite "" → "DOORMAN_42"
                     │           Bind("DOORMAN_42") ────►
                     │           ...

Cache layers

PgDoorman keeps prepared-statement state at three levels:

  Pool-level    DashMap<hash, CacheEntry>
                One per pool. Holds Arc<Parse> with name "DOORMAN_N".
                Size:    prepared_statements_cache_size (default 8192).
                Eviction: approximate LRU.

  Client-level  Named:     AHashMap<String, CachedStatement>, unbounded.
                Anonymous: LruCache<u64, CachedStatement> bounded by
                           client_anonymous_prepared_cache_size (defaults to
                           prepared_statements_cache_size when unset),
                           or AHashMap if size = 0.
                Eviction of an Anonymous entry is local: the Arc<Parse>
                is dropped, the underlying DOORMAN_<N> on the backend
                stays.

  Server-level  LruCache<String, ()>, per backend connection.
                Tracks which DOORMAN_N this backend already holds.
                True LRU; on eviction issues Close to the backend.

When the Anonymous LRU evicts an entry, PgDoorman drops the local reference and does not send Close to the backend. The underlying DOORMAN_<N> is recycled by the server-level LRU or server_lifetime (default 20 min), whichever comes first.

The query text itself is interned via Arc<str>: ten clients sending the same anonymous query share one allocation in memory.

When the remap helps

• API workloads with a small set of hot queries. A handful of unique SELECT / INSERT shapes shared across thousands of clients. Pool-cache hit rate near 100 %, planner runs once per backend per query shape, and later calls go through Bind against already prepared backend state.
• Drivers that pin to anonymous prepared. lib/pq, libpq PQexecParams, pgjdbc before the prepareThreshold is reached. Without the remap they would re-plan on every call.
• Mixed pools where named and anonymous coexist. Anonymous statements get the same plan-cache benefit as named ones, without growing the per-client named cache.

When the remap doesn't help

• Ad-hoc / OLAP traffic. Each query is unique. When the pool cache is full, each new query shape must find an old entry to evict with an O(N) scan. Disable prepared-statement remapping with prepared_statements: false if the instance only serves this traffic.
• Single-statement scripts. A connect → Parse → 1 Bind → disconnect pattern doesn't accumulate enough hits to repay the bookkeeping. The overhead per Parse is small (~700 ns) but measurable.
• Async drivers in pipeline mode. Each session gets a unique DOORMAN_async_<N> name to avoid name collisions between in-flight operations, so the server cache can't reuse entries across sessions. The pool-level cache still shares the query text across sessions; the backend planner still runs once per session.

Track effectiveness with rate(pg_doorman_servers_prepared_hits_total[5m]) and rate(pg_doorman_servers_prepared_misses_total[5m]). A sustained miss share above 30 % means the remap is spending CPU and memory without enough backend plan reuse. Either disable it or raise prepared_statements_cache_size.

How other poolers handle this

PgBouncer added prepared-statement support in 1.21, but limited it to named statements: an anonymous Parse is forwarded as-is and each Bind re-runs the planner. Odyssey's pool_reserve_prepared_statement requires named statements; it does nothing for anonymous traffic. PgCat behaves the same way.

In this comparison, only PgDoorman caches anonymous Parse.

Configuration

The Named part of the per-client cache is always unlimited and is not affected by client_anonymous_prepared_cache_size.

To disable prepared-statement remapping entirely (rare, for OLAP-only deployments):

general:
  prepared_statements: false

There is no separate anonymous-only switch. Do not use prepared_statements_cache_size: 0 as the disable switch: pg_doorman rejects that general config while prepared_statements is enabled.

Differences from PostgreSQL semantics

The remap changes a few protocol-level behaviours that strict applications may rely on:

• The same anonymous Parse issued twice does not discard the previous one. Each (query, param_types) lives independently in the pool cache under a separate DOORMAN_<N>.
• Close with an empty name is a no-op for PgDoorman's caches. The underlying DOORMAN_<N> lives until pool-level LRU evicts it or the pool shuts down.
• PostgreSQL's custom/generic plan decision is shared by all clients using the same DOORMAN_<N>. A named statement starts with custom plans; after five custom executions PostgreSQL may switch to a generic plan if its estimated cost is close enough. With PgDoorman, those executions can come from different clients, so a generic-plan decision can reflect mixed parameter distributions.

Applications that depend on PostgreSQL's "anonymous Parse discards the previous one" semantics should switch to named statements with explicit Close.

Tuning

Sizing the cache

PgDoorman's prepared-statement cache has three layers, governed by three related config knobs:

• prepared_statements_cache_size (default 8192) sizes the pool-level shared cache — one map per pool, keyed by query hash. This is the upper bound on distinct query shapes the pool will remember across all clients. Approximate LRU; eviction is O(N) over the whole map and never sends Close to a backend (other clients may still hold the Arc).
• server_prepared_statements_cache_size (default: inherits from prepared_statements_cache_size) sizes the per-backend cache — one LRU per backend connection, keyed by DOORMAN_<N> name. This is the upper bound on distinct prepared statements PgDoorman will let a single PostgreSQL backend hold. True LRU (O(1)); eviction queues a Close message for the backend, sent on the next Sync or Flush — your pg_prepared_statements view may temporarily show more rows than the cap until the next Sync arrives.
• client_anonymous_prepared_cache_size (default: inherits from prepared_statements_cache_size) sizes the per-client Anonymous LRU. Set to 0 to disable the LRU and use an unlimited map; set to a number to bound the per-client cache independently of the pool size.

The pool-level and server-level knobs accept per-pool overrides:

general:
  prepared_statements_cache_size: 8192
  server_prepared_statements_cache_size: 1024  # tighter per-backend

pools:
  oltp:
    # inherits both from general
    pool_mode: "transaction"
  reporting:
    # this pool has wider query diversity; let server cache hold more
    server_prepared_statements_cache_size: 4096
    pool_mode: "transaction"

Setting prepared_statements: false disables the entire remap and forces the pool-level and server-level caches to 0. Setting server_prepared_statements_cache_size: 0 while leaving the pool size positive is allowed but rarely useful — the per-backend cache becomes a pass-through that re-Parses on every cross-backend hit.

When to lower server_prepared_statements_cache_size below the pool size:

• Backends carry too many DOORMAN_<N> rows (pg_prepared_statements near the cap, plan memory ballooning).
• You want faster Close recycling without shrinking pool-cache hit rate.

When to keep them equal (the default):

• You don't have a measured backend-memory problem. Leave the inheritance.

Sizing client_anonymous_prepared_cache_size

When unset, the per-client Anonymous LRU inherits the resolved prepared_statements_cache_size for the pool (default 8192). Set an explicit value to override that inheritance — 0 disables the LRU and uses an unlimited map, any positive number caps the LRU at that size.

Each entry holds a lightweight (hash, async_name?, Arc<Parse>) record — the Arc<Parse> is shared with the pool-level cache, so the per-client overhead is roughly ~80 bytes of bookkeeping per entry. At 10 000 connected clients × 256 entries × ~80 bytes that adds up to about 200 MB of headroom on the pooler — predictable and bounded.

Raise the cap when:

• An ORM or generated SQL framework mints stmt_<seq> per query and the Anonymous LRU keeps recycling entries (visible as a sustained non-zero rate on pg_doorman_clients_prepared_anonymous_evictions_total).
• The application has a known wide working set per session and the eviction rate matches that pressure.

Lower the cap for very large connection counts (50 000+ clients): at that scale clients × cache_size × 80 bytes of pooler bookkeeping can cross 1 GB, and trimming the cap halves it. max_memory_usage does not cap prepared-statement bookkeeping; it protects in-flight query buffers.

Named is unbounded by design

The Named part of the per-client cache has no upper bound. PgDoorman holds the Arc<Parse> for every named statement the client created until the client disconnects or sends DEALLOCATE / DEALLOCATE ALL. This matches PostgreSQL's own contract — named statements live for the session — and avoids the failure mode where evicting a Named entry under pressure causes the next Bind to fail with prepared statement does not exist.

The flip side: drivers that mint per-query named statements (some pgjdbc and Hibernate flows, some .NET Npgsql configurations) can grow the per-client Named map without limit. PgDoorman cannot bound this safely; the application is responsible for either reusing names or sending DEALLOCATE on names it no longer uses.

The Anonymous LRU eviction counter (pg_doorman_clients_prepared_anonymous_evictions_total) is the only side that has a built-in pressure signal. The Named side has none — watch the client_named_count column in SHOW POOLS_MEMORY and pg_doorman_clients_prepared_named_entries for unexpected growth.

Backend memory creep window

When the Anonymous LRU evicts an entry on the client side, PgDoorman drops only the local Arc<Parse>. The corresponding DOORMAN_<N> prepared statement stays alive on every PostgreSQL backend that ever served it. Two mechanisms eventually clean it up:

• Server-level LRU. Each backend tracks its own LruCache<String, ()> of DOORMAN_<N> names, capped at server_prepared_statements_cache_size (or prepared_statements_cache_size when unset). When the cap is reached, the backend issues Close on the least recently used name, releasing the plan.
• Backend rotation. A backend reaches server_lifetime (default 20 min) and pg_doorman closes it; the new backend starts with an empty plan cache.

The worst-case memory footprint per backend is therefore server_prepared_statements_cache_size × average plan memory (8192 × ~100 KB is about 800 MB) on the PostgreSQL side. To shrink the window:

• Lower server_prepared_statements_cache_size so the server-level LRU recycles plans sooner.
• Lower server_lifetime so backends rotate faster.

The PostgreSQL system view pg_prepared_statements reports the names held by the current backend. Counting rows there per backend tells you how close the backend is to the cap.

Observability

Admin commands:

• SHOW PREPARED_STATEMENTS — pool, hash, name, query text, count_used, kind. Top rows by count_used show the hot queries that benefit most from the cache. The kind column is the last column and reports named, anonymous, or mixed depending on how clients have used the entry over its lifetime.

  Example output:

   pool         | hash               | name        | query             | count_used | kind
  --------------+--------------------+-------------+-------------------+------------+-----------
   sharded.user | 1234567890123456   | DOORMAN_1   | SELECT * FROM t1  |     150234 | anonymous
   sharded.user | 2345678901234567   | DOORMAN_2   | INSERT INTO t2 .. |      87654 | named
   sharded.user | 3456789012345678   | DOORMAN_3   | SELECT * FROM t3  |      45678 | mixed
  

• SHOW POOLS_MEMORY — pool_prepared_count, client_prepared_count, pool_prepared_bytes, client_prepared_bytes, plus the breakdown by kind: client_named_count, client_anonymous_count, client_anonymous_evictions_alive. The last column sums the per-client eviction counters across the currently connected clients only — disconnected clients drop out of the sum, so this column is not monotonic. For the cumulative counter, scrape pg_doorman_clients_prepared_anonymous_evictions_total from the Prometheus surface instead.

Prometheus metrics (full list in Prometheus):

• pg_doorman_pool_prepared_cache_entries{user, database}
• pg_doorman_pool_prepared_cache_bytes
• pg_doorman_clients_prepared_cache_entries
• pg_doorman_clients_prepared_cache_bytes
• pg_doorman_clients_prepared_named_entries{user, database}
• pg_doorman_clients_prepared_anonymous_entries{user, database}
• pg_doorman_clients_prepared_anonymous_evictions_total{user, database}
• pg_doorman_servers_prepared_hits{user, database}
• pg_doorman_servers_prepared_misses{user, database}
• pg_doorman_servers_prepared_hits_total{user, database}
• pg_doorman_servers_prepared_misses_total{user, database}
• pg_doorman_async_clients_count

Use the _total metrics for rate() and alerting. The non-_total server metrics are live backend aggregates and can drop when backends rotate.

Alerting

Anonymous LRU eviction rate

A sustained non-zero rate on the Anonymous eviction counter means the LRU is recycling entries faster than the application reuses them. Alert template:

rate(pg_doorman_clients_prepared_anonymous_evictions_total[5m]) > 10
  for 10m

The threshold of 10 evictions/second per pool is a starting point — the right value depends on traffic shape and connection count. Treat the alert as "the cap is too tight or the application's working set is wider than expected", then either raise client_anonymous_prepared_cache_size or investigate whether the application is generating unique queries on the hot path.

kind = mixed interpretation

Each pool-level cache entry remembers whether clients have used it under a Named statement name, an Anonymous one, or both. kind = mixed means the same (query, param_types) pair has been parsed by at least one client as named and at least one client as anonymous in its current lifetime. Most workloads do not see mixed rows; a pool dominated by mixed entries indicates a heterogeneous client base (different drivers or driver configurations against the same database) worth verifying — sometimes intentional, sometimes a sign that one of the clients is configured wrong.

Backend prepared statement count

PostgreSQL exposes pg_prepared_statements per backend. If pooler memory is fine but PostgreSQL backend RSS keeps growing, count rows per backend:

SELECT count(*) FROM pg_prepared_statements;

Numbers near server_prepared_statements_cache_size per backend mean the server-level LRU is at its cap. Backend rotation is the other mechanism that releases plan memory. If the server cache inherits prepared_statements_cache_size, use that value as the cap. Lowering the server cap or server_lifetime releases plan-memory pressure at the cost of more frequent re-Parses on the backend.

Bounded query interner

The pool-level interner that deduplicates Parse query texts is split into two halves:

• NAMED — text for named prepared statements. An entry stays alive as long as any pool or client cache holds an Arc<str> reference. The GC task collects entries when nothing outside the interner holds a reference any more, with a two-cycle grace period to avoid thrash on cold-but-still-needed hashes.
• ANON — text for anonymous prepared statements. An entry expires after query_interner_anon_idle_ttl_seconds of idle time (default 60 seconds). Setting the knob to 0 disables TTL eviction — the pre-3.7 unbounded behaviour, kept as an escape hatch for legacy deployments.

If an anonymous Bind or Describe arrives after pg_doorman has lost the matching anonymous prepared-statement state, pg_doorman returns ERROR: unnamed prepared statement does not exist (SQLSTATE 26000). Common causes are client Anonymous LRU eviction, RESET INTERNER, interner TTL eviction, or a driver pattern that reuses unnamed prepared statements across batches. This is the same error native PostgreSQL raises for the same condition; standard drivers handle it transparently by re-issuing Parse.

Binary upgrade (SIGUSR2) carries both NAMED and ANON entries to the new process. Anonymous entries land in the new ANON interner with a fresh last_used timestamp, so the TTL clock starts over at the upgrade moment.

Operator surface

SHOW INTERNER (admin SQL) prints aggregate counts and bytes per kind:

kind      | entries | bytes
named     |     420 |    87654
anonymous |    1337 |   234567

SHOW INTERNER N returns the top N entries by interned text length with hash, kind, bytes, idle_ms (-1 for named — the named half tracks GC state, not last-used timestamps), and a 120-character preview of the SQL.

RESET INTERNER clears both halves. In-flight clients re-Parse on next reuse — diagnostics-only.

The Prometheus surface mirrors SHOW INTERNER plus a histogram for sweep duration and a counter for the synthetic 26000s. Raise query_interner_anon_idle_ttl_seconds only when synthetic misses correlate with anonymous TTL evictions or a known cross-batch unnamed statement pattern. If misses correlate with pg_doorman_clients_prepared_anonymous_evictions_total, increase client_anonymous_prepared_cache_size instead.

Reference

• Pool Modes — transaction mode, where prepared-statement remapping is enabled.
• General Settings — prepared_statements_cache_size, server_prepared_statements_cache_size, client_anonymous_prepared_cache_size, query_interner_gc_interval_seconds, query_interner_anon_idle_ttl_seconds.
• Admin Commands — SHOW PREPARED_STATEMENTS, SHOW POOLS_MEMORY, SHOW INTERNER, RESET INTERNER.
• Prometheus — full metric list.

PostgreSQL startup parameters

Use startup_parameters when a pool needs PostgreSQL GUC defaults at backend startup and you do not want to change postgresql.conf, ALTER ROLE, or ALTER DATABASE.

• A hot OLTP pool gets stuck on a generic plan after the plan_cache_mode = auto heuristic flips. Setting force_custom_plan on the role would affect every workload using that role; setting it on one pool keeps the change local.
• An application that does not set its own statement_timeout or idle_in_transaction_session_timeout and cannot be patched fast enough. The DBA needs a server-side default that survives the application's own session resets.
• A single application that should announce a stable application_name regardless of what the connecting driver negotiates, so pg_stat_activity and audit logs stay legible.

Configuration

Values apply in three layers. The more specific layer wins per key:

[general.startup_parameters]
statement_timeout = "5s"

[pools.checkout.startup_parameters]
plan_cache_mode = "force_custom_plan"
work_mem        = "64MB"

After SIGHUP (or RELOAD on the admin console) every new backend for the checkout pool starts with statement_timeout = 5s, plan_cache_mode = force_custom_plan, and work_mem = 64MB. Other pools keep statement_timeout = 5s from general and the PG default for the rest. Already-open backends are not affected; the change takes hold as the pool rotates connections.

When auth_query runs in passthrough mode (no server_user), the lookup SQL may return an optional startup_parameters text column holding a JSON object. Values from that column override both general and per-pool settings for that user only:

SELECT
  rolpassword AS passwd,
  CASE rolname
    WHEN 'vip' THEN '{"work_mem":"256MB"}'::text
    ELSE NULL::text
  END AS startup_parameters
FROM pg_authid
WHERE rolname = $1;

The column may be text, json, or jsonb; pg_doorman dispatches by the column type without requiring a cast. The content must be a JSON object whose values are strings. Other PostgreSQL types (or a custom domain on top of jsonb) log a warning and the per-user overlay is ignored.

Dedicated auth_query mode (server_user set) ignores the per-user column and logs once per (pool, username): one shared backend serves many users, so a per-user override cannot apply.

Changes to a per-user startup_parameters row apply to new backend connections, but only after pg_doorman re-reads the row. The auth_query cache holds positive entries for auth_query.cache_ttl (default one hour) and on a refresh detects the overlay change and drops the dynamic pool so the next login rebuilds it against the new values. Until the cache entry expires, reconnecting clients still see the old overlay. To force an immediate rollout: lower cache_ttl and reload the config, restart pg_doorman, or wait for the TTL to elapse. Backends that are already checked out keep the values captured when their pool was created.

What pg_doorman does with the values

pg_doorman adds the resolved parameter set to the PostgreSQL StartupMessage for each new backend. PostgreSQL records each value as the session default for that setting (pg_settings.reset_val and pg_settings.source = 'client'), so client-side RESET ALL and DISCARD ALL return to the configured value. Operators get a stable session default without editing postgresql.conf or running ALTER ROLE.

The values can be observed from the client:

checkout=> SHOW plan_cache_mode;
 plan_cache_mode
-------------------
 force_custom_plan

checkout=> SET plan_cache_mode = 'auto'; RESET ALL; SHOW plan_cache_mode;
 plan_cache_mode
-------------------
 force_custom_plan

Validation

At config load:

• Keys must match PG GUC naming ^[A-Za-z_][A-Za-z0-9_.]*$. Namespaced names like auto_explain.log_min_duration are accepted; arbitrary punctuation is not.
• Reserved keys (user, database, replication, options, role, session_authorization, and anything starting with _pq_.) are refused. pg_doorman manages them itself or PG treats them specially in the StartupMessage.
• Values must not contain null bytes.
• Each level (general or per-pool) must fit within the startup-parameter budget: MAX_STARTUP_PACKET_LENGTH (10 000 bytes) minus 512 bytes reserved for pg_doorman-managed keys.

Before each backend start, pg_doorman checks the resolved parameter set against the same cap. Layers that fit individually can exceed the limit after merging: general + pool can already be too large, and an auth_query row can push a valid baseline over the limit. Any overflow now returns a PostgreSQL-style error (SQLSTATE 53400) to the client instead of sending a partial or empty StartupMessage. The warning log records the byte counts, and pg_doorman_startup_parameters_dropped_total increments for each rejected backend start.

What happens when PG rejects a parameter

If PostgreSQL rejects a configured parameter at backend startup, pg_doorman returns PostgreSQL's ErrorResponse to the client unchanged. The client sees the same sqlstate (22023, 42704, 42501, 55P02, or any other code under the startup family) and the same message it would have seen when connecting to PostgreSQL directly.

pg_doorman does not retry with the parameter removed and does not automatically disable that key for the pool. The next client connection sends the same StartupMessage and gets the same error until the operator fixes the config.

Observability

The admin SQL console shows the resolved parameters for each pool:

admin> SHOW STARTUP_PARAMETERS;
 user | database | parameter         | value             | source  | state
------+----------+-------------------+-------------------+---------+--------
 shop | checkout | plan_cache_mode   | force_custom_plan | pool    | applied
 shop | reports  | statement_timeout | 10s               | general | applied

The Web UI shows the same rows on the pool detail page in the "Startup parameters (configured)" section.

Prometheus exports counters for both failure points:

• pg_doorman_backend_startup_parameter_errors_total{pool, sqlstate} counts every backend startup PostgreSQL rejected because of an configured parameter. The failing parameter name and username are written to the warning log line, not to metric labels.
• pg_doorman_startup_parameters_dropped_total{pool, reason} counts parameter sets pg_doorman dropped before sending StartupMessage.

Alert when pg_doorman_backend_startup_parameter_errors_total keeps growing for the same pool for several minutes. That usually means new backend startups for the pool are failing on the same configured GUC.

When not to use this

• The application already sets the parameter on every connection. Duplicating the value in startup_parameters adds another config path and does not change runtime behavior.
• Per-transaction tuning (SET LOCAL). startup_parameters is for session defaults; transaction-scoped tuning belongs in the application.
• Anything that needs to depend on which query the application is running. Startup parameters apply to every transaction on every backend for the lifetime of that backend; there is no per-statement variant.

Reference

• General Settings: startup_parameters.
• Pool Settings: pools.<name>.startup_parameters.
• auth_query: passthrough vs dedicated modes, where the startup_parameters column is read.
• Admin Commands: SHOW STARTUP_PARAMETERS.
• Prometheus: full metric list.

Pool pressure

Pool pressure is how pg_doorman handles many clients asking for a backend connection at the same time when the idle pool is empty. Two mechanisms decide who gets a connection, who waits, who triggers a fresh backend connect, and who is rejected: per-pool anticipation + bounded burst inside each (database, user) pool, and the cross-pool coordinator that caps total backend connections per database.

Audience: DBA or production operator who already knows PgBouncer and wants to understand how pg_doorman differs and what to watch.

Why pool pressure exists

Take a pool with pool_size = 40 and a workload of 200 short transactions arriving in the same millisecond. The pool has 4 idle connections. In a naive pooler the first 4 clients pick the idle connections, and the remaining 196 each independently call connect() against PostgreSQL. PostgreSQL receives 196 simultaneous TCP connect attempts, each followed by SCRAM authentication and parameter negotiation, only to discover that the pool allows 36 more. Backend pg_authid lookups spike, the max_connections ceiling is hit, the kernel accept() queue saturates, and tail latency for already-connected clients climbs because the PostgreSQL postmaster is spawning backends instead of running queries. This is the thundering herd problem.

Time:  ----------------------------------------->

Client_1   -[idle hit]--[query]-----[done]
Client_2   -[idle hit]--[query]-----[done]
Client_3   -[idle hit]--[query]-----[done]
Client_4   -[idle hit]--[query]-----[done]
Client_5   -[connect]-[auth]-[query]-[done]
Client_6   -[connect]-[auth]-[query]-[done]
   .             ^
   .             196 backend connect()s
   .             fired in the same instant
Client_200 -[connect]-[auth]-[query]-[done]

PostgreSQL: 196 spawning backends + 4 running queries

Pool pressure suppresses this. pg_doorman makes most of those 196 callers reuse a connection that another client is about to release, or wait a few milliseconds behind a small number of in-flight backend connects. The connect() rate against PostgreSQL stays bounded even when client arrival is bursty.

Plain pool mode

This runs when max_db_connections is not configured. Pools are independent, no cross-pool coordination, and pressure is managed inside each (database, user) pool. This is the default, and most deployments live here.

Pool growth from cold

A pool with pool_size = 40 and min_pool_size = 0 starts with zero connections. The first client to arrive does not wait: pg_doorman creates a backend connection immediately. The second does the same, the third does the same, until the pool reaches the warm threshold.

The warm threshold is pool_size × scaling_warm_pool_ratio / 100. With the default ratio of 20% and pool_size = 40, the threshold is 8 connections. Below it, pg_doorman creates connections without hesitation: the pool is cold, the cost of a wait is higher than the cost of a connect, and clients cannot contend for idle connections that do not exist.

Above the threshold, the anticipation zone activates. When a client misses the idle pool, pg_doorman first tries to catch a connection that another client is about to return.

A third zone overlays both: at any pool size, if inflight_creates reaches scaling_max_parallel_creates (default 2), the pool enters the burst-capped state for new creates. Additional callers wait for a slot regardless of how many idle connections exist.

                        Three pressure zones
                        --------------------

Pool size:  0 ----------- 8 ---------------------------- 40
            ^             ^                              ^
            |             |                              |
            |  WARM ZONE  |  ANTICIPATION ZONE           |
            |             |                              |
            |  size <     |  size >= warm_threshold      |
            |  warm_thr   |                              |
            |             |                              |
            |  Skip       |  Phase 3: fast spin          |
            |  phases 3   |  Phase 4: direct handoff     |
            |  and 4.     |   (oneshot channel, bounded  |
            |  Go straight|    by query_wait_timeout     |
            |  to phase 5 |    minus 500 ms reserve)     |
            |  (burst gate|  Then phase 5                |
            |  + connect) |                              |

                  Burst-capped state (orthogonal)
                  -------------------------------

inflight_creates: 0 ---- 1 ---- 2 (= scaling_max_parallel_creates)
                                ^
                                |  At cap: any caller reaching the
                                |  burst gate registers a handoff
                                |  waiter and listens for a peer
                                |  create completion.

The warm/anticipation zones track current pool size. The burst-capped state tracks concurrent backend creates. A pool can be in the anticipation zone and the burst-capped state at the same time; this is the common case under load. A pool below the warm threshold can also hit the burst cap if many clients arrive at once during cold-start fill.

Acquiring a connection

When a client requests a connection through pool.get(), pg_doorman walks through the following phases. Each phase either returns a connection or hands off to the next phase.

Phase 1 — Hot path recycle. Pop the front of the idle queue. If a connection is there and passes the recycle check, return it. The recycle check rolls back any open transaction, runs a liveness probe if the connection has been idle longer than server_idle_check_timeout, and verifies that the connection's reconnect epoch matches the pool's current epoch. The pool bumps its reconnect epoch on the RECONNECT admin command and after detected backend failures; connections from before the bump fail this check and are dropped instead of being returned. A healthy steady-state pool only takes this path. Cost: a mutex acquire and the recycle check.

Phase 2 — Warm zone gate. If the pool size is below the warm threshold, skip anticipation and jump straight to creating a new backend connection. Cold pools fill fast.

Phase 3 — Anticipation spin. Above the warm threshold, retry the recycle 10 times in a tight yield_now loop (controlled by scaling_fast_retries). This catches the case where another client finished its query in the same microsecond range and is about to push the connection back. Total cost is around 10–50 microseconds. No sleep, no blocking I/O.

Phase 4 — Direct handoff. If the spin did not catch a return, register a oneshot channel in a per-pool waiters queue (a VecDeque inside Slots). When any client returns a connection via return_object(), the returned connection is sent directly through the oldest registered oneshot channel, bypassing the idle VecDeque entirely. The waiter receives the connection without racing any other task — there is no contention with Phase 1/2 semaphore waiters because the connection never enters the idle queue.

If the oneshot receive succeeds, the connection goes through a recycle check (recycle_handoff). On recycle success the connection is returned to the caller. On recycle failure (stale backend), the pool decrements slots.size and the caller falls through to the create path.

If no connection arrives before the deadline, the oneshot receiver is dropped. return_object detects the dropped receiver (send returns Err), skips the stale waiter, and tries the next one in the queue. This way timed-out waiters are cleaned up lazily without a separate garbage-collection pass.

The deadline is adaptive: min(query_wait_timeout - 500 ms, adaptive_cap) where adaptive_cap is derived from real transaction latency:

The budget is measured against a timestamp captured at the top of timeout_get. Phase 1/2 semaphore wait consumes from the same budget, so the cumulative wait across phases cannot exceed the caller's query_wait_timeout.

The ±20% jitter prevents a timeout cliff: without it, N clients that entered Phase 4 at the same instant all exit simultaneously and stampede into the burst gate, creating N new backend connections for a pool that needs far fewer. With jitter, clients exit in staggered batches — early exiters create connections, and by the time later exiters time out, those connections have already been used and returned to the idle queue for recycling.

Phase 5 — Bounded burst gate. Try to take one of scaling_max_parallel_creates slots (default 2) for in-flight backend connects. If a slot is free, take it and call connect() against PostgreSQL. If all slots are full, register a direct-handoff oneshot waiter and also listen for create_done (another in-flight create finishing). The select! uses biased; to always check the oneshot first, preventing a race where create_done or the 5 ms backoff timer wins and silently drops the delivered connection. If a connection arrives via the oneshot channel, recycle it and return. Otherwise, re-try the recycle and the gate after the wake.

Phase 6 — Backend connect. Run connect(), authenticate, hand the connection to the client. The burst slot is released automatically when this phase finishes, regardless of success or failure.

                  Plain mode acquisition flow
                  ---------------------------

   pool.get()
       |
       v
   +--------------+
   |  Phase 1:    |  --- HIT ----> return idle connection
   |  recycle pop |
   +------+-------+
          | MISS
          v
   +--------------+
   |  Phase 2:    |  --- below warm ---> jump to phase 5
   |  warm gate   |
   +------+-------+
          | above warm
          v
   +--------------+
   |  Phase 3:    |  --- HIT ----> return idle connection
   |  fast spin   |
   +------+-------+
          | MISS
          v
   +--------------+
   |  Phase 4:    |  --- handoff  ----> return connection
   |  anticipate  |  --- timeout  ----> fall through
   |  direct h/o  |
   +------+-------+
          |
          v
   +--------------+
   |  Phase 5:    |  --- slot taken --> proceed to phase 6
   |  burst gate  |  --- slot full  --> wait, retry recycle
   +------+-------+
          |
          v
   +--------------+
   |  Phase 6:    |
   |  connect()   | ----> return new connection
   +--------------+

Burst suppression in action

The same 200-client thundering herd scenario, this time with plain mode and scaling_max_parallel_creates = 2:

Time:   t=0ms     t=5ms    t=10ms   t=15ms   t=20ms   t=25ms

C_1     [idle]--[query]-[done]
C_2     [idle]--[query]-[done]
C_3     [idle]--[query]-[done]
C_4     [idle]--[query]-[done]
C_5     [spin/wait]------[recycled C_1]--[query]-[done]
C_6     [spin/wait]------[recycled C_2]--[query]-[done]
C_7     [gate=1]-[connect]----[auth]--[query]-[done]
C_8     [gate=2]-[connect]----[auth]--[query]-[done]
C_9     [gate full, wait]---[recycled C_3]--[query]
C_10    [gate full, wait]---[recycled C_4]--[query]
  .
  .     [...196 clients use a mix of recycle, anticipation, and at
  .      most 2 in-flight connects...]
  .
C_200   [gate=2]-[connect]--[auth]--[query]--[done]

PostgreSQL: at most 2 spawning backends at any moment
            + the 4 connections that were already there

The same pool serves all 200 clients, but PostgreSQL never sees more than scaling_max_parallel_creates (default 2) concurrent backend spawns from this pool. Most clients land on a recycled connection from a peer that finished moments earlier, not a fresh connect().

Non-blocking checkout

When a client sets query_wait_timeout = 0 it asks for either an immediate idle hit or a fresh connect, with no waiting. The anticipation phase and the burst-gate wait are both skipped. pg_doorman runs the hot-path recycle, tries the burst gate once, then either creates a connection or returns a wait timeout error.

Limitation when the coordinator is enabled. Non-blocking only skips the anticipation and burst-gate waits inside the per-pool path. If max_db_connections is configured and the coordinator's wait phases (B–D) take time, a non-blocking caller still blocks inside coordinator.acquire() for up to reserve_pool_timeout (default 3000 ms) before returning. For a strict zero-wait deadline on coordinator-managed databases, set reserve_pool_timeout low enough to fit your tolerance.

Background replenish

When min_pool_size is set, a background task periodically tops up the pool to its minimum. It uses the same burst gate as client traffic. It does not queue behind a busy gate: it gives up immediately and retries on the next retain cycle (default every 30 seconds, controlled by retain_connections_time).

The reasoning: during a load spike, clients are already saturating the gate creating connections they need right now. Having the replenish task fight them for slots buys nothing; client-driven creates will lift the pool above min_pool_size anyway. The replenish_deferred counter increments each time the background task backs off this way.

Consequence: min_pool_size is best-effort under load. For a hard floor, see the troubleshooting section.

Direct handoff on return

When a connection is returned, return_object first checks the direct-handoff waiters queue inside Slots. If at least one waiter is registered, the connection is sent through the oldest oneshot channel, bypassing the idle VecDeque and the semaphore entirely. The waiter already holds a semaphore permit, so no add_permits call is needed. Waiters whose receiver has been dropped (the caller timed out) are skipped: send returns Err with the connection, and return_object tries the next waiter in the queue.

If no waiters are registered (the common case at high throughput where every checkout hits the hot path), the connection is pushed into the idle VecDeque and semaphore.add_permits(1) wakes a Phase 1/2 waiter as before.

In both cases, the coordinator (if configured) is notified via notify_return_observers so peer-pool Phase C waiters can scan for eviction candidates. Same-pool waiters never park on a Notify — they receive connections directly through the oneshot channel.

FIFO fairness and latency distribution

The waiters queue is a VecDeque. push_back on registration, pop_front on delivery. The oldest waiter always gets the next returned connection.

This produces a measurably different latency shape from poolers that use broadcast-notify or LIFO scheduling. With 500 clients sharing a 40-connection pool on AWS Fargate:

Odyssey's p50 is 11x lower than pg_doorman's — most transactions hit a hot connection immediately. But its p99 is 2x higher. Some clients wait over 22 ms while others finish in under 1 ms. Under FIFO, every client pays roughly the same queue cost.

Why this matters for operations:

• SLO compliance. An SLO of "p99 < 15 ms" is achievable with pg_doorman at this load. With Odyssey, the same pool configuration violates it. The only fix is overprovisioning — adding connections until even the unlucky clients finish fast enough.

• No starvation. Under broadcast-notify, a client can lose the wake-up race repeatedly. With direct handoff, the connection goes to exactly one recipient and skips stale waiters. No thundering herd, no repeated race losses.

• Predictable capacity planning. When p50 ≈ p99, doubling the client count roughly doubles latency. With a 25x tail ratio, load changes produce unpredictable p99 spikes.

Queueing theory confirms this: among non-preemptive scheduling disciplines, FIFO minimises wait-time variance while keeping the same mean wait as LIFO. The mean is identical — the difference is entirely in the tail.

Pre-replacement for lifetime expiry

When server_lifetime is configured, backend connections are closed after reaching their individual lifetime limit (base ± 20% jitter). Closing a connection means the pool has one fewer idle backend — subsequent checkouts may enter the anticipation phase or create path, adding several milliseconds to p99 during lifetime expiry clusters.

Pre-replacement removes this spike. When a checkout recycles a connection whose age has reached 95% of its lifetime, a background task creates a replacement connection and places it in the idle queue. When the old connection eventually fails recycle at 100% lifetime, the next checkout finds the pre-created replacement via the hot path — zero wait.

Up to 3 concurrent pre-replacements may run per pool. During the overlap window the pool temporarily holds max_size + 3 connections and a matching number of extra semaphore permits. When old connections die, slots.size drops back to max_size.

Guards that prevent runaway growth:

Pre-replacement only fires on the checkout path (try_recycle_one), not from the retain loop. Idle connections that expire without being checked out are closed by the retain loop without replacement — this is how the pool shrinks naturally when load drops.

Sizing the cap against PostgreSQL

Before reading about the coordinator, check that your worst-case backend connection count fits PostgreSQL. Without max_db_connections set, the worst case for one database is:

N pools (users) × pool_size  =  ceiling on backend connections

Worked example: three pools, pool_size = 40 each, no max_db_connections. Worst case is 120 simultaneous backend connections to that database, throttled only by scaling_max_parallel_creates per pool (default 2 each, so up to 6 concurrent connect() calls in flight). If PostgreSQL is configured with max_connections = 100, the database refuses new connections during a workload-wide spike and clients see FATAL: too many connections.

Two fixes:

• Lower pool_size so N × pool_size fits below max_connections, with margin for superuser_reserved_connections, replication slots, and any direct connectors that bypass pg_doorman.
• Set max_db_connections to enforce a hard cap (next section).

Rule of thumb: keep aggregate pg_doorman demand at most 80% of PostgreSQL max_connections - superuser_reserved_connections. The remaining 20% is headroom for admin connections, replication, and burst.

Coordinator mode

Coordinator mode activates when you set max_db_connections on a pool. It adds a second pressure layer above the per-pool one: a shared semaphore that caps total backend connections to a database across all user pools serving it. Without it, the N × pool_size ceiling from the previous section is the only limit. With max_db_connections = 80, only 80 can exist at once regardless of pool configuration, and the coordinator decides which pools may grow.

When max_db_connections = 0 (the default), the coordinator does not exist. When set, every plain-mode mechanism described above still runs; the coordinator adds a single permit acquisition step on the new-connection path. Idle reuse never touches the coordinator.

What the coordinator adds

Three things:

1. A hard cap on total connections per database. If 80 are in use, the 81st request waits or fails, regardless of which pool asks.

2. A reserve pool. When the cap is reached and reserve_pool_size has room, the coordinator grants a permit from the reserve immediately — a small extra pool above max_db_connections that acts as a burst buffer. This is Phase R (reserve-first) in the acquisition flow below: no peer backend is closed, no wait is incurred. The reserve is bounded by reserve_pool_size (default 0, meaning disabled) and prioritised: starving users (those below their effective minimum) and users with many queued clients are served first by the arbiter.

3. Eviction. Fallback when the reserve is either disabled (reserve_pool_size = 0) or already fully used: the coordinator closes an idle connection from a different user's pool to free a main slot. Candidates are sorted by p95 transaction time (descending): slow pools donate first because they tolerate the re-create cost better (1 ms of pool wait adds 6.7% to a 15 ms p95 but 104% to a 0.96 ms p95). Spare count above effective minimum is the tiebreaker among pools with similar p95. Only connections older than min_connection_lifetime (default 30 000 ms) are eligible. The 30-second floor suppresses cyclic reconnect between peer pools that take turns stealing slots from each other.

   The effective minimum for a user pool is max(user.min_pool_size, pool.min_guaranteed_pool_size). Both knobs protect connections from eviction; whichever is larger wins. Lowering either drops the floor.

Coordinator acquisition phases

When the per-pool path reaches the new-connection step, the coordinator walks six phases. The first phase that hands back a permit ends the sequence.

Phase A — Try-acquire. Non-blocking semaphore acquire. If the cap is not reached, take the slot and return.

Phase R — Reserve-first. Phase A proved the database is full. Before closing any peer backend, the coordinator checks whether the reserve pool has headroom (reserve_in_use < reserve_pool_size). If yes, it asks the reserve arbiter for a permit directly. On success, the caller gets a reserve permit — no eviction, no peer backend closed, no wait on connection_returned. The arbiter responds in sub-millisecond time under normal load.

Reserve-first is the p99-latency path: a reserve permit costs one arbiter round-trip, while the old flow (Phase B + Phase C) could block for the full reserve_pool_timeout even when the reserve had empty slots. Phase R does not run when reserve_pool_size = 0, and falls through to Phase B when the arbiter denies the grant (every reserve permit is already in use, or the arbiter is racing another caller).

Phase B — Eviction. Reached when Phase R did not hand back a permit: either reserve_pool_size = 0, or the reserve semaphore was fully in use at the check (reserve_in_use == reserve_pool_size), or the arbiter denied the grant. Walk all other user pools for the same database, sort by p95 transaction time (descending, slow pools first) with spare count as tiebreaker, and close one idle connection older than min_connection_lifetime from the top candidate. The evicted permit drops synchronously, freeing the slot. Re-try the semaphore acquire. If two callers race, the loser falls through to the next phase. The p95 value is cached every 15 seconds (stats cycle) as an atomic, so the eviction scan reads one AtomicU64 per candidate without locking the histogram.

Phase C — Wait. Reached when reserve is disabled or fully in use and Phase B found nothing evictable. Register a Notify woken on two events:

1. A CoordinatorPermit was dropped — a peer's server connection was physically destroyed (server_lifetime expiry, recycle error, RECONNECT), and a semaphore slot is now free.
2. A peer pool returned a connection to its idle queue via Pool::return_object — the slot is NOT free, but the peer's spare_above_min may have just grown.

On every wake, Phase C runs try_acquire first and only calls try_evict_one if the cheap path fails. A permit-drop wake leaves a free slot in the semaphore — the cheap path takes it and no peer backend is closed. An idle-return wake does not free a slot directly but may have grown a peer's spare_above_min, so the eviction retry finds a candidate that was not evictable a moment ago, drops the peer's permit, and the subsequent try_acquire succeeds. This ordering (cheap first, evict second) is pinned by a regression test so a future refactor cannot re-introduce peer closes on permit-drop wakes.

Wait up to reserve_pool_timeout (default 3000 ms) for a wake or the deadline. This timeout applies even when reserve_pool_size = 0: it is the wait-phase budget, not just the reserve gating window. If your query_wait_timeout is shorter than reserve_pool_timeout, the client gives up first and you see wait timeout errors instead of the more diagnostic all server connections to database 'X' are in use. See troubleshooting for the symptom.

Phase D — Reserve retry. Phase R already tried this path once. Phase D runs again after Phase C exhausted its wait budget, in case a peer reserve holder dropped its permit during the wait. Requests are scored by (starving, queued_clients) so users that need connections most get them first. The arbiter is a single tokio task that drains reserve permits from a priority heap.

Phase E — Error. If Phase D also fails or reserve is not configured, the client receives an error: all server connections to database 'X' are in use (max=N, ...).

Reserve → main upgrade (retain task)

Reserve permits are a burst buffer, not persistent state. Once a burst passes, the backend that held a reserve permit stays alive and healthy, but its CoordinatorPermit still counts against reserve_in_use — even when current < max_db_connections leaves free slots in the main semaphore. Without active housekeeping, SHOW POOL_COORDINATOR reports a reserve pool that looks occupied while the real burst capacity is empty, and the next spike has nowhere to grow.

The retain task runs every retain_connections_time (default 30 s) and performs a book-keeping swap: for each pool not under pressure (see definition below), it walks the idle vec and, for every backend still holding a reserve permit, tries to steal a main semaphore permit.

A pool is under pressure when its per-pool semaphore has zero available permits. There is no single column in SHOW POOLS that reports the semaphore state directly, and the observable columns lag the internal state:

• Strong proxy: sv_active == pool_size. Every active server connection holds a permit, so when every server in the pool is active, every permit is taken. This direction is strict.
• Weak proxy: cl_waiting > 0 means at least one client is inside timeout_get, which often means the semaphore is empty — but a client that already grabbed a permit and is parked in Phase 4 anticipation or coordinator Phase C still shows as waiting. Use it as an indicator, not a proof.

The retain task skips pools under pressure for two reasons: upgrading a reserve permit at that moment hands the slot to the waiting client (no effect on reserve_used), and closing a reserve connection would force a fresh connect() in front of that client. Cleanup runs on the next cycle. On success, the reserve permit is released back to the reserve semaphore, reserve_in_use drops by one, and the backend's permit flips from reserve to main. No reconnect, no peer churn — just two atomic operations. The walk stops on the first upgrade failure in a pool because that proves the main semaphore is saturated; no point checking the rest of the pool's idle vec. The same retain cycle then runs close_idle_reserve_connections to close reserve backends that could not be upgraded and have been idle longer than min_connection_lifetime.

Under this scheme, reserve_in_use > 0 means exactly one thing: a burst is actually in flight or finished within the last retain_connections_time. Historical reserve usage converges back to zero as soon as main has headroom.

JIT coordinator permits (burst gate first)

Inside the per-pool acquisition flow, the burst gate runs before the coordinator permit is acquired. This is the JIT (just-in-time) ordering: a coordinator permit is taken only when the caller actually holds a burst gate slot and is about to call connect().

The previous ordering (coordinator first, then gate) caused phantom permits: N callers each acquired a coordinator permit and then queued behind the burst gate (cap=2). Only 2 callers were actually creating connections, but the coordinator saw N permits in use and started issuing reserve permits to peer pools — even though the database was far from full.

With JIT ordering, at most max_parallel_creates callers hold coordinator permits at any instant. The rest wait for a gate slot without consuming coordinator budget.

Head-of-line blocking is avoided by splitting the coordinator acquire into a fast and a slow path. The fast path is a non-blocking try_acquire() inside the gate slot — no time is wasted. If it fails, the caller releases the gate slot, waits on the coordinator (may evict / wait for a peer return), and then re-acquires a gate slot.

        Coordinator + plain mode acquisition flow (JIT)
        -----------------------------------------------

   pool.get()
       |
       v
   Phase 1: hot path recycle   --- HIT ---> return
       | MISS
       v
   Phase 2: warm gate          --- below ---+
       | above warm                         |
       v                                    |
   Phase 3: fast spin          --- HIT ---> return
       | MISS                               |
       v                                    |
   Phase 4: direct handoff     --- HIT ---> return
       | deadline                           |
       v                                    |
       | <----------------------------------+
       v
   Phase 5: bounded burst gate (scaling_max_parallel_creates)
              | slot acquired
              v
   +---------------------------+
   | JIT coordinator acquire   |  only when max_db_connections > 0
   |  fast: try_acquire()      |  non-blocking CAS
   |  slow: release gate slot  |  wait on coordinator (evict/return)
   |        → re-acquire slot  |  then proceed to create
   +------------+--------------+
                | permit granted
                v
   Phase 6: server_pool.create()
                |
                v
                return new connection

The phases are numbered identically to plain mode. The coordinator acquire is not a numbered phase: it runs inside the burst gate slot when max_db_connections > 0. In plain mode it does not run.

When the coordinator is configured but the cap is not reached

If max_db_connections = 80 and current usage is 30, the coordinator's phase A always succeeds. Phases B–E never run. The behaviour is identical to plain mode plus one atomic semaphore increment per new connection. The hot path (idle reuse) does not touch the coordinator at all, so it has no measurable cost there. Only new connection creation does, and only by the duration of one atomic operation.

By design, the coordinator is a cap, not a queue: it costs you only when you bump against the limit.

Background replenish under coordinator

replenish acquires its coordinator permit using try_acquire (non-blocking). If the database is at the cap, replenish gives up and retries on the next retain cycle. Same logic as the burst gate backoff: don't have a background task fight client traffic for scarce permits.

Tuning parameters

The scaling parameters are global by default, with per-pool overrides for scaling_warm_pool_ratio and scaling_fast_retries. scaling_max_parallel_creates is global only; per-pool overrides are not supported.

When to raise scaling_max_parallel_creates

Raise when:

• burst_gate_waits is consistently growing across scrapes and replenish_deferred is also non-zero, meaning client traffic and the background task are both fighting for slots that don't exist;
• backend connect() is fast (< 50 ms) and PostgreSQL has spare max_connections;
• connection latency spikes correlate with burst_gate_waits rate increases.

Hard ceiling. Never raise scaling_max_parallel_creates above either of these limits:

• pool_size / 4 for the smallest pool that uses this setting. Above this, the cap loses meaning: half the pool can be in flight at once, defeating the smoothing.
• (PostgreSQL max_connections - superuser_reserved_connections) / (10 × N pools) where N pools counts all pools sharing this PostgreSQL instance. Above this, the aggregate concurrent connect rate exceeds what the backend can absorb without accept() queue overflow.

Lower when:

• PostgreSQL connect() is expensive (> 200 ms, e.g., SSL with cert verification, or a slow pg_authid lookup);
• pg_authid contention shows up in PostgreSQL logs;
• the backend shows accept() queue overflow.

Symptom of too low: burst_gate_waits rate climbs faster than client arrival rate. Symptom of too high: PostgreSQL connect() latency climbs and the connection storm reappears.

Sizing for many pools. The aggregate concurrent connect ceiling is N pools × scaling_max_parallel_creates. If you operate one PostgreSQL behind 10 pools and want at most 8 concurrent backend connects across all of them at any moment, set scaling_max_parallel_creates to roughly desired_aggregate / N pools, rounding down. Below 1 is not allowed; if the math gives <1, lower N pools by consolidating users.

When to raise scaling_warm_pool_ratio

Raise when:

• pools are slow to warm at startup and min_pool_size is not used;
• clients wait for anticipation when the pool is mostly empty (anticipation only activates above the warm threshold, so this shouldn't happen, but a high ratio narrows the window where it can).

Lower when:

• pools are over-sized and you want anticipation to suppress creates earlier in the size range.

This knob rarely needs touching. The default of 20% works for most workloads.

When to set max_db_connections

Set it when:

• one PostgreSQL host serves multiple (database, user) pools and the sum of pool_size across pools exceeds the database's max_connections;
• you want a hard ceiling that survives misconfiguration of any single pool;
• you want cross-pool fairness via eviction.

Leave it unset when:

• one pool serves one database and pool_size is the whole story;
• you don't want any cross-pool eviction (some workloads prefer hard per-user isolation).

reserve_pool_size and reserve_pool_timeout

The reserve is a temporary overflow valve, not extra steady-state capacity. It prevents client-visible exhaustion errors during brief bursts. Under normal operation reserve_in_use should be 0 most of the time.

Sizing rule of thumb: reserve_pool_size ≤ 0.25 × max_db_connections. Past that ratio the reserve stops behaving like a buffer. If half your workload lives in the reserve continuously, raise max_db_connections instead of extending the overflow.

reserve_pool_timeout is how long a client waits in coordinator phase C before the reserve is consulted. Default 3000 ms is conservative. Lower it if your query_wait_timeout is short and you would rather fall through to the reserve fast than block clients on coordinator wait.

Tuning recipe: bring checkout p99 down on a coordinator-managed database

Workload shape: PostgreSQL answers in ~1 ms (p99 query latency is low), but clients see 100–500 ms p99 checkout latency on a coordinator-managed pool. The checkout time is coming from the coordinator, not PostgreSQL.

1. Confirm the phase. Run SHOW POOL_COORDINATOR during a latency spike. Compute main_used = current - reserve_used — current includes reserve permits, and this recipe hinges on whether the main semaphore alone is full.
   • main_used == max_db_conn and exhaustions not climbing → wait-phase dominated. The client spends its budget in Phase C before falling into Phase D. Continue to step 2.
   • main_used < max_db_conn with no exhaustions → checkout latency is not coming from the coordinator. Check SHOW POOL_SCALING create_fallback and the plain-mode troubleshooting section.
2. Enable reserve-first if it is not already. Set reserve_pool_size to at least max(2, 0.1 × max_db_connections). Reserve-first grants a permit in sub-ms when the reserve has headroom, so a client that used to sit in Phase C now pays one arbiter round-trip.
3. Shorten reserve_pool_timeout to 2 × p99 query latency, never lower. For a 1 ms query the floor is typically 20 ms; start at 50 ms and watch reserve_acq and evictions for a week.
4. Leave min_connection_lifetime at the 30 000 ms default unless you specifically want cross-pool rebalancing to react faster; lowering it increases eviction rate and connection churn.

What to watch after each change (all in SHOW POOL_COORDINATOR):

If checkout p99 does not drop after steps 2–3, the path is not coordinator-bound. Re-read SHOW POOL_SCALING on the affected pool — create_fallback > 0 means the pool itself cannot serve offered load from returns, and the fix is pool_size, not reserve_pool_size.

Floor. Never lower reserve_pool_timeout below 2 × your p99 query latency. Below that floor, the wait phase always times out before a peer returns a connection, and the reserve becomes a required permit for every new connection rather than an overflow valve. Reserve permits are scarce by design; using them as steady state defeats the purpose.

Trap: query_wait_timeout < reserve_pool_timeout. When the client deadline is shorter than the coordinator wait phase, the client gives up first and you see wait timeout errors instead of the more diagnostic all server connections to database 'X' are in use. The coordinator's wait and reserve phases run their full course but no client is left to receive the result. The pg_doorman config validator emits a warning at startup; act on it.

Observability

pg_doorman exposes pool pressure state through the admin console and through Prometheus. Both show the same counters; pick whichever fits your monitoring stack.

Admin: SHOW POOL_SCALING

Per-pool counters for the anticipation + bounded burst path. Connect to the pgdoorman admin database and run:

pgdoorman=> SHOW POOL_SCALING;

All counters are monotonic since startup. Compute deltas between scrapes; absolute values are only useful for ratios.

Admin: SHOW POOL_COORDINATOR

Per-database coordinator state. Only present for databases with max_db_connections > 0.

pgdoorman=> SHOW POOL_COORDINATOR;

Reading SHOW POOL_COORDINATOR output

Three snapshots and what each one means for the operator:

Healthy idle database:

 database | max_db_conn | current | reserve_size | reserve_used | evictions | reserve_acq | exhaustions
----------+-------------+---------+--------------+--------------+-----------+-------------+-------------
 mydb     |          80 |      24 |           10 |            0 |         0 |           0 |           0

Normal steady state. Plenty of headroom, reserve is dormant, no evictions, no exhaustions. Alerts must be silent here.

Post-burst, upgrade in progress:

 database | max_db_conn | current | reserve_size | reserve_used | evictions | reserve_acq | exhaustions
----------+-------------+---------+--------------+--------------+-----------+-------------+-------------
 mydb     |          80 |      65 |           10 |            3 |         0 |          12 |           0

A burst consumed most of max_db_connections and spilled three connections into the reserve. current < max_db_conn means main has headroom, so the retain task will upgrade these three permits to main on its next cycle; reserve_used should drop to 0 within retain_connections_time (default 30 s). If it does not, see the troubleshooting section below. evictions = 0 and reserve_acq > 0 together confirm reserve-first absorbed the burst without closing peer backends.

Sustained overload:

 database | max_db_conn | current | reserve_size | reserve_used | evictions | reserve_acq | exhaustions
----------+-------------+---------+--------------+--------------+-----------+-------------+-------------
 mydb     |          80 |      95 |           20 |           15 |       300 |         500 |           0

Main is full (main_used = current - reserve_used = 80, equal to max_db_conn), reserve is 75% used, evictions are high, and reserve grants are high. The database is not occasionally pressured — it is permanently short of capacity and surviving only because eviction rotates connections between users and reserve-first absorbs every new arrival. exhaustions = 0 means the arbiter still keeps up, but any transient spike tips it over. Action: raise max_db_connections after confirming PostgreSQL has headroom, or find the runaway pool via SHOW POOLS and lower its pool_size.

Prometheus metrics

Two metric families per pool, two per coordinator. All four use pg_doorman_pool_scaling* and pg_doorman_pool_coordinator* namespaces.

Alerts to set

The following alerts cover the failure modes that warrant a page or warn. They're written in Prometheus syntax; adapt to your stack. All use sustained-condition windows so brief bursts do not page the on-call.

If you reload pg_doorman frequently and pools come and go, scope the alerts to recently-active pools (e.g., add pg_doorman_pool_scaling_total{type="creates_started"} > 0 as a gating filter).

Each alert below has a Runbook block with one diagnostic command and two or three branches tied to concrete counter values.

Coordinator exhaustion (page). A client received a "database exhausted" error. Hard failure — reserve and eviction both failed.

rate(pg_doorman_pool_coordinator_total{type="exhaustions"}[5m]) > 0

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_COORDINATOR'
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOLS'

current is the combined main+reserve count (current == max_db_conn + reserve_size means both semaphores are fully drained).

• current == max_db_conn + reserve_size → both semaphores are fully drained. Raise max_db_connections (verify PostgreSQL max_connections has headroom first) or add a larger reserve.
• reserve_size == 0 and current == max_db_conn → reserve is disabled and main is full. Set reserve_pool_size to absorb bursts, then raise max_db_connections if exhaustions keeps firing after that.
• current < max_db_conn + reserve_size but exhaustions climbing → race in Phase R/D — should not happen sustained; file a bug with the matching SHOW POOL_COORDINATOR snapshot.
• One user in SHOW POOLS has sv_idle much larger than others → runaway pool is hoarding connections. Lower that pool's pool_size, or set min_guaranteed_pool_size to protect the victims.

Burst gate saturated (warn). The burst gate is waiting behind other creates more often than it proceeds directly. Brief spikes above the threshold during failover or restart are normal; sustained values mean scaling_max_parallel_creates is too low for offered load.

rate(pg_doorman_pool_scaling_total{type="burst_gate_waits"}[5m])
  > 0.5 * rate(pg_doorman_pool_scaling_total{type="creates_started"}[5m])

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_SCALING'

• inflight_creates sits at the configured cap AND clients are visible in SHOW POOLS cl_waiting → connect() is slow on the backend side, see Burst gate is the bottleneck even with low traffic troubleshooting before raising the cap.
• inflight_creates cycles below the cap but gate_waits climbs → many short bursts. Raise scaling_max_parallel_creates, stay within the hard ceiling documented under tuning.
• Only one pool is hot → consider min_guaranteed_pool_size on the neighbours or lower that pool's pool_size.

Create fallback firing (warn). Phase 4 anticipation is giving up without finding a return and falls through to a fresh connect(). Steady-state should be zero.

rate(pg_doorman_pool_scaling_total{type="create_fallback"}[5m]) > 0.1
  and
  rate(pg_doorman_pool_scaling_total{type="creates_started"}[5m]) > 0.1

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_SCALING'
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman \
    -c 'SHOW STATS' | grep -E 'database|avg_xact_time|avg_query_time'

• create_fallback is high on one pool AND avg_xact_time on that database is growing → slow queries are holding connections out of rotation. Fix the slow query first; the pool is sized for normal queries, not this transaction length.
• create_fallback is high across all pools AND creates_started rate is also high → offered load exceeds what returns can serve within the deadline. Raise pool_size.
• create_fallback is high but query_wait_timeout is short (< 1 s) → the anticipation deadline (query_wait_timeout − 500 ms capped at 500 ms) is too short to catch even normal returns. Raise query_wait_timeout to at least 2 × p99 query latency.

Replenish deferred persistently (warn). Background replenish cannot sustain min_pool_size because the burst gate is busy with client traffic.

increase(pg_doorman_pool_scaling_total{type="replenish_deferred"}[1h]) > 60

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_SCALING'
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOLS'

• The affected pool shows sv_idle + sv_active < min_pool_size while gate_waits is also climbing → replenish is losing to client traffic. Raise scaling_max_parallel_creates so the background task has spare bandwidth, or accept the defer as cosmetic (under load, client-driven creates will lift the pool above min_pool_size anyway).
• inflight_creates sits at the cap continuously → gate is full for a different reason (slow connect()); fix that first.

Reserve pool continuously in use (warn). Reserve permit gauge has not returned to zero over 15 minutes. The retain task upgrades idle reserve permits back to main every retain_connections_time (default 30 s), so this alert means the upgrade path is unable to run or succeed, not that it forgot to run.

min_over_time(pg_doorman_pool_coordinator{type="reserve_in_use"}[15m]) > 0

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_COORDINATOR'
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOLS'

Compute main_used = current - reserve_used from the row — current is the combined total of main and reserve permits, not main alone.

• main_used == max_db_conn → main is fully used; upgrade has no slot to steal. The database is undersized; raise max_db_connections.
• main_used < max_db_conn AND every pool in SHOW POOLS shows sv_active == pool_size (or cl_waiting > 0 as an indicator) → every pool is under pressure, retain task skips upgrade. Increase pool_size on whichever pool has the highest cl_waiting or the tightest sv_active / pool_size ratio.
• main_used < max_db_conn AND no pool shows either sign, yet the gauge stays non-zero → file a bug with the SHOW POOL_COORDINATOR and SHOW POOLS snapshots; this should not happen.

Coordinator approaching cap (warn). Lead time before exhaustion.

pg_doorman_pool_coordinator{type="max_connections"} > 0
  and
  pg_doorman_pool_coordinator{type="connections"}
    / pg_doorman_pool_coordinator{type="max_connections"} > 0.85

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_COORDINATOR'
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOLS'

• current climbing monotonically over hours → capacity planning problem. Raise max_db_connections (check PostgreSQL headroom first) before the next burst.
• current oscillating near the cap → burst-driven. Raise reserve_pool_size so bursts absorb without touching max_db_connections, and watch reserve_acq rate afterward.
• One pool dominates SHOW POOLS (sv_active + sv_idle much larger than peers) → runaway pool; lower its pool_size or add min_guaranteed_pool_size to the victims.

Inflight stuck at cap (warn). inflight_creates sitting at the configured cap for 5+ minutes means connect() calls are not finishing.

min_over_time(pg_doorman_pool_scaling{type="inflight_creates"}[5m])
  >= 2  # adjust to your scaling_max_parallel_creates value

Runbook:

time psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c 'SELECT 1'
psql -h $PG_HOST -p $PG_PORT -c \
    "SELECT state, count(*) FROM pg_stat_activity GROUP BY state"

• psql timing shows connect() > 500 ms → backend connect is slow. Check pg_stat_ssl for SSL handshake cost, pg_authid for role lookup contention, and DNS resolution time from the pg_doorman host.
• pg_stat_activity shows many startup or authenticating sessions → backend is spawning but not clearing the handshake queue. Likely max_connections is hit at the backend level — run SELECT setting FROM pg_settings WHERE name = 'max_connections' and compare with actual active sessions.
• pg_stat_activity is empty on the pg_doorman-side user → network / firewall issue between pg_doorman and PostgreSQL.

Coordinator thrashing (warn). Cap is full and evictions are happening: the coordinator is constantly closing peer connections to make room. The pool is undersized for offered load.

pg_doorman_pool_coordinator{type="connections"}
    / pg_doorman_pool_coordinator{type="max_connections"} > 0.95
  and
  rate(pg_doorman_pool_coordinator_total{type="evictions"}[5m]) > 0

Runbook:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman -c 'SHOW POOL_COORDINATOR'

• evictions rate high AND reserve_used == 0 → reserve is off or exhausted, eviction is the only release valve. Enable / raise reserve_pool_size to absorb the burst without closing peer backends.
• evictions AND reserve_acq both climbing → reserve is consumed and still not enough. Raise max_db_connections or reserve_pool_size; check PostgreSQL max_connections first.

Reading the admin output during an incident

The admin console accepts only SHOW <subcommand>, SET, RELOAD, SHUTDOWN, UPGRADE, PAUSE, RESUME, and RECONNECT. SHOW is not a virtual table, so there is no SELECT against the admin database. To query the counters in shell pipelines, run SHOW from psql and post-process the output.

The patterns below use psql against the admin listener (default credentials admin/admin):

# Highest burst-gate-wait ratio first (the hot pool).
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman \
     -c 'SHOW POOL_SCALING' --no-align --field-separator='|' \
  | awk -F'|' 'NR>1 && $4>0 { printf "%-20s %-20s %.3f  inflight=%d  defer=%d\n", $1, $2, $5/$4, $3, $9 }' \
  | sort -k3 -nr | head

# Pools where anticipation exhausted its deadline (undersized or slow returns).
# Sorts by the create_fallback share of total creates.
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman \
     -c 'SHOW POOL_SCALING' --no-align --field-separator='|' \
  | awk -F'|' 'NR>1 && $4>0 { printf "%-20s %-20s %.3f  fallback=%d  creates=%d\n", $1, $2, $8/$4, $8, $4 }' \
  | sort -k3 -nr | head

# Coordinator: closest databases to exhaustion.
psql -h 127.0.0.1 -p 6432 -U admin pgdoorman \
     -c 'SHOW POOL_COORDINATOR' --no-align --field-separator='|' \
  | awk -F'|' 'NR>1 && $2>0 { printf "%-30s %.3f  used=%d/%d  reserve=%d  exhaustions=%d\n", $1, $3/$2, $3, $2, $5, $8 }' \
  | sort -k2 -nr

Field positions in awk follow the column order documented above: POOL_SCALING is user|database|inflight|creates|gate_waits|antic_notify|antic_timeout|create_fallback|replenish_def, POOL_COORDINATOR is database|max_db_conn|current|reserve_size|reserve_used|evictions|reserve_acq|exhaustions.

Comparison with PgBouncer

PgBouncer and pg_doorman both pool, but they handle pressure differently.

Two differences matter most in production:

1. Bounded burst gate. PgBouncer's pool size limits how many connections you have, but does not limit how many connect() calls fire at the same time when many clients arrive in the same instant. pg_doorman caps the simultaneous backend connect() rate independently of pool size, so a sudden traffic spike does not translate into a connection storm against PostgreSQL.

2. Cross-pool eviction. PgBouncer's max_db_connections is a hard ceiling with no way to redistribute. If user A holds 80 idle connections and user B needs one but the cap is reached, user B waits or fails. pg_doorman's coordinator can close one of A's idle connections (if older than min_connection_lifetime) and give the slot to B.

3. FIFO direct handoff. PgBouncer queues clients in arrival order and hands out the next free connection, but PgBouncer processes events serially on a single thread — under high contention, scheduling order depends on libevent's readiness callbacks. pg_doorman sends returned connections through a per-waiter oneshot channel in strict FIFO order. The result is a tight p50/p99 ratio (typically under 1.1x) regardless of client count, while poolers without strict FIFO ordering show 10-25x tail inflation under the same load.

Troubleshooting

Multiple simultaneous backend connect log lines

Symptom. Server logs (or pg_doorman debug logs) show 5 or more backend connect() events in the same millisecond, suggesting the burst gate is not working.

Cause. Either scaling_max_parallel_creates is set too high (verify in SHOW CONFIG or your pg_doorman.yaml), or there are 5 or more pools each independently issuing concurrent connects (the gate is per-pool, not global).

Fix. Lower scaling_max_parallel_creates. The default of 2 fits most workloads. With many pools, the aggregate concurrent connect rate is pools × scaling_max_parallel_creates, which is expected. To bound the aggregate, set max_db_connections per database; the coordinator will then queue creates beyond the cap.

min_pool_size is not being maintained

Symptom. A pool with min_pool_size = 10 shows sv_idle = 4 in SHOW POOLS and stays there for minutes.

Cause. Background replenish is deferring because the burst gate is busy with client traffic. Check replenish_def in SHOW POOL_SCALING. If it keeps growing, replenish skips every retain cycle.

Fix. By design, under load, client-driven creates own the gate. The pool reaches min_pool_size once client traffic eases. For a hard floor, raise scaling_max_parallel_creates so replenish has spare capacity, or shorten retain_connections_time so replenish runs more often.

For transaction pooling (pool_mode = transaction), setting min_pool_size higher than pool_size / 2 usually indicates an undersized pool: most connections should be available for client checkouts, not pinned at minimum. For session pooling the heuristic does not apply: min_pool_size = pool_size is a legitimate setup to keep all session-scoped state hot.

Latency p99 climbing without obvious cause

Symptom. Client p99 latency rises while p50 stays flat. Pool size looks fine, no errors in logs.

First thing to check. create_fallback rate in SHOW POOL_SCALING. If it is above zero and growing, anticipation is exhausting the full deadline (query_wait_timeout - 500 ms) without finding a return. Clients are paying the wait plus a fresh connect() on top of their query latency.

Fix. Two cases.

• create_fallback is growing. The pool cannot serve offered load from returns within the client's wait deadline. Raise pool_size, raise query_wait_timeout (if clients can tolerate it), or find the slow queries holding connections out of rotation.
• create_fallback is flat at zero and antic_notify is climbing in step with pool turnover. The direct handoff is working: returns are being caught, no connection storm is firing. The latency is somewhere else. Check SHOW STATS avg_wait_time, PostgreSQL-side wait events, network, and client code.

max_db_connections exhausted, clients receive errors

Symptom. Clients see errors like all server connections to database 'X' are in use (max=80, ...). pg_doorman_pool_coordinator_total{type="exhaustions"} is climbing.

Cause. All five coordinator phases failed: try-acquire failed, nothing was evictable, the wait timed out, and either the reserve was exhausted or reserve_pool_size = 0.

Fix. Walk the phases in order.

1. Check current vs max_db_conn in SHOW POOL_COORDINATOR. If current is at the cap consistently, your offered load exceeds the cap. Either raise max_db_connections or look for a runaway pool.
2. Check evictions rate. If it's zero or near-zero, eviction is not helping: every pool's idle connections are younger than min_connection_lifetime (default 30 000 ms), or every other pool is at its min_guaranteed_pool_size. Lower min_connection_lifetime if your workload has very short queries and you explicitly want faster cross-pool rebalancing, or increase max_db_connections.
3. Check reserve_used vs reserve_size. If the reserve is fully occupied, raise reserve_pool_size. If it's empty but exhaustions are happening, the reserve is not configured (reserve_pool_size = 0). Set it to absorb bursts.
4. Look at SHOW POOLS for the database. If one user has a much larger sv_idle than others, that user is hoarding connections; consider min_guaranteed_pool_size to protect smaller users from being crushed by it, or lower the hoarder's pool_size.

Coordinator wait phase is the bottleneck

Symptom. Clients pay 3 seconds of latency on average, exactly matching reserve_pool_timeout.

Cause. Phase C wait is consistently timing out. With reserve-first enabled, reaching Phase C means the reserve was already full when the caller arrived, so a peer return is the only way out. Either the database is genuinely at the cap with no connections returning, or reserve_pool_size = 0 so the wait runs to completion before the client receives any response.

Fix. Lower reserve_pool_timeout to fail fast, or set reserve_pool_size > 0 so Phase R / Phase D handles the overflow within the same acquisition path without parking in Phase C at all.

reserve_used stays non-zero but the pool looks idle

Symptom. SHOW POOL_COORDINATOR shows reserve_used = 4 (or any non-zero number) while SHOW POOLS shows no cl_waiting, low cl_active, and current < max_db_conn. The reserve pool looks occupied by "ghosts".

Cause. On builds before the reserve→main upgrade, a reserve permit stayed attached to its backend until the backend aged out past min_connection_lifetime and the retain cycle caught it idle. Under steady client traffic, last_used() on the backend kept refreshing faster than min_connection_lifetime, so the permit was never released.

Fix. On current builds this is resolved automatically: the retain task runs upgrade_reserve_to_main every retain_connections_time (default 30 s). Each reserve backend in a pool not under pressure gets its permit swapped for a main permit as long as db_semaphore has headroom. Watch the reserve_used gauge drop to zero within one retain cycle.

If reserve_used still sticks, the pool is either under sustained pressure (under_pressure() == true skips upgrade, which is correct — a queued client would re-grab the slot immediately) or current == max_db_connections (no main slot to steal into). Either condition means the database is genuinely full; the fix is more capacity, not a workaround.

Burst gate is the bottleneck even with low traffic

Symptom. gate_waits rate is significant but creates rate is low, and inflight_creates is at the cap continuously.

Cause. Backend connect() is slow. Each create holds a slot for seconds; even with two slots, you can only create roughly 2 / connect_seconds connections per second.

Fix. Investigate why connect() is slow on the PostgreSQL side (SCRAM iterations too high, pg_authid lock contention, slow DNS, SSL handshake). Once connect() is fast, the gate stops being the bottleneck. Raising scaling_max_parallel_creates papers over the problem and pushes the storm to PostgreSQL. Investigate first, raise the cap second.

is_starving users keep getting reserve permits

Symptom. reserve_acquisitions_total keeps increasing. The same small user is the one acquiring most reserves.

Cause. A user is below its effective minimum (max(user.min_pool_size, min_guaranteed_pool_size)) and the coordinator cannot satisfy that minimum without evicting from peers. Each client request from that user hits Phase R (reserve-first) as soon as the database is full and grabs a reserve permit — the arbiter scores starving users highest, so they win the grant. The deeper question is why the user keeps needing fresh connections: either its pool_size is too low to absorb its own load, or its traffic is bursty and the reserve is doing what reserves are for.

Fix. Three options, pick by the deeper cause:

• If the user's pool_size is genuinely too small for steady-state load, raise pool_size and (if needed) max_db_connections so the larger pool fits.
• If the user has a high effective minimum that the coordinator cannot satisfy, lower whichever knob is actually setting the floor (check both user.min_pool_size and min_guaranteed_pool_size).
• If the traffic is genuinely bursty and reserves are catching the bursts, leave it alone. Brief reserve usage is the design.

Clients receive wait timeout, not database exhausted

Symptom. Under coordinator pressure clients see PoolError::Timeout(Wait), but pg_doorman_pool_coordinator_total{type="exhaustions"} stays at zero. The coordinator never declared exhaustion, but every client times out.

Cause. query_wait_timeout is shorter than reserve_pool_timeout. The client gives up before the coordinator's wait phase finishes. The exhaustions counter never increments because the coordinator eventually gets a permit for a request that no longer has a waiting client.

Fix. Either raise query_wait_timeout above reserve_pool_timeout plus typical connect() time, or lower reserve_pool_timeout (within the floor noted in the tuning section). The startup config validator emits a warning for this configuration; act on it.

PostgreSQL was restarted, what now

Symptom. PostgreSQL master restarted (failover, crash, planned). You see a flash mob of clients hitting the burst gate, inflight_creates sitting at the cap, and creates_started rate spiking.

Cause. When pg_doorman detects an unusable backend (via server_idle_check_timeout or a failed query), it bumps the pool's reconnect epoch and drains all idle connections at once. Every client that arrives after the drain misses the hot path and hits the anticipation → burst-gate → connect path. With scaling_max_parallel_creates = 2, the pool refills at most 2 connections at a time per pool, gated by PostgreSQL's connect() latency.

What healthy recovery looks like. inflight_creates = 2 continuously for the first few seconds, creates_started rate climbing rapidly, burst_gate_waits rate climbing in lockstep, anticipation_wakes_notify climbing as the first refilled connections start cycling back and the direct handoff delivers them to waiting callers. create_fallback should stay flat: the deadline window is wide enough that the handoff catches returns before giving up. Within pool_size / 2 × connect() seconds, the pool returns to normal.

Fix. Usually nothing. The bounded burst gate is doing its job by preventing a connection storm against a recovering primary. If connect() is genuinely fast (< 50 ms) and your max_connections has headroom, raise scaling_max_parallel_creates to 4 or 8 to shorten recovery, but stay within the hard ceiling from the tuning section.

Glossary

• bounded burst gate — per-pool limiter capped at scaling_max_parallel_creates concurrent backend connect() calls. Tasks beyond the cap register a direct-handoff waiter and listen for a peer create completion until a slot frees up.
• CoordinatorPermit — RAII guard that accounts for one coordinator slot. Carries an is_reserve flag. Dropped when the backend is physically destroyed (not when it returns to the idle vec), at which point it releases its slot back to either db_semaphore (main) or reserve_semaphore (reserve).
• effective minimum — the eviction floor for a user pool, computed as max(user.min_pool_size, pool.min_guaranteed_pool_size). The coordinator protects this many connections per user from being evicted by peers.
• direct handoff — Phase 4 delivery mechanism. return_object sends the connection through a oneshot channel to the oldest registered waiter, bypassing the idle queue. No race with Phase 1/2 semaphore waiters — the connection goes to a specific caller.
• Phase R (reserve-first) — coordinator shortcut inserted between Phase A and Phase B. When the database is full but the reserve pool has headroom, Phase R grants a reserve permit directly via the arbiter instead of closing a peer backend or parking in Phase C.
• PHASE_4_HARD_CAP — compile-time constant with uniform jitter: each checkout draws a random cap between 300 ms and 500 ms. Upper bound on Phase 4 anticipation wall time, regardless of query_wait_timeout. Not configurable. The jitter prevents synchronized timeouts that cause burst-gate stampedes.
• reserve arbiter — single tokio task that owns the reserve permits. Reserve requests are scored by (starving, queued_clients) and drained from a priority heap so the neediest users are served first.
• reserve → main upgrade — retain-time book-keeping swap. When an idle backend holds a reserve permit and db_semaphore has headroom, the retain task steals a main permit, returns the reserve slot, and flips is_reserve on the permit. No reconnect.
• spare_above_min — slots.size - effective_minimum for a user pool, where slots.size is the pool's currently allocated connection count (active + idle together, not just idle). Used by the coordinator to pick eviction victims: the user pool with the largest spare_above_min loses a connection first. The underlying connection still has to be idle in the vec to be eligible for eviction — spare_above_min only selects the pool, not the specific connection.
• starving user — a user pool whose current connection count is below its effective minimum. The reserve arbiter gives starving users absolute priority over non-starving users.
• under_pressure() — predicate that returns true when a pool's per-pool semaphore has zero available permits, equivalent to every slot being checked out right now. Used by the retain task to skip upgrade/close on pools that would just hand the freed slot to a waiting client.
• warm threshold — pool_size × scaling_warm_pool_ratio / 100. Below this size, the pool skips anticipation and goes straight to connect(). Above it, anticipation is active and the pool tries to catch returns before creating new backends.

Patroni-assisted fallback

When pg_doorman runs next to PostgreSQL on the same machine and connects via unix socket, a Patroni switchover or an unexpected PostgreSQL crash leaves doorman without a backend. Until Patroni finishes promoting a replica or restarting the local PostgreSQL, every client query fails.

Patroni-assisted fallback bridges that gap. When the local PostgreSQL stops responding, pg_doorman queries the Patroni REST API, picks another cluster member, and routes new connections there. Existing pooled connections to the dead backend are recycled normally.

This is a short-term measure. It bridges the 10-30 seconds while Patroni completes its own failover. Once Patroni restores the local PostgreSQL — as a replica of the new primary, or as the recovered primary itself — pg_doorman returns to the local socket.

Quick start

The recommended deployment puts pg_doorman next to PostgreSQL on the same host and talks to it through the unix socket. With Patroni's REST API also on localhost, fallback turns on with one line in [general]:

general:
  patroni_api_urls: ["http://localhost:8008"]

Every pool picks this up automatically. When the unix socket stops responding, pg_doorman queries /cluster, prefers sync_standby over replica over leader, and routes new connections to the chosen host until the local PostgreSQL recovers. Defaults: cooldown 30s, HTTP timeout 5s, TCP timeout 5s, fallback connection lifetime 30s. Override them under Tuning parameters.

When it helps

Planned switchover. A DBA runs patroni switchover --candidate node2. Patroni promotes node2, then shuts down PostgreSQL on node1. Between the shutdown and Patroni restarting node1 as a replica of node2, doorman on node1 has no backend. With fallback enabled, the next client request that fails to reach the local socket triggers a /cluster lookup and the new connection is opened to node2.

Unplanned crash. PostgreSQL on node1 is killed by the OOM killer. Patroni hasn't detected the failure yet. Doorman gets connection refused on the unix socket, queries the Patroni API, and connects to the sync_standby (most likely the next leader).

When it does not help

Machine failure. If the entire machine is down, doorman dies with it. No fallback logic can run. This scenario requires external routing (HAProxy, patroni_proxy, DNS failover, VIP).

Authentication errors. If PostgreSQL rejects doorman's credentials, the backend is alive. Fallback does not activate.

How it works

Normal:
  client --unix--> doorman --unix--> PostgreSQL (local)

Fallback:
  client --unix--> doorman --TCP---> PostgreSQL (remote, from /cluster)
                      |
                      +-- GET /cluster --> Patroni API

1. Doorman tries the local unix socket.
2. Connection refused or socket error: doorman puts the local backend into cooldown for fallback_cooldown (default 30 seconds).
3. Doorman sends GET /cluster to all configured Patroni URLs in parallel and takes the first successful response.
4. From the member list, doorman drops members in cooldown and partitions the rest into two waves by role: wave 1 — every sync_standby; wave 2 — every other member (replica + leader, in discovery order).
5. Wave 1 (strict-priority race). Doorman runs Server::startup against every sync_standby in parallel, each under fallback_connect_timeout (default 5 seconds). The first sync_standby to finish startup wins immediately and its connection is delivered to the client. While any sync_standby is still in-flight no replica/leader is considered, even if a replica would have answered sooner — the goal is to preserve write traffic, and the sync_standby is the lowest-data-loss promotion target.
6. Wave 2 (no sub-priority). Only entered if every sync_standby failed (or none exists). Doorman races startup against the rest in parallel under the same per-candidate timeout; whichever candidate completes startup first wins — replica and leader compete on equal footing.
7. Exhaustion. If both waves finish with no winner, the doorman log records all fallback candidates rejected (3 startup_error, 1 timeout) with a deterministic per-reason breakdown. The client always sees the same sanitized FATAL pg_doorman uses for startup-time errors — Unable to retrieve server parameters … may be unavailable or misconfigured — read the doorman log for the wave/winner trace.
8. The successful connection enters the pool with a reduced lifetime (default 30 seconds, matching the cooldown). It follows all normal pool rules: coordinator limits, idle timeout, recycle.
9. Subsequent connections during the cooldown go to the same fallback host directly, without re-querying the Patroni API. If that cached host fails on a later startup, doorman clears the cache and runs one extra discovery round.
10. When the cooldown expires, doorman tries the local socket again. If it works, normal mode resumes. If not, the cycle repeats.

Per-candidate failures (auth error, database is starting up, timeout) mark the candidate unhealthy with exponential backoff; subsequent discovery rounds skip those hosts until their cooldown lapses.

Wait time bounds

A client never waits for fallback longer than query_wait_timeout (default 5 seconds). When that deadline elapses, doorman aborts the fallback path with fallback: outer deadline {ms}ms exceeded in the log and the client sees the same sanitized FATAL as any other startup-time failure. The deadline is soft: per-candidate fallback_connect_timeout is the hard guarantee against hangs, the outer deadline is just the upper bound on how long the client itself is willing to wait.

Per-host cooldown

A candidate that fails startup stays out of the next discovery for fallback_connect_timeout (default 5 seconds). Each consecutive failure on the same host doubles the cooldown, capped at 60 seconds. After the window elapses the entry is dropped (lazy cleanup on the next discovery cycle) and the counter resets on the next failure. This prevents a stuck candidate (postgres in recovery, persistent auth misconfiguration, slow network path) from being retried on every client request and hammering both the candidate and the Patroni API.

Write queries on a replica

If the fallback host is a replica that hasn't been promoted yet, write queries return:

ERROR: cannot execute INSERT in a read-only transaction

Read queries work normally. In a typical switchover, sync_standby is promoted before doorman even detects the failure, so most write queries succeed. Worst case, write errors last until the reduced lifetime expires (30 seconds) and the next connection attempt finds the new primary via a fresh /cluster call.

Configuration

Add patroni_api_urls to any pool that should use fallback. Without this setting, the feature is disabled and doorman behaves as before.

pools:
  mydb:
    pool_mode: transaction
    server_host: "/var/run/postgresql"
    server_port: 5432

    # Patroni API endpoints. Specify at least 2 for redundancy.
    # The first URL that responds wins; order does not matter.
    patroni_api_urls:
      - "http://10.0.0.1:8008"
      - "http://10.0.0.2:8008"
      - "http://10.0.0.3:8008"

TOML equivalent:

[pools.mydb]
pool_mode = "transaction"
server_host = "/var/run/postgresql"
server_port = 5432

patroni_api_urls = [
    "http://10.0.0.1:8008",
    "http://10.0.0.2:8008",
    "http://10.0.0.3:8008",
]

Tuning parameters

All parameters are optional and have sensible defaults.

What to put in patroni_api_urls

List the Patroni REST API addresses of your cluster nodes. The /cluster endpoint on any Patroni node returns the full cluster topology, so even a single URL is enough to enumerate all members.

Two or more URLs are recommended: if the first URL points to the same machine as the dead PostgreSQL, it won't respond either. Doorman queries all URLs in parallel and takes the first response.

Prometheus metrics

Active transactions

If PostgreSQL crashes while a client is in the middle of a transaction, the client receives a connection error. doorman does not migrate in-flight transactions to a fallback host — the client must retry.

New queries from the same or other clients go through the fallback path automatically.

Operational notes

Credentials. All cluster nodes must accept the same username and password that doorman uses. Patroni clusters typically share pg_hba.conf via bootstrap configuration, but this is not guaranteed. Verify that fallback nodes accept the configured credentials.

TLS. Fallback connections use the same server_tls_mode as the local backend. If the local backend uses a unix socket (no TLS), fallback TCP connections will also run without TLS. Configure server_tls_mode explicitly if fallback connections must be encrypted.

DNS. Use IP addresses in patroni_api_urls and in Patroni member.host, not hostnames. The startup-timeout wrapper covers DNS resolution via TcpStream::connect, but a 5s DNS hang consumes the full fallback_connect_timeout budget for that candidate before the next one is tried.

Log volume under failure storm. The per-candidate <host>:<port> rejected (...) WARN is rate-limited to one line per 10 seconds per (pool, host, port). Suppressed lines log at DEBUG. If you see only one WARN where you expected many, that's the rate-limit, not lost data — check the pg_doorman_fallback_candidate_failures_total counter for the real attempt count.

Whitelist switchover and pg_doorman_fallback_host. When the fallback target changes (cooldown drains, retry round picks a different host), the gauge for the previous (host, port) is removed in the same operation that sets the gauge for the new one. Dashboards do not see two hosts marked active at once during the transition.

standby_leader. Patroni standby clusters use the standby_leader role. doorman treats it as "other" (lowest priority, after sync_standby and replica). For a primary-cluster deployment this matches what you want; if you are running pg_doorman on a standby cluster you most likely don't want fallback at all because you have no writeable target.

Relationship to patroni_proxy

patroni_proxy and Patroni-assisted fallback solve different problems.

patroni_proxy is a TCP load balancer deployed near application clients. It routes connections to the correct PostgreSQL node based on role (leader, sync, async). It does not pool connections.

Patroni-assisted fallback is built into the doorman pooler deployed next to PostgreSQL. It handles the case where the local backend dies and doorman needs a temporary alternative. It does pool connections.

In the recommended deployment (patroni_proxy → pg_doorman → PostgreSQL), fallback keeps read traffic flowing at the doorman layer when the local backend dies, without affecting patroni_proxy routing.

Patroni Proxy

patroni_proxy is a TCP load balancer for Patroni-managed PostgreSQL clusters. It listens on one or more ports, asks the Patroni REST API who is leader / sync / async, and forwards new connections to the chosen role using least-connections balancing. It does not pool connections, parse the wire protocol, or know what SQL is being sent — that part is pg_doorman's job, deployed downstream of patroni_proxy.

What it does

• Discovers cluster members by polling Patroni's /cluster endpoint at cluster_update_interval (default 3 s) and on demand via GET /update_clusters.
• Routes by role. Each listen port is bound to one or more roles (leader, sync, async, any). Connections to that port land on a member matching one of those roles.
• Balances by least connections. For ports bound to multiple eligible members, the proxy keeps a connection counter per member and picks the one with the fewest live connections. Counters survive cluster updates.
• Drops replicas with stale data. Per-port max_lag_in_bytes excludes members whose replication_lag (from /cluster) is over the threshold. Leader is never excluded by lag.
• Skips members that aren't running. Only state: "running" members are eligible; starting, stopped, crashed, and members with noloadbalance are filtered out.

The behaviour that matters operationally is what happens on a topology change: when a new member appears or an old one disappears, patroni_proxy updates its routing table for future connections only. Existing TCP connections to a still-running backend are not touched. Compared to HAProxy + confd, where a config reload tears down all connections that pass through the affected backend section, this means cluster_update_interval doesn't have to fight with long-running transactions.

Roles

Recommended deployment

graph TD
    App1[Application A] --> PP(patroni_proxy<br/>TCP load balancing)
    App2[Application B] --> PP
    App3[Application C] --> PP

    PP --> D1(pg_doorman<br/>pooling)
    PP --> D2(pg_doorman<br/>pooling)
    PP --> D3(pg_doorman<br/>pooling)

    D1 --> PG1[(PostgreSQL<br/>leader)]
    D2 --> PG2[(PostgreSQL<br/>sync replica)]
    D3 --> PG3[(PostgreSQL<br/>async replica)]

• pg_doorman lives on the PostgreSQL hosts. It does the pooling, prepared-statement cache, and protocol parsing — work that benefits from low latency to the local socket.
• patroni_proxy lives near the application. It routes TCP, owns the role-aware failover decision, and stays out of the pooler's way.

If the application traffic is small enough that one pg_doorman per cluster is sufficient, you can collapse the diagram and run pg_doorman directly with Patroni-assisted fallback and skip patroni_proxy entirely.

Configuration

Example patroni_proxy.yaml:

# Cluster update interval in seconds (default: 3)
cluster_update_interval: 3

# HTTP API listen address for health checks and manual updates (default: 127.0.0.1:8009)
listen_address: "127.0.0.1:8009"

clusters:
  my_cluster:
    # Patroni API endpoints (multiple for redundancy)
    hosts:
      - "http://192.168.1.1:8008"
      - "http://192.168.1.2:8008"
      - "http://192.168.1.3:8008"
    
    # Optional: TLS configuration for Patroni API
    # tls:
    #   ca_cert: "/path/to/ca.crt"
    #   client_cert: "/path/to/client.crt"
    #   client_key: "/path/to/client.key"
    #   skip_verify: false
    
    ports:
      # Primary/master connections
      master:
        listen: "0.0.0.0:6432"
        roles: ["leader"]
        host_port: 5432
      
      # Read-only connections to replicas
      replicas:
        listen: "0.0.0.0:6433"
        roles: ["sync", "async"]
        host_port: 5432
        max_lag_in_bytes: 16777216  # 16MB

Configuration Options

Usage

Starting patroni_proxy

# Start with configuration file
patroni_proxy /path/to/patroni_proxy.yaml

# With debug logging
RUST_LOG=debug patroni_proxy /path/to/patroni_proxy.yaml

Configuration Reload

Reload configuration without restart (add/remove ports, update hosts):

kill -HUP $(pidof patroni_proxy)

Manual Cluster Update

Trigger immediate update of all cluster members via HTTP API:

curl http://127.0.0.1:8009/update_clusters

HTTP API

Comparison with HAProxy + confd

Building

# Build release binary
cargo build --release --bin patroni_proxy

# Run tests
cargo test --test patroni_proxy_bdd

Troubleshooting

No backends available

If you see warnings like no backends available, check:

1. Patroni API is accessible from patroni_proxy host
2. Cluster members have state: "running"
3. Roles in configuration match actual member roles
4. If using max_lag_in_bytes, check replica lag values

Connection drops after update

This should not happen with patroni_proxy. If connections are being dropped:

1. Check if the backend host was actually removed from the cluster
2. Verify max_lag_in_bytes threshold is not being exceeded
3. Enable debug logging to see detailed connection lifecycle

Binary upgrade

Replace the pg_doorman binary on a running server. Idle clients are handed to the new process over a Unix socket together with their cancel keys and prepared-statement cache, so they keep using the same TCP connection without reconnecting. Clients inside a transaction finish on the old process and migrate the moment they become idle. Operators get a kill -USR2 and an exit status; applications get neither a reconnect storm nor a wave of auth/SCRAM handshakes against PostgreSQL.

Quick start

# 1. Install the new binary at the path used by the running service.
install -m 0755 pg_doorman_new /usr/bin/pg_doorman

# 2. Validate the new binary against the live config before triggering
#    the upgrade. SIGUSR2 also runs `-t` and aborts on failure, but
#    catching it here gives you a chance to fix the config without
#    touching the running server.
/usr/bin/pg_doorman -t /etc/pg_doorman/pg_doorman.toml

# 3. Trigger the upgrade. With `ExecReload=/bin/kill -SIGUSR2 $MAINPID`
#    in the unit, `systemctl reload` sends SIGUSR2 to start binary
#    upgrade. pg_doorman then validates config, starts the child,
#    migrates state where possible, and drains the old process.
#    systemd delivers the signal to the
#    tracked MainPID, so this targets the single correct process even
#    when other pg_doorman instances are running on the host. Direct
#    `kill -USR2 $(pgrep -f /usr/bin/pg_doorman)` works but matches by
#    command line and can hit every instance, which is why packaged
#    installs go through systemctl.
sudo systemctl reload pg_doorman.service

# A successful reload only means systemd delivered SIGUSR2. Validation,
# child startup, MAINPID handoff, and client migration happen inside
# pg_doorman. Verify them in the next step and in the logs.

# 4. Verify: systemd tracks the new MainPID (Type=notify receives
#    `MAINPID=<new_pid>` from the child during the handoff). Active
#    state and the admin console confirm clients are still attached.
systemctl show -p MainPID --value pg_doorman.service
psql -h pgdoorman -p 6432 -c 'SHOW POOLS;'  # served by the new process

The same upgrade can be triggered from the admin console:

UPGRADE;

UPGRADE sends SIGUSR2 to the running process, which is the same code path as kill -USR2. A successful command response means the signal was sent, not that validation and migration have finished.

How the upgrade works

                        SIGUSR2
                           |
                           v
               +-----------------------+
               | 1. Validate config    |
               |    (pg_doorman -t)    |   -- fail --> abort, keep serving
               +-----------+-----------+
                           |
                           v
               +-----------------------+
               | 2. Spawn new process  |
               |    socketpair()       |
               |    inherit-fd         |
               |    readiness pipe     |   -- wait up to 10s
               +-----------+-----------+
                           |
             +-------------+-------------+
             |                           |
             v                           v
  +---------------------+    +---------------------+
  | OLD process         |    | NEW process         |
  |                     |    |                     |
  | 3. Idle clients     |    | migration_receiver  |
  |    serialize state  +--->+    reconstruct      |
  |    dup() + SCM_RIGHTS    |    spawn client     |
  |                     |    |    handle()         |
  | 4. In-tx clients    |    |                     |
  |    finish tx        |    | Accepts new conns   |
  |    migrate on idle  +--->+                     |
  |                     |    |                     |
  | 5. Shutdown timer   |    +---------------------+
  |    poll 250ms       |
  |    exit when empty  |
  +---------------------+

Phase 1: Config validation

The running process executes the same binary path it was started with, using -t and the current config file. After the install in the quick start, that path points to the new binary, so the check validates the binary that will take over. If validation fails, the upgrade is aborted and the old process keeps serving traffic. An error banner appears in the logs:

!!!  BINARY UPGRADE ABORTED - SHUTDOWN CANCELLED  !!!
!!!  FIX THE CONFIGURATION BEFORE ATTEMPTING BINARY UPGRADE AGAIN  !!!
!!!  THE SERVER WILL CONTINUE RUNNING WITH THE CURRENT BINARY  !!!

Phase 2: Spawn new process

Foreground mode:

1. A Unix socketpair() is created for client migration.
2. The listener fd passes to the child via --inherit-fd.
3. A pipe signals readiness: the parent waits up to 10 seconds for a single byte. If the child starts and begins accepting, it writes to the pipe.
4. The parent closes its listener -- new connections go to the child.

Daemon mode:

A new daemon process starts. The old daemon closes its listener. Client migration via socketpair is not used — existing clients stay on the old process. When shutdown_timeout expires, the old process exits and any remaining client sockets close. Use foreground mode if clients must migrate to the new process.

Phase 3: Idle client migration (foreground)

When MIGRATION_IN_PROGRESS is set, each idle client (not in a transaction, no pending deferred BEGIN, no buffered reads) migrates:

1. Serialize: connection_id, secret_key, pool name, username, server parameters, full prepared statement cache.
2. dup() + SCM_RIGHTS: the TCP socket fd is duplicated and sent to the new process over the Unix socketpair.
3. Reconstruct: the new process rebuilds the Client struct, assigns it to the correct pool, and calls handle().

The client sees no interruption. No reconnect, no error, no re-authentication. The TCP connection is the same physical socket.

Phase 4: In-transaction client drain

A client inside BEGIN ... COMMIT continues running on the old process. Its server connection stays alive. After the transaction ends (COMMIT or ROLLBACK), the client becomes idle and migrates on the next loop iteration.

A deferred BEGIN (no server checked out yet) also blocks migration. The client must send a query (flushing the deferred BEGIN) and then COMMIT before it can migrate.

Phase 5: Shutdown timer

The shutdown timer polls CURRENT_CLIENT_COUNT every 250 ms. When all clients have migrated or disconnected, the old process calls process::exit(0).

If shutdown_timeout elapses before all clients finish, the old process exits regardless -- force-closing remaining connections.

During migration, drain_all_pools() is deferred. In-transaction clients still need their server connections. Pool draining starts only after migration completes or when MIGRATION_IN_PROGRESS is cleared.

Prepared statements

Each client's prepared statement cache is serialized during migration:

• Statement key (named or anonymous hash)
• Query hash
• Full query text
• Parameter type OIDs

In the new process:

1. Each entry is registered in the pool-level shared cache (DashMap).
2. Server backends are fresh -- they have no prepared statements.
3. On the first Bind to a migrated statement, pg_doorman transparently sends Parse to the new backend. The client does not see this extra round-trip.

Limits:

• If the new config has a smaller client_anonymous_prepared_cache_size, excess Anonymous entries are evicted (LRU). Named entries are unbounded and survive in full. The remaining entries work normally.
• Anonymous prepared statements (empty-name Parse) survive migration but require a re-Parse before Bind in the new process.
• DEALLOCATE ALL after migration clears the transferred cache. Re-Parse with the same name uses the new query text.

TLS migration

By default, TLS clients cannot be migrated -- the encrypted session requires key material that lives inside the OpenSSL state machine. These clients drain during upgrade: their connection is closed when shutdown_timeout expires, and the client reconnects to the new process.

The opt-in tls-migration feature solves this. A patched OpenSSL exports the symmetric cipher state, passes it alongside the fd over the Unix socket, and the new process imports it to resume encryption mid-stream. The client does not re-handshake.

What gets exported

The patch adds SSL_export_migration_state() and SSL_import_migration_state() to OpenSSL 3.5.5. Exported data:

• TLS protocol version
• Cipher suite ID and tag length
• Read/write symmetric keys (AES key schedule input, not expanded)
• Read/write IVs (nonce)
• Read/write sequence numbers (8 bytes each)
• For TLS 1.3: server and client application traffic secrets

This is enough to reconstruct the record layer in the new process and continue encrypting/decrypting on the same TCP connection.

Building with TLS migration

cargo build --release --features tls-migration

Requires perl and patch in the build environment. Vendored OpenSSL 3.5.5 compiles from source with the migration patch applied.

Offline builds

# Download the tarball in advance
curl -fLO https://github.com/openssl/openssl/releases/download/openssl-3.5.5/openssl-3.5.5.tar.gz

# Build with the local tarball
OPENSSL_SOURCE_TARBALL=./openssl-3.5.5.tar.gz \
  cargo build --release --features tls-migration

SHA-256 is verified automatically.

Restrictions

• Linux only. macOS and Windows use platform-native TLS (Security.framework / SChannel), not OpenSSL. TLS migration is not possible with native-tls backends.
• Same certificates. Both processes must use the same tls_private_key and tls_certificate. The cipher state is bound to the SSL_CTX created from the certificate. Changed certificates cause import failure and client disconnection.
• FIPS incompatible. Vendored OpenSSL is not FIPS-validated. For FIPS compliance, build without tls-migration (TLS clients drain instead of migrating).
• No HSM/PKCS#11. Vendored OpenSSL is built with no-engine.

Known limitations

• TLS 1.3 KeyUpdate changes cipher keys. If either side sends a KeyUpdate message after the cipher state was exported, the imported keys become invalid and the connection will fail with AEAD authentication errors.

  Driver-specific behavior (verified April 2026):

  Driver	Auto KeyUpdate?	Risk
  libpq (psql, pgbench)	No — OpenSSL does not auto-send	None
  asyncpg (Python)	No — Python ssl wraps OpenSSL	None
  node-postgres	No — Node.js tls wraps OpenSSL	None
  Npgsql (.NET)	No — SslStream has no KeyUpdate API	None
  pgjdbc (Java)	Yes — JSSE sends after ~128 GB (jdk.tls.keyLimits)	High
  tokio-postgres (rustls)	Yes — rustls rotates at AEAD limit	Medium
  PostgreSQL server	No — renegotiation disabled, no KeyUpdate calls	None

  Java clients: JSSE automatically sends KeyUpdate after ~128 GB of encrypted data per connection. JDK bug JDK-8329548 can cause a storm of KeyUpdate messages. For Java clients with long-lived, high-throughput connections, TLS migration may lose connections after the threshold. Workaround: increase the threshold via jdk.tls.keyLimits in java.security, or disable TLS between client and pg_doorman for Java workloads.

  Rust clients with rustls: rustls tracks AEAD usage and rotates keys at cipher suite limits (very high threshold, ~2^36 records for AES-GCM). Unlikely to hit in practice for PostgreSQL workloads. Using native-tls (OpenSSL) backend instead of rustls eliminates the risk.

  All OpenSSL-based drivers are safe. OpenSSL explicitly does not perform automatic key updates (openssl#23566).

• SSL_pending data not checked. The migration happens at the idle point, where no application data is buffered. The idle-point invariant guarantees this, but there is no explicit SSL_pending() assertion.

• Tied to OpenSSL 3.5.5. The patch modifies internal OpenSSL structures (ssl_local.h, rec_layer_s3.c, ssl_lib.c). Upgrading OpenSSL requires reviewing and re-applying the patch against the new version.

Signal reference

Daemon vs foreground

For zero-downtime upgrades with client migration, run in foreground mode. systemd manages the process lifecycle. Use Type=notify so the unit reaches active only after pg_doorman signals readiness, and the child process can update MainPID to itself during SIGUSR2 upgrades:

[Service]
Type=notify
# The child process that takes over on SIGUSR2 must be allowed to send
# READY=1 and MAINPID=<new_pid> during handoff.
NotifyAccess=exec
ExecStart=/usr/bin/pg_doorman /etc/pg_doorman/pg_doorman.toml
# `systemctl reload` triggers binary upgrade: validate config, spawn
# the new process, migrate clients where possible, then drain the old
# process according to pg_doorman's shutdown_timeout.
ExecReload=/bin/kill -SIGUSR2 $MAINPID
# `systemctl stop` is immediate shutdown. It is not a binary upgrade
# path and it does not wait for active transactions to migrate.
ExecStop=/bin/kill -SIGTERM $MAINPID
# During binary upgrade the new child becomes MainPID via sd_notify.
# With KillMode=mixed, systemd sends SIGTERM only to MainPID on stop
# and SIGKILLs remaining cgroup processes only after TimeoutStopSec.
KillMode=mixed
TimeoutStopSec=60
# Do not restart after a clean manual stop or after the old process exits
# successfully during binary upgrade.
Restart=on-failure
Nice=-15
# pg_doorman is connection-heavy: each client + each backend uses an
# fd, plus internal pipes. 65536 covers most OLTP pools; size it from
# `general.pool_size * num_pools` plus a few thousand for clients.
LimitNOFILE=65536
# Run as a non-privileged service account that owns the PID file. On
# many deployments postgres already exists; reusing it keeps file
# ownership consistent with PostgreSQL itself.
User=postgres
Group=postgres
SyslogIdentifier=pg_doorman

systemctl reload pg_doorman sends SIGUSR2; a zero exit status only means the signal was delivered. pg_doorman then runs -t on the new binary, cancels the upgrade if the config is bad, otherwise spawns the new process and drains the old one. UPGRADE; from the admin console reaches the same code path. The drain window is controlled by shutdown_timeout in pg_doorman.toml; TimeoutStopSec controls normal systemctl stop, not how long systemctl reload waits for migrated sessions.

Production deployments often layer more resource controls on top of the above, such as MemoryMax= and CPUAffinity=2,3,4,5,6,7,8,9. These are workload-specific and orthogonal to the upgrade contract.

Configuration

shutdown_timeout

Maximum time to wait for in-transaction clients before force-closing connections. The old process exits after this timeout regardless of remaining clients.

Default: 10 seconds.

For production with long-running analytics queries: 30-60 seconds.

[general]
shutdown_timeout = 60000  # milliseconds

Setting it too low risks killing active transactions. Setting it too high delays the old process exit when a client is stuck (e.g., idle-in-transaction). Choose a value that covers your longest expected transaction, plus margin.

tls_private_key / tls_certificate

For the tls-migration feature to succeed, both the old and the new process must load the same client-facing certificate and private key. The cipher state is bound to the SSL_CTX created from those files, and import fails on mismatch — affected clients drop and reconnect.

Client-facing TLS material is not reloaded on SIGHUP (only server-facing certificates are; see Hot reload of server TLS). Do not combine client-facing certificate rotation with an upgrade where you expect TLS sessions to migrate. If the files change between old and new process, TLS import fails and affected clients reconnect even with tls-migration enabled. Rotate the client-facing certificate in a maintenance window where reconnects are acceptable, or keep the same certificate files for the binary upgrade and rotate later with a restart.

prepared_statements_cache_size

Pool-level prepared statement cache. Does not directly affect migration, but the pool cache in the new process must be large enough to hold entries registered by migrated clients.

client_anonymous_prepared_cache_size

Per-client Anonymous prepared statement LRU. The client's full cache (both Named and Anonymous) is serialized during migration. If the new config has a smaller value, only Anonymous entries are subject to LRU eviction; Named entries are unbounded and migrate intact.

Rollback

Binary upgrade has no separate undo path. Roll back by staging the previous binary at the same path and running another SIGUSR2 upgrade. If validation fails, the current process keeps serving traffic. If the new process already took over, treat the rollback as a normal binary upgrade in the opposite direction.

Avoid systemctl restart or SIGTERM for rollback unless reconnects are acceptable: both close client sessions instead of migrating them.

Monitoring

Logs

Key log lines during migration:

INFO  Got SIGUSR2, starting binary upgrade and graceful shutdown
INFO  Validating configuration with: /usr/bin/pg_doorman -t pg_doorman.toml
INFO  Configuration validation successful
INFO  Starting new process with inherited listener fd=5
INFO  New process signaled readiness
INFO  Client migration enabled
INFO  [user@pool #c42] client 10.0.0.1:51234 migrated to new process
INFO  waiting for 3 clients in transactions
INFO  All clients disconnected, shutting down
INFO  Migration sender finished

In the new process:

INFO  migration receiver: listening for migrated clients
INFO  [user@pool #c42] migrated client accepted from 10.0.0.1:51234
INFO  migration receiver done: migration socket closed
INFO  migration receiver: stopped

Prometheus metrics

Admin console

-- On the new process (old rejects non-admin connections)
SHOW POOLS;
SHOW CLIENTS;

Troubleshooting

Client receives 58006 or disconnects instead of migrating

Ctrl+C in foreground mode. SIGINT in TTY = shutdown without upgrade. Use kill -USR2 or the UPGRADE admin command.

Daemon mode. Daemon mode does not use fd-based migration. Existing clients stay on the old process and are closed when it exits. Switch to foreground mode for migration.

PG_DOORMAN_CI_SHUTDOWN_ONLY=1 is set. This env var forces shutdown-only mode (used in CI tests). Unset it.

Old process does not exit

Long transaction. A client is stuck in BEGIN without COMMIT. Wait for shutdown_timeout or end the transaction manually.

Admin connections. Admin connections do not migrate. Close the admin session on the old process.

Force exit: kill -TERM <old_pid> sends SIGTERM for immediate exit.

TLS connection dropped after upgrade

Binary built without --features tls-migration. TLS clients drain instead of migrating. Rebuild with --features tls-migration.

Not running on Linux. TLS migration is Linux-only.

Certificate or key changed. The old process exported cipher state bound to the old certificate. Use the same files for both processes if you need TLS migration. Client-facing certificate rotation requires a restart or a planned reconnect window.

"TLS migration not available" in logs

The new process received a migration payload with TLS data but was built without --features tls-migration or is not running on Linux. The client is disconnected. Rebuild the new binary with --features tls-migration.

"migration channel not ready" in logs

The MIGRATION_TX channel has not been initialized yet. This can happen if the new process has not finished starting when a client tries to migrate. The client retries on the next idle iteration (within milliseconds).

"migration channel send failed" in logs

The migration channel is full (capacity: 4096). Possible when thousands of clients migrate simultaneously. The client retries on the next idle iteration.

"prepare_migration failed" in logs

The client's raw fd is unavailable or dup() failed. Possible causes: fd exhaustion, or the client connected through a code path that does not store the raw fd. Check ulimit -n.

Operational checklist

Before rolling out binary upgrade to production:

• Run in foreground mode (not daemon) for fd-based migration
• Set shutdown_timeout to cover your longest expected transaction (recommendation: 30-60 seconds for OLTP, longer for analytics)
• If using TLS: build with --features tls-migration, verify both processes use the same certificate and key files
• Test the upgrade in staging: open a session, trigger SIGUSR2, verify the session continues working
• Verify the systemd unit is Type=notify with NotifyAccess=exec, ExecReload=/bin/kill -SIGUSR2 $MAINPID (so systemctl reload runs binary upgrade with config validation), KillMode=mixed, and Restart=on-failure
• Monitor logs for migration errors after the first production upgrade
• Confirm old process exits (check PID file or pgrep)
• Verify Prometheus metrics show clients on the new process

Signals and Reload

PgDoorman responds to four POSIX signals: SIGHUP, SIGINT, SIGUSR2, and SIGTERM. Each does one specific thing.

Quick reference

Reload (SIGHUP)

kill -HUP $(pidof pg_doorman)

Re-reads the config file and applies changes. What reloads:

• Pool definitions (added, removed, resized).
• User lists, passwords, auth_query blocks.
• pg_hba.conf rules (file or inline content).
• Server-side TLS certificates and CA bundles (lock-free swap; existing TLS connections keep their original context).
• Talos and JWT public keys.
• Log level and format.

What does not reload:

• general.host, general.port — listening socket is fixed at startup.
• general.tcp_socket_buffer_size on existing sockets — the new value is applied only when pg_doorman accepts a new client TCP socket or opens a new backend TCP socket.
• Client-facing TLS certificates — process restart required. Do not rotate them during an upgrade where TLS session migration is required.
• Worker thread count and Tokio runtime parameters.

After reload, SHOW CONFIG reflects the new values. Existing client connections are not re-evaluated against the new pg_hba.conf — only new connections. Existing TCP sockets also keep the socket buffer size that was applied when the socket was created.

Immediate shutdown (SIGTERM)

kill -TERM $(pidof pg_doorman)

pg_doorman logs how many clients are still in transactions and exits. It does not wait for shutdown_timeout and it does not migrate active transactions. All client connections are closed by process exit.

shutdown_timeout applies to SIGUSR2 binary upgrade drain, not to plain SIGTERM shutdown.

Binary upgrade (SIGUSR2)

kill -USR2 $(pidof pg_doorman)

The recommended way to replace the binary without dropping clients:

1. Replace the binary on disk with the new version using an atomic rename.
2. Send SIGUSR2 to the running process.
3. The current process validates the new binary with -t.
4. The current process spawns a child running the new binary, hands over the listening socket, and continues serving existing clients until they finish.
5. New clients connect to the child immediately.
6. The old process exits when the last client transaction completes (or on shutdown_timeout).

The child sends sd_notify MAINPID=<new_pid> so systemd Type=notify units track the new main PID correctly.

Migrated client TCP sockets are configured again in the child process, so a changed general.tcp_socket_buffer_size applies to those clients during binary upgrade. Backend TCP sockets are opened by the new process and use the new value when they connect.

For the full protocol, TLS migration, and rollback, see Binary Upgrade.

SIGINT (Ctrl+C)

SIGINT is context-sensitive:

• Foreground with a TTY (development, cargo run): shutdown only.
• Daemon mode or no TTY (legacy production): triggers binary upgrade and old-process drain, like SIGUSR2.

The legacy SIGINT upgrade path exists for backward compatibility with deployments that send SIGINT from init scripts. New deployments should use SIGUSR2 for upgrade and SIGTERM for shutdown explicitly.

systemd integration

PgDoorman supports Type=notify. The shipped pg_doorman.service unit runs the binary in the foreground and notifies systemd via sd_notify:

[Service]
Type=notify
NotifyAccess=exec
ExecStart=/usr/bin/pg_doorman /etc/pg_doorman/pg_doorman.toml
ExecReload=/bin/kill -SIGUSR2 $MAINPID
ExecStop=/bin/kill -SIGTERM $MAINPID
SyslogIdentifier=pg_doorman
KillMode=mixed
TimeoutStopSec=60
Restart=on-failure
Nice=-15
User=postgres
Group=postgres
LimitNOFILE=65536

sd_notify READY=1 is sent after the listening socket is bound and pools are initialized. sd_notify MAINPID=<child> is sent during binary upgrade so systemd tracks the new process correctly.

With this unit, systemctl reload pg_doorman means binary upgrade (SIGUSR2), not config reload (SIGHUP). Use kill -HUP <pid> when you only need to reload configuration.

If you migrate from Type=forking + --daemon, drop --daemon and switch to Type=notify — fewer moving parts and proper readiness tracking. Older deployments using --daemon continue to work but do not benefit from sd_notify.

Daemon mode

pg_doorman --daemon forks into the background and writes its PID to daemon_pid_file (default /tmp/pg_doorman.pid). For systemd users, prefer Type=notify over --daemon.

general:
  daemon_pid_file: "/var/run/pg_doorman.pid"

Where to next

• Binary Upgrade — full upgrade protocol with TLS migration.
• Troubleshooting — what to check when reload does not pick up changes.
• TLS — SIGHUP reload semantics for server-side certificates.

Fastpath and Large Objects

Use this page when pgjdbc or Hibernate works with PostgreSQL large objects through a pg_doorman transaction pool.

pgjdbc LargeObjectManager uses PostgreSQL Fastpath FunctionCall (F) for large object functions such as lo_creat, lo_open, lo_read, lo_write, and lo_close. PostgreSQL replies with FunctionCallResponse (V) and then ReadyForQuery (Z). The V message contains the function result; the transaction status is in the following ReadyForQuery.

Before 3.10.7, pg_doorman did not forward FunctionCall in transaction pooling. A client could send a large object call and then wait forever for a response. Since 3.10.7, pg_doorman forwards the call, passes FunctionCallResponse back to the client, and releases the backend only after ReadyForQuery says the session is idle.

Transaction Pooling

Large object descriptors live inside a PostgreSQL transaction. If ReadyForQuery reports status T or E after a fastpath call, pg_doorman keeps the same backend assigned to the client. The backend is released only after PostgreSQL later reports idle status I, normally after COMMIT or ROLLBACK.

Autocommit fastpath calls release the backend as soon as ReadyForQuery reports idle.

This matches PgBouncer transaction-pooling behavior for FunctionCall traffic.

Pool Sizing

Each active large object call holds one backend until PostgreSQL sends ReadyForQuery. Size the pool for concurrent large object reads and writes, not only for ordinary SQL statement rate.

Watch these signals after enabling this traffic:

• SHOW POOLS: active clients, active servers, and waiting clients.
• query_wait_timeout errors.
• Latency percentiles for pools used by large object traffic.

If large object bursts push clients close to query_wait_timeout, increase pool capacity for that user/database or reduce application-side large object concurrency.

Large Reads

pg_doorman streams large DataRow, CopyData, and FunctionCallResponse messages when they exceed general.message_size_to_be_stream. A large fastpath lo_read response is forwarded without buffering the full response in pg_doorman memory first.

Streaming limits pg_doorman heap use; it does not make large single reads free. A large read still holds a backend and socket buffers while PostgreSQL sends the response, and PostgreSQL protocol message limits still apply. Keep application-side large object reads chunked.

Timeouts

server_lifetime applies to idle pooled backends. It does not interrupt a backend that is serving a large object read or write.

Large object descriptors also depend on PostgreSQL transaction state. If an application leaves a large object transaction idle between fastpath calls, PostgreSQL idle_in_transaction_session_timeout can terminate the backend. pg_doorman then returns a connection error to the client. Keep large object transactions short, or tune PostgreSQL timeouts for sessions that perform large object work.

Monitoring the Query Interner

The query interner deduplicates Parse texts in pg_doorman's process memory. Two halves run different policies: NAMED is bounded by passive Arc::strong_count GC, ANON by per-entry idle TTL (query_interner_anon_idle_ttl_seconds). Both expose Prometheus gauges, eviction counters, and a sweep duration histogram, plus a counter for the synthetic SQLSTATE 26000 returned to clients whose anonymous prepared statement is no longer in any cache.

This page is the operator companion to those metrics: dashboard recipe, alert rules, and tuning guidance.

Dashboard

Above-the-fold (top three panels)

1. Stat — interner total bytes. sum(pg_doorman_query_interner_bytes) per instance, with red threshold at 1.5 GiB and yellow at 500 MiB. Drives most memory-related decisions.
2. Time series — entries by kind. Two lines:
   • pg_doorman_query_interner_entries{kind="named"}
   • pg_doorman_query_interner_entries{kind="anonymous"} Six-hour window. Sustained growth on either line is the cue to open the drill-down panels.
3. Time series — synthetic 26000 rate. rate(pg_doorman_query_interner_synthetic_misses_total[5m]). Flat zero is the normal case; any spike means TTL trimmed something a client referenced or the driver depended on cross-batch unnamed.

Drill-down

4. Eviction rate, stacked by reason: sum by (kind, reason) (rate(pg_doorman_query_interner_evictions_total[5m]))
5. GC sweep duration heatmap: histogram_quantile(0.5, rate(pg_doorman_query_interner_gc_duration_seconds_bucket[5m])), with a P99 line on top.
6. Average bytes per entry: pg_doorman_query_interner_bytes / pg_doorman_query_interner_entries, per kind.

Correlations

7. Anon eviction rate vs total query rate. Linear correlation = normal traffic; non-linear = ORM dynamic-SQL explosion.
8. Synthetic 26000 rate vs P99 query latency. Correlation = TTL is killing real traffic; investigate the slow path.

Recommended dashboard variables

• instance — to compare replicas.
• kind — to slice gauges and counters down to one half at a time.

Pool, user, and database labels do not apply to the interner — it is process-global. Adding those labels to interner panels would mislead readers.

Alert rules

A complete groups: block is shipped at monitoring/prometheus-rules/query-interner.yaml. The five alerts:

• PgDoormanAnonInternerMemoryHigh (critical) — ANON bytes

  1.5 GiB. Tighten TTL or check for ORM dynamic SQL.

• PgDoormanAnonTTLTooShort (critical) — synthetic 26000 rate

  1/s for 10 min. Find whether the misses come from client LRU churn, RESET INTERNER, anonymous TTL eviction, or the offending driver before changing TTL.

• PgDoormanAnonInternerNotShrinking (warning) — ANON keeps growing while TTL evictions are flat. Either TTL is set too long or the workload is pushing unique queries faster than they expire.
• PgDoormanInternerGCSlow (warning) — GC sweep P99 > 50 ms for 15 min. Lengthen query_interner_gc_interval_seconds (this knob is restart-only; reload won't change the running sweep cadence) or shrink the interner via RESET INTERNER plus cache-size tuning.
• PgDoormanNamedInternerGrowsUnbounded (warning) — NAMED entries above 100k with near-zero eviction rate. Almost always a code bug holding Arc<str> strong refs forever.

Cold-start guard: every alert above uses for: > 5m, so the empty interner immediately after process start does not trip them.

Sizing

Steady-state ANON interner footprint, assuming 50% of queries take the prepared path and the average SQL text is 2 KiB:

The interner is process-global, so the cluster-wide footprint scales linearly with the number of pg_doorman replicas. Use this as the starting estimate for query_interner_anon_idle_ttl_seconds and the RAM budget per host; the live pg_doorman_query_interner_bytes gauge is authoritative.

Effective TTL

The eviction policy is two-cycle mark-and-sweep over a sweep that ticks at gc_interval / 4. With the defaults (gc_interval = 60 s, anon_idle_ttl = 60 s) the sweep runs every 15 s, so an entry is marked between 60 s and 75 s after it last got touched, and removed on the next sweep that still sees it as a candidate — i.e. between 75 s and 120 s of total idle time. A shorter TTL than the 60 s default does not buy you sub-15-second eviction: gc_interval controls the sweep cadence.

Tuning recipes

Reduce TTL when memory pressure dominates

Trigger: PgDoormanAnonInternerNotShrinking fires, ANON bytes approaches the budget for the host.

Action: drop query_interner_anon_idle_ttl_seconds in general config (e.g. 60 → 30). Reload pg_doorman. Watch the eviction rate catch up to the new threshold.

Investigate synthetic 26000 before raising TTL

Trigger: PgDoormanAnonTTLTooShort fires.

Action: identify which client and what query — the synthetic-miss counter has no labels, so use the WARN log line emitted with each miss for client / pool / connection_id context. Check pg_doorman_clients_prepared_anonymous_evictions_total and pg_doorman_query_interner_evictions_total{kind="anonymous"} before changing config. If misses come from the client Anonymous LRU, increase client_anonymous_prepared_cache_size. If they come from anonymous TTL or from a driver that legitimately reuses unnamed Bind across batches, raise TTL to cover the gap (e.g. 60 → 300). If it is not, switch that client to named prepared.

Run RESET INTERNER

Trigger: ad-hoc diagnostics or memory containment incident.

Action: psql "host=127.0.0.1 port=6432 user=admin dbname=pgdoorman" -c "RESET INTERNER". Returns CommandComplete RESET. In-flight clients re-Parse on next reuse; short-lived ones see no effect because their last_anonymous_hash remembers the hash they registered before the reset, and the next Bind discovers the missing entry and emits 26000 once before the client driver re-issues Parse.

Recording rules

Cluster-wide aggregates worth pre-computing for cheaper dashboards:

groups:
  - name: pg_doorman_query_interner_recording
    interval: 30s
    rules:
      - record: pg_doorman:query_interner_total_bytes:5m
        expr: sum without (instance) (pg_doorman_query_interner_bytes)
      - record: pg_doorman:query_interner_eviction_rate:5m
        expr: |
          sum without (instance) (rate(pg_doorman_query_interner_evictions_total[5m]))

The first lets the cluster-wide stat panel scrape one series; the second drives the eviction-rate-by-reason panel without re-running rate() on every dashboard load.

Troubleshooting

Symptoms you are likely to hit during the first week of running PgDoorman, and what to look at when you do.

Authentication errors when connecting to PostgreSQL

Symptom: PgDoorman accepts the client connection, but the first query returns password authentication failed from PostgreSQL.

The pool username matches the backend role

PgDoorman uses passthrough authentication by default — the cryptographic proof the client sent (MD5 hash or SCRAM ClientKey) is reused to authenticate against PostgreSQL. The password field in your config must hold the exact hash from pg_authid / pg_shadow:

SELECT usename, passwd FROM pg_shadow WHERE usename = 'your_user';

For SCRAM, both processes must see the same salt and iteration count — even a one-character difference in the stored verifier breaks passthrough.

The pool username differs from the backend role

When the client-facing username in PgDoorman does not match the actual PostgreSQL role, passthrough cannot work — there is nothing to pass through. Provide explicit credentials:

users:
  - username: "app_user"              # client-facing name
    password: "md5..."                # hash for client → pg_doorman auth
    server_username: "pg_app_user"    # actual PostgreSQL role
    server_password: "plaintext_pwd"  # plaintext password for that role
    pool_size: 40

This is also the path for JWT auth, where the client never sends a password and there is nothing to pass through.

Configuration file not found

Symptom: PgDoorman exits with configuration file not found on startup.

By default the binary looks for pg_doorman.toml in the current working directory. Either name your file that way and cd to its directory, or pass the path explicitly:

pg_doorman /etc/pg_doorman/pg_doorman.yaml

Validate before starting:

pg_doorman -t /etc/pg_doorman/pg_doorman.yaml

Clients receive 58006 (pooler is shut down now)

The pool is shutting down or the binary upgrade was issued in daemon mode. Check the server logs around the timestamp of the error:

• Got SIGUSR2, starting binary upgrade … — a binary upgrade is in progress. In foreground mode, idle clients should migrate transparently; only clients still inside a transaction past shutdown_timeout get 58006. In daemon mode there is no fd-based migration and every client gets 58006 when its connection is closed. See Binary upgrade → Troubleshooting.
• No SIGUSR2 log line — someone sent SIGTERM or SIGINT and the pooler shut down without spawning a successor. Check the systemd unit, the pid in question, and your operator runbook.

If the 58006 happened during a planned upgrade, this is expected for that subset of clients. Configure the application's connection pool to retry on transient errors.

Pool size too small

Symptom: Queries take much longer end-to-end than they do when run directly against PostgreSQL.

Look at SHOW POOLS and SHOW POOLS_EXTENDED:

cl_waiting   — how many clients are queued for a backend right now
maxwait      — longest time any waiter has been queued, in seconds
sv_idle      — idle backends in the pool
sv_active    — backends currently checked out

If cl_waiting > 0 consistently and sv_idle == 0, the pool is undersized for the load. Either raise pool_size for that user, or look at why sv_active stays high — long transactions, idle-in-transaction sessions, or a slow downstream call holding the backend.

If you are also using max_db_connections, watch SHOW POOL_COORDINATOR for evictions (donors are giving up connections under pressure) and exhaustions (the cap was hit even after evictions). See Pool Coordinator.

Where to file what is left

Admin Commands

PgDoorman exposes a Postgres-compatible admin database. Connect to the same port as your data clients, but with dbname=pgdoorman and the admin credentials from your config:

psql -h 127.0.0.1 -p 6432 -U admin pgdoorman

Or via psql connection string:

psql "host=127.0.0.1 port=6432 user=admin dbname=pgdoorman"

Admin commands are read with SHOW <subcommand> or executed with bare verbs (PAUSE, RESUME, RECONNECT, RELOAD, SHUTDOWN, RESET INTERNER, SET <param> = <value>).

SHOW commands

SHOW POOL_COORDINATOR and SHOW POOL_SCALING have no equivalent in PgBouncer or Odyssey — they expose PgDoorman-specific machinery.

Control commands

PAUSE/RESUME are useful during failovers or maintenance windows. RECONNECT after rotating credentials in pg_authid ensures backends use the new password.

Reading common output

SHOW POOLS

database | user | cl_idle | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait
mydb     | app  | 12      | 4         | 0          | 4         | 36      | 0       | 0.0

• cl_waiting > 0 means clients are stuck waiting for a backend. Either raise pool_size or check for slow queries.
• sv_idle matches free backends; sv_active is in-use; sv_used is reserved by the coordinator (see below).
• maxwait is the longest current wait in seconds. If it grows beyond query_wait_timeout, clients get errors.

SHOW STARTUP_PARAMETERS

user | database | parameter         | value             | source  | state
app  | mydb     | statement_timeout | 5s                | general | applied
app  | mydb     | plan_cache_mode   | force_custom_plan | pool    | applied

• source shows where the value came from: general, pool, or auth_query.
• state shows whether the next backend StartupMessage will carry the value: applied, dropped_due_to_budget, or stale.

SHOW POOL_COORDINATOR

database | max_db_conn | current | reserve_size | reserve_used | evictions | reserve_acq | exhaustions
mydb     | 80          | 78      | 16           | 2            | 142       | 18          | 0

• evictions rising rapidly: a user is starved repeatedly. Set or raise min_guaranteed_pool_size for that user.
• reserve_acq high: bursts are normal but you might be undersized. Consider raising max_db_connections instead of relying on the reserve.
• exhaustions non-zero: even reserve was full. Clients hit query_wait_timeout. Raise the cap.

See Pool Coordinator for tuning.

SHOW POOL_SCALING

user | database | inflight | creates | gate_waits | burst_gate_budget_ex | antic_notify | antic_timeout | create_fallback | replenish_def
app  | mydb     | 1        | 12345   | 87         | 3                    | 142          | 8             | 22              | 0

• inflight is current backend creations in progress.
• gate_waits rising: scaling_max_parallel_creates is throttling you. Acceptable if PostgreSQL is under load; raise it if PG can handle more parallel connect() calls.
• antic_notify vs antic_timeout ratio: high timeout count means anticipation is not finding a returning connection in time. Raise scaling_warm_pool_ratio so the pool grows ahead of demand.
• create_fallback rising means pre-replacement is firing — connections expired before naturally being returned.

See Pool Pressure → Tuning.

Authentication

The admin database uses the credentials from general.admin_username and general.admin_password:

general:
  admin_username: "admin"
  admin_password: "change_me"

Admin connections do not pass through pg_hba.conf rules — they go directly to the admin handler. Restrict admin access at the network layer (listen_addresses, firewall) or use Unix sockets.

Where to next

• Prometheus reference — the metric form of the same state.
• Pool Coordinator — what SHOW POOL_COORDINATOR is telling you.
• Pool Pressure — what SHOW POOL_SCALING is telling you.
• Troubleshooting — common failure modes and their SHOW output.

Web UI

pg_doorman ships a single-page operator console that runs on the same listener as the Prometheus exporter. The frontend bundle is embedded in the binary, so the deployment story is identical to a UI-less build: one process, one binary, one TCP port.

Enabling

The console lives under the [web] section of the config. The legacy [prometheus] block name is still accepted as an alias.

[web]
enabled = true
host = "0.0.0.0"
port = 9127

# Operator console (off by default)
ui = true
ui_anonymous = false
log_tap_max_entries = 8192

web.ui = true is silently demoted to "metrics only" at startup when general.admin_password is empty or the literal "admin". The listener keeps serving /metrics, but every admin-only endpoint would otherwise be trivially open. Set a real password before flipping ui = true. The log line web.ui = true ignored: admin_password is default/empty confirms this gate fired.

URL endpoints

Access roles

The listener resolves every request to one of three roles. The role check runs on the server; the SPA mirrors it on the client only to hide controls the operator cannot use.

When a request carries both Basic and an SSO token, the listener prefers Basic. A correct admin password resolves to Admin regardless of any SSO state. A wrong Basic password does not block the SSO branch: the SSO sources still validate, and a valid JWT resolves to Sso (or Admin, depending on the group claim). This covers the common case of a stale JWT in localStorage next to a working Basic password.

The Basic password compare runs in constant time relative to the configured credentials. JWTs are validated against the public key in [web].sso_public_key_file; the listener caches the parsed key for the process lifetime and reloads it on RELOAD.

The SPA fetch wrapper sends Accept: application/json, which makes the listener emit a plain 401 without WWW-Authenticate: Basic. Without that, the browser would cache whatever the operator typed in its native Basic dialog and replay it on top of the React sign-in modal. Tools that send Accept: */* (curl, gh) still receive the challenge and behave normally.

401 Unauthorized is returned when no credentials reached the listener or every credential failed to parse or validate. 403 Forbidden is returned when credentials validated but the resolved role is too low for the path; the body is {"error":"forbidden","message":"admin role required"}. The SPA re-opens the sign-in modal on 401 and shows a non-blocking "admin role required" banner on 403.

Configuring SSO

SSO is opt-in. With [web].sso_enabled = false (the default), the listener serves only the Anonymous and Admin (Basic) roles. To wire an external SSO proxy:

1. Obtain the RSA public key the proxy uses to sign JWTs and store it in a PEM file (e.g. /etc/pg_doorman/sso-public.pem). For oauth2-proxy, extract it from the private key with openssl rsa -in private.pem -pubout -out public.pem. For Keycloak, see Keycloak below.

2. Add the SSO fields to [web]:

   [web]
   enabled = true
   ui = true
   host = "127.0.0.1"
   port = 9127
   ui_anonymous = false
   
   sso_enabled = true
   sso_proxy_url = "https://sso.example.com/oauth2/start"
   sso_public_key_file = "/etc/pg_doorman/sso-public.pem"
   sso_audience = ["pg_doorman"]
   sso_allowed_users = ["*"]
   

3. Reload the config with kill -SIGHUP <pid> or psql -h <host> -p 6432 -U admin -d pgbouncer -c 'RELOAD'.

4. Verify with curl http://<host>:9127/api/auth/config. The response should carry "sso_enabled":true and the configured sso_proxy_url.

Promoting SSO users to Admin via group claim

By default an SSO login lands in Sso — read-only with access to logs and SQL text, but no POST /api/admin/*. To let SSO operators run mutating admin actions without sharing the Basic password, configure sso_groups_claim and sso_admin_groups:

[web]
sso_enabled = true
sso_public_key_file = "/etc/pg_doorman/sso-public.pem"
sso_audience = ["pg_doorman"]
sso_groups_claim = "groups"
sso_admin_groups = ["pg-doorman-admins"]

When the validated JWT carries "groups": [..., "pg-doorman-admins"], the request resolves to Admin. The access log records the promotion as auth_role=admin auth_source=sso, so SSO admins are still distinguishable from Basic admins. /api/auth/config reports sso_admin_groups_configured = true, which lets the SPA stop promising "SSO grants read-only access" in the sign-in modal.

Keycloak

Keycloak signs every JWT with the realm's RSA key. Export the public half once per realm into a PEM file pg_doorman can read.

The non-interactive way uses the realm's JWKS endpoint:

REALM=https://kc.example.com/realms/operators
curl -s "$REALM/protocol/openid-connect/certs" \
  | jq -r '.keys[] | select(.alg=="RS256") | "-----BEGIN CERTIFICATE-----\n" + .x5c[0] + "\n-----END CERTIFICATE-----"' \
  | openssl x509 -pubkey -noout \
  > /etc/pg_doorman/sso-public.pem

Or copy it from the admin UI: Realm settings → Keys → row with Algorithm = RS256 and Use = SIG → Public key → wrap the copied base64 body into a -----BEGIN PUBLIC KEY----- PEM file.

A Keycloak-backed [web] section then looks like this:

[web]
sso_enabled = true
sso_proxy_url = "https://kc.example.com/realms/operators/protocol/openid-connect/auth"
sso_public_key_file = "/etc/pg_doorman/sso-public.pem"
sso_audience = ["pg_doorman"]    # client_id configured on Keycloak
sso_groups_claim = "groups"      # default with the "groups" mapper enabled
sso_admin_groups = ["pg-doorman-admins"]

For Admin via group claim to work, add a Group Membership mapper to the client (Clients → your client → Mappers). Without that mapper Keycloak issues tokens without groups, and every operator stays on Sso.

When Keycloak rotates the realm signing key, refetch the PEM and issue RELOAD. pg_doorman picks the new key up without a restart.

When SSO config is broken

A typo in the SSO section never knocks the operator console offline. When sso_enabled = true but the runtime cannot load (missing PEM file, empty audience, unparsable PEM), the listener logs the reason at error level, keeps SSO disabled for that run, and serves only Basic and Anonymous requests. The same reason is shown in two places so an operator notices the broken rollout instead of silently falling back:

• /api/auth/config.sso_config_error carries a human-readable message. The SPA renders a banner with that text in the sign-in modal.
• The pg_doorman_web_sso_config_error Prometheus gauge stays at 1 while SSO is asked-for but not loaded. Pair it with pg_doorman_web_sso_enabled to alert.

Browser sign-in flow

On first load the SPA fetches /api/auth/config and renders the sign-in modal. When the response carries sso_proxy_url, the modal shows a Sign in via SSO button next to the Basic form; otherwise only the Basic form appears.

Clicking Sign in via SSO sends the browser to ${sso_proxy_url}?redirect_to=<current href>. The proxy runs the OAuth/OIDC flow and bounces the browser back with ?token=<jwt>. The SPA stores the token in localStorage, rewrites the URL clean of the parameter, and sends Authorization: Bearer <jwt> on every later request.

The sidebar footer shows the resolved username: admin for Basic, or sso: <preferred_username> for SSO. Sign out clears both pgdoorman.admin-auth and pgdoorman.sso-token from localStorage and re-opens the sign-in modal.

A silent-refresh poller wakes every 60 seconds. When the JWT is less than 90 seconds from exp, the SPA opens a hidden iframe at ${origin}/?sso_silent=1. The App router renders a minimal SilentCallback component there (no normal polling effects), which posts the new token to the parent via window.postMessage. If silent refresh fails:

• when a Basic credential is also present, the SPA discards the SSO token without redirecting and falls back to Basic for further requests;
• otherwise the SPA performs a full redirect through the SSO proxy.

Configure JWT lifetime to at least 5 minutes; tokens shorter than that may expire before the refresh fires.

The SPA never sends cookies (credentials: "omit" on every fetch). The sso_access_token cookie path exists for sidecars, curl, and oauth2-proxy variants that paste the token into a cookie on the shared domain.

The Basic credential lives only in React state by default and is lost on a hard refresh. Remember me on this device in the sign-in modal persists it in localStorage so the console survives a reload. Clearing site storage in the browser wipes both the Basic and the SSO entry.

Access log

Every response (200/401/403/404/5xx, /metrics scrapes included) emits one logfmt line on the pg_doorman::web::access target:

INFO pg_doorman::web::access method=GET path=/api/admin/reload query=false status=200 bytes=42 latency_ms=12 peer=10.0.1.5:42312 auth_role=admin auth_source=basic auth_user=admin

Fields:

• method, path — verb and URL path. Bodies are not logged.
• query=true|false — whether the request carried a query string. The string itself is reduced to a presence flag so JWTs in ?token= never reach the log.
• status, bytes, latency_ms — response status, body size, and end-to-end latency.
• peer — the request peer address. By default this is the TCP peer. When the TCP peer falls in [web].trusted_proxies, the listener parses X-Forwarded-For (or Forwarded, RFC 7239), walks right to left skipping any further trusted hops, and uses the first untrusted address as peer. An untrusted client cannot spoof the field — the proxy headers are ignored when the peer is not trusted.
• auth_role — admin, sso, anonymous, or rejected.
• auth_source — basic, sso, or -.
• auth_user — resolved username, or - for anonymous and rejected.

Levels:

• info — every admin action (POST /api/admin/*), every personal-data read (/api/logs, /api/prepared/text/*, /api/interner/top, /api/top/queries), every auth/SSO endpoint (/api/auth/*, /api/sso/*), and every non-2xx response.
• debug — every other successful 2xx read, anonymous or authenticated. The SPA polls /api/overview, /api/pools, /api/clients, /api/process every 1.5–3 s; with the previous rule that every authenticated 2xx was info, an operator sitting on the Logs page saw their own polls. Routine reads are logged at debug, so RUST_LOG=info is limited to admin actions, auth traffic, and failures.

The dedicated pg_doorman::web::access target lets operators filter the access feed independently of the rest of the logger. The LogTap filter dropdown in the Logs page can include or exclude this target with one click.

Real client IP behind a reverse proxy

By default peer records the TCP address that connected to the listener, which is the proxy when pg_doorman sits behind one. List the proxy's CIDR in [web].trusted_proxies to record the real client IP:

[web]
trusted_proxies = ["10.0.0.0/8", "192.168.0.0/16"]

Both X-Forwarded-For and Forwarded are recognised. Multiple trusted hops in the chain are skipped. An untrusted client that sends X-Forwarded-For is ignored, so this knob does not give arbitrary callers control over the access-log field.

Metrics

A sustained spike in signature means the SSO proxy rotated keys without updating sso_public_key_file. A spike in allowlist means a JWT outside sso_allowed_users is repeatedly trying to log in. A spike in 4xx for the sso role usually points at a broken proxy in front of pg_doorman.

Troubleshooting

401 on a JWT that should be valid. Check that aud matches one of the sso_audience values and that exp has not passed. Validate the PEM with openssl rsa -pubin -in <pem> -text -noout. The pg_doorman_web_sso_validation_errors_total{reason} counter shows which check failed.

403 on a JWT that should be valid. The path requires Admin (e.g. POST /api/admin/reload). Either log in with the Basic admin password, or add the user's group to [web].sso_admin_groups and reload the config.

SPA never offers Sign in via SSO. /api/auth/config is not returning sso_proxy_url. Either [web].sso_enabled = false, or sso_proxy_url is unset, or the runtime failed to load (look for sso_config_error in the same response).

Silent refresh does not fire. The SSO proxy must return a fresh token without rendering a login screen when the iframe carries an active session. With oauth2-proxy, set --silent-refresh=true.

Cookie-based JWT is ignored. The cookie must reach pg_doorman on the same domain, and aud must be in sso_audience. The SPA itself sends no cookies; cookie auth targets curl, sidecars, and oauth2-proxy variants that forward the token via cookie on the shared domain.

Pages

The sidebar has eight routes. War room opens from Overview. Pages that expose SQL text or logs require the Sso or Admin role and are hidden from anonymous users.

Overview (/overview)

Default page for pooler health: main metrics, queues, pool saturation, common SQLSTATE codes, and a collapsed resource block. If Patroni fallback is active, a banner lists the affected pools.

Pools (/pools)

Table of all user@database pools: size, active connections, waiting clients, p95, errors, saturation, and fallback state. Selecting a row opens Pool detail.

Pool detail (/pools/:poolId)

Single-pool view: mode, limits, current connections, TLS, fallback state, SQLSTATE counts, PostgreSQL startup parameters, and active threshold reasons. Pool actions PAUSE, RESUME, RECONNECT, and global RELOAD are available here.

Clients (/clients)

Client table with URL filters:

/clients?pool=shop_checkout&state=waiting&user=app

Filters cover pool, database, user, state, application_name, and peer address. Sorting covers queries, errors, connection age, and current-query age. Use it with Servers to map a client to a PostgreSQL pid.

Servers (/servers)

Backend connections from SHOW SERVERS: server_id, process_id, database, user, application, state, active-query age, counters, traffic, and TLS. Use a client's server_id here to find the pid in pg_stat_activity.

Apps (/apps)

One row per application_name: active clients, qps, tps, totals, and err / 1k q.

Caches (/caches)

Prepared-statement cache by pool and process-wide SQL-text cache. Both can show SQL text, so both require Sso or Admin.

Logs (/logs)

LogTap stream with URL filters:

/logs?level=ERROR&q=53300

Pause freezes the view only; the server buffer keeps filling. If [web].log_tap_max_entries = 0, the page reports that log streaming is disabled. Access requires Sso or Admin.

Config & state (/config)

Read-only mirror of SHOW CONFIG, SHOW DATABASES, SHOW USERS, SHOW AUTH_QUERY, SHOW LOG_LEVEL, SHOW STARTUP_PARAMETERS, SHOW SOCKETS, SHOW POOL_SCALING, and SHOW POOL_COORDINATOR. It shows which config keys apply on RELOAD and which require restart. Reload config is available only to Admin.