Migrating legacy databases to Aurora PostgreSQL
How we rehearsed and orchestrated a database migration and engine conversion to Aurora PostgreSQL while keeping reports available in read-only mode.
In 2022, I wrote about a database migration at Simply Wall St: moving databases from dedicated servers to managed Amazon Aurora PostgreSQL while also converting legacy relational databases to PostgreSQL.
The migration had two constraints. We wanted to reduce dependence on manually maintained database hardware and standardise on PostgreSQL. We also wanted reports to remain available while data moved, so we planned a short write pause and kept the applications serving data in read-only mode during the migration.
The constraints
The source environment used a three-server, quorum-based replication setup on dedicated hardware. Capacity changes and maintenance required planned downtime, and physical failures could interrupt backups and replication while transaction logs accumulated. At the same time, database engines differed across older services, adding maintenance and developer-experience costs.
Continuous replication during the move would have added risk. The existing backup and replication model depended on transaction logs; an extended migration or a failover could leave logs growing while disk space was finite. We chose a controlled migration window with writes stopped, while leaving reports available in read-only mode.
Make the migration repeatable
We grouped related tables into migration domains. That made the work easier to run concurrently and gave us smaller units to validate. We also left obsolete tables behind when they were no longer referenced by application code.
I built a version-controlled Bash workflow around PgLoader, AWS CLI and AWS Database Migration Service (DMS). Rehearsals could run in isolated schemas, and a clean-slate option made it practical to repeat a migration after changing the conversion or load steps.
PgLoader created the target schema and converted source data to PostgreSQL. We used follow-up SQL for conversions such as text-backed JSON to JSONB. AWS DMS then performed the full data load. The script coordinated the repetitive work around each domain, including starting or resuming tasks and applying the required post-load steps.
Trade indexes for load speed
Indexes and foreign-key constraints can slow a large bulk load. We removed them on the target before loading, then restored constraints, synchronised generated sequence values and rebuilt indexes after the data arrived. This required explicit migration steps, but gave us control over conversion and post-load work that the DMS schema-conversion path did not provide for our needs.
The published result was a reduction from more than seven hours to under one hour for table loads involving tables with over 190 million records and tables with large JSON columns. That figure describes the affected table loads; it is not a claim about the duration of the whole migration or the write pause.
What made the approach useful
The main confidence came from being able to repeat the process in a controlled environment. Each rehearsal exposed conversion or performance issues before the planned migration, and the version-controlled script made the sequence reviewable and reproducible.
The trade-off was operational complexity: indexes and constraints had to be restored correctly, sequences resynchronised, and each domain validated. That explicit work made the cutover more predictable while the source systems had limited disk headroom for a long-running migration.
Source
- Migrating and Converting Databases to Amazon Aurora RDS Postgres, P. Tran, Simply Wall St, 9 May 2022.