Pool configuration

autopg-pool is configured with a single TOML file, mounted at /etc/autopgpool/autopgpool.toml. It has three kinds of section: the users that may connect, the pools they route to, and the global pgbouncer settings.

The TOML file

A complete, if small, configuration:

[[users]]
username = "app"
password = "$APP_PASSWORD"
grants = ["main"]

[pools.main.remote]
host = "postgres"
port = 5432
database = "main"
username = "main_user"
password = "$MAIN_DB_PASSWORD"

[pgbouncer]
listen_addr = "0.0.0.0"
listen_port = 6432
pool_mode = "transaction"
max_client_conn = 200
default_pool_size = 20

Any $NAME reference is substituted from the container environment at startup, so passwords live in Docker secrets rather than in the file.

Users and grants

Each [[users]] entry is a client that may connect to the pooler. grants lists the pools that user is allowed to reach, which keeps your application users separate from the backend database credentials.

[[users]]
username = "app"
password = "$APP_PASSWORD"
grants = ["main", "analytics"]

[[users]]
username = "reporting"
password = "$REPORTING_PASSWORD"
grants = ["analytics"]

MD5 hashes for these passwords are computed automatically and written to the generated userlist.txt.

Pools and upstreams

A pool maps a name to an upstream Postgres. The [pools.<name>.remote] table describes where to connect and with which backend credentials.

[pools.main.remote]
host = "10.0.0.5"
port = 5432
database = "main"
username = "main_user"
password = "$MAIN_DB_PASSWORD"

You can define several pools to route different databases through the same pooler, each with its own upstream and, if you want, its own pool mode.

[pools.analytics]
pool_mode = "session"

[pools.analytics.remote]
host = "10.0.0.6"
port = 5432
database = "analytics"
username = "analytics_user"
password = "$ANALYTICS_PASSWORD"

Pooling modes

The pool mode controls when a server connection returns to the pool. Set it globally under [pgbouncer] or per pool.

Mode	A server connection is held for	Use when
`session`	the whole client session	clients need session state, like `SET` or advisory locks
`transaction`	one transaction	the common default for web apps, maximum reuse
`statement`	one query	autocommit-only workloads

transaction is the default and the right choice for most applications. Note that transaction and statement pooling do not preserve session-level state between statements.

Global settings

The [pgbouncer] table holds pooler-wide settings. The defaults are chosen for a typical production deployment.

Setting	Default	Description
`listen_addr`	`0.0.0.0`	Address the pooler binds.
`listen_port`	`6432`	Port clients connect to.
`pool_mode`	`transaction`	Default pooling mode.
`max_client_conn`	`100`	Total client connections accepted.
`default_pool_size`	`10`	Server connections per pool.
`idle_transaction_timeout`	`60`	Seconds before an idle transaction is closed, so a stuck client cannot saturate the pool.
`max_prepared_statements`	`10`	Prepared statement support for drivers like asyncpg and SQLAlchemy.
`auth_type`	`md5`	Authentication method.

Anything pgbouncer supports that is not surfaced directly can be passed through:

[pgbouncer.passthrough_kwargs]
server_reset_query = "DISCARD ALL"
log_connections = 1
log_disconnections = 1

Environment variables

autopg-pool reads no special environment variables of its own. Instead, any $NAME you reference in the TOML is filled from the environment at startup. Pass those values into the container the usual way:

environment:
  - APP_PASSWORD=changeme
  - MAIN_DB_PASSWORD=changeme
  - ANALYTICS_PASSWORD=changeme

This keeps every credential out of the config file and lets you manage them as Docker or orchestrator secrets.