Mastodon PGBouncer Guide

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: