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. enter image description here

  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 enter image description here

  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 enter image description here

  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

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