Migrate from SQL Server
to PostgreSQL
Migrating from SQL Server to PostgreSQL eliminates Microsoft licensing costs while moving to a database with superior extensibility, JSON support, and cross-platform compatibility. The migration requires converting T-SQL to PL/pgSQL, replacing SQL Server ecosystem tools like SSIS, SSRS, and SQL Server Agent with open-source alternatives, and adapting Windows-integrated authentication to PostgreSQL's authentication framework.
When SQL Server stops working
SQL Server becomes a constraint when Enterprise Edition licensing costs—which can reach tens of thousands of dollars per core—make scaling prohibitively expensive, particularly in virtualized or cloud environments where core counts multiply. Organizations discover that SQL Server's tight coupling with the Windows ecosystem limits deployment flexibility as teams adopt containerized and Linux-based infrastructure. The vendor lock-in becomes acute when SSIS packages, SSRS reports, and SQL Server Agent jobs create an ecosystem dependency that extends far beyond the database engine itself, making every infrastructure decision revolve around Microsoft compatibility.
What PostgreSQL unlocks
PostgreSQL provides Enterprise-grade features like table partitioning, parallel query execution, logical replication, and row-level security without any licensing cost. Its extension ecosystem (PostGIS for geospatial, TimescaleDB for time-series, pg_vector for embeddings) enables specialized workloads without additional database products. PostgreSQL runs natively on Linux, enabling deployment on Kubernetes, ARM-based instances, and any cloud provider without licensing considerations. The foreign data wrapper system allows PostgreSQL to query external data sources including other databases, CSV files, and REST APIs, replacing SQL Server's linked server functionality with a more flexible and extensible approach.
Who should not migrate
Organizations heavily invested in the Microsoft ecosystem where SQL Server integrates tightly with .NET applications using Entity Framework, Power BI with DirectQuery, Azure Synapse Analytics, and Azure Data Factory should weigh the integration rewrite costs carefully. Teams relying on SQL Server's in-memory OLTP (Hekaton) for extreme transaction throughput have no direct PostgreSQL equivalent. If your deployment depends on Always On Availability Groups with automatic page repair and readable secondary replicas across multiple data centers, replicating this exact topology in PostgreSQL requires significant architectural work with Patroni, repmgr, or cloud-managed services.
What usually goes wrong
T-SQL to PL/pgSQL conversion appears straightforward but hides significant complexity in areas like temporary table scoping (SQL Server's #temp tables are session-scoped with automatic cleanup while PostgreSQL temp tables behave differently in connection pooling scenarios), error handling (TRY-CATCH blocks versus BEGIN-EXCEPTION blocks with different transaction rollback semantics), and identity column behavior (SQL Server IDENTITY with SCOPE_IDENTITY() versus PostgreSQL GENERATED ALWAYS AS IDENTITY with currval()). SSIS package migration is consistently the most underestimated workstream because SSIS packages encode complex data transformation logic, error handling flows, and orchestration dependencies that must be decomposed and rebuilt in tools like Apache Airflow, dbt, or custom Python scripts. Teams also struggle with replacing SQL Server's cross-database queries (USE database; SELECT...) since PostgreSQL schemas and databases have different isolation boundaries.
Risk Matrix: SQL Server to PostgreSQL
T-SQL and PL/pgSQL differ in transaction handling within stored procedures, temporary table visibility across nested procedure calls, dynamic SQL execution (EXEC/sp_executesql vs EXECUTE), string concatenation behavior with NULL values, and date/time arithmetic. SQL Server's implicit transaction mode and PostgreSQL's explicit transaction control create different default behaviors for error recovery.
Use pgLoader or AWS SCT for initial automated conversion, then manually review each procedure. Build a T-SQL compatibility layer in PostgreSQL with functions mimicking ISNULL, GETDATE, DATEADD, DATEDIFF, CHARINDEX, and STUFF. Create a regression test suite that exercises every stored procedure with boundary conditions, NULL inputs, and concurrent execution patterns.
SSIS packages contain visual data flow logic with transformations, lookups, conditional splits, and error output routing that have no one-to-one mapping to open-source ETL tools. Packages often embed connection managers with Windows authentication, encrypted credentials, and environment-specific configurations that must be redesigned.
Inventory all SSIS packages and classify by complexity and business criticality. Rebuild data pipelines in Apache Airflow for orchestration with dbt for SQL-based transformations or Python scripts for complex logic. Migrate packages incrementally, running old and new pipelines in parallel with output comparison until the new pipeline is validated. Document all SSIS package configurations including schedules, error handling, and notification settings.
SQL Server applications often use Windows Integrated Authentication (Kerberos/NTLM) through Active Directory, allowing transparent single sign-on without managing database passwords. PostgreSQL supports Kerberos via GSSAPI but requires explicit configuration, and many applications expect Windows authentication to work transparently via their connection strings.
Configure PostgreSQL GSSAPI authentication against Active Directory for applications that require Kerberos. For applications that can use password-based authentication, deploy a secrets management solution like HashiCorp Vault to rotate and distribute PostgreSQL credentials. Use LDAP authentication in pg_hba.conf for organizations that want Active Directory integration without full Kerberos complexity. Test every application's connection flow in a staging environment before cutover.
SQL Server Agent provides integrated job scheduling with step-based workflows, alerts on failure, operator notifications, and job history that operations teams rely on. Replacing this with pg_cron, pgAgent, or OS-level cron jobs fragments the monitoring and alerting surface, creating blind spots where failed jobs go unnoticed.
Adopt pg_cron for database-level scheduled tasks and Apache Airflow for complex multi-step workflows. Integrate job execution monitoring with existing observability platforms (Datadog, PagerDuty, or Prometheus with AlertManager). Build a centralized job dashboard that tracks execution history, duration trends, and failure rates to match the visibility SQL Server Agent provided.
SSRS reports use RDL format with T-SQL queries, linked reports, drill-through navigation, and subscription schedules that depend on SQL Server infrastructure. Business users rely on scheduled report delivery via email and SharePoint integration. No single open-source tool replaces all SSRS functionality.
Evaluate replacement options: Apache Superset, Metabase, or JasperReports for self-service reporting; pgAdmin's built-in reporting for simple cases; or commercial tools like Looker or Tableau for enterprise needs. Migrate reports in priority order, starting with the most frequently accessed. Maintain SSRS in read-only mode during transition, connected to a PostgreSQL read replica via linked server if necessary.
What Must Not Change During This Migration
All T-SQL stored procedures must produce identical results to their PL/pgSQL equivalents for the same input parameters, validated by automated regression tests
Data type mappings must preserve precision and range: SQL Server decimal/numeric precision, datetime2 microsecond accuracy, and nvarchar full Unicode support
Every SSIS data pipeline must have a validated replacement that produces identical output datasets on the same schedule before the original is decommissioned
Authentication and authorization must enforce identical access controls in PostgreSQL as existed in SQL Server, with no privilege escalation or access gaps
Backup and recovery procedures must be validated to meet existing RTO and RPO targets using PostgreSQL's pg_basebackup, WAL archiving, and point-in-time recovery
Migration Process: SQL Server to PostgreSQL
Schema assessment
Use AWS Schema Conversion Tool or pgLoader's schema inspection to generate a compatibility report. Map SQL Server data types to PostgreSQL equivalents: int to integer, bigint to bigint, datetime2 to timestamp, nvarchar to varchar (PostgreSQL uses UTF-8 natively), uniqueidentifier to uuid, money to numeric(19,4), bit to boolean, varbinary to bytea. Inventory all tables, views, indexes, computed columns, filtered indexes, and columnstore indexes. Document cross-database references and linked server dependencies.
Query translation
Convert T-SQL stored procedures, functions, triggers, and views to PL/pgSQL. Replace SQL Server-specific constructs: TOP with LIMIT, ISNULL with COALESCE, GETDATE() with NOW(), DATEADD/DATEDIFF with interval arithmetic, STRING_AGG with PostgreSQL's STRING_AGG, CROSS APPLY with LATERAL JOIN, PIVOT with crosstab or filtered aggregation. Convert Common Table Expressions that use the OUTPUT clause to PostgreSQL RETURNING. Replace SQL Server's TRY-CATCH error handling with PL/pgSQL BEGIN-EXCEPTION blocks, noting that PostgreSQL rolls back to the last savepoint on exception rather than allowing partial transaction continuation.
Data migration
Use pgLoader for direct SQL Server to PostgreSQL data migration with on-the-fly type casting and transformation. For databases exceeding 1 TB, export to CSV or Parquet format and use PostgreSQL COPY for bulk loading. Configure Debezium with Kafka Connect to establish change data capture from SQL Server's transaction log for ongoing synchronization during the parallel-run period. Disable foreign keys and triggers during bulk load, then re-enable and validate referential integrity after loading completes.
Parallel validation
Run the complete application test suite against PostgreSQL. Execute business-critical SQL queries on both databases and compare results. Validate that scheduled jobs (migrated to pg_cron or Airflow) execute correctly and produce expected outcomes. Test application behavior under concurrent load with connection pooling via PgBouncer. Verify that all reporting queries return consistent results with the same data. Benchmark query performance against SQL Server baselines and tune PostgreSQL configuration parameters accordingly.
Traffic cutover
Update application connection strings from SQL Server ODBC/OLE DB drivers to PostgreSQL libpq or Npgsql (.NET). For applications using Entity Framework, switch the database provider from SqlServer to Npgsql. Redirect all ETL pipelines from SSIS to the new Airflow/dbt stack. Switch DNS or load balancer targets from SQL Server to PostgreSQL. Maintain SQL Server in read-only mode for 48-72 hours as a rollback option. Cut over reporting tools and dashboards to PostgreSQL connections.
Verification and decommission
Monitor PostgreSQL using pg_stat_statements for query performance, pg_stat_user_tables for table access patterns, and pg_stat_bgwriter for buffer and checkpoint metrics. Compare against SQL Server DMV baselines. Verify backup schedules and test point-in-time recovery. After two full business cycles without incidents, archive SQL Server databases as .bak files for regulatory compliance, decommission SQL Server instances, and terminate Windows Server and SQL Server licenses.
How This Migration Changes at Scale
More than 200 SSIS packages in production
SSIS migration becomes a standalone project requiring dedicated data engineering resources. Each package must be analyzed, decomposed into individual data flows, and rebuilt in Airflow DAGs or dbt models. Expect the SSIS migration to take 2-3x longer than the database migration itself. Prioritize packages by business criticality and migrate in waves.
Application uses SQL Server-specific features like Service Broker or FileStream
Service Broker message queuing must be replaced with RabbitMQ, Apache Kafka, or PostgreSQL LISTEN/NOTIFY for simpler pub/sub patterns. FileStream and FileTable functionality requires migration to object storage (S3 or GCS) with PostgreSQL storing metadata and references. These architectural changes ripple through application code and require dedicated development sprints.
Multi-database architecture with cross-database queries
SQL Server allows queries spanning multiple databases on the same instance (USE db; SELECT...) which PostgreSQL does not support across database boundaries. Options include consolidating into a single PostgreSQL database with schemas, using postgres_fdw to create foreign tables, or refactoring application queries to avoid cross-database joins. The consolidation approach is most common but requires careful namespace management.
Always On Availability Groups with readable secondaries across data centers
Replicating this architecture requires PostgreSQL streaming replication with synchronous commit for the primary standby, hot standby configuration for read replicas, and Patroni for automated failover. Geographic distribution adds network latency considerations for synchronous replication. Evaluate whether the application can tolerate asynchronous replication for cross-region replicas to avoid latency impact on write transactions.
Related Migration Paths
Organizations standardizing on PostgreSQL often migrate both SQL Server and Oracle databases concurrently, leveraging shared PostgreSQL operational expertise, tooling, and infrastructure across both migration tracks.
SQL Server migration frequently accompanies broader application modernization from .NET Framework monoliths to .NET Core microservices, creating natural synergies in refactoring data access layers and deployment pipelines.
Moving away from SQL Server often aligns with reducing Azure dependency, as organizations shift workloads to AWS with RDS for PostgreSQL or Aurora PostgreSQL as managed database alternatives.
Related Analysis
SQL Server vs PostgreSQL
For organizations reconsidering their database platform, PostgreSQL offers an open-source alternative to SQL Server with strong feature parity and zero licensing constraints.
Read analysisWhen SQL Server Becomes Too Expensive
5 warning signs that SQL Server has outgrown its limits.
Read analysisIf you're evaluating a migration from SQL Server to PostgreSQL, the first step is validating risk, scope, and invariants before any build work begins.