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