How to Migrate Mastodon Instance Postgres to a Managed Service

Running a Mastodon instance requires managing a PostgreSQL database that stores all user accounts, posts, and relationships. As your instance grows, self-hosting Postgres can become a bottleneck for performance, backups, and uptime. Migrating to a managed database service such as Amazon RDS, DigitalOcean Managed Databases, or a dedicated Postgres provider offloads maintenance tasks and improves reliability. This article explains the complete process of migrating your Mastodon instance’s Postgres database to a managed service without losing data or causing extended downtime.

Why Move Mastodon’s Postgres to a Managed Service

A Mastodon instance relies on PostgreSQL as its primary data store. Self-hosting Postgres means you must handle backups, replication, patching, and performance tuning yourself. A managed service automates these tasks: it provides automated backups, point-in-time recovery, read replicas, and automatic failover. This reduces the risk of data loss and frees your time for instance administration.

Managed services also offer better uptime guarantees. If your server hardware fails, the managed provider spins up a replacement database node from the latest backup. Self-hosted setups require you to restore from your own backup archives, which can take hours. For a Mastodon instance with hundreds or thousands of users, the difference in recovery speed is significant.

Another reason is scalability. As your instance grows, the database workload increases. Managed services allow you to resize the database instance with a few clicks or API calls. Scaling a self-hosted Postgres server requires migrating to larger hardware or setting up complex replication manually.

What You Need Before Starting the Migration

Before you begin, you need the following:

• SSH access to the Mastodon application server where the current Postgres database resides.
• Credentials and connection details for the target managed Postgres service, including hostname, port, database name, user, and password.
• The managed service must allow connections from your Mastodon application server’s IP address. Add an inbound firewall rule or security group entry for that IP.
• Enough disk space on the application server to hold a compressed dump of the database. A typical Mastodon database with 10,000 users is around 2–5 GB uncompressed.
• Sudo privileges or be the mastodon user to run Rails commands and restart services.

Steps to Migrate Mastodon Postgres to a Managed Service

Follow these steps in order. The migration uses a logical dump approach, which minimizes downtime to only a few minutes.

1. Put Mastodon in maintenance mode
   Run cd /home/mastodon/live && RAILS_ENV=production bin/tootctl maintenance mode enable. This prevents new posts and interactions during the final dump. This step is optional but recommended to guarantee data consistency.
2. Create a compressed dump of the current database
   Use pg_dump -U mastodon -h localhost mastodon_production -Fc -Z 9 -f /tmp/mastodon_dump.dump. Replace mastodon_production with your actual database name. The -Fc flag creates a custom format that is faster to restore. The -Z 9 flag applies maximum compression.
3. Restore the dump to the managed service
   Run pg_restore -U your_managed_user -h your_managed_host -d your_managed_db -p 5432 -v /tmp/mastodon_dump.dump. Enter the password when prompted. The -v flag shows progress. This may take several minutes for large databases. Monitor the output for errors.
4. Update Mastodon’s environment configuration
   Edit /home/mastodon/live/.env.production. Change these values:
   DB_HOST=your_managed_host
DB_PORT=5432
DB_NAME=your_managed_db
DB_USER=your_managed_user
DB_PASS=your_managed_password. Save the file.
5. Restart Mastodon services
   Run systemctl restart mastodon-web mastodon-sidekiq mastodon-streaming. This makes the application use the new database connection.
6. Verify the connection and data
   Check the logs: journalctl -u mastodon-web -n 50. Look for lines that say ActiveRecord::ConnectionAdapters::PostgreSQLAdapter without errors. Then test the instance by posting a toot and checking that it appears on your profile.
7. Disable maintenance mode
   Run cd /home/mastodon/live && RAILS_ENV=production bin/tootctl maintenance mode disable. Your instance is now fully operational on the managed service.

Alternative Method: Streaming Replication for Near-Zero Downtime

If your instance cannot accept even five minutes of downtime, use streaming replication. This method requires that the managed service supports replication from an external primary. Set up the managed service as a replica of your current Postgres, then promote it to primary. This is more complex and requires configuring wal_level = logical and max_replication_slots on the source database. Only use this method if you have experience with Postgres replication.

Common Issues After Migrating Mastodon Postgres

Mastodon Cannot Connect to the New Database

The most common cause is an incorrect hostname or port in .env.production. Double-check the values. Also verify that the managed service’s firewall allows inbound connections from the Mastodon server’s IP address. Run psql -h your_managed_host -U your_managed_user -d your_managed_db -c 'SELECT 1' from the application server. If this fails, the network or credentials are wrong.

Missing Indexes or Extensions After Restore

pg_restore does not always recreate extensions like pg_trgm or btree_gin that Mastodon requires. Log into the managed database and run CREATE EXTENSION IF NOT EXISTS pg_trgm and CREATE EXTENSION IF NOT EXISTS btree_gin. Then run cd /home/mastodon/live && RAILS_ENV=production bundle exec rails db:migrate to update indexes.

Performance Slower Than Self-Hosted

Managed services often have default configuration that is not optimized for Mastodon. Check the work_mem and shared_buffers settings. For a Mastodon instance with 10,000 active users, set shared_buffers to 25% of available RAM. Contact your provider to adjust these parameters.

Item	Self-Hosted Postgres	Managed Postgres Service
Backup responsibility	Manual cron jobs or scripts	Automated daily backups with point-in-time recovery
Patching and upgrades	Manual apt upgrade or similar	Provider handles minor and major version upgrades
High availability	Requires manual replication setup	Built-in automatic failover to standby replica
Scalability	Requires hardware migration or manual replication	Resize instance type with a few clicks
Cost	Only server cost	Server cost plus managed service fee

Migrating your Mastodon instance’s Postgres database to a managed service removes the burden of database administration and improves reliability. You now have a database that backs itself up automatically and can fail over without manual intervention. After the migration, monitor the mastodon-sidekiq logs for any delayed jobs that might indicate connection issues. For an extra layer of safety, configure a read replica on the managed service and point your monitoring tools to it for analytics queries without affecting the primary database.