PostgreSQL → SQL Server / Azure SQL

📖 Overview & Scope

This SOP covers a full copy (schema + data) from a PostgreSQL 13+ source into a SQL Server 2019+ or Azure SQL Database target using the Endrias Bridge GUI. All tables in the selected schema are migrated; PostgreSQL-specific types (BOOLEAN, SERIAL, UUID, JSONB, ARRAY, PostGIS) are converted automatically, with warnings logged for any lossy mappings.

✅ Prerequisites — Source (PostgreSQL)

Run the following checks and grants on the source PostgreSQL server before starting the migration wizard:

-- Check WAL level (must be 'logical' for CDC mode; restart required to change)
SHOW wal_level;

-- Check replication slot capacity (must be >= 2 for CDC mode)
SHOW max_replication_slots;

-- Recommended: set in postgresql.conf
-- wal_keep_size = 51200    (MB)

-- Grant migration user access
GRANT CONNECT ON DATABASE EndriasBridge TO MigrationUser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO MigrationUser;

-- CDC mode only (requires REPLICATION role):
-- ALTER ROLE MigrationUser REPLICATION;

✅ Prerequisites — Target (SQL Server / Azure SQL)

Open Endrias Bridge and navigate to Migration Projects. Create a new project and fill in both connection blocks as shown:

Switch to the Assessment tab and click Run Assessment. Endrias Bridge will enumerate all tables and columns on the PostgreSQL source and report type-compatibility warnings before any data is moved.

Return to Migration Projects. Click Load Tables from Source. All 44 tables are selected by default. Deselect any tables you want to skip. Enable Auto-create target DB if EndriasBridgeTarget does not yet exist on the target server.

Click Start Migration. The migration log updates live as each table's schema is created and rows are bulk-copied. Review all WARNING lines before closing the panel.

1. Click Verify Data Integrity — compares source vs. target row counts for every migrated table and logs any mismatches.
2. Manually spot-check rows in tables flagged with WARNING (ARRAY, JSONB, geometry columns).
3. Click Export Report (enabled once migration finishes) to save an HTML summary of all tables created, rows copied, type conversions applied, and warnings generated.
4. Archive the report with the project change ticket for sign-off.

Complete the following tasks on the target SQL Server / Azure SQL after verifying row counts:

-- 1. Reset IDENTITY seeds to the current max migrated value
DBCC CHECKIDENT ('EndriasBridgeTarget.dbo.users', RESEED);
-- Repeat for every table with an IDENTITY column.

-- 2. JSONB columns are stored as NVARCHAR(MAX) — update app queries
-- PostgreSQL:  WHERE metadata->>'key' = 'value'
-- SQL Server:  WHERE JSON_VALUE(metadata, '$.key') = 'value'
--              or use OPENJSON() for multi-row extraction

-- 3. Array columns serialized to NVARCHAR(MAX) — notify application team
-- Application must parse the serialized text format.

-- 4. Rebuild fragmented indexes after bulk insert
ALTER INDEX ALL ON dbo.users REBUILD;
-- Repeat for each migrated table.

-- 5. Update statistics
UPDATE STATISTICS dbo.users;

⚠️ Known Issues

📊 Type Conversions Applied (PostgreSQL → SQL Server)