Zero downtime database migrations: Lessons from moving a live production database

Tines undertook a complex database migration to move a customer's critical workloads between two AWS regions while ensuring 100% system availability. The process, initially underestimated in scope, involved challenges such as handling gzipped content with Lambda@Edge, managing database connection states, and synchronizing data without losing any events or disrupting workflows. The project utilized a blue/green deployment approach with PostgreSQL's logical replication and required enhancements to tools like pg_easy_replicate for real-time monitoring and selective table replication. Despite encountering unexpected issues like DDL changes and BLOB synchronization, the migration succeeded through automation with Tines stories and robust request routing strategies. The experience led to significant infrastructure improvements and provided valuable lessons for future migrations, emphasizing the complexity of maintaining seamless operations during such transitions.