PgDoorman Basic Usage Guide

PgDoorman is a high-performance PostgreSQL connection pooler based on PgCat. This comprehensive guide will help you get started with configuring, running, and managing PgDoorman for your PostgreSQL environment.

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 and their descriptions, see the Settings Reference Guide.

Automatic Configuration Generation

PgDoorman provides a powerful generate command that can automatically create a configuration file by connecting to your PostgreSQL server and detecting databases and users. By default, the generated config includes detailed 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 your PostgreSQL server, automatically detects all databases and users, and creates a complete, well-documented configuration file. This is especially useful for quickly setting up PgDoorman in new environments.

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 provides a powerful administrative interface that allows you to monitor and manage the connection pooler. You can access this interface by connecting to the special administration database named pgdoorman:

$ 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|CLIENTS|SERVERS|USERS|VERSION
	SHOW LISTS
	SHOW CONNECTIONS
	SHOW STATS
	RELOAD
    SHUTDOWN
	SHOW

# 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 with the following metrics:

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 low-level information about network sockets:

pgdoorman=> SHOW SOCKETS;

This command includes all information shown in SHOW CLIENTS and SHOW SERVERS plus additional low-level details about the socket connections.

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

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

Signal Handling

PgDoorman responds to standard Unix signals for control and management. These signals can be sent using the kill command (e.g., kill -HUP <pid>).