SQL Server to PostgreSQL Migration: Full Project
On this page
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:
-- 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:
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:
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:
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
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 | 87All 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):
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.dumpThen compare row counts in every table between the two databases.
Sign-off checklist. This is what a reviewer will ask to see:
| Check | Evidence |
|---|---|
| Tables, types and constraints match the mapping | Schema comparison in DataGrip, plus a reviewed DDL file |
| Row counts and key totals match the source | A validation query run on both servers, with results saved |
| Procedure gives the same results | Totals recalculated and compared for sample orders |
| Trigger behavior is correct for multi-row inserts | The test above, plus a written note about the source bug |
| Sequences continue after the loaded ids | A test insert gets the next id |
| Foreign key indexes exist and are used | EXPLAIN ANALYZE on realistic volume |
| Backup restores, including roles | Restore log and row-count comparison |
| Case-sensitivity and NULL-ordering differences reviewed | Notes from Days 7 and 17 applied to application queries |
Stretch goals
- Partition
ordersby month (Day 19). Createretail.orders_ppartitioned byorder_date, copy the rows across, and switch over. The primary key becomes(order_id, order_date), and foreign keys pointing at it have to includeorder_datetoo. 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.