I did some changes to the initial commit by Nolan to the documentation of Mastodon PgBouncer Guide.
As my pull request is still waiting to be approved, I leave it also here because I had some trouble to get it to work with the instructions that were initially shared.
Installing PgBouncer
On Ubuntu/Debian:
sudo apt install pgbouncer
Then:
nano /etc/default/pgbouncer
START=1
Starting:
sudo service pgbouncer start
(Note that this guide assumes you aren’t using Docker.)
Configuring PgBouncer
Setting a password
First off, if your mastodon user in Postgres is set up wthout a password, you will need to set a password. There seems to be no way to use PgBouncer with an empty password.
Here’s how you might reset the password:
su - postgres
psql
ALTER USER mastodon WITH PASSWORD 'pineapple';
\q
exit
Configuring PgBouncer
PgBouncer has two config files: pgbouncer.ini and userlist.txt both in /etc/pgbouncer/. The first contains the configuration, whereas the second just contains a list of usernames and passwords.
Configuring userlist.txt
Add the mastodon user to the userlist.txt using md5 or plain text:
"mastodon" "pineapple"
Or using md5:
"mastodon" "md5bc8a988ff49c4d1d8c43c4747c68fc0b"
The md5 password is just the md5sum of password+username with the string md5 prepended. For instance, to derive the hash for user mastodon with pineapple password, you can do:
# ubuntu, debian, etc.
echo -n "pineapplemastodon" | md5sum
# macOS, openBSD, etc.
md5 -s "pineapplemastodon"
You will get this as the result bc8a988ff49c4d1d8c43c4747c68fc0b and you prepend the string md5, resulting in md5bc8a988ff49c4d1d8c43c4747c68fc0b.
You’ll also want to create a pgbouncer admin user to log in to the PgBouncer admin database. So here’s a sample userlist.txt:
"mastodon" "md5bc8a988ff49c4d1d8c43c4747c68fc0b"
"pgbouncer" "p4ssw0rd"
Configuring pgbouncer.ini
Add a line under [databases] listing the Postgres databases you want to connect to. Here we’ll just have PgBouncer use the same username/password and database name to connect to the underlying Postgres database:
ini
[databases]
mastodon_production = host=127.0.0.1 port=5432 dbname=mastodon_production user=mastodon password=pineapple
The listen_addr and listen_port tells PgBouncer which address/port to accept connections. The defaults are fine:
ini
listen_addr = 127.0.0.1
listen_port = 6432
Put md5 as the auth_type (auth_type md5 allows both plain text and md5 passwords):
ini
auth_type = md5
Make sure the pgbouncer user is an admin:
ini
admin_users = pgbouncer
This next part is very important! The default pooling mode is session-based, but for Mastodon we want transaction-based. In other words, a Postgres connection is created when a transaction is created and dropped when the transaction is done. So you’ll want to change the pool_mode from session to transaction:
ini
pool_mode = transaction
Next up, max_client_conn defines how many connections PgBouncer itself will accept, and default_pool_size puts a limit on how many Postgres connections will be opened under the hood. (In PgHero the number of connections reported will correspond to default_pool_size because it has no knowledge of PgBouncer.)
The defaults are fine to start, and you can always increase them later:
ini
max_client_conn = 100
default_pool_size = 20
Debugging that it all works
You should be able to connect to PgBouncer just like you would with Postgres:
psql -p 6432 -U mastodon mastodon_production
And then use your password to log in.
You can also check the PgBouncer logs like so:
tail -f /var/log/postgresql/pgbouncer.log
Configuring Mastodon to talk to PgBouncer
In your .env.production file, first off make sure that this is set:
bash
PREPARED_STATEMENTS=false
Since we’re using transaction-based pooling, we can’t use prepared statements.
Next up, configure Mastodon to use port 6432 (PgBouncer) instead of 5432 (Postgres) and you should be good to go:
bash
DB_HOST=localhost
DB_USER=mastodon
DB_NAME=mastodon_production
DB_PASS=password
DB_PORT=6432
After you need to restart your Mastodon services for the system to assume the .env.production changes:
systemctl restart mastodon-web.service mastodon-sidekiq.service mastodon-streaming.service
Your instance should be working and using PgBouncer.
Administering PgBouncer
The easiest way to reboot is:
sudo service pgbouncer restart
But if you’ve set up a PgBouncer admin user, you can also connect as the admin:
psql -p 6432 -U pgbouncer pgbouncer
And then do:
RELOAD;
If you want to see if PgBouncer is working, you can use:
SHOW STATS;
The total_request should not be 0.
There are plenty other values you may wish to look at, like:
SHOW SERVERS;
SHOW CLIENTS;
SHOW POOLS;
SHOW LISTS;