Database Administration
Database Migrations
Learn how to safely perform database migrations while maintaining data replication with Artie.
Overview 🎯
Database migrations are a critical part of maintaining and upgrading your database infrastructure. This guide explains how to safely perform database migrations while ensuring your data replication continues to work correctly with Artie.Major version upgrade ⚠️
Major version upgrades (e.g., PostgreSQL 14 to 15) require stopping database replication to perform the necessary upgrade and restart your database. This is because major version upgrades often include breaking changes that can affect replication.
If you are using Postgres, make sure to drop the replication slot after you paused the Artie pipeline. See the Postgres tips section for more information.
- Schedule an application downtime and pause your application.
- Check that Artie is no longer processing any data from our Analytics portal and pause your Artie pipeline.
- Perform the database upgrade and restart your database server.
- Resume your Artie pipeline.
- Resume your application.
Database-specific considerations
PostgreSQL
PostgreSQL
- Ensure
wal_levelis set tologicalin your new version - Verify that all required extensions are available in the new version
- Check that your replication slots are properly configured
MySQL
MySQL
- Verify
binlog_formatis set toROW - Ensure
binlog_retention_hoursis set appropriately (recommended 24+ hours) - Check that GTID is enabled if you’re using it
MongoDB
MongoDB
- Ensure your replica set is properly configured in the new version
- Verify that change streams are enabled
- Check that your service account has the correct permissions
Minor version upgrade
Minor version upgrades (e.g., PostgreSQL 14.1 to 14.2) do not require stopping database replication. These upgrades are typically handled automatically by your database provider and applied during your maintenance window.While minor version upgrades are generally safe, it’s still recommended to:
- Monitor your replication lag during the upgrade
- Have a rollback plan ready
- Test the upgrade in a staging environment first
Best practices ⭐
- Always test migrations in a staging environment first
- Back up your data before any major version upgrade
- Monitor replication lag during and after the upgrade
- Keep your Artie pipeline paused until the upgrade is complete
- Verify data consistency after the upgrade
Troubleshooting 🔧
What happens when Artie is resumed?
What happens when Artie is resumed?
For Postgres, Artie will find or create a replication slot (default name is
artie, unless you specified an override) and we will start capturing changes moving forward.For other data sources, Artie will seek the earliest checkpoint from the database and start replicating from that point.What should I do if there is data loss? 🚨
What should I do if there is data loss? 🚨
If you experience data loss, please reach out to us and we will kick off a backfill process. We recommend:
- Document the extent of data loss
- Note the approximate time window affected
- Contact our support team immediately
- Keep your pipeline paused until we can assist
Postgres tips
Postgres tips
For Postgres, you can run this query to see all the replication slots on the server:Ensure that
active is false and then drop the replication slot.Dropping replication slots will delete any unprocessed WAL files. Make sure you have paused your Artie pipeline before dropping slots.
Common issues
Common issues
- Replication slot overflow: If you see this error, you may need to increase your WAL retention period
- Connection issues: Verify your database credentials and network connectivity
- Permission errors: Ensure your service account has the correct permissions after the upgrade
- Schema changes: Check if any schema changes were made during the upgrade that might affect replication