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

Leave a Reply

Your email address will not be published. Required fields are marked *