PgDoorman

https://ozontech.github.io/pg_doorman
Admin
PgDoorman is a multi-threaded PostgreSQL connection pooler written in Rust that provides...
Tokens:127,450
Snippets:1,001
Trust Score:9.7
Update:3 weeks ago
Show doc for...
Tokens:
Raw
### Build and install PgDoorman from source

Source: https://ozontech.github.io/pg_doorman/tutorials/installation.html

Clones the repository, builds a release binary, and installs it to /usr/local/bin. Requires Rust 1.87 or newer.

```bash
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
```

--------------------------------

### Start patroni_proxy with configuration file

Source: https://ozontech.github.io/pg_doorman/tutorials/patroni-proxy.html

Starts the patroni_proxy with a specified YAML configuration file. Optionally, set RUST_LOG=debug for detailed logging.

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

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

--------------------------------

### TOML configuration example

Source: https://ozontech.github.io/pg_doorman/reference/general.html

Example TOML configuration for pg_doorman, equivalent to the YAML example. Supported for backward compatibility. Uses the same options and structure as YAML.

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

[pools.mydb]
server_host = "localhost"
server_port = 5432
pool_mode = "transaction"

[[pools.mydb.users]]
username = "myuser"
password = "md5..."  # hash from pg_shadow / pg_authid
pool_size = 40

```

--------------------------------

### Start PgDoorman with explicit config path

Source: https://ozontech.github.io/pg_doorman/tutorials/troubleshooting.html

Starts PgDoorman with an explicit configuration file path. By default the binary looks for pg_doorman.toml in the current working directory.

```bash
pg_doorman /etc/pg_doorman/pg_doorman.yaml
```

--------------------------------

### Download and install static binary from releases page

Source: https://ozontech.github.io/pg_doorman/tutorials/installation.html

Downloads a static binary from the releases page, verifies its SHA-256 checksum, and installs it to /usr/local/bin. Replace VERSION and TARGET with values from the releases page. The checksum step is required to avoid trusting the network path.

```bash
# 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/

```

--------------------------------

### Install, validate, trigger, and verify binary upgrade

Source: https://ozontech.github.io/pg_doorman/tutorials/binary-upgrade.html

Manual upgrade steps for direct-binary deployments: install the new binary, validate with `-t`, trigger via `systemctl reload` (which sends SIGUSR2), and verify the new MainPID and client connections.

```bash
install -m 0755 pg_doorman_new /usr/bin/pg_doorman
```

```bash
/usr/bin/pg_doorman -t /etc/pg_doorman/pg_doorman.toml
```

```bash
sudo systemctl reload pg_doorman.service
```

```bash
systemctl show -p MainPID --value pg_doorman.service
psql -h pgdoorman -p 6432 -c 'SHOW POOLS;'  # served by the new process
```

--------------------------------

### pg_hba configuration examples in TOML

Source: https://ozontech.github.io/pg_doorman/reference/general.html

Shows three ways to specify the general.pg_hba setting: as a multi-line TOML string, loading from a file, or embedding as a single-line string. The multi-line example includes sample rules for host, hostssl, and hostnossl connection types.

```toml
general.pg_hba = """
# type   database  user   address         method
host     all       all    10.0.0.0/8      md5
hostssl  all       all    0.0.0.0/0       scram-sha-256
hostnossl all      all    192.168.1.0/24  trust
"""

# Or load from file
# pg_hba = { path = "./pg_hba.conf" }

# Or embed as a single-line string
# pg_hba = { content = "host all all 127.0.0.1/32 trust" }
```

--------------------------------

### Include files configuration example

Source: https://ozontech.github.io/pg_doorman/reference/general.html

Example of including external configuration files in a pg_doorman config. Include files can be in either YAML or TOML format, and formats can be mixed (e.g., a YAML main config can include TOML files).

```yaml
include:
  files:
    - "pools.yaml"
    - "users.toml"

```

--------------------------------

### Install pg-doorman from Ubuntu PPA

Source: https://ozontech.github.io/pg_doorman/tutorials/installation.html

Installs pg-doorman from the Ubuntu PPA. Requires the `add-apt-repository` command and an Ubuntu release supported by the PPA (jammy, noble, questing, or resolute). The package is built without TLS support.

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

```

--------------------------------

### SHOW PREPARED_STATEMENTS example output

Source: https://ozontech.github.io/pg_doorman/tutorials/prepared-statements.html

Example output of the SHOW PREPARED_STATEMENTS admin command. Columns are pool, hash, name, query, count_used, and kind. The kind column reports named, anonymous, or mixed based on client usage over the entry's lifetime.

```text
 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

```

--------------------------------

### Access log line example

Source: https://ozontech.github.io/pg_doorman/guides/web-ui.html

Example log line emitted for every response on the pg_doorman::web::access target. Fields include method, path, query flag, status, bytes, latency_ms, peer, auth_role, auth_source, and auth_user.

```logfmt
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
```

--------------------------------

### Shell test feature file example

Source: https://ozontech.github.io/pg_doorman/tutorials/contributing.html

Example BDD feature file for shell tests. Uses Gherkin syntax with tags @go and @mytag. Background sets up PostgreSQL with trust authentication, applies fixtures, and starts pg_doorman with a config. Scenario runs a Go test command and checks for success and output containing 'PASS'.

```gherkin
@go @mytag
Feature: My feature description

  Background:
    Given PostgreSQL started with pg_hba.conf:
      """
      local all all trust
      host all all 127.0.0.1/32 trust
      """
    And fixtures from "tests/fixture.sql" applied
    And pg_doorman started with config:
      """
      [general]
      host = "127.0.0.1"
      port = ${DOORMAN_PORT}
      admin_username = "admin"
      admin_password = "admin"

      [pools.example_db]
      server_host = "127.0.0.1"
      server_port = ${PG_PORT}

      [pools.example_db.users.0]
      username = "example_user_1"
      password = "md58a67a0c805a5ee0384ea28e0dea557b6"
      pool_size = 40
      """

  Scenario: Test my Go client
    When I run shell command:
      """
      export DATABASE_URL="postgresql://example_user_1:test@127.0.0.1:${DOORMAN_PORT}/example_db?sslmode=disable"
      cd tests/go && go test -v -run TestMyTest ./mypackage
      """
    Then the command should succeed
    And the command output should contain "PASS"
```

--------------------------------

### SHOW STARTUP_PARAMETERS command and sample output

Source: https://ozontech.github.io/pg_doorman/observability/admin-commands.html

Shows startup parameters for backends. The source column indicates where the value came from (general, pool, or auth_query), and state shows whether the next backend StartupMessage will carry the value (applied, dropped_due_to_budget, or stale).

```text
SHOW STARTUP_PARAMETERS
```

```text
user | database | parameter         | value             | source  | state
app  | mydb     | statement_timeout | 5s                | general | applied
app  | mydb     | plan_cache_mode   | force_custom_plan | pool    | applied
```

--------------------------------

### Configure startup_parameters in general and pools.checkout

Source: https://ozontech.github.io/pg_doorman/tutorials/startup-parameters.html

Defines startup_parameters in three layers: general and per-pool. The more specific layer wins per key. After SIGHUP or RELOAD, new backends for the checkout pool start with statement_timeout = 5s, plan_cache_mode = force_custom_plan, and work_mem = 64MB. Other pools keep statement_timeout from general and PG defaults for the rest. Already-open backends are not affected.

```ini
[general.startup_parameters]
statement_timeout = "5s"

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

--------------------------------

### SHOW STARTUP_PARAMETERS

Source: https://ozontech.github.io/pg_doorman/tutorials/startup-parameters.html

Displays the resolved startup parameters for each pool in the admin SQL console. The output shows the user, database, parameter name, value, source, and state for each configured parameter.

```sql
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

```

--------------------------------

### Prometheus percentile metrics example

Source: https://ozontech.github.io/pg_doorman/observability/percentiles.html

Example Prometheus output for query and transaction latency percentiles and average wait time. The avg_wait_time metric is a mean, not a percentile.

```prometheus
# HELP pg_doorman_pools_queries_percentile Query latency percentiles in milliseconds
# TYPE pg_doorman_pools_queries_percentile gauge
pg_doorman_pools_queries_percentile{percentile="50",user="app",database="mydb"} 1.2
pg_doorman_pools_queries_percentile{percentile="90",user="app",database="mydb"} 4.7
pg_doorman_pools_queries_percentile{percentile="95",user="app",database="mydb"} 8.1
pg_doorman_pools_queries_percentile{percentile="99",user="app",database="mydb"} 24.5

# HELP pg_doorman_pools_transactions_percentile Transaction latency percentiles in milliseconds
# TYPE pg_doorman_pools_transactions_percentile gauge
pg_doorman_pools_transactions_percentile{percentile="50",user="app",database="mydb"} 3.8
# ... (90, 95, 99)

# HELP pg_doorman_pools_avg_wait_time Average client wait time in milliseconds
# TYPE pg_doorman_pools_avg_wait_time gauge
pg_doorman_pools_avg_wait_time{user="app",database="mydb"} 0.05
```

--------------------------------

### Example HBA rules for database-specific access

Source: https://ozontech.github.io/pg_doorman/authentication/hba.html

Example rules restricting access per database and user. Rules are evaluated top to bottom, first match wins.

```text
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

```

--------------------------------

### Example HBA rules for SCRAM-only

Source: https://ozontech.github.io/pg_doorman/authentication/hba.html

Example rules forcing SCRAM-SHA-256 for all TCP connections and rejecting others. If the database stores MD5 hashes only, authentication fails with a clear error.

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

```

--------------------------------

### Coordinator + plain mode acquisition flow (JIT) diagram

Source: https://ozontech.github.io/pg_doorman/tutorials/pool-pressure.html

ASCII diagram of the JIT acquisition flow in coordinator + plain mode. Shows the six numbered phases (hot path recycle, warm gate, fast spin, direct handoff, bounded burst gate, server_pool.create()) and the coordinator acquire step that runs inside the burst gate slot only when max_db_connections > 0. The fast path uses non-blocking try_acquire(); the slow path releases the gate slot, waits on the coordinator, and re-acquires the slot.

```text
        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
```

--------------------------------

### Install pg-doorman via distro package manager

Source: https://ozontech.github.io/pg_doorman/index.html

Install pg-doorman on Ubuntu/Debian using the PPA and on Fedora/RHEL using COPR. The distro packages are built without the tls-migration and pam features.

```bash
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
```

--------------------------------

### Healthy percentile output example

Source: https://ozontech.github.io/pg_doorman/observability/percentiles.html

Example output showing healthy latency percentiles. p99 is within 20x of p50, typical for OLTP workloads. Wait time is in microseconds, indicating the pool is not a bottleneck.

```text
queries:    p50=1.2  p90=4.7   p95=8.1   p99=24.5
xacts:      p50=3.8  p90=11.2  p95=18.5  p99=42.7
wait avg:   0.05ms
```

--------------------------------

### pg_hba rule example for admin access

Source: https://ozontech.github.io/pg_doorman/reference/general.html

Example of a pg_hba rule that allows admin access to the pgdoorman database using the trust method. This rule is applied to the special admin database and permits connections from localhost.

```text
host  pgdoorman  admin  127.0.0.1/32  trust
```

--------------------------------

### Inspect pools and clients after binary upgrade with SHOW POOLS and SHOW CLIENTS

Source: https://ozontech.github.io/pg_doorman/tutorials/binary-upgrade.html

SQL commands to run on the new process after a binary upgrade to inspect pool and client state. The old process rejects non-admin connections, so run these on the new process.

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

--------------------------------

### YAML configuration example

Source: https://ozontech.github.io/pg_doorman/reference/general.html

Example YAML configuration for pg_doorman. Defines general settings (host, port, admin credentials) and a pool named 'mydb' with a user entry. The password is expected to be an MD5 hash from pg_shadow/pg_authid.

```yaml
general:
  host: "0.0.0.0"
  port: 6432
  admin_username: "admin"
  admin_password: "change_me_to_a_long_random_secret"

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

```
Tokens:
Raw