pgagroal configuration ​

The configuration is loaded from either the path specified by the -c flag or /etc/pgagroal/pgagroal.conf.

The configuration of pgagroal is split into sections using the [ and ] characters.

The main section, called [pgagroal], is where you configure the overall properties of the connection pool.

The other sections configure each one server. There can be no more than 64 server sections, therefore no more than 64 servers configured as backends. Server sections don't have any requirements to their naming so you can give them meaningful names like [primary] for the primary PostgreSQL instance.

In any case, it is not possible to have duplicated sections, that means section names must be unique within the configuration file.

All properties within a section are in the format key = value.

The characters # and ; can be used for comments. A line is totally ignored if the very first non-space character is a comment one, but it is possible to put a comment at the end of a line. The Bool data type supports the following values: on, yes, 1, true, off, no, 0 and false.

Each value can be quoted by means of " or '. Quoted strings must begin and end with the very same quoting character. It is possible to nest quotes. As an example of configuration snippet including quoting and comments:

# This line is ignored since it starts with '#'
# and so is this one
log_line_prefix = "PGAGROAL #%Y-%m-%d-%H:%M:%S" # quoted because it contains spaces
log_type= console#log to stdout
pipeline = 'performance' # no need to quote since it does not contain any spaces

# This line is ignored since it starts with '#'
# and so is this one
log_line_prefix = "PGAGROAL #%Y-%m-%d-%H:%M:%S" # quoted because it contains spaces
log_type= console#log to stdout
pipeline = 'performance' # no need to quote since it does not contain any spaces

See a more complete sample configuration for running pgagroal on localhost.

[pgagroal] ​

This section is mandatory and the pooler will refuse to start if the configuration file does not specify one and only one. Usually this section is place on top of the configuration file, but its position within the file does not really matter. The available keys and their accepted values are reported in the table below.

Danger zone

Server section ​

Each section with a name different from pgagroal will be treated as an host section. There can be up to 64 host sections, each with an unique name and different combination of host and port settings, otherwise the pooler will complain about duplicated server configuration.

Note, that if host starts with a / it represents a path and pgagroal will connect using a Unix Domain Socket.

pgagroal_hba configuration ​

The pgagroal_hba configuration controls access to pgagroal through host-based authentication.

The configuration is loaded from either the path specified by the -a flag or /etc/pgagroal/pgagroal_hba.conf.

The format of the file follows the similar PostgreSQL HBA configuration format, and as such looks like

#
# TYPE  DATABASE USER  ADDRESS  METHOD
#
host    all      all   all      all

#
# TYPE  DATABASE USER  ADDRESS  METHOD
#
host    all      all   all      all

Remote management users needs to have their database set to admin in order for the entry to be considered.

There could be up to 64 HBA entries in the configuration file.

pgagroal_databases configuration ​

The pgagroal_databases configuration defines limits for a database or a user or both. The limits are the number of connections from pgagroal to PostgreSQL for each entry.

The file also defines the initial and minimum pool size for a database and user pair. Note, that this feature requires a user definition file, see below.

The configuration is loaded from either the path specified by the -l flag or /etc/pgagroal/pgagroal_databases.conf.

#
# DATABASE USER    MAX_SIZE INITIAL_SIZE MIN_SIZE
#
mydb       myuser  all
anotherdb  userB   10           5       3

#
# DATABASE USER    MAX_SIZE INITIAL_SIZE MIN_SIZE
#
mydb       myuser  all
anotherdb  userB   10           5       3

Database Aliases ​

Database aliases allow clients to connect using alternative names for a configured database. This is useful for:

• Application migrations where legacy database names need to be supported
• Multi-tenancy scenarios where different clients use different logical names
• Providing user-friendly names without exposing actual backend database names

Alias Rules and Behavior ​

• Alias Resolution: When a client connects using an alias, pgagroal automatically resolves it to the real database name before establishing or reusing backend connections
• Connection Pooling: Connections established with the real database name can be reused by clients connecting with any of its aliases
• Transparent Mapping: All authentication queries and backend communication use the real database name
• Uniqueness: Aliases must be unique across all database entries and cannot conflict with any real database name
• Limit: Maximum 8 aliases per database entry

Configuration Examples ​

# Database with aliases
production_db=prod,main,primary    myuser    10    5    2    
 
development_db=dev,test,staging,qa  devuser   5     2    1    

new_app_db=legacy_app,old_db      appuser    15    8    3

# Database with aliases
production_db=prod,main,primary    myuser    10    5    2    
 
development_db=dev,test,staging,qa  devuser   5     2    1    

new_app_db=legacy_app,old_db      appuser    15    8    3

There can be up to 64 entries in the configuration file.

In the case a limit entry has incoherent values, for example INITIAL_SIZE smaller than MIN_SIZE, the system will try to automatically adjust the settings on the fly, reporting messages in the logs.

The system will find the best match limit entry for a given DATABASE-USER pair according to the following rules:

1. Use the first entry with an exact DATABASE and USER match.
2. If there is no exact match, use the entry with a USER match and DATABASE set to all.
3. If Rule 2 does not apply, use the entry with a DATABASE match and USER set to all.

Note: For alias matching in Rule 1, if a client connects using an alias name, pgagroal will find the entry where the alias is defined and treat it as an exact database match.

Alias Validation ​

The configuration system validates aliases to ensure:

• No duplicate aliases within the same entry
• No alias conflicts with main database names in other entries
• No duplicate aliases across different entries
• Aliases are not empty strings
• Total alias count does not exceed the maximum limit

Changes to aliases can be reloaded without restarting pgagroal, making it easy to add or modify aliases for existing databases.

pgagroal_users configuration ​

The pgagroal_users configuration defines the users known to the system. This file is created and managed through the pgagroal-admin tool.

The configuration is loaded from either the path specified by the -u flag or /etc/pgagroal/pgagroal_users.conf.

There can be up to 64 users known to pgagroal.

pgagroal_frontend_users configuration ​

The pgagroal_frontend_users configuration defines the passwords for the users connecting to pgagroal. This allows the setup to use different passwords for the pgagroal to PostgreSQL authentication. This file is created and managed through the pgagroal-admin tool.

All users defined in the frontend authentication must be defined in the user vault (-u).

Frontend users (-F) requires a user vault (-u) to be defined.

The configuration is loaded from either the path specified by the -F flag or /etc/pgagroal/pgagroal_frontend_users.conf.

pgagroal_admins configuration ​

The pgagroal_admins configuration defines the administrators known to the system. This file is created and managed through the pgagroal-admin tool.

The configuration is loaded from either the path specified by the -A flag or /etc/pgagroal/pgagroal_admins.conf.

If pgagroal has both Transport Layer Security (TLS) and management enabled then pgagroal-cli can connect with TLS using the files ~/.pgagroal/pgagroal.key (must be 0600 permission), ~/.pgagroal/pgagroal.crt and ~/.pgagroal/root.crt.

pgagroal_superuser configuration ​

The pgagroal_superuser configuration defines the superuser known to the system. This file is created and managed through the pgagroal-admin tool. It may only have one user defined.

The configuration is loaded from either the path specified by the -S flag or /etc/pgagroal/pgagroal_superuser.conf.

Configuration Directory ​

You can specify a directory for all configuration files using the -D flag (or --directory).
Alternatively, you can set the PGAGROAL_CONFIG_DIR environment variable to define the configuration directory.

Behavior:

• When the directory flag (-D) is set, pgagroal will look for all configuration files in the specified directory.
• If a required file is not found in the specified directory, pgagroal will look for it in its default location (e.g., /etc/pgagroal/pgagroal.conf).
• If the file is not found in either location:
  • If the file is mandatory, pgagroal will log an error and fail to start.
  • If the file is optional, pgagroal will log a warning and continue without it.
• All file lookup attempts and missing files are logged for troubleshooting.

Precedence Rules:

• Individual file flags (such as -c, -a, -l, etc.) always take precedence over the directory flag and environment variable for their respective files.
• The directory flag (-D) takes precedence over the environment variable (PGAGROAL_CONFIG_DIR).
• If neither the directory flag nor individual file flags are set, pgagroal uses the default locations for all configuration files.

Using the Environment Variable:

1. Set the environment variable before starting pgagroal:

   export PGAGROAL_CONFIG_DIR=/path/to/config_dir
   pgagroal -d

   export PGAGROAL_CONFIG_DIR=/path/to/config_dir
   pgagroal -d

2. If both the environment variable and the -D flag are set, the flag takes precedence.

Example:

pgagroal -D /custom/config/dir -d

pgagroal -D /custom/config/dir -d

or

export PGAGROAL_CONFIG_DIR=/custom/config/dir
pgagroal -d

export PGAGROAL_CONFIG_DIR=/custom/config/dir
pgagroal -d

Refer to logs for details about which configuration files were loaded and from which locations.