Category Archives: Woodpecker.social

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

Updated periodically – About Woodpecker.social
Updated periodically – Migrating to Woodpecker.social
Updated periodically – Introduction to Settler Colonialism
Updated periodically – Decolonization is not a metaphor
Updated periodically – Mvskoke History & Resources

Follow on Mastodon – Woodpecker.social/@Yehuda

This page is subject to content updates/additions. If you think any content should be updated or added, please leave a private comment on Mastodon – Woodpecker.social/@Yehuda.

Mastodon cleanup scripts

Comments and improvement suggestions welcomed!

Anything to improve is good! I have been running this script with days 5. All of this may update frequently, so check back.

This script is adapted from Hosting your own Mastodon server.

Currently running unmodified Mastodon v4.1.2 on Ubuntu 20.04.
With 16 users, disk space used averages around 34GB.
All files are in /home/mastodon/maint/

cron

# Once a day cleanup Monday-Sunday 2:45AM CST (7:45 UTC)
45 7 * * * /home/mastodon/maint/cleanup-daily.sh > /home/mastodon/maint/cleanup-daily.log

cleanup-daily.sh

#!/bin/bash
PATH=$PATH:/home/mastodon/.rbenv/shims:/home/mastodon/.rbenv/bin:/usr/sbin:/usr/bin:/sbin:/bin

date

# This does the heavy lifting and removes locally cached copies of
# media attachments, avatars and headers from other servers
# By default it's set to delete media older than 7 days
# Adjust days with --days, eg --days 3 for a more aggressive setting.
RAILS_ENV=production /home/mastodon/live/bin/tootctl media remove --days 5
RAILS_ENV=production /home/mastodon/live/bin/tootctl media remove --prune-profiles --days 5
RAILS_ENV=production /home/mastodon/live/bin/tootctl media remove --remove-headers --days 5

# New instead of above + --include-follows --dryrun --verbose ***
# RAILS_ENV=production /home/mastodon/live/bin/tootctl media remove --attachments --avatars --headers --days 5

# Remove local thumbnails for preview cards (the thumbnail when links are shared, etc)
# The default is 180 days and under 14 days is not recommended as they will not be refetched
RAILS_ENV=production /home/mastodon/live/bin/tootctl preview_cards remove --days 90

# Deletes remote accounts that never interacted with local accounts
# Removes many avatar and header images as part of this
RAILS_ENV=production /home/mastodon/live/bin/tootctl accounts prune

# Removes files that do not belong to existing media attachments
# For example if you attached a file to a post composer but never posted it
# This command and the one after it are slow to run, so I like to keep them for last
RAILS_ENV=production /home/mastodon/live/bin/tootctl media remove-orphans

# Remove unreferenced statuses from the database, such as statuses that
# came from relays or from users who are no longer followed by any local accounts,
# and have not been replied to or otherwise interacted with (90 days default)
RAILS_ENV=production /home/mastodon/live/bin/tootctl statuses remove --days 90

date

*** Note it appears tootctl media remove is going to have an update to flags, re: Issue 23628

Thanks!
-Yehuda

Updated periodically – About Woodpecker.social
Updated periodically – Migrating to Woodpecker.social
Updated periodically – Introduction to Settler Colonialism
Updated periodically – Decolonization is not a metaphor
Updated periodically – Mvskoke History & Resources

Follow on Mastodon – Woodpecker.social/@Yehuda

This page is subject to content updates/additions. If you think any content should be updated or added, please leave a private comment on Mastodon – Woodpecker.social/@Yehuda.