PgDoorman Basic Usage Guide

PgDoorman is a PostgreSQL connection pooler based on PgCat. This guide covers configuration, operation, and administration.

Command Line Options

PgDoorman offers several command-line options to customize its behavior when starting the service:

$ 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                 disable colors in the log output [env: NO_COLOR=]
  -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

PgDoorman will handle the connection pooling transparently, so your application doesn't need to be aware that it's connecting through a pooler.

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>).