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';

    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.
    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!

    Leave a Reply

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