Getting started with pgagroal
First of all, make sure that
pgagroal is installed and in your path by
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 README on how to
compile and install
pgagroal in your system.
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
localhost on port
Now we need a host based authentication (HBA) file. Create one called
with the content
# # TYPE DATABASE USER ADDRESS METHOD # host all all all all
pgagroal that it can accept connections from all network addresses
for all databases and all user names.
We are now ready to run
See Configuration for all configuration options.
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
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
\q to quit psql and
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
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
SIGTERM signal to the process using
pgagroal has a run-time administration tool called
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 [ -c CONFIG_FILE ] [ COMMAND ] Options: -c, --config CONFIG_FILE Set the path to the pgagroal.conf file -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 -v, --verbose Output text string of result -V, --version Display version information -?, --help Display help Commands: flush-idle Flush idle connections flush-gracefully Flush all connections gracefully flush-all Flush all connections. USE WITH CAUTION ! is-alive Is pgagroal alive enable Enable a database disable Disable a database gracefully Stop pgagroal gracefully stop Stop pgagroal cancel-shutdown Cancel the graceful shutdown status Status of pgagroal details Detailed status of pgagroal switch-to Switch to another primary reload Reload the configuration reset Reset the Prometheus statistics reset-server Reset the state of a server
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
or by using the
If pgagroal has both Transport Layer Security (TLS) and
management enabled then
connect with TLS using the files
~/.pgagroal/pgagroal.key (must be 0600 permission),
pgagroal has an administration tool called
pgagroal-admin, which is used to control user
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 in improving pgagroal's configuration could be
pgagroal.confwith the required settings for your system
- Set the access rights in
pgagroal_hba.conffor each user and database
- Add a
pgagroal-adminwith a list of known users
- Disable access for unknown users by setting
- Define a
pgagroal_databases.conffile with the limits and prefill settings for each database
- Enable Transport Layer Security v1.2+ (TLS)
- Deploy Grafana dashboard
See Configuration for more information on these subjects.
There are a few short tutorials available to help you better understand and configure
- Installing pgagroal
- Enabling prefill
- Enabling remote management
- Enabling Prometheus metrics
- Enabling split security
The pgagroal community hopes that you find the project interesting.
Feel free to
All contributions are most welcome !
Please, consult our Code of Conduct policies for interacting in our community.
Consider giving the project a star on GitHub if you find it useful. And, feel free to follow the project on Twitter as well.