Database Migration: SQL Server to PostgreSQL via AWS DMS
SQL Server to PostgreSQL Migration for a Food Ordering Platform
A food ordering and menu-aggregation platform needed its two core databases, order/customer data and the menu catalog, off SQL Server and onto PostgreSQL, while staying live. I ran the migration with AWS DMS, one replication task per table across close to 190 tables, so any single table's failure never blocked the rest. The database move itself was the visible part. The harder part was everything Postgres needed that a migration tool does not carry over: sequences, indexes, and SQL Server's own procedural logic.
The problem
The platform's order/customer data and its menu/catalog data lived in SQL Server, spread across roughly 190 tables between the two databases, supporting live production ordering with aggregator and POS integrations. The business wanted PostgreSQL on RDS instead, without a long shutdown window. A single bulk copy does not fit a system this size: some tables are safe to fully reload, others are append-only archives that must never be truncated, and SQL Server carries triggers, stored procedures, and identifier conventions that have no automatic Postgres equivalent. The naive one-shot migration does not survive a live production system at this scale.
Constraints
- ·Live platform, no long maintenance window: change data capture had to run, not a one-time copy.
- ·Private network only: both databases sit inside a VPC behind a bastion, no public endpoint.
- ·Close to 190 tables with uneven risk: reference tables, transactional tables, and append-only archives all needed different reload behavior.
- ·Source-only logic to port: SQL Server triggers, functions, and procedures had no automatic Postgres equivalent.
- ·Two databases, one migration: the order/customer platform and the menu catalog had to move on largely the same schedule.
Architecture
AWS DMS moves each table through its own replication task: full load first, then ongoing change data capture, from a SQL Server source (private VPC, dbo schema) to a PostgreSQL target on RDS (Postgres 15). All access, migration tooling included, goes through a Teleport-brokered SSH bastion tunnel, since neither database has a public endpoint. A generation layer (table list to per-table DMS mapping JSON) creates and controls the tasks. Once a table lands, its identity sequence is resynced, its secondary indexes are hand-rebuilt (DMS does not migrate them), and high-volume tables get a pg_cron archiving job.
- ›AWS DMS: one replication task per table (~188 tasks), full load then CDC
- ›Table-mapping generator: reads a table-name list, writes one DMS rule JSON per table
- ›Task control scripts: create, restart (resume-processing), start/stop in parallel batches
- ›Teleport (tsh) bastion SSH tunnels: the only path to either database, staging and prod
- ›Table-prep-mode triage: TRUNCATE_BEFORE_LOAD vs DO_NOTHING, decided per table
- ›Post-load sequence resync: setval(MAX(id) + 100000) per identity column
- ›Manual index recreation: hand-written Postgres btree indexes for hot-path tables
- ›pg_cron scheduled archiving: rolling moves of old rows out of the largest live tables
How one request flows
- 01A table name is added to the source list, and the mapping generator writes its DMS table-mapping rule (schema, table, a unique rule ID).
- 02A script creates a replication task scoped to that one table, tagged with the right table-prep mode: truncate-and-reload for reference tables, do-nothing for append-only archives.
- 03The task runs a full load, copying existing rows, then holds open in change data capture, streaming inserts, updates, and deletes until cutover.
- 04A CDC error on one table (a cast or LOB failure, as happened on the platform's user and menu-process tables) blocks only that task. The rest keep running while the failing one is triaged, and escalated to AWS DMS support if needed.
- 05Once a table's load is clean, its Postgres identity sequence is resynced past the highest migrated ID, so the next application insert cannot collide with a copied row.
- 06Hot-path tables get their secondary indexes hand-rebuilt as native Postgres btree indexes, since DMS carries over structure and data but not secondary indexes.
- 07High-volume tables (orders, order line items) get a scheduled pg_cron job that archives old rows on a rolling basis once the table is live.
Engineering decisions
One DMS task per table, not one per database
What. Each table got its own table-mapping rule and its own replication task, generated from a table-name list rather than one task selecting many tables.
Why. A failure, a slow reload, or a prep-mode change on one table never has to touch the other ~187 tasks.
Tradeoff. Roughly 188 tasks to track instead of one or two, which is why the task-inventory and restart scripts exist, managing that count by hand was never the plan.
Truncate-or-not decided per table, not globally
What. Every task's actual table-prep-mode setting was pulled from AWS and split into a truncate list and a do-nothing list.
Why. A blanket reload policy would eventually truncate an append-only archive or log table by accident.
Tradeoff. More upfront classification work per table, in exchange for a reload that can never silently destroy accumulated history.
Sequences resynced with a deliberate buffer, not the exact next value
What. Every identity sequence was set to the migrated table's max ID plus a 100,000 buffer, not the precise next value.
Why. DMS copies row data but not Postgres sequence state, so the first live insert after cutover can otherwise collide with an already-migrated ID.
Tradeoff. A visible gap in ID sequences going forward, accepted for a wide margin against in-flight CDC writes still landing during the cutover window.
Indexes rebuilt by hand instead of trusted to the migration tool
What. Secondary indexes on the busiest tables, including covering indexes on the largest order table, were hand-recreated as native Postgres btree indexes.
Why. DMS migrates table structure and the primary key, not secondary indexes, so leaving it alone would have quietly dropped query performance on the hottest tables.
Tradeoff. Manual, table-by-table effort, in exchange for not discovering a performance regression after cutover.
SQL Server procedural logic dropped and reimplemented, not auto-converted
What. SQL Server triggers, procedures, and its auto-generated constraint names (invalid Postgres identifiers once quoted) were dropped in a staging pass, then rebuilt natively where the platform still needed the behavior.
Why. There is no reliable automatic translation from SQL Server procedural code to Postgres.
Tradeoff. Manual rewrite effort, chosen over trusting an automated schema converter to produce correct, maintainable Postgres logic.
Task control scripts throttle themselves on purpose
What. Restart and stop scripts fire against saved task ARNs in parallel batches of five, not one at a time and not all ~188 at once.
Why. A burst of simultaneous requests against the DMS API or the target database is a real risk once CDC traffic is already flowing.
Tradeoff. Slower batch operations, accepted to avoid overwhelming the API or the target mid-migration.
Database access only through a bastion tunnel
What. Every session, staging and prod alike, reaches the source or target database through a Teleport-brokered SSH port-forward tunnel to a bastion host.
Why. Both databases sit in a private VPC with no public endpoint.
Tradeoff. An extra hop for every connection, accepted so a production database port is never exposed during a migration window.
What changed along the way
- →Started with broader table-mapping files covering many tables per task, then moved to one task per table once isolating failures mattered more than reducing task count.
- →Added the table-prep-mode split (truncate vs do-nothing) after recognizing a blanket reload policy put archive tables at risk.
- →CDC-stage data-type and LOB errors on live tables were not caught by full-load testing alone and needed a direct AWS DMS support engagement to diagnose.
Security and reliability
- ·Private-network-only access: no direct connection string to either database, every session goes through a Teleport bastion tunnel.
- ·Per-table isolation: a failing table's replication task does not block or roll back any other table.
- ·Controlled reload behavior: table-prep mode is set per table so a reload cannot accidentally truncate an archive or log table.
- ·Scoped database access: team access is granted per user across every non-template database through an explicit grant script, not a shared credential.
- ·Vendor support engagement: production CDC errors were escalated to AWS DMS support with logs and table definitions rather than guessed at.
Metrics
What shipped
- ✓Migrated the order/customer platform and the menu-catalog database from SQL Server to PostgreSQL on RDS, table by table, with isolated failure handling per table.
- ✓Resynced every identity sequence post-load and hand-rebuilt the secondary indexes DMS does not carry over, so the target matched the source on both correctness and query performance.
- ✓Diagnosed and resolved live CDC data-type and LOB errors in direct correspondence with AWS DMS support.
- ✓Set up pg_cron-based rolling archiving for the platform's highest-volume tables on the new Postgres target.
- ✓Handed over a working set of task-control scripts (create, restart, stop) and a documented Teleport bastion access path for the team.
Lessons learned
- ·At this table count, per-table isolation beats a single migration job every time. Restarting one failing table without touching 187 others is worth the extra task-management overhead.
- ·A migration tool that copies rows is not a migration tool that copies behavior. Sequences, indexes, and procedural logic all needed separate, deliberate handling.
- ·CDC errors that never show up in full-load testing show up once real traffic flows through change data capture. A vendor support channel is worth having ready before cutover, not after.
- ·A wide buffer on a sequence resync is cheap insurance against a narrow but very disruptive class of bug.