PostgreSQL to SQL Server Migration: Batch Load and Real-Time CDC

You can move PostgreSQL data to Microsoft SQL Server in two main ways: perform an offline batch migration using PostgreSQL `COPY` and SQL Server bulk-import tools, or use change data capture (CDC) to backfill existing rows and continuously replicate subsequent changes.

A batch migration is suitable for small or relatively static databases when a maintenance window is acceptable. CDC is better for active production databases that must remain available while SQL Server is populated, tested, and prepared for cutover.

A complete PostgreSQL to SQL Server migration involves more than copying rows. You must also translate data types, sequences, indexes, constraints, functions, triggers, views, SQL syntax, and application behavior.

This guide compares both data-movement methods, explains important compatibility differences, and shows how Estuary can continuously replicate PostgreSQL tables into SQL Server with minimal downtime.

Key takeaways

• Use CSV and SQL Server bulk-import tools for small, one-time migrations where downtime is acceptable.
• Use CDC when PostgreSQL remains active and SQL Server must stay current during testing and cutover.
• PostgreSQL functions, triggers, views, extensions, indexes, and application queries do not automatically become equivalent SQL Server objects.
• Estuary can backfill table data and continuously capture row-level changes from PostgreSQL's write-ahead log.
• Estuary is the data-movement layer of the migration. Schema redesign, application conversion, testing, and cutover planning remain separate tasks.

PostgreSQL to SQL Server migration methods compared

Why teams move from PostgreSQL to SQL Server

Organizations usually move from PostgreSQL to SQL Server to align with a broader Microsoft technology strategy rather than because one database is universally better.

Common reasons include:

• Standardizing database operations within a Microsoft and Azure environment.
• Integrating operational data more closely with Power BI, Excel, and other Microsoft tools.
• Consolidating database administration, security, monitoring, and procurement.
• Meeting application or vendor requirements that specify SQL Server.
• Using existing SQL Server expertise and support arrangements.

Whatever the reason, the migration must account for differences in data types, SQL syntax, database objects, security models, and application behavior.

Choose an offline or low-downtime migration strategy

The migration strategy should be based primarily on database size, schema complexity, change volume, and acceptable downtime.

Offline batch migration

In an offline migration, applications stop writing to PostgreSQL while data is exported and loaded into SQL Server. This is the simplest approach, but the maintenance window must cover the final export, import, validation, and application cutover.

Use this strategy when:

• The database is small or changes infrequently.
• A maintenance window is acceptable.
• Only a limited number of tables must be migrated.
• The team is comfortable creating and validating the SQL Server schema manually.

Phased low-downtime migration with CDC

In a phased migration, existing PostgreSQL rows are backfilled first. CDC then captures subsequent changes while SQL Server is validated in parallel.

When the destination is current and application testing is complete, writes to PostgreSQL are paused, the remaining changes are applied, and production traffic is moved to SQL Server.

Use this strategy when:

• PostgreSQL must remain available during most of the migration.
• The database is large or changes continuously.
• SQL Server must be tested against current production data.
• The business requires a short final cutover window.

CDC reduces data-movement downtime, but it does not convert database functions, triggers, views, extensions, permissions, or application code.

Seven-step PostgreSQL to SQL Server migration playbook

1. Inventory the PostgreSQL database

Document tables, row counts, data volumes, primary keys, sequences, indexes, views, functions, triggers, extensions, permissions, and dependencies. Identify tables with large objects, arrays, JSON, or unusually high update rates.

2. Map PostgreSQL features to SQL Server

Define how PostgreSQL data types and database objects will be represented in SQL Server. Identify objects that require manual redesign, including PL/pgSQL functions, expression indexes, materialized views, extensions, and PostgreSQL-specific queries.

3. Choose the data-movement method

Use CSV and SQL Server bulk-import tools when downtime is acceptable. Use CDC when PostgreSQL must remain active while SQL Server is populated and tested.

4. Prepare the destination

For a manual batch migration, create the SQL Server tables, keys, identity behavior, and required data-type mappings before loading data.

When using the Estuary SQL Server materialization connector, allow the connector to create its bound destination tables. Plan additional indexes, constraints, permissions, and application-specific database objects separately.

5. Load existing data

Export and bulk-load each table for an offline migration, or allow the CDC pipeline to backfill existing PostgreSQL rows into SQL Server.

6. Validate and cut over

Compare row counts, keys, checksums or aggregates, timestamps, null values, JSON documents, and critical business-query results. Test the application against SQL Server before pausing PostgreSQL writes and completing the final cutover.

7. Monitor and decommission

Monitor SQL Server performance, application errors, and data correctness after cutover. Retain PostgreSQL according to the rollback and compliance plan before decommissioning it.

PostgreSQL to SQL Server differences cheat sheet

Data-type mappings are starting points rather than universal conversions. Validate precision, range, encoding, time-zone behavior, and application expectations before loading production data.

Database objects that require manual conversion

Review the following separately from table data:

• PL/pgSQL functions and triggers, which must be rewritten in T-SQL or application code.
• PostgreSQL extensions that do not have SQL Server equivalents.
• Partial and expression indexes. Some partial indexes can become SQL Server filtered indexes, but not every predicate is supported.
• PostgreSQL full-text search configurations and queries.
• Materialized views. SQL Server indexed views have additional restrictions and are not direct equivalents.
• ON CONFLICT, PostgreSQL casts, operators, and JSONB queries.
• Case sensitivity, quoted identifiers, and collation behavior.
• Roles, grants, authentication, and row-level security.

PostgreSQL to SQL Server migration methods

Method 1: Migrate PostgreSQL to SQL Server with CSV files

For a small database or a one-time offline migration, you can export PostgreSQL tables as CSV files and import them into SQL Server.

Step 1: Create the SQL Server schema

Create the destination tables before loading data. Translate PostgreSQL data types, primary keys, identity behavior, nullability, and default values into appropriate SQL Server definitions.

Do not rely entirely on automatic CSV type inference. A value appearing later in a file may require a wider string, different numeric precision, or nullable column than the initial sample suggests.

Step 2: Export PostgreSQL tables

Use PostgreSQL's \copy command from psql to write a table or query result to a file on the client machine:

sql
\copy public.customers TO 'customers.csv'
WITH (
    FORMAT CSV,
    HEADER TRUE,
    ENCODING 'UTF8'
);

Unlike the server-side COPY command, \copy reads and writes files through the client, which is usually more practical when you do not have access to the PostgreSQL server's filesystem.

Step 3: Import the CSV files into SQL Server

When the SQL Server tables have already been created, use the SQL Server Import and Export Wizard:

1. Open SQL Server Management Studio and connect to the destination.
2. Right-click the target database.
3. Select Tasks → Import Data.
4. Select Flat File Source and choose the exported CSV file.
5. Select the SQL Server database as the destination.
6. Map the file to the appropriate existing destination table.
7. Review column mappings, data types, string lengths, nullability, and identity behavior.
8. Run the import and review any warnings or rejected rows.
9. Compare the imported row count with the PostgreSQL source.

The separate Import Flat File option is useful when SQL Server should infer a schema and create a new table. It should not be used when the destination table has already been created.

For larger or repeatable migrations, use bcp, BULK INSERT, SSIS, or another controlled bulk-loading process instead of manually importing every file.

Limitations of the CSV method

• It does not capture changes made after a table is exported.
• Every destination table and data-type mapping must be prepared.
• Large objects, arrays, JSON, timestamps, and escaped characters require careful testing.
• Functions, triggers, views, extensions, permissions, and application queries are not migrated.
• The required downtime increases with database size and validation time.

Method 2: Continuously replicate PostgreSQL to SQL Server with Estuary

Estuary is a managed real-time data movement platform that supports CDC, streaming, and batch pipelines.

Its PostgreSQL capture connector first backfills existing rows from selected tables. It then uses PostgreSQL logical replication to read committed inserts, updates, and deletes from the write-ahead log and store them in Estuary collections.

A SQL Server materialization writes those collections into connector-managed SQL Server tables. Standard updates are the default and maintain the latest reduced state based on each collection's key. Delta updates can be enabled for append-oriented workloads.

This method is suitable for keeping SQL Server current while PostgreSQL remains the system of record. It reduces the downtime needed for data movement, but it does not automatically convert PostgreSQL functions, views, triggers, extensions, constraints, or application queries.

Prerequisites

Before you set up the pipeline in Estuary, make sure both ends are ready.

PostgreSQL prerequisites

The PostgreSQL source requires:

• PostgreSQL 10 or later.
• Logical replication enabled with wal_level = logical.
• A capture user with the REPLICATION attribute and permission to read the selected tables.
• A logical replication slot. The connector can create one when it has sufficient permissions.
• A publication containing the tables to capture. This can also be created automatically when permissions allow.
• A watermarks table for consistent backfills under the default capture mode.
• A primary key or suitable replica identity for tables whose updates and deletes must be captured.
• A publication limited to the tables being captured, rather than an unrestricted publication containing unrelated keyless tables.

Read-only capture mode is available when the connector cannot write to a watermarks table. In this mode, at least one captured table must change regularly, or a dedicated heartbeat table must be updated periodically so that the replication slot can continue advancing.

Capturing from a read-only standby requires PostgreSQL 16 or later and additional configuration, including hot_standby_feedback.

SQL Server prerequisites

The SQL Server destination requires:

• SQL Server 2017 or later.
• A destination database and at least one Estuary collection.
• Credentials that can create, read, and write the destination tables as required by the connector.
• Direct network access through allowlisted Estuary IP addresses or an SSH tunnel.
• User/password authentication, AWS IAM authentication, or Azure IAM authentication, depending on the hosting environment.

The SQL Server materialization connector creates destination tables from the configured bindings. It is not intended to write into manually pre-created tables by default. Create additional indexes, constraints, permissions, and application-specific objects after planning how they will interact with ongoing replication.

AWS IAM authentication for Amazon RDS for SQL Server requires an RDS Proxy configured for IAM authentication. Azure IAM authentication uses an Azure App Registration. Follow the hosting-specific connector documentation rather than applying one authentication procedure to every SQL Server deployment.

Step 1: Capture changes from PostgreSQL

1. Sign in to Estuary 
   • Create a free Estuary account or log in to your existing one.
2. Create a new PostgreSQL capture
   
   • From the dashboard, go to Sources and click + New Capture.
   • Search for PostgreSQL in the connector list and select the PostgreSQL capture connector.
     
     

     
     
     
3. Configure the PostgreSQL endpoint
   On the PostgreSQL Create Capture page, fill in:
   
   

   
   
   
   • Name: a unique name for this capture.
   • Address: the PostgreSQL host and port (for example, mydb.example.com:5432).
   • Database: the logical database name to capture from.
   • User and authentication: Provide the capture user and choose user/password authentication or a supported AWS, Google Cloud, or Azure IAM authentication method.
4. If needed, expand advanced options to set things like publication name, replication slot name, watermarks table, SSL mode, or to skip backfills for very large tables.
5. Select tables to capture
   
   • Run discovery if prompted so Estuary can list schemas and tables.
   • Choose the tables or schemas you want to capture into Estuary collections.
6. Save and publish the capture
   
   • Click Next and review the bindings (table to collection mappings).
   • Click Save and publish to start the capture. Estuary will backfill existing rows by default, then switch to streaming CDC events from PostgreSQL.

Step 2: Materialize collections into SQL Server

1. Create a new SQL Server materialization
   
   • From the dashboard, go to Destinations and click + New Materialization.
   • Search for SQL Server in the connector list and select the Microsoft SQL Server materialization connector.
2. Configure the SQL Server endpoint
   On the SQL Server Create Materialization page, provide:
   
   

   
   
   
   • Name: a unique name for this materialization.
   • Address: the SQL Server host and port (for example, sql.example.com:1433).
   • Database: the name of the database where tables should be created.
   • Schema: The default SQL Server schema for materialized tables, when required.
   • User and authentication: Provide the destination user and select user/password, AWS IAM, or Azure IAM authentication according to the hosting environment.
3. Map collections to tables
   
   • In the Source collections section, select the collections that come from your PostgreSQL capture.
   • For each collection, confirm or adjust the target table name and options such as whether to use delta updates.
4. Save and publish the materialization
   
   • Click Next to review your mappings.
   • Click Save and publish. Estuary will create the target tables if they do not exist and begin applying changes from the collections into SQL Server.

Step 3: Validate and cut over

After the capture and materialization are published:

1. Allow the initial backfill to complete.
2. Confirm that ongoing PostgreSQL changes continue reaching the Estuary collections and SQL Server.
3. Compare source and destination row counts, accounting for any intentionally filtered records.
4. Validate primary keys, null values, numeric precision, timestamps, Unicode text, JSON, arrays, and large objects.
5. Test representative inserts, updates, and deletes.
6. Compare the results of critical business queries in both databases.
7. Recreate and test required SQL Server indexes, constraints, permissions, functions, and triggers.
8. Test the application against SQL Server in a staging environment.
9. Monitor replication freshness until SQL Server is current.
10. Pause writes to PostgreSQL during the final cutover window.
11. Confirm that the final source changes have reached SQL Server.
12. Run final validation before changing production connection strings.

This is a one-way PostgreSQL-to-SQL Server pipeline. Writes made to SQL Server do not automatically return to PostgreSQL. The rollback plan must account for this before SQL Server begins accepting production writes.

If PostgreSQL remains the system of record after the project, the pipeline can continue keeping SQL Server updated. If SQL Server becomes the new system of record, stop or disable the PostgreSQL-source pipeline after the rollback window and final validation are complete.

Conclusion

For small PostgreSQL databases where downtime is acceptable, exporting tables with \copy and loading them through SQL Server bulk-import tools can be sufficient. For active production databases, an initial backfill followed by continuous CDC provides a lower-downtime path to populating and validating SQL Server before cutover.

Data replication does not replace schema and application migration. Data types, functions, triggers, extensions, indexes, permissions, queries, and application behavior must still be converted and tested separately.

Want to keep SQL Server continuously updated while PostgreSQL remains operational? Start building a PostgreSQL-to-SQL Server CDC pipeline with Estuary or review the PostgreSQL capture connector and SQL Server materialization connector.