Mastodon PgBouncer Guide

Hugo GameiroMastodon, SysAdmin

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


nano /etc/default/pgbouncer


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
ALTER USER mastodon WITH PASSWORD 'pineapple';

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:


mastodon_production = host= 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:

listen_addr =
listen_port = 6432

Put `md5` as the `auth_type` (auth_type md5 allows both plain text and md5 passwords):

auth_type = md5

Make sure the `pgbouncer` user is an admin:

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`:

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:

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:


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:


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:


If you want to see if PgBouncer is working, you can use:


The total_request should not be 0.

There are plenty other values you may wish to look at, like:



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