Migrating your Mastodon PostgreSQL database to its own server.

Ready for another Mastodon guide? Well today we’re going to go over how to migrate your PostgreSQL database to its own server in a few easy steps! This will remove quite a bit of the resource usage from your main Mastodon server since the database loves CPU and RAM.

Migrating PostgreSQL

1. Create a mastodon user on the new database server.
   adduser --disabled-login mastodon
2. 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
3. Enable the services.
   systemctl daemon-reload

systemctl enable postgresql@
4. Edit the /etc/postgresql/15/main/postgresql.conf file and set the server’s IP to the listen_addresses value and set the port to 5432.

   

5. 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).
   host all all MASTODON_SERVER_IP password

   

6. Restart the PostgreSQL service.
   systemctl restart postgresql
7. Create the mastodon user in PostgreSQL and set a password.
   sudo -u postgres psql

CREATE USER mastodon CREATEDB;

ALTER USER mastodon WITH PASSWORD 'password123';

q

   

8. Switch over to the mastodon user and create an empty database.
   su - mastodon

createdb -T template0 mastodon_production
9. Login to your Mastodon server and stop all of the Mastodon services.
   systemctl stop mastodon-*
10. Switch over to the mastodon user and make a backup of your current PostgreSQL database.
    su mastodon

cd;pg_dump -Fc mastodon_production -f backup.dump
11. Copy the backup file to the database server.
    rsync -avz backup.dump root@DATABASE_SERVER_IP:/home/mastodon/backup.dump

12. Switch back to the database server and as the mastodon user import the database backup.pg_restore -Fc -U mastodon -n public –no-owner –role=mastodon -d mastodon_production backup.dump

13. Switch back to the Mastodon server and edit the .env.production file with the database server IP address.
    DB_HOST=DATABASE_SERVER_IP
14. Switch back to root and start the Mastodon services.
    systemctl start mastodon-sidekiq

systemctl start mastodon-web

systemctl start mastodon-streaming
15. Stop and disable the PostgreSQL service.
    systemctl stop postgresql

systemctl disable postgresql

And that’s all there is to it. Make sure you don’t have any firewall rules blocking your connection between the servers and everything should be working properly with your PostgreSQL database on its own server now.

Go out and do good things!
-KuJoe