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;

Resources

– Mastodon PgBouncer Guide
– Scaling Mastodon
– PgBouncer documentation
– Connection Pooling in PostgreSQL using pgbouncer
– PgBouncer on the Postgres wiki