Opalstack's managed PostgreSQL service limits PostgreSQL users to 30 simultaneous connections. This is sufficient for most applications but if you're having trouble staying under that limit then you might benefit from using a connection pool.
pgbouncer is a lightweight connection pooler for PostgreSQL that is pre-installed on all Opalstack servers. If you'd like to use it:
- Create a new "proxy port" application named "pgbouncer" and make a note of the app's port assignment.
- Log in to SSH as the new app's shell user and create a file
~/apps/pgbouncer/pgbouncer.conf with the following contents, replacing "name_of_db" with the name of your database, "NNNNN" with your port assignment from step 1, and "appuser" with the app's shell user name:
[databases]
name_of_db1 = host=localhost dbname=name_of_db1
name_of_db2 = host=localhost dbname=name_of_db2
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = NNNNN
auth_file = /home/appuser/apps/pgbouncer/userlist.txt
unix_socket_dir = /home/appuser/apps/pgbouncer
pidfile = /home/appuser/apps/pgbouncer/pid
- Run the following commands to create the auth file for the bouncer, replacing "db_user" and "db_pass" with your database username and password, repeat the commands for each database user that will connect to the pool:
DBUSER=db_user
DBPASS=db_pass
PWHASH=$( echo -n "md5"; echo -n $DBPASS$DBUSER | md5sum | awk '{print $1}' )
echo '"'$DBUSER'" "'$PWHASH'"' >> /home/appuser/apps/pgbouncer/userlist.txt
chmod 600 /home/appuser/apps/pgbouncer/userlist.txt
- Start the bouncer:
pgbouncer -d /home/appuser/apps/pgbouncer/pgbouncer.conf
- Configure your app to connect to the database on port NNNNN from step 1 instead of port 5432, then restart the app.
At that point you'll have basic connection pooling running which should help keep you well under the connection limit.
If you need to stop the pooler the command is: kill $( cat /home/appuser/apps/pgbouncer/pid )
This is just a basic example for a working connection pool. For more information please refer to the official pgbouncer docs at:
