Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 5 · Lesson 29 · 8 min read

SQL Server to PostgreSQL Migration: Full Project

On this page
  1. The RetailOrdersDB migration brief
  2. Part 1: Schema
  3. Part 2: The procedure
  4. Part 3: The multi-row trigger bug
  5. Part 4: Data, and proof the logic works
  6. Part 5: Indexes, backup and sign-off
  7. Stretch goals
  8. Check yourself

The RetailOrdersDB migration brief

A small retailer runs its order system on SQL Server in a database called RetailOrdersDB. Migrate it to PostgreSQL 18 without changing its behavior, and prove with evidence that the migration is correct. Treat it as a work assignment: the sign-off checklist at the end matters as much as the scripts. Budget a few hours, and try each part yourself before reading the walkthrough.

The source:

SQL
-- T-SQL
CREATE TABLE dbo.customers (
    customer_id  INT IDENTITY(1,1) PRIMARY KEY,
    first_name   NVARCHAR(50) NOT NULL,
    last_name    NVARCHAR(50) NOT NULL,
    email        NVARCHAR(150) NOT NULL UNIQUE,
    created_at   DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);
CREATE TABLE dbo.products (
    product_id         INT IDENTITY(1,1) PRIMARY KEY,
    product_name       NVARCHAR(100) NOT NULL,
    sku                VARCHAR(30) NOT NULL UNIQUE,
    unit_price         DECIMAL(10,2) NOT NULL,
    quantity_in_stock  INT NOT NULL DEFAULT 0,
    is_active          BIT NOT NULL DEFAULT 1
);
CREATE TABLE dbo.orders (
    order_id      INT IDENTITY(1,1) PRIMARY KEY,
    customer_id   INT NOT NULL REFERENCES dbo.customers(customer_id),
    order_date    DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    order_status  VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    total_amount  DECIMAL(12,2) NOT NULL DEFAULT 0
);
CREATE TABLE dbo.order_items (
    order_item_id  INT IDENTITY(1,1) PRIMARY KEY,
    order_id       INT NOT NULL REFERENCES dbo.orders(order_id),
    product_id     INT NOT NULL REFERENCES dbo.products(product_id),
    quantity       INT NOT NULL CHECK (quantity > 0),
    unit_price     DECIMAL(10,2) NOT NULL
);
GO
CREATE PROCEDURE dbo.usp_RecalculateOrderTotal @OrderId INT AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.orders
    SET total_amount = (SELECT ISNULL(SUM(quantity * unit_price), 0)
                        FROM dbo.order_items WHERE order_id = @OrderId)
    WHERE order_id = @OrderId;
END;
GO
CREATE TRIGGER dbo.trg_order_items_after_insert ON dbo.order_items AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p SET p.quantity_in_stock = p.quantity_in_stock - i.quantity
    FROM dbo.products p JOIN inserted i ON p.product_id = i.product_id;

    DECLARE @OrderId INT;
    SELECT @OrderId = order_id FROM inserted;
    EXEC dbo.usp_RecalculateOrderTotal @OrderId;
END;

Look closely at the trigger before you start. It has a bug that the migration will expose.

Part 1: Schema

Apply the type rules from Day 5 and the naming rules from Day 4. Put the tables in their own schema, retail, as Day 1 recommended for data that used to live in its own database:

SQL
CREATE SCHEMA retail;

CREATE TABLE retail.customers (
  customer_id  integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name   varchar(50) NOT NULL,
  last_name    varchar(50) NOT NULL,
  email        varchar(150) NOT NULL UNIQUE,
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE retail.products (
  product_id         integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_name       varchar(100) NOT NULL,
  sku                varchar(30) NOT NULL UNIQUE,
  unit_price         numeric(10,2) NOT NULL,
  quantity_in_stock  integer NOT NULL DEFAULT 0,
  is_active          boolean NOT NULL DEFAULT true
);
CREATE TABLE retail.orders (
  order_id      integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id   integer NOT NULL REFERENCES retail.customers,
  order_date    timestamptz NOT NULL DEFAULT now(),
  order_status  varchar(20) NOT NULL DEFAULT 'PENDING',
  total_amount  numeric(12,2) NOT NULL DEFAULT 0
);
CREATE TABLE retail.order_items (
  order_item_id  integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id       integer NOT NULL REFERENCES retail.orders,
  product_id     integer NOT NULL REFERENCES retail.products,
  quantity       integer NOT NULL CHECK (quantity > 0),
  unit_price     numeric(10,2) NOT NULL
);

CREATE INDEX ON retail.orders (customer_id);
CREATE INDEX ON retail.order_items (order_id);
CREATE INDEX ON retail.order_items (product_id);

BY DEFAULT identities accept the migrated ids as they are. The foreign key indexes are added by hand, since neither database creates them (Day 6). Compare both schemas in DataGrip with Ctrl+D (Day 3), and check the diagram view.

Part 2: The procedure

It doesn't return rows or commit partway, so it becomes a plain function (Day 9). One UPDATE needs no PL/pgSQL:

SQL
CREATE FUNCTION retail.recalculate_order_total(p_order_id integer)
RETURNS void
LANGUAGE sql
AS $$
  UPDATE retail.orders
  SET total_amount = (SELECT coalesce(sum(quantity * unit_price), 0)
                      FROM retail.order_items WHERE order_id = p_order_id)
  WHERE order_id = p_order_id;
$$;

Part 3: The multi-row trigger bug

Here's the bug. SELECT @OrderId = order_id FROM inserted keeps one order id. An INSERT that adds lines to two orders updates stock for both, but recalculates only one order's total. The other total is wrong.

A naive row-level port (Day 10) fixes the bug by accident, because it runs once per row. But it recalculates an order once per line, which is slow for big batches. The faithful and correct translation is a statement-level trigger with a transition table, which works on the whole set of inserted rows, like the original:

SQL
CREATE FUNCTION retail.order_items_after_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE retail.products p
  SET quantity_in_stock = p.quantity_in_stock - i.qty
  FROM (SELECT product_id, sum(quantity) AS qty FROM inserted GROUP BY product_id) i
  WHERE p.product_id = i.product_id;

  UPDATE retail.orders o
  SET total_amount = t.total
  FROM (SELECT oi.order_id, sum(oi.quantity * oi.unit_price) AS total
        FROM retail.order_items oi
        WHERE oi.order_id IN (SELECT order_id FROM inserted)
        GROUP BY oi.order_id) t
  WHERE o.order_id = t.order_id;

  RETURN NULL;
END;
$$;

CREATE TRIGGER order_items_after_insert
AFTER INSERT ON retail.order_items
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE FUNCTION retail.order_items_after_insert();

Stock is grouped per product, so a statement with two lines for the same product subtracts both. Totals are recalculated once for every order in the statement. Record this in your migration notes: the old system had a bug, the new one fixes it, and the application owner needs to know, because reports produced before cutover may contain wrong totals.

Part 4: Data, and proof the logic works

SQL
INSERT INTO retail.customers (first_name, last_name, email) VALUES
  ('Ana', 'Kovac', 'ana@example.com'), ('Marco', 'Nilsson', 'marco@example.com'),
  ('Priya', 'Raman', 'priya@example.com');
INSERT INTO retail.products (product_name, sku, unit_price, quantity_in_stock) VALUES
  ('Wireless Mouse', 'SKU-1001', 19.99, 200), ('Mechanical Keyboard', 'SKU-1002', 79.50, 120),
  ('27-inch Monitor', 'SKU-1003', 219.00, 60), ('USB-C Dock', 'SKU-1004', 89.00, 90);
INSERT INTO retail.orders (customer_id) VALUES (1), (2), (3);

-- one statement, lines for three different orders: the case the old trigger got wrong
INSERT INTO retail.order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 2, 19.99), (1, 2, 1, 79.50), (2, 3, 1, 219.00), (3, 4, 3, 89.00), (3, 1, 1, 19.99);

SELECT order_id, total_amount FROM retail.orders ORDER BY order_id;
-- 1 | 119.48    2 | 219.00    3 | 286.99
SELECT product_id, quantity_in_stock FROM retail.products ORDER BY product_id;
-- 1 | 197    2 | 119    3 | 59    4 | 87

All three totals are right, and the mouse's stock dropped by 3 across two orders. Under the old trigger, two of these totals would have stayed at 0.

In a real migration, data comes from pgloader or DMS (Day 13). Afterwards, move each identity sequence past the loaded ids with setval, and run ANALYZE (Day 17).

Part 5: Indexes, backup and sign-off

Check that the indexes are used, on realistic volume. With five rows, PostgreSQL correctly prefers a sequential scan. Load a few hundred thousand synthetic order lines with generate_series (as on Day 21), run EXPLAIN ANALYZE on "lines for one order", and keep the plan as evidence.

Prove the backup, including roles (Day 23):

Shell
pg_dumpall --globals-only -f globals.sql
pg_dump -d retail_orders -Fc -f retail_orders.dump
createdb retail_orders_restore_test
pg_restore -d retail_orders_restore_test retail_orders.dump

Then compare row counts in every table between the two databases.

Sign-off checklist. This is what a reviewer will ask to see:

CheckEvidence
Tables, types and constraints match the mappingSchema comparison in DataGrip, plus a reviewed DDL file
Row counts and key totals match the sourceA validation query run on both servers, with results saved
Procedure gives the same resultsTotals recalculated and compared for sample orders
Trigger behavior is correct for multi-row insertsThe test above, plus a written note about the source bug
Sequences continue after the loaded idsA test insert gets the next id
Foreign key indexes exist and are usedEXPLAIN ANALYZE on realistic volume
Backup restores, including rolesRestore log and row-count comparison
Case-sensitivity and NULL-ordering differences reviewedNotes from Days 7 and 17 applied to application queries

Stretch goals

  • Partition orders by month (Day 19). Create retail.orders_p partitioned by order_date, copy the rows across, and switch over. The primary key becomes (order_id, order_date), and foreign keys pointing at it have to include order_date too. Note that knock-on effect in your design notes.
  • Plan a read replica (Day 24). Describe in a paragraph how monthly sales reports would be routed to a hot standby, and what changes in the application's connection settings.

Check yourself

A row-level trigger recalculates an order once per inserted line, which gets slow for big batches. The statement-level version with a transition table does each order once per statement, matching the set-based original but without its bug.

The procedure never restored the global objects. Roles live at the cluster level and aren't included in pg_dump, so the dump's ownership and grants point at roles that don't exist. Add a pg_dumpall --globals-only step to the backup, and restore its output before pg_restore.

Fix it in the new system, write it down in the migration notes, and tell the application owner, because historical totals created by the bug may be wrong and may need correcting.

Evidence: matching counts and checksums per table, the converted procedures and triggers tested against real cases, application smoke tests, a successful backup and restore on the new platform, and agreed performance numbers for the key queries.

It turns unknowns into measured facts: how long the data load takes, which conversions fail, which sequences need resetting and how long validation runs. The real cutover then follows a timed, tested runbook instead of discovering problems during the downtime window.