Mastodon database server

Steps to add a dedicated database server to Mastodon

Note these steps have been heavily derived from Migrating your Mastodon PostgreSQL database to its own server which uses an Ubuntu 20.04 LTS server. The steps documented here use an Ubuntu 22.04 LTS server. These steps have also been used successfully with an Ubuntu 22.10 server.

Currently I use a 2 vCPU virtual private server with 4GB of memory and 80GB SSD from DigitalOcean, but it can work with as little as a 1 vCPU virtual private server with 2GB of memory and 25GB SSD. My main Mastodon server is also a 2 vCPU virtual private server with 4GB of memory and 80GB SSD. With 16 users, this has plenty of spare room for growth and will be easier to backup.

These instructions presume your Mastodon server is already up and running, and the database server has been created.

VERY IMPORTANT: Before you start be sure both servers can use ports 22 & 5432 to access each other.

On the database server

a. Update and upgrade Ubuntu

# apt update; apt -y upgrade

b. Create a Mastodon user on the new database server as root

# adduser --disabled-login mastodon

c. Install PostgreSQL on your new database server

# wget -O /usr/share/keyrings/postgresql.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc

# echo "deb [signed-by=/usr/share/keyrings/postgresql.asc] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/postgresql.list

# apt update

# apt install postgresql postgresql-contrib

d. Enable services

# systemctl daemon-reload

# systemctl enable postgresql

e. Edit the /etc/postgresql/15/main/postgresql.conf file and in the Connection Settings, set the listen_addresses value to your database server’s IP and set the port to 5432

listen_addresses = 'DATABASE_SERVER_IP'
port = 5432

f. Edit the /etc/postgresql/15/main/pg_hba.conf file and add the following line to the bottom of the file (substituting your Mastodon server’s IP and be sure to leave the /32 netmask)

host all all MASTODON_SERVER_IP/32 password

g. Restart the PostgreSQL service

# systemctl restart postgresql

h. Create the mastodon user in PostgreSQL and set a password

# cd /tmp; sudo -u postgres psql

postgres=# CREATE USER mastodon CREATEDB;

postgres=# ALTER USER mastodon WITH PASSWORD 'your_database_password';

postgres=# \q

i. Change to the mastodon user and create an empty database

# su - mastodon

$ createdb -T template0 mastodon_production

On the Mastodon server

j. As root stop all Mastodon services

# systemctl stop mastodon-*

k. Change to mastodon user & backup your current PostgreSQL database

# su mastodon

$ cd /home/mastodon; pg_dump -Fc mastodon_production -f /home/mastodon/backup.dump

l. Change back to root user

$ exit

m. Ok now at this point it is VERY IMPORTANT you can ssh as root from your Mastodon server to your database server. I will leave that up to you, but do not proceed until you have achieved that capability.

n. Copy the backup file to the database server

# rsync -avz /home/mastodon/backup.dump root@DATABASE_SERVER_IP:/home/mastodon/backup.dump

On the database server

o. As the mastodon user import the database backup

$ pg_restore -Fc -U mastodon -n public --no-owner --role=mastodon -d mastodon_production /home/mastodon/backup.dump

On the Mastodon server

p. Still as the root user, edit the /home/mastodon/live/.env.production file. Change DB_HOST to the database server IP address and DB_PASS to the database password you set in step h.

DB_HOST=DATABASE_SERVER_IP

DB_PASS=YOUR_DATABASE_PASSWORD

q. Start the Mastodon services

# systemctl start mastodon-sidekiq

# systemctl start mastodon-web

# systemctl start mastodon-streaming

r. Stop and disable the PostgreSQL service

# systemctl stop postgresql

# systemctl disable postgresql

Ok, that’s it – enjoy!

Special thanks to @oliphant@oliphant.social and @kujoe@mindly.social!

Thanks!
-Yehuda