SQL Server to PostgreSQL: 3 Ways to Migrate or Replicate Data

SQL Server data can be moved to PostgreSQL using batch migration, AWS-native migration tooling, or continuous change data capture (CDC).

The right approach depends mainly on how much downtime you can tolerate, whether schema conversion is required, and whether PostgreSQL must stay synchronized with SQL Server while the migration is in progress.

In this guide, we’ll compare three practical approaches:

• SSIS: batch-oriented data migration for teams already using the Microsoft data stack
• AWS DMS + Schema Conversion: AWS-native schema conversion, full load, and ongoing change replication
• Estuary: managed SQL Server CDC with an initial backfill followed by ongoing table-data replication into PostgreSQL

For a one-time migration with an acceptable maintenance window, a batch approach may be sufficient. If the source SQL Server must remain active while PostgreSQL catches up, use a method that supports ongoing change replication to reduce the final cutover window.

SQL Server to PostgreSQL Migration Methods Compared

The biggest distinction is between batch migration and continuous replication.

SSIS is useful when data can be transferred in batches. AWS DMS is better suited to heterogeneous migration workflows that also require schema conversion. Estuary is a fit when selected SQL Server tables need an initial backfill followed by ongoing CDC into PostgreSQL.

None of these methods eliminates the need to review SQL Server-specific features such as T-SQL stored procedures, identity behavior, data types, collations, and application SQL before cutover.

What You Need Before Starting This Migration

Before you run a single command or open any tool, confirm the following technical requirements are in place. Skipping this pre-flight check is the leading cause of failed or corrupted migrations.

On the SQL Server side:

• SQL Server 2017 or later (Estuary's connector is regularly tested against SQL Server 2017 and up; SQL Server 2016 supports CDC but is not in the documented test matrix)
• sa or a user account with db_owner or CONTROL DATABASE permissions
• SQL Server Agent must be running (required for SSIS job scheduling and CDC)
• TCP/IP protocol enabled in SQL Server Configuration Manager
• Sufficient disk space for a full data export (at minimum 1.5x the current database size)

On the PostgreSQL side:

• PostgreSQL 12 or later recommended
• A target database already created: CREATE DATABASE target_db;
• A dedicated migration user with CREATE privileges on the target schema
• pg_hba.conf configured to allow connections from your migration host

Network requirements:

• Firewall rules allowing outbound traffic from migration host to SQL Server port 1433
• Firewall rules allowing outbound traffic to PostgreSQL port 5432
• For Estuary CDC: outbound HTTPS (port 443) to Estuary's cloud endpoint

SQL Server to PostgreSQL Data Type Mapping

SQL Server and PostgreSQL support many equivalent data types, but several require explicit conversion during migration.

AWS DMS maps SQL Server numeric, Boolean, date/time, and string types through its internal data types before writing them to PostgreSQL. For example, SQL Server BIT maps to Boolean, numeric types map to PostgreSQL DECIMAL/NUMERIC, and common SQL Server date/time values map to PostgreSQL timestamps. See the AWS DMS SQL Server source and PostgreSQL target documentation for current mappings.

PostgreSQL does have a native money type, but its formatting and fractional precision depend on locale settings. For heterogeneous migrations, NUMERIC is often easier to control consistently.

Common SQL Server to PostgreSQL Migration Gotchas

Identity Columns and Sequences

SQL Server commonly uses IDENTITY columns for generated IDs. PostgreSQL can use identity columns or sequences.

After migration, verify that the PostgreSQL sequence or identity value is ahead of the highest migrated key. Otherwise, new inserts may collide with existing rows.

ROWVERSION Is Not a Timestamp

SQL Server ROWVERSION is a binary version counter, not a date or time value.

Do not map it directly to PostgreSQL TIMESTAMP. If the application relies on it for optimistic concurrency or change detection, redesign that behavior explicitly on PostgreSQL.

Case Sensitivity and Collations

SQL Server installations commonly use case-insensitive collations, while PostgreSQL string comparisons and identifiers may behave differently.

Review:

• case-sensitive queries
• unique constraints
• indexed text columns
• application assumptions about upper/lower case

AWS DMS Schema Conversion can optionally map SQL Server character types to PostgreSQL CITEXT when preserving case-insensitive comparison behavior is important.

T-SQL Requires Conversion

SQL Server-specific T-SQL does not automatically become PostgreSQL-compatible SQL.

Review and test:

• stored procedures
• functions
• triggers
• temporary-table logic
• MERGE
• SQL Server built-in functions
• dynamic SQL
• transaction behavior

AWS DMS Schema Conversion can convert some procedures and functions, while unsupported constructs are surfaced as action items requiring manual work.

Index Names and Schema Names Can Conflict

SQL Server and PostgreSQL have different naming rules and namespace behavior.

For example, SQL Server can reuse an index name across different tables, while PostgreSQL index names must be unique within a schema. AWS DMS Schema Conversion includes an option to generate unique PostgreSQL index names for this reason.

Application SQL Must Be Tested

Even when the data and schema migrate successfully, application queries may still depend on SQL Server behavior.

Test:

• pagination syntax
• date functions
• string functions
• generated IDs
• stored-procedure calls
• quoting and identifier case
• transaction isolation assumptions

A successful row-count comparison does not guarantee that the application is ready to switch to PostgreSQL.

Method 1: Migrate SQL Server to PostgreSQL with SSIS

SQL Server Integration Services (SSIS) can move data from SQL Server to PostgreSQL using a traditional extract, transform, and load workflow.

The flow is:

SQL Server → SSIS data flow → PostgreSQL

SSIS is best suited to batch-oriented migrations where you can extract source tables, transform SQL Server-specific data types or values, and load the results into PostgreSQL.

Step 1: Prepare the PostgreSQL Target

Before loading data, create the PostgreSQL schema and map SQL Server objects to PostgreSQL equivalents.

Review:

• SQL Server data types
• identity columns
• collations and case sensitivity
• primary and foreign keys
• indexes and constraints
• T-SQL-specific logic

For heterogeneous migrations, do not assume the SQL Server schema can be copied unchanged.

Step 2: Configure the SQL Server Source

In SSIS, create a connection to SQL Server and select the source tables or queries you want to migrate.

Use source queries when you need to:

• rename columns
• filter rows
• reshape data
• convert unsupported types
• split a large migration into smaller batches

Step 3: Configure the PostgreSQL Destination

Connect SSIS to PostgreSQL using an appropriate PostgreSQL driver or provider.

Map source columns to the PostgreSQL target columns and verify that data types are compatible before running the full migration.

For example:

• UNIQUEIDENTIFIER → UUID
• BIT → BOOLEAN
• DATETIME2 → TIMESTAMP
• NVARCHAR → VARCHAR or TEXT
• SQL Server MONEY → typically NUMERIC(19,4) for predictable precision

Step 4: Run the Data Load

Execute the SSIS package to extract rows from SQL Server and load them into PostgreSQL.

For larger migrations, consider:

• loading tables in parallel where safe
• migrating in batches
• temporarily adjusting indexes or constraints if appropriate
• monitoring PostgreSQL write performance
• validating failed rows and conversion errors

Step 5: Validate the Migration

After the load completes, compare:

• row counts
• null values
• key columns
• date/time values
• numeric precision
• constraints
• application queries

Also validate any transformed data separately from the raw row counts.

When Is SSIS a Good Fit?

Use SSIS when:

• your team already uses the Microsoft data stack
• the migration can be performed in batches
• you need custom transformations during the move
• ongoing source-to-target synchronization is not required
• a defined migration window is acceptable

Things to Consider

• SSIS does not automatically convert the full SQL Server schema or T-SQL application logic into PostgreSQL equivalents.
• Ongoing changes made after the batch load require a separate synchronization strategy.
• Migration duration depends on data volume, network throughput, transformation complexity, and target performance.
• There is no universal database-size threshold where SSIS stops being suitable; the practical limit depends on the maintenance window and migration design.

Method 2: Migrate SQL Server to PostgreSQL with AWS DMS

AWS Database Migration Service (AWS DMS) can move SQL Server data into PostgreSQL using a full load, ongoing change replication, or both.

For heterogeneous migrations such as SQL Server to PostgreSQL, AWS also provides DMS Schema Conversion to help convert source database objects into PostgreSQL-compatible equivalents.

The migration flow is:

SQL Server → schema conversion → AWS DMS full load + CDC → PostgreSQL

AWS DMS supports Microsoft SQL Server as a source and PostgreSQL as a target. See the AWS DMS SQL Server source documentation and PostgreSQL target documentation for current requirements.

Step 1: Assess and Convert the Schema

Before moving the data, review SQL Server objects that need to be converted for PostgreSQL.

DMS Schema Conversion can help convert supported objects such as:

• tables
• views
• indexes
• functions
• stored procedures

Objects that cannot be converted automatically are identified for manual remediation.

See the AWS DMS Schema Conversion guidance for SQL Server to PostgreSQL.

Pay particular attention to:

• SQL Server-specific data types
• T-SQL syntax
• identity columns
• stored procedures and functions
• collations
• case sensitivity
• triggers and application queries

Step 2: Configure SQL Server as the Source

Create a SQL Server source endpoint in AWS DMS.

For ongoing change replication, SQL Server must be configured so DMS can read transaction changes. The exact requirements depend on the SQL Server edition and deployment type.

Configure:

• source hostname and port
• database credentials
• network connectivity
• permissions required by DMS
• CDC or transaction-log access when ongoing replication is needed

Step 3: Configure PostgreSQL as the Target

Create the PostgreSQL target endpoint and provide:

• PostgreSQL hostname and port
• database name
• credentials
• network configuration

Test both source and target endpoints before starting the migration task.

Step 4: Choose the Migration Type

AWS DMS supports:

• Full load: migrate existing SQL Server data once
• Full load + CDC: load existing data and continue replicating ongoing changes
• CDC only: replicate changes from a defined starting point

For migrations where SQL Server must remain active while PostgreSQL catches up, full load + CDC is usually the most relevant pattern.

Step 5: Run and Monitor the Migration

During the migration, monitor:

• full-load progress
• CDC latency
• failed or suspended tables
• task errors
• source log retention
• target PostgreSQL performance
• row counts and validation results

Once PostgreSQL is caught up and the converted schema has been validated, plan the application cutover.

When AWS DMS Is a Good Fit

Use AWS DMS when:

• you want an AWS-native heterogeneous migration workflow
• you need schema conversion assistance
• you need an initial full load followed by ongoing replication
• SQL Server must remain active during most of the migration
• your team is comfortable managing AWS networking, IAM, endpoints, and migration tasks

Things to Consider

• Not every SQL Server object can be converted automatically.
• T-SQL stored procedures, functions, triggers, and application SQL may require manual rewriting.
• CDC reduces the final migration window but does not eliminate the need for a controlled application cutover.
• Schema conversion and data replication should be treated as separate migration concerns and validated independently.

Method 3: Replicate SQL Server Data to PostgreSQL with Estuary

Estuary can continuously replicate selected SQL Server tables into PostgreSQL using SQL Server change data capture (CDC).

The pipeline looks like this:

SQL Server → CDC change tables → Estuary collections → PostgreSQL

Estuary’s SQL Server CDC connector reads changes from SQL Server CDC change tables and continuously captures inserts, updates, and deletes into Estuary collections. It supports self-hosted SQL Server as well as managed deployments such as Amazon RDS, Azure SQL Database, and Google Cloud SQL.

Step 1: Prepare SQL Server for CDC

For the CDC connector, you need:

• CDC enabled on the SQL Server database
• CDC enabled on the tables you want to capture
• a capture user with access to the source and CDC schemas
• network connectivity from Estuary to SQL Server

For tables without a primary key, Estuary allows you to manually define a collection key when creating the capture.

Step 2: Create the SQL Server Capture

In Estuary:

1. Create a SQL Server capture.
2. Enter the SQL Server connection details.
3. Discover the schemas and tables.
4. Select the tables you want to replicate.
5. Publish the capture.

Estuary can first backfill the selected table data and then continue capturing changes from SQL Server CDC.

Step 3: Configure PostgreSQL as the Destination

Use Estuary’s PostgreSQL destination connector to materialize the captured collections into PostgreSQL.

You need:

• PostgreSQL host and port
• database name
• destination credentials
• network access from Estuary

The PostgreSQL connector creates the destination tables from the materialization configuration. Tables manually created in advance are not supported by this connector.

Step 4: Start Ongoing Replication

Once the capture and materialization are published, Estuary continues moving SQL Server changes into PostgreSQL.

This is useful when you need:

• an initial load of selected SQL Server tables
• ongoing inserts, updates, and deletes replicated into PostgreSQL
• PostgreSQL kept close to the active SQL Server source during a migration
• managed table-data replication rather than operating CDC infrastructure yourself

How Are SQL Server Schema Changes Handled?

SQL Server CDC does not automatically update an existing CDC change table when the source table schema changes.

For example, when a column is added, Microsoft’s recommended approach is to create a new CDC capture instance and transition to it. Estuary can detect a second capture instance and switch to the newer one when appropriate; it also offers automatic capture-instance management when the required permissions are available.

This means schema evolution should still be planned and monitored during long-running migrations.

What Estuary Does Not Migrate

Estuary should be treated as a table-data replication approach, not a complete SQL Server-to-PostgreSQL schema conversion tool.

You still need separate handling for SQL Server-specific objects and behavior such as:

• T-SQL stored procedures and functions
• triggers
• linked servers
• server-level roles and permissions
• indexes or constraints that require PostgreSQL-specific redesign
• SQL Server-specific data types or application queries

Use schema-conversion tooling or manual migration work for those objects.

How to Minimize Downtime When Migrating SQL Server to PostgreSQL

To reduce downtime, use a migration method that performs an initial load followed by ongoing change replication.

A typical low-downtime migration looks like this:

1. Convert or prepare the PostgreSQL schema.
2. Load the existing SQL Server data into PostgreSQL.
3. Keep SQL Server online while new inserts, updates, and deletes continue replicating.
4. Monitor replication lag until PostgreSQL is nearly caught up.
5. Pause application writes briefly.
6. Apply the remaining changes and validate critical tables.
7. Update the application connection to PostgreSQL.
8. Resume writes against PostgreSQL.

This pattern can be implemented with AWS DMS or a CDC pipeline such as Estuary.

The important point is that CDC can reduce the amount of data that must be moved during the final cutover, but it does not eliminate the need to plan the application switch, validate the target, and handle SQL Server-specific schema or application logic separately.

SQL Server to PostgreSQL Pre-Migration Checklist

Before starting the migration, confirm:

• Schema conversion: Review tables, indexes, constraints, procedures, functions, triggers, and SQL Server-specific objects.
• Data type mapping: Validate types such as UNIQUEIDENTIFIER, BIT, DATETIME2, MONEY, ROWVERSION, and Unicode strings.
• Identity behavior: Make sure PostgreSQL identity columns or sequences are configured correctly after the data load.
• Collations and case sensitivity: Check whether application queries depend on SQL Server’s collation behavior.
• Application SQL: Test T-SQL-specific queries and stored-procedure calls against PostgreSQL equivalents.
• Source and target connectivity: Verify credentials, firewall rules, network access, and required permissions.
• Migration method: Decide whether the move is batch-only or requires ongoing CDC during the migration.
• Validation plan: Define how you will compare row counts, critical tables, key business queries, and transformed values.
• Cutover plan: Document when writes will be paused, how final changes will be applied, and how the application will switch to PostgreSQL.
• Rollback plan: Define how to return to SQL Server if validation fails after cutover.

The migration should not be considered complete until both the data and the application behavior have been validated on PostgreSQL.

Which SQL Server to PostgreSQL Method Should You Use?

Choose the method based on whether you need a batch migration, AWS-native heterogeneous migration, or ongoing table-data replication during the move.

Use SSIS when the migration can run in batches and your team needs custom transformations during the move.

Use AWS DMS + Schema Conversion when you want an AWS-native migration workflow that combines schema conversion with full-load and ongoing replication options.

Use Estuary when PostgreSQL needs to stay synchronized with selected SQL Server tables during the migration and you want managed CDC rather than operating the replication pipeline yourself.

The main decision is whether you need schema conversion, ongoing synchronization, or both. Data replication alone does not replace the work required to convert T-SQL, stored procedures, triggers, collations, and application-specific SQL.

Need to keep SQL Server data synchronized with PostgreSQL during migration?

Start building with Estuary or review the SQL Server source connector and PostgreSQL destination connector documentation.