Low-Downtime Migration SQL Server → PostgreSQL — Strategies for the Cutover

The question comes up reliably in the first planning round, and it rarely comes from IT: how long will the database be unavailable during the move? The answer decides whether the data is moved in one piece inside a maintenance window, whether the bulk is copied ahead of time with only the changes following until the switchover, or whether a replication tool keeps both systems in step for days. Anyone looking to migrate SQL Server to PostgreSQL with minimal downtime finds plenty of promises and few honest numbers.

This article orders the three strategies for moving from SQL Server to PostgreSQL into tiers, from the longest standstill to the shortest. Each tier adds technology that has to work on the night of the cutover. On top comes the cutover runbook, the sequence of the switchover itself, because it is the same for all three.

The essentials up front:

• The downtime budget first: How long the database may be gone is decided by the business, not by the tool. A window in which nothing may be written is something different from one in which nothing works at all, and considerably easier to get.
• Three tiers: big bang in a maintenance window, snapshot with delta sync, or replication via CDC. Every tier of less standstill is paid for with more technology and more points of failure. As soon as the transfer no longer fits the maintenance window, the middle tier is the best compromise.
• The blind spot of delta sync: Anyone who pulls changes via a timestamp or rowversion column finds new and changed rows, but not deleted ones. Change Tracking in SQL Server closes this gap without touching the schema.
• The cutover runbook: write freeze, final delta run, verification, advance the sequences, release. Up to the release, the cutover can be aborted without losing anything, because the source stays unchanged. That is why verification and a real write test come before the release.

Prerequisite: SQL Server 2017+ as the source, PostgreSQL 14+ as the target. The target schema exists and a bulk load works, because both are covered by the sister articles on schema migration and data transfer. This article is about how changes are carried over during the move and about when and how to switch over. The SQL Server concepts rowversion, Change Tracking, and CDC are explained on first mention.

Contents

• Determining the Downtime Budget
• Tier 1: Big Bang in a Maintenance Window
• Tier 2: Snapshot and Delta Sync
• Tier 3: CDC and Replication
• The Cutover Runbook
• SQL Server to PostgreSQL with Minimal Downtime: the Decision Matrix
• FAQ
• Related Articles

Determining the Downtime Budget

Standstill costs money. The database itself does not notice, but a web shop loses orders, a warehouse stands still, and a report delivers no numbers on Monday morning. The question about the window therefore goes to the business and to operations, and it needs a number, not a mood: how many hours of standstill are acceptable on a Sunday at three in the morning, how many minutes on a Tuesday mid-morning?

It pays to keep two windows apart that usually blur into one in conversation:

• Full stop: Nobody can read, nobody can write. The application shows a maintenance page.
• Write freeze: Reading continues, only changes are blocked. Customers see their orders, reports run, but nothing new is created.

What makes the move complicated is not the reads but the changes that arrive in the source during the transfer and are missing in the target. A write freeze removes exactly this problem without making the database disappear. Business units that balk at “the database is gone for four hours” often accept “read-only for four hours”. Such a read-only window frequently saves an entire tier of technology.

It does come with one condition. SQL Server can enforce a write freeze with ALTER DATABASE … SET READ_ONLY WITH ROLLBACK IMMEDIATE, where the clause, according to the documentation, rolls back open transactions and disconnects their sessions so that the switch gets the exclusive access it needs. The application, however, then receives an error on every write attempt, and whether that turns into a friendly message or a crash is decided by its code. A usable read-only window is therefore a decision of the application, which switches off its write paths through a feature flag, a maintenance mode, or a changed connection configuration. The database only provides the safety net behind it.

With the budget in hand, the strategy can be chosen. The decision matrix at the end refines this choice by data volume and change rate. The three tiers at a glance:

What the table does not say, but what holds in every row: every component that is added can fail on the night of the cutover.

Tier 1: Big Bang in a Maintenance Window

In a big bang inside a maintenance window, the source is stopped, the complete data set is transferred and checked, and the target is released. The standstill lasts as long as transfer and verification together, typically hours. In return, the procedure is the simplest of all, and an abort costs nothing: up to the release, the source stays unchanged.

This tier does not deserve its bad reputation. For a database of a few dozen gigabytes, an internal system without night operation, or an application with a fixed maintenance slot, it is the right choice, because every further tier adds technology that nobody needs here. The transfer itself runs with the tools from the sister article on data transfer: bcp and COPY, pgloader, or an ETL route.

What this tier demands is a measurement, because the maintenance window has to match the duration of the transfer. A restore of the latest backup onto a test instance is enough, with the complete route run against it: export, load, index build, constraint checks, ANALYZE, and verification. What counts is the time from start to finish, because the index build on large tables in particular often takes longer than the transfer of the data itself. Whoever adds a margin of half the measured time, places the result into the window, and still gets the window stays with Tier 1. Whoever does not get it has thereby justified the move to Tier 2.

The procedure inside the window is the cutover runbook further down, with one difference: the “final delta run” here is the entire transfer.

Tier 2: Snapshot and Delta Sync

In a snapshot with delta sync, the data set is transferred ahead of time while operations continue. Afterwards, a repeatable run pulls only the rows that have changed since the previous run. The standstill shrinks to the last of these delta runs, the verification, and the switchover, typically minutes. In return, you have to make sure yourself that changed rows can be recognized at all, for example through a column that receives a new value on every change. Deleted rows cannot be found this way, which needs a step of its own.

The procedure has four steps. First, a high-water mark is saved, the point from which the first delta run will read. Then the snapshot runs, the bulk load of the complete data set, while the application keeps working. Delta runs follow in any number, each one reads the changes since the last mark and writes them to the target. Finally comes the cutover with one last, short delta run after the write freeze.

The snapshot does not have to be a consistent image of the database at a single point in time, and that is exactly why the mark is saved before it. A bulk load reads every table at a different moment, and within a table rows change while the export is running. Every row that the snapshot saw in an outdated state or not at all carries a value at or above the saved mark afterwards and comes again with the first delta run. All that counts is the state after the final run, and that one is created after the write freeze. For this, the export only has to read every row in a committed state and skip no unchanged row, and that is exactly what an export with NOLOCK does not guarantee. Deleted rows are excluded from this self-healing, more on that shortly.

The High-Water Mark

The mark needs a column that receives a new, increasing value on every change. SQL Server provides the rowversion data type for this: an eight-byte number that is assigned anew from a database-wide counter on every INSERT and UPDATE, without any involvement of the application. An updated_at column maintained by the application or a trigger works too, but it is more fragile, because a forgotten code path or a skewed clock makes rows invisible.

There is a trap in reading the mark. The obvious value @@DBTS returns the most recently assigned rowversion value. A transaction that is still open, however, may already have assigned a smaller value that only becomes visible after its commit. Anyone who saves @@DBTS as the mark never reads that row in any delta run. The function MIN_ACTIVE_ROWVERSION() instead returns the smallest value still held by an open transaction, and if none is open, the next value that will be assigned. For the delta run, that is the upper bound below which only completed changes remain. Microsoft names data synchronization as the use case of this function in the documentation.

The delta run first pulls the new upper bound and then reads the window between the old and the new mark:

  1: DECLARE @last binary(8) = (SELECT next_rowver FROM dbo.sync_watermark WHERE table_name = N'dbo.customer');
  2: DECLARE @new  binary(8) = MIN_ACTIVE_ROWVERSION();
  3: 
  4: SELECT
  5:     customer_id
  6:    ,email
  7:    ,credit_limit
  8:    ,row_ver
  9: FROM
 10:    dbo.customer
 11: WHERE
 12:        row_ver >= @last
 13:    AND row_ver <  @new;
 14: -- a@example.com (UPDATE) and c@example.com (INSERT); b@example.com is missing

  1: DECLARE @last binary(8) = (SELECT next_rowver FROM dbo.sync_watermark WHERE table_name = N'dbo.customer');
  2: DECLARE @new  binary(8) = MIN_ACTIVE_ROWVERSION();
  3: 
  4: SELECT
  5:     customer_id
  6:    ,email
  7:    ,credit_limit
  8:    ,row_ver
  9: FROM
 10:    dbo.customer
 11: WHERE
 12:        row_ver >= @last
 13:    AND row_ver <  @new;
 14: -- a@example.com (UPDATE) and c@example.com (INSERT); b@example.com is missing

Line by line: line 1 fetches the saved mark from a small state table, line 2 pulls the new upper bound, and lines 12 and 13 bound the window. The comparison operators are no accident. The mark is the first value not yet processed, which is why the column is called next_rowver: without an open transaction, MIN_ACTIVE_ROWVERSION() returns @@DBTS + 1, exactly the value the next change will receive. The window therefore includes the lower mark and excludes the upper one, and every value falls into exactly one run. With a > at the lower bound, the row that received exactly the mark value would be lost, in the example the UPDATE on a@example.com. The new mark is stored only after the target has taken over the rows. If the run aborts before that, the next one reads the same window again.

To make repetition harmless, the target writes by upsert, in PostgreSQL INSERT … ON CONFLICT (customer_id) DO UPDATE: a row that the snapshot already contained and that the delta delivers again is simply overwritten. For the same reason, every table in the delta sync needs a primary key or at least a unique key, because without one there is no target for the upsert.

The Blind Spot of Delta Sync: Deleted Rows

A deleted row leaves nothing behind in the source that could carry a mark. That is why b@example.com is missing in the code block above. In the target, the row lives on, and after the switchover a customer reappears whom somebody deleted two weeks ago. This gap is part of the method and needs a decision of its own.

Four ways close it:

• Change Tracking in SQL Server records the operation for every changed row, including the delete. It is the cleanest way and is shown in the next section.
• Soft delete: The application does not delete but sets a flag such as is_deleted. For the mark, that is an ordinary UPDATE. This presupposes that the application is built that way, and it is not a rebuild for a migration.
• Tombstone table: A DELETE trigger writes the key of every deleted row into a log table that the delta run reads along. This works everywhere but costs one trigger per table.
• Key reconciliation before the switchover: For small tables it is enough to check all keys in the target against the source after the final delta run and delete the rows that are missing there. At millions of rows, that takes too long for a window of minutes.

Change Tracking: Seeing Deletes Without Touching the Schema

Change Tracking is a SQL Server feature that records per table which rows have changed since a given version and whether it was an INSERT, an UPDATE, or a DELETE. It stores only key, operation, and version number, no values, and that is enough, because the delta run reads the current values from the table anyway. According to Microsoft’s edition overview, it is available in all editions, including Express, needs no additional column, and is switched on once at database level and once per table.

The delta run then queries the CHANGETABLE function instead of a column:

  1: DECLARE @last bigint = @mark;   -- from the persisted sync state
  2: 
  3: SET TRANSACTION ISOLATION LEVEL SNAPSHOT;   -- needs ALLOW_SNAPSHOT_ISOLATION ON
  4: SET XACT_ABORT ON;                          -- so the THROW below rolls back
  5: BEGIN TRAN;
  6: 
  7: IF @last < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.customer'))
  8:    THROW 50001, N'Change tracking retention exceeded, repeat the snapshot.', 1;
  9: 
 10: DECLARE @new bigint = CHANGE_TRACKING_CURRENT_VERSION();
 11: 
 12: SELECT
 13:     T01.customer_id
 14:    ,T01.sys_change_operation          -- I = Insert, U = Update, D = Delete
 15:    ,T01.sys_change_version
 16:    ,T02.email
 17:    ,T02.credit_limit
 18: FROM
 19:    CHANGETABLE(CHANGES dbo.customer, @last) T01
 20:    LEFT JOIN dbo.customer T02
 21:    ON
 22:      T02.customer_id = T01.customer_id
 23: WHERE
 24:    T01.sys_change_version <= @new
 25: ORDER BY
 26:    T01.customer_id;
 27: -- 1 | U | a@example.com | 150.00
 28: -- 2 | D | NULL          | NULL
 29: -- 3 | I | c@example.com | 300.00
 30: 
 31: COMMIT;

  1: DECLARE @last bigint = @mark;   -- from the persisted sync state
  2: 
  3: SET TRANSACTION ISOLATION LEVEL SNAPSHOT;   -- needs ALLOW_SNAPSHOT_ISOLATION ON
  4: SET XACT_ABORT ON;                          -- so the THROW below rolls back
  5: BEGIN TRAN;
  6: 
  7: IF @last < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.customer'))
  8:    THROW 50001, N'Change tracking retention exceeded, repeat the snapshot.', 1;
  9: 
 10: DECLARE @new bigint = CHANGE_TRACKING_CURRENT_VERSION();
 11: 
 12: SELECT
 13:     T01.customer_id
 14:    ,T01.sys_change_operation          -- I = Insert, U = Update, D = Delete
 15:    ,T01.sys_change_version
 16:    ,T02.email
 17:    ,T02.credit_limit
 18: FROM
 19:    CHANGETABLE(CHANGES dbo.customer, @last) T01
 20:    LEFT JOIN dbo.customer T02
 21:    ON
 22:      T02.customer_id = T01.customer_id
 23: WHERE
 24:    T01.sys_change_version <= @new
 25: ORDER BY
 26:    T01.customer_id;
 27: -- 1 | U | a@example.com | 150.00
 28: -- 2 | D | NULL          | NULL
 29: -- 3 | I | c@example.com | 300.00
 30: 
 31: COMMIT;

Line by line: line 19 returns every key that has changed since version @last, together with the operation. The LEFT JOIN in line 20 adds the current values, and for a deleted row the right-hand side stays empty, as line 28 shows. The target then simply deletes that key. Lines 7 and 8 guard against the only real trap of the method: Change Tracking keeps the versions only for a configured duration, the retention. If the saved mark lies below the oldest version still known, because the runs paused for too long, only a new snapshot helps. The retention therefore has to cover the longest conceivable gap between two runs, with a margin.

Lines 3, 5, and 31 implement what Microsoft recommends for Change Tracking: the database gets ALLOW_SNAPSHOT_ISOLATION ON once, and the delta run reads retention check, version, changes, and current values inside one snapshot transaction, so that everything belongs to the same state. For the values alone, a deviation would be tolerable, because a row that changes once more between pulling @new and reading its values receives its version number only at commit, thus lies above @new, and comes again in the next run. Two other reasons matter more in practice. The cleanup can tidy up between the retention check and the reading of the changes. Inside the transaction, that remains invisible, and the result stays valid. And under the default isolation level READ COMMITTED, the query waits for every open transaction that is currently changing a tracked row. Measured on SQL Server 2022, it ran into the lock timeout next to a single open transaction, while under snapshot isolation it immediately returned the last committed state. Line 4 makes sure that the THROW in line 8 does not leave the transaction open.

What Tier 2 Requires and Where It Ends

Three rules apply for the whole period between snapshot and cutover:

• Schema freeze: The source schema no longer changes between snapshot and cutover. A column that is added in this period is known neither to the target nor to the delta run. If a schema change is still pending, it is applied before the snapshot, and the snapshot starts only afterwards.
• Constraints stay active in the delta. During the bulk load it is common to disable foreign keys and triggers in the target so that load order does not matter. The delta run, by contrast, works with active constraints, otherwise violations pile up in the target that only surface after the switchover. Every run therefore writes inside one transaction, parent tables before child tables or with deferred foreign keys (DEFERRABLE INITIALLY DEFERRED).
• The delta runs have to catch up. If a run takes longer than the source needs for the same amount of new changes, the lag never shrinks. That is the point where Tier 2 ends and Tier 3 begins.

Tables without a key and tables with very many changes per minute do not fit this method. They can be moved in the maintenance window following Tier 1, while the rest runs via delta sync. A migration does not have to choose the same tier for all tables.

Tier 3: CDC and Replication

With replication via CDC (Change Data Capture), SQL Server writes every committed change into change tables, and a tool carries them from there to PostgreSQL continuously. Both systems run in parallel until the lag is zero, and the standstill shrinks to the switchover itself, to seconds up to a few minutes. In return, you operate a third component with its own risk of failure, and a residual window remains here too.

What Replication Between SQL Server and PostgreSQL Really Means

Anyone searching for replication methods for this pair has to budget for a disappointment. PostgreSQL’s logical replication with PUBLICATION and SUBSCRIPTION connects PostgreSQL to PostgreSQL. SQL Server’s transactional replication delivers to SQL Server subscribers and, according to the documentation, to Oracle and IBM Db2, with Microsoft listing these foreign subscribers as deprecated and recommending CDC and SSIS for data transport instead. PostgreSQL is not on that list. There is no built-in replication from SQL Server directly to PostgreSQL.

What does exist are tools that build on CDC. Change Data Capture is SQL Server’s second change-capture feature next to Change Tracking, and the difference between the two decides which tier can be built:

In Intervals Instead of a Stream: SSIS as a Delta Run

Anyone who has CDC switched on does not have to stream right away. An SSIS package that runs every few minutes reads the changes accumulated since the previous run from the CDC tables, splits them into inserts, updates, and deletes, and writes them to PostgreSQL. Technically, that is a delta sync following Tier 2, only with CDC instead of rowversion or Change Tracking as the change detection. For SQL Server teams it is often the shortest route, because tool, scheduling via the Agent, and operational experience are already there, even without the CDC components of SSIS itself, more on that in a moment. The lag amounts to the interval plus the package’s runtime. For the cutover that is enough, because after the write freeze only one final run is due anyway.

Three things belong to it. First, the building blocks: SSIS has shipped its own CDC components since 2012 (CDC Control Task, CDC Source, CDC Splitter), but Microsoft marked them as deprecated in February 2024 and ended support in December 2025, and as of October 2026 the documentation carries the notice. New packages therefore do better to build directly on the CDC functions: sys.fn_cdc_get_max_lsn() returns the upper mark, cdc.fn_cdc_get_net_changes_<capture_instance> the changes between two marks, and __$operation tells whether a row was deleted, inserted, or updated. The net changes function condenses several changes to the same row into one and for that requires a primary key or unique index, which the upsert in the target needs anyway. The mark lands in a state table like sync_watermark above, and with Change Tracking instead of CDC, the CHANGETABLE query becomes the package’s source. Second, the target: SSIS has no PostgreSQL destination of its own, the route goes through ODBC. Sending updates and deletes row by row through an OLE DB Command is slow. Faster is a staging table in PostgreSQL in which the batch of one run is applied set-based, with INSERT … ON CONFLICT DO UPDATE for inserts and updates and DELETE … USING for deletes. Third, the limit: this route too needs CDC, hence the Standard or Enterprise edition and the Agent, and it remains an interval. Anyone who wants to push the lag down to seconds ends up with streaming.

Streaming Tools

The best-known free streaming tool is Debezium, at version 3.7 as of October 2026. According to its documentation, its SQL Server connector requires CDC to be enabled on the database and on every table, SQL Server to run at least as 2016 SP1 in the Standard or Enterprise edition, and the SQL Server Agent to be working, because only its job fills the change tables. On start, the connector reads a snapshot of all tables and then continuously streams the changes from the last processed log position into a message stream, usually Kafka, from which a second connector, Debezium’s JDBC sink, writes them to PostgreSQL. Anyone who does not want to run Kafka takes Debezium Server, which does without a broker and brings its own JDBC sink. Still, between source and target sits an infrastructure of its own with its own configuration, its own monitoring, and its own type mapping, and the type mapping is no more trivial than in a bulk load.

The big cloud providers offer the same as a service, for example AWS Database Migration Service: that saves the setup, not the configuration, and it costs by the hour for as long as the replication runs. On an Express edition without CDC and Agent, what remains is SymmetricDS, a free tool that collects changes via triggers, and the source has to bear these triggers on every write during live operation.

When the Effort Pays Off

Tier 3 pays off when three things come together: a budget of minutes or seconds that cannot be negotiated, a data set for which a delta run following Tier 2 no longer catches up, and a team that can operate the additional infrastructure. One side effect makes the tier additionally attractive for large systems: because both databases run in parallel for days, the application can be tested against PostgreSQL while SQL Server stays in production.

If one of these three conditions is missing, Tier 3 brings operational effort with no matching gain in downtime. The schema freeze applies here too, and breaking it costs more than in Tier 2: a DDL change in the source does not stop the capture, but the change table keeps its old column list, and a new column is missing from every event until a second capture instance has been created and the connector has switched over to it. Debezium describes one procedure with and one without stopping the connector for this. For a plannable cutover, the freeze remains the simpler way. And the residual switchover window remains even with perfect replication: the connections to the source have to drain, the last lag has to reach zero, the sequences have to be advanced, and only then does the application point to the new target. That holds as long as only one of the two databases is written to. Dual writes from the application bypass the window, but they are a rebuild of the application with consistency questions of their own and not a topic of this article. Anyone who promises “zero downtime” here means “so short that nobody notices”. That is a legitimate goal, but a different statement.

The Cutover Runbook

The cutover is the moment in which the application is switched from the source to the target. Its sequence is the same for all three tiers, only the duration of the individual stations differs. It belongs in a written runbook, with times, owners, and for every station a check that has to pass before the next one begins.

1. Check the preconditions. The trial run is green, the schema freeze has been in effect since a known date, the lag of the delta runs or the replication is small, and the abort plan is written. If one of these is missing, the date is moved, not the check.
2. Write freeze. The application goes into maintenance or read-only mode. The database backs this up, in SQL Server with SET READ_ONLY. Check: there are no more open write transactions.
3. Final delta run. In Tier 1 that is the entire transfer, in Tier 2 the final run after the write freeze, in Tier 3 waiting until the replication reports no more lag.
4. Verification. Row reconciliation for every table against the source’s count, checksums for the critical tables, a search for orphaned foreign keys. The full form with checksums and samples is in the sister article on verifying the migration.
5. Advance the sequences. Every identity and every serial column in PostgreSQL draws its values from a sequence, and after a load with explicit keys that sequence still sits at its start value. It has to be raised to the highest assigned value, and that after the final delta run, not after the snapshot. Otherwise the sequence sits at the snapshot’s maximum while the delta has long delivered higher keys, and the first INSERT after the switchover runs into a primary key violation.
6. Switch over and write test. Connection configuration, DNS, or feature flag point to PostgreSQL. Check: a write through the application path, still in maintenance mode and not a SELECT 1. Because such a write can trigger mails, messages, or follow-up bookings, it runs with a test record created for exactly this purpose whose side effects are known. If the test fails, the configuration points back to SQL Server, and the source is unchanged.
7. Release. Maintenance or read-only mode is lifted. From now on, PostgreSQL is the only production database, and the time goes into the log.
8. Observation window. The old database stays in place read-only, for a few days to weeks, as a reference state for questions. During this time, error rates, response times, batch jobs, and the sequences are watched. Only then is the source decommissioned.

For station 5 there is a short check on the target side that raises the sequences of all identity and serial columns in the schema to their maximum:

  1: DO $$
  2: DECLARE
  3:    l_col   record;
  4:    l_seq   text;
  5:    l_max   bigint;
  6: BEGIN
  7:    FOR l_col IN
  8:       SELECT
  9:           table_schema
 10:          ,table_name
 11:          ,column_name
 12:          ,column_default
 13:       FROM
 14:          information_schema.columns
 15:       WHERE
 16:              table_schema = 'public'
 17:          AND (   is_identity    = 'YES'
 18:               OR column_default LIKE 'nextval(%')
 19:    LOOP
 20:       l_seq := COALESCE(
 21:                   pg_get_serial_sequence(format('%I.%I', l_col.table_schema, l_col.table_name), l_col.column_name)
 22:                  ,substring(l_col.column_default FROM '^nextval\(''(.+)''::regclass\)$')
 23:                );
 24: 
 25:       IF l_seq IS NULL THEN
 26:          RAISE WARNING '%.%: no sequence found, check by hand', l_col.table_name, l_col.column_name;
 27:          CONTINUE;
 28:       END IF;
 29: 
 30:       EXECUTE format('SELECT max(%I) FROM %I.%I', l_col.column_name, l_col.table_schema, l_col.table_name)
 31:          INTO l_max;
 32: 
 33:       IF l_max IS NULL THEN
 34:          EXECUTE format('ALTER SEQUENCE %s RESTART', l_seq);
 35:       ELSE
 36:          PERFORM setval(l_seq, l_max);
 37:       END IF;
 38:    END LOOP;
 39: END;
 40: $$;

  1: DO $$
  2: DECLARE
  3:    l_col   record;
  4:    l_seq   text;
  5:    l_max   bigint;
  6: BEGIN
  7:    FOR l_col IN
  8:       SELECT
  9:           table_schema
 10:          ,table_name
 11:          ,column_name
 12:          ,column_default
 13:       FROM
 14:          information_schema.columns
 15:       WHERE
 16:              table_schema = 'public'
 17:          AND (   is_identity    = 'YES'
 18:               OR column_default LIKE 'nextval(%')
 19:    LOOP
 20:       l_seq := COALESCE(
 21:                   pg_get_serial_sequence(format('%I.%I', l_col.table_schema, l_col.table_name), l_col.column_name)
 22:                  ,substring(l_col.column_default FROM '^nextval\(''(.+)''::regclass\)$')
 23:                );
 24: 
 25:       IF l_seq IS NULL THEN
 26:          RAISE WARNING '%.%: no sequence found, check by hand', l_col.table_name, l_col.column_name;
 27:          CONTINUE;
 28:       END IF;
 29: 
 30:       EXECUTE format('SELECT max(%I) FROM %I.%I', l_col.column_name, l_col.table_schema, l_col.table_name)
 31:          INTO l_max;
 32: 
 33:       IF l_max IS NULL THEN
 34:          EXECUTE format('ALTER SEQUENCE %s RESTART', l_seq);
 35:       ELSE
 36:          PERFORM setval(l_seq, l_max);
 37:       END IF;
 38:    END LOOP;
 39: END;
 40: $$;

Line by line: lines 8 to 18 search the schema for every column that is an identity column or draws its default from a sequence. Lines 20 to 23 determine the associated sequence, first via pg_get_serial_sequence, which knows identity and serial columns, otherwise from the column default, because the function does not find a hand-made sequence without OWNED BY. Without this fallback, setval would silently do nothing with a NULL, the block would run through without error, and the first INSERT after the switchover would still hit the key conflict. Lines 25 to 28 report what neither way recognizes. Line 30 reads the highest assigned value, lines 33 to 37 set the sequence to it. An empty table gets an ALTER SEQUENCE … RESTART without a value in line 34, which resets the sequence to its own start value. A setval(…, 1, false) would not do that: it ignores a START WITH 100 and fails on a MINVALUE above 1. Why the step is necessary at all is explained by the sister article on schema migration in its section on the sequence reset.

The Abort Plan

The rollback of this runbook is an abort before the release, and that is easy in every tier, because the source stays unchanged until then: the application points back to SQL Server, and nothing is lost except the maintenance window. That is why verification and the write test come before the release, and that is why the runbook fixes in advance, for every station, which finding leads to an abort. Whoever has written that down does not have to decide it at night.

After the release, a way back is no longer an abort, because from then on work happens only in the new database. Anyone who later wants to return to SQL Server plans a migration in the opposite direction, a project of its own that this article does not cover. The old database stays in place during the observation window as a reference state, not as a reserve.

SQL Server to PostgreSQL with Minimal Downtime: the Decision Matrix

Three quantities determine the tier: the downtime budget, the data volume in relation to the window, and the change rate, meaning how many rows change per hour and how often deletes occur.

The last row is the most important one. Anyone who negotiates a read-only window instead of a full stop with the business has solved the problem of ongoing changes before writing a single line of delta logic.

Downtime is not a property of a tool but a decision about the architecture of the move, and it is made before the first transfer. As soon as the transfer no longer fits the maintenance window, Tier 2 is the right compromise for migrating SQL Server to PostgreSQL: minutes instead of hours, with means SQL Server itself provides. What is realistically achievable is minimal downtime, not none, and the residual window at switchover belongs in the runbook and in the commitment to the business, not in a footnote.

FAQ

Related Articles

This article is part of a series on migrating SQL Server to PostgreSQL. The remaining parts:

• Overview: Data Migration: SQL Server to PostgreSQL — the Complete Guide
• Data types: Data Type Mapping SQL Server → PostgreSQL — What Converts Cleanly and What Breaks
• Schema: Schema Migration SQL Server → PostgreSQL — Identity, Constraints, Defaults, Sequences
• Data transfer: Transferring Data: bcp, COPY, pgloader, ETL — Which Method When
• Code porting: Porting T-SQL to PL/pgSQL — Migrating Procedures and Functions
• Verification: Verifying the Migration — Data Quality and Row Reconciliation After the Move