pgagroal
home
releases
getting started
security
failover
pipelines
github
discussions
issues
license
pgagroal
Configuration Metrics News About
Getting started with pgagroal
First of all, make sure that pgagroal is installed and in your path by using "pgagroal -?". You should see
pgagroal 1.6.0
High-performance connection pool for PostgreSQL
Usage:
pgagroal [ -c CONFIG_FILE ] [ -a HBA_FILE ] [ -d ]
Options:
-c, --config CONFIG_FILE Set the path to the pgagroal.conf file
-a, --hba HBA_FILE Set the path to the pgagroal_hba.conf file
-l, --limit LIMIT_FILE Set the path to the pgagroal_databases.conf file
-u, --users USERS_FILE Set the path to the pgagroal_users.conf file
-F, --frontend FRONTEND_USERS_FILE Set the path to the pgagroal_frontend_users.conf file
-A, --admins ADMINS_FILE Set the path to the pgagroal_admins.conf file
-S, --superuser SUPERUSER_FILE Set the path to the pgagroal_superuser.conf file
-d, --daemon Run as a daemon
-V, --version Display version information
-?, --help Display help
If you don't have pgagroal in your path see our README
on how to compile and install pgagroal in your system.
Configuration
Lets create a simple configuration file called pgagroal.conf with the content
[pgagroal]
host = *
port = 2345
log_type = file
log_level = info
log_path = /tmp/pgagroal.log
max_connections = 100
idle_timeout = 600
validation = off
unix_socket_dir = /tmp/
[primary]
host = localhost
port = 5432
In our main section called [pgagroal] we setup pgagroal to listen on all
network addresses on port 2345. Logging will be performed at "info" level and
put in a file called "/tmp/pgagroal.log". We want a maximum of 100 connections
that are being closed if they have been idle for 10 minutes, and we also specify that
we don't want any connection validation to be performed. Last we specify the
location of the "unix_socket_dir" used for management operations.
Next we create a section called [primary] which has the information about our
PostgreSQL instance. In this case it is running
on "localhost" on port "5432".
Now we need a host based authentication (HBA) file. Create one called pgagroal_hba.conf
with the content
#
# TYPE DATABASE USER ADDRESS METHOD
#
host all all all all
This tells pgagroal that it can accept connections from all network addresses
for all databases and all user names.
We are now ready to run pgagroal.
See Configuration for all configuration options.
Running
We will run pgagroal using the command
pgagroal -c pgagroal.conf -a pgagroal_hba.conf
If this doesn't give an error, then we are ready to connect.
We will assume that we have a user called "test" with the password "test" in our
PostgreSQL instance. See their
documentation on
how to setup PostgreSQL,
add a user,
and
add a database.
We will connect to pgagroal using the psql
application.
psql -h localhost -p 2345 -U test test
That should give you a password prompt where "test" should be typed in. You are now connected
to PostgreSQL through pgagroal.
Type "\q" to quit psql and pgagroal
will now put the connection that you used into its pool.
If you type the above psql command again pgagroal will reuse the existing connection and
thereby lower the overhead of getting a connection to PostgreSQL.
Now you are ready to point your applications to use pgagroal instead of going directly to
PostgreSQL. pgagroal will work with any
PostgreSQL compliant driver, for example pgjdbc,
Npgsql and pq.
pgagroal is stopped by pressing Ctrl-C (^C) in the console where you started it, or by sending
the SIGTERM signal to the process using "kill pid".
Run-time administration
pgagroal has a run-time administration tool called pgagroal-cli. You can see the commands it supports by using "pgagroal-cli -?" which will give
pgagroal-cli 1.6.0
Command line utility for pgagroal
Usage:
pgagroal-cli [ OPTIONS ] [ COMMAND ]
Options:
-c, --config CONFIG_FILE Set the path to the pgagroal.conf file
Default: /etc/pgagroal/pgagroal.conf
-h, --host HOST Set the host name
-p, --port PORT Set the port number
-U, --user USERNAME Set the user name
-P, --password PASSWORD Set the password
-L, --logfile FILE Set the log file
-F, --format text|json Set the output format
-v, --verbose Output text string of result
-V, --version Display version information
-?, --help Display help
Commands:
flush [mode] [database] Flush connections according to [mode].
Allowed modes are:
- 'gracefully' (default) to flush all connections gracefully
- 'idle' to flush only idle connections
- 'all' to flush all connections. USE WITH CAUTION!
If no [database] name is specified, applies to all databases.
ping Verifies if pgagroal is up and running
enable [database] Enables the specified databases (or all databases)
disable [database] Disables the specified databases (or all databases)
shutdown [mode] Stops pgagroal pooler. The [mode] can be:
- 'gracefully' (default) waits for active connections to quit
- 'immediate' forces connections to close and terminate
- 'cancel' avoid a previously issued 'shutdown gracefully'
status [details] Status of pgagroal, with optional details
switch-to <server> Switches to the specified primary server
conf <action> Manages the configuration (e.g., reloads the configuration
The subcommand <action> can be:
- 'reload' to issue a configuration reload;
- 'get' to obtain information about a runtime configuration value;
conf get <parameter_name>
- 'set' to modify a configuration value;
conf set <parameter_name> <parameter_value>;
- 'ls' lists the configuration files used.
clear <what> Resets either the Prometheus statistics or the specified server.
<what> can be
- 'server' (default) followed by a server name
- a server name on its own
- 'prometheus' to reset the Prometheus metrics
pgagroal: <https://agroal.github.io/pgagroal/>
Report bugs: <https://github.com/agroal/pgagroal/issues>
This tool can be used on the machine running pgagroal to flush connections.
To flush all idle connections you would use
pgagroal-cli -c pgagroal.conf flush idle
To stop pgagroal you would use
pgagroal-cli -c pgagroal.conf stop
Check the outcome of the operations by verifying the exit code, like
echo $?
or by using the -v flag.
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.
Administration
pgagroal has an administration tool called pgagroal-admin, which is used to control user registration with pgagroal. You can see the commands it supports by using "pgagroal-admin -?" which will give
pgagroal-admin 1.6.0
Administration utility for pgagroal
Usage:
pgagroal-admin [ -f FILE ] [ COMMAND ]
Options:
-f, --file FILE Set the path to a user file
-U, --user USER Set the user name
-P, --password PASSWORD Set the password for the user
-g, --generate Generate a password
-l, --length Password length
-V, --version Display version information
-?, --help Display help
Commands:
master-key Create or update the master key
add-user Add a user
update-user Update a user
remove-user Remove a user
list-users List all users
In order to set the master key for all users you can use
pgagroal-admin -g master-key
The master key must be at least 8 characters.
Then use the other commands to add, update, remove or list the current user names, f.ex.
pgagroal-admin -f pgagroal_users.conf add-user
Next Steps
Next steps in improving pgagroal's configuration could be- Update pgagroal.conf with the required settings for your system
- Set the access rights in pgagroal_hba.conf for each user and database
- Add a pgagroal_users.conf file using pgagroal-admin with a list of known users
- Disable access for unknown users by setting allow_unknown_users to false
- Define a pgagroal_databases.conf file with the limits and prefill settings for each database
- Enable Transport Layer Security v1.2+ (TLS)