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

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

New website

I finally managed to get around and do a better website for Masto.host.

As all things with Masto.host it is still a work in progress but I think it looks much better. Also, the old website was just an HTML theme I found online and that I adapted to the service I wanted to offer but without any experience on actually running the service.

It lasted almost one year like that but it was past time to take some time and start building a better one. This one is also just a theme I bought and it runs on top of WordPress to allow me to quickly add and edit content. I didn’t find a need to build one from scratch and it would take me much longer than just using this solution.

The only thing that I decided to port was the old Japanese version. Sorry to all the Japanese friends but you are still stuck the old one. The Japanese version was pretty awesome to have but it slows me down a lot. Whenever I think of a change, I need to find someone to help translate and it just isn’t feasible for a one person team. For now, I will leave it online and in the future will decide what to do.

Please, let me know if you find any errors in this new website or have suggestions and ideas on what to improve it. You know you can reach me @hugo@masto.pt or @mastohost@mastodon.social.