Migrating a Legacy SQL Server Workload to PostgreSQL on AWS EC2

Many Australian IT teams still carry SQL Server installations that grew organically through the 2010s. With cloud-native tooling maturing and licence audits becoming more aggressive, a move to PostgreSQL on EC2 has shifted from a fringe experiment to a realistic modernisation path. For organisations operating in Sydney or Melbourne, the question is rarely whether the database engine is the right one. It is how to execute the cutover without losing sleep over data integrity or compliance reporting under the Privacy Act and APRA's CPS 234.

This walkthrough covers the practical sequence I use when helping clients plan and execute such a move. We look at the commercial and compliance triggers, the schema and code translation work, the EC2 landing zone, performance validation, and the operational handover. The aim is to give a working engineer enough scaffolding to scope a migration confidently and to know where the sharp edges hide.

Why Australian Teams Look at PostgreSQL Now

The trigger is rarely technical on its own. SQL Server continues to work fine for transactional workloads; the friction tends to come from licensing terms, audit pressure, and the broader shift toward open-source stacks in cloud architectures. AWS billings arrive in Australian dollars, and a per-core enterprise agreement that once looked reasonable on a Sydney-based finance server starts to look expensive when the same workload can run on EC2 without a database engine tax attached.

Compliance considerations matter too. Under APRA CPS 234, regulated entities must demonstrate that information assets are protected to a standard commensurate with the risk, and a self-managed PostgreSQL cluster on EC2 offers more transparency for that conversation than a black-box managed licence. Pair that with the Notifiable Data Breaches scheme under the Privacy Act, and the case for owning a clearly documented database tier becomes compelling. A migration also removes one more dependency on a vendor whose roadmap an Australian team cannot directly influence.

For organisations headquartered in Brisbane or Perth, the geographic question is real. AWS operates the Sydney region (ap-southeast-2) and recently opened Melbourne (ap-southeast-4), giving most Australian teams a primary site and a secondary region without leaving the country. For data that must remain onshore, particularly health, finance, or government datasets, that alignment simplifies legal review considerably.

Schema and Code Translation Between SQL Server and PostgreSQL

The mechanical translation work is more straightforward than people fear, but it does require discipline. A short list of the items I always check first:

• Identity columns map to SERIAL or, preferably, IDENTITY columns introduced in PostgreSQL 10.
• DATETIME, DATETIME2, and DATETIMEOFFSET convert to TIMESTAMP and TIMESTAMPTZ, with a careful audit of any code that relied on SQL Server's implicit date arithmetic.
• NVARCHAR and VARCHAR(MAX) become TEXT or VARCHAR with no length cap; BLOB-style data moves to BYTEA or large-object storage depending on access patterns.
• T-SQL stored procedures rewrite to PL/pgSQL functions; SQL Server's TRY/CATCH blocks need restructuring since PostgreSQL uses BEGIN/EXCEPTION/END blocks instead.
• Built-in functions such as ISNULL, GETDATE, and DATEPART have direct equivalents, but DATEPART semantics for week numbers differ, so any reporting logic touching ISO weeks needs explicit testing.

Tools like AWS DMS, pgLoader, and pg_dump can move data, but the application code is where most projects lose time. A short feedback loop with a staging environment, ideally in the same AEST business hours window where developers can respond quickly, is worth the extra EC2 spend.

Standing Up the PostgreSQL Landing Zone on EC2

The target environment does not need to be exotic. A common starting point is two EC2 instances behind a Network Load Balancer, running PostgreSQL 16 on Ubuntu 22.04 LTS, with EBS gp3 volumes for the data directory and io2 Block Express for the write-ahead log. Cross-AZ replication handles availability, and automated snapshots feed into an S3 bucket with a lifecycle policy that pushes to a colder storage class after 30 days.

The core components to deploy on day one are usually:

• Two EC2 instances sized for the workload, deployed across separate Availability Zones within the Sydney region.
• A dedicated EBS volume layout: gp3 for the data directory, io2 for the write-ahead log, and a smaller gp3 for the archive WAL destination.
• Security Groups that restrict PostgreSQL port 5432 to application subnets only, with no public ingress.
• An S3 bucket for WAL archives and base backups, versioned and encrypted with a customer-managed KMS key.
• A Network Load Balancer in front of the two instances, with health checks tuned for PostgreSQL's readiness rather than a generic TCP probe.

Instance sizing follows the existing SQL Server footprint with a margin for growth. A workload that previously ran on a four-core Standard Edition instance with 32 GB of RAM typically maps well to an m6i.2xlarge or m7i.2xlarge running PostgreSQL, because PostgreSQL tends to be more efficient with buffer cache and connection handling. For leaner operations based in Adelaide or Hobart, a smaller instance class with read replicas is often more cost-effective than a single oversized box.

Network design matters. PostgreSQL replication traffic should never traverse the public internet, so Security Groups and a dedicated subnet within a VPC keep the wire-level traffic between primary and replica inside AWS's backbone. For teams that need to demonstrate controls to APRA auditors, a tightly scoped security group with a documented rule set is far easier to defend than a permissive default.

Data Conversion, Testing and Cutover

Once the environment exists, the focus shifts to validating that the converted code produces the same results as the legacy system. I split this into three phases: schema validation, data integrity validation, and behavioural validation. Schema validation confirms table layouts, constraints, and indexes translated cleanly. Data integrity validation runs row counts, checksums, and spot-check business rules against the source database. Behavioural validation runs a parallel workload against both systems for a defined window, usually a fortnight covering at least one month-end cycle, and compares query results and timings.

Date and time handling is the single largest source of bugs in any SQL Server to PostgreSQL move. A TIMESTAMP WITHOUT TIME ZONE column behaves differently from DATETIME, and any code that mixed implicit time zone conversions on the SQL Server side will need an explicit rewrite. I always include a regression suite focused on reports that aggregate across midnight AEST, since the shift to daylight saving in October and April catches out code that assumes a fixed UTC offset.

Performance tuning after cutover centres on three areas: indexing strategy, autovacuum configuration, and connection pooling. PostgreSQL relies more heavily on partial and expression indexes than SQL Server does, and the default autovacuum thresholds are tuned for very small tables. A modest increase to autovacuum workers and a tailored cost limit pays back quickly on busy systems. PgBouncer or an equivalent proxy helps when applications open hundreds of short-lived connections.

Operating PostgreSQL After the Move

The handover phase often determines whether the migration is seen as a success six months later. Documentation must cover the backup and restore procedure, the runbook for failover, and the on-call escalation path. In a small Sydney-based operations team, this often means one engineer owns the database tier and another owns the application, with cross-training documented so that annual leave does not become a single point of failure.

The operational checks worth wiring up early are:

• Replication lag alerts on the replica, with a threshold tight enough to catch problems before they affect reads.
• Long-running transaction monitoring via pg_stat_activity, since idle-in-transaction sessions block autovacuum.
• Table bloat tracking through the pgstattuple contrib module or a third-party tool like pg_repack.
• Disk space forecasting that accounts for WAL retention and base backup accumulation in S3.
• Connection saturation alerts on PgBouncer, because PostgreSQL performs poorly above a few hundred active connections.

Monitoring is where PostgreSQL diverges most from SQL Server. There is no equivalent of SQL Server Management Studio's built-in dashboards, and teams that relied on those will need to learn pg_stat_statements, pg_stat_activity, and the contrib modules that ship with PostgreSQL. Pairing those with Prometheus and Grafana, or with CloudWatch metrics scraped via the AWS integration, gives a familiar pane of glass. Alerts on replication lag, long-running transactions, and table bloat catch most problems before users notice.

Backups deserve explicit attention. Logical backups using pg_dump are fine for small databases, but anything over a few hundred gigabytes needs physical backups with pg_basebackup or a managed snapshot approach. Point-in-time recovery through WAL archiving is straightforward once the archive command is wired to an S3 bucket, and it is also the feature that makes auditors comfortable when discussing recovery time objectives.

Where to Get Help With the Heavy Lifting

A migration of this kind touches every layer of the stack, including application code, database schema, infrastructure, security, and operational practice. If the scope feels larger than your team can absorb alongside the day job, infrastructure planning and cloud migration support is available through consulting engagements that scope, design, and sometimes co-deliver the work alongside your engineers. The right time to ask for help is usually before the cutover plan is written, not after the first failed dry run.