Migrating from SQL Server to PostgreSQL isn't a weekend project. I've seen teams assume it is — and pay for that assumption with months of painful rollbacks and production incidents. But with the right preparation, it's absolutely manageable. Done correctly, you'll cut licensing costs dramatically, gain a more flexible open-source platform, and position your infrastructure for cloud-native growth.
Here's the practical, no-nonsense guide I wish I'd had the first time I ran this migration.
Why Companies Are Making the Move
SQL Server is a capable platform. But licensing costs are real — especially as databases grow. A SQL Server Enterprise license can run 15,000+ per core, which adds up fast in a multi-server or cloud environment. PostgreSQL is free, battle-hardened, and backed by one of the most active open-source communities in the world.
Beyond cost, PostgreSQL's extensibility is hard to match: native JSON support, full-text search, powerful indexing options (BRIN, GIN, GiST), and a rich ecosystem of extensions like PostGIS for geospatial data. If you're running in AWS, Azure, or GCP, managed PostgreSQL options (RDS, Azure Database, Cloud SQL) remove the operational overhead without the licensing bill.
The tradeoff? PostgreSQL isn't a drop-in replacement. You'll encounter differences in T-SQL syntax, data types, transaction handling, and tooling that require careful planning.
Step 1: Assess Before You Touch Anything
The worst migrations start with someone running a schema export and hoping for the best. Start with a thorough assessment:
- Catalog your T-SQL dependencies. Stored procedures, triggers, functions, and views written in T-SQL won't run in PostgreSQL without modification. Tools like pgloader and the AWS Schema Conversion Tool (SCT) can automate much of this, but they won't catch everything.
- Identify SQL Server-specific features. Features like
IDENTITY,GETDATE(),TOP,WITH (NOLOCK), andISNULL()all have PostgreSQL equivalents — but they're not identical. - Audit your application layer. ORM frameworks like Entity Framework or Hibernate may handle some abstraction, but raw SQL queries scattered through your codebase will need updates.
- Profile your data volumes. A 500GB database migrates differently than a 10TB one. Know your row counts, LOB storage usage, and transaction rates.
Step 2: Convert the Schema
Schema conversion is where most projects underestimate the work. Here are the most common translation issues I encounter:
| SQL Server | PostgreSQL Equivalent |
|---|---|
IDENTITY(1,1) | SERIAL or GENERATED ALWAYS AS IDENTITY |
NVARCHAR(MAX) | TEXT |
DATETIME | TIMESTAMP |
BIT | BOOLEAN |
GETDATE() | NOW() or CURRENT_TIMESTAMP |
ISNULL(x, y) | COALESCE(x, y) |
TOP N | LIMIT N |
WITH (NOLOCK) | Remove — PostgreSQL uses MVCC |
That last one trips up a lot of teams. SQL Server's NOLOCK hint is often used as a quick fix for blocking issues. PostgreSQL's MVCC (Multi-Version Concurrency Control) makes it unnecessary — readers never block writers — so just remove those hints.
For stored procedures and functions, plan on manual rewriting. T-SQL and PL/pgSQL are similar in structure but different enough to require a careful read-through of each object. Budget 1–2 hours per complex procedure.
Step 3: Migrate the Data
For the actual data transfer, you have a few solid options depending on your downtime tolerance:
Option A: pgloader (low downtime, good for most migrations)
pgloader handles schema conversion and data migration in one pass, including transformations for incompatible types. It supports continuous replication for near-zero downtime migrations. It's my default choice for databases under 500GB.
Option B: AWS Database Migration Service (DMS)
If you're moving to AWS RDS or Aurora PostgreSQL, DMS handles the heavy lifting with a CDC (change data capture) mode that keeps source and target in sync while you run parallel testing. It's not free, but for large migrations or strict uptime requirements, it's worth every dollar.
Option C: Custom ETL scripts
For complex transformations that neither tool handles cleanly, Python or Node.js scripts using psycopg2 or pg give you full control. Slower to build, but sometimes necessary.
Regardless of method: always migrate to a non-production environment first. Validate row counts, check data integrity, and run your application test suite against it before you touch production.
Step 4: Rewrite and Test Application Code
This step often gets shortchanged. After schema and data are in place:
- Update connection strings to point to PostgreSQL (driver change required —
pyodbc→psycopg2,mssql→pg, etc.) - Find and replace T-SQL constructs in application code
- Re-run your full regression test suite
- Load-test the new environment with production-equivalent query volumes
PostgreSQL query plans can differ significantly from SQL Server's. A query that ran in 50ms under SQL Server might run in 5 seconds on PostgreSQL without the right indexes — and vice versa. Run EXPLAIN ANALYZE on your most critical queries post-migration and tune accordingly.
Step 5: Plan Your Cutover
Cutover is where preparation pays off. A few things that will save your sanity:
- Set up replication before cutover day. Run source and target in sync for at least 48 hours before you switch.
- Pick a low-traffic window. Weekday nights or Sunday mornings, depending on your business.
- Have a rollback plan. Know exactly how you'll switch back to SQL Server if something goes wrong. Document it. Practice it.
- Monitor aggressively for 72 hours post-cutover. PostgreSQL's
pg_stat_statementsextension is your best friend for catching unexpected slow queries.
Common Pitfalls (From Experience, Not Textbooks)
- Underestimating stored procedure rewrites. A 200-procedure database can easily be 3–4 weeks of rewrite work.
- Ignoring case sensitivity. PostgreSQL is case-sensitive for identifiers by default (unless you use double quotes). SQL Server isn't. This breaks things quietly.
- Skipping connection pooling. PostgreSQL handles connections differently. Add PgBouncer or pgpool-II before you go to production.
- Forgetting autovacuum tuning. Out-of-the-box PostgreSQL autovacuum settings are conservative. Tune them for your workload — especially on high-write tables.
When to Bring in a DBA
If your team is handling this migration alongside regular development work, consider bringing in external DBA help for the assessment, schema conversion review, and post-migration performance tuning. These phases have the highest risk and the least room for trial and error.
A remote DBA engagement for a SQL Server to PostgreSQL migration typically runs 40–120 hours depending on complexity — a fraction of the cost of a botched production cutover.
Migrating databases is never trivial, but with careful planning and the right expertise, a SQL Server to PostgreSQL migration is very achievable. The long-term payoff in licensing savings and platform flexibility makes it worth the investment.
Questions about your specific migration? Contact Server Side Technology Solutions — we've guided dozens of these transitions and can help you avoid the expensive surprises.