Table of Contents

1. Why Migrate from SQL Server to PostgreSQL?
2. Pre-Migration Checklist
3. Step 1: Export SQL Server Schema
4. Step 2: Translate Schema to PostgreSQL
5. Step 3: Migrate Your Data
6. Step 4: Validate and Test
7. Common Type Mapping Issues
8. Migration Pitfalls to Avoid
9. Frequently Asked Questions

Why Migrate from SQL Server to PostgreSQL?

Enterprise teams are increasingly moving from SQL Server to PostgreSQL for these reasons:

• Cost reduction — Eliminate SQL Server licensing costs ($7,000+ per core for Enterprise Edition). PostgreSQL is open-source and free.
• Cloud flexibility — PostgreSQL runs natively on AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, and self-managed VMs without vendor lock-in.
• Modern data types — Native JSONB, arrays, UUID, full-text search, geospatial (PostGIS), and vector (pgvector) support.
• Active open-source community — Faster security patches, broader tooling ecosystem, and no single vendor controlling the roadmap.
• CI/CD integration — PostgreSQL's transactional DDL makes schema migrations safer and more predictable in automated pipelines.

Pre-Migration Checklist

Step 1: Export SQL Server Schema

Use SQL Server Management Studio (SSMS) to generate schema scripts:

1. Right-click your database → Tasks → Generate Scripts
2. Select specific tables or script entire database
3. In "Set Scripting Options", click Advanced
4. Set "Types of data to script" to Schema only
5. Set "Script for Server Version" to your target (or leave as current)

Alternatively, use sqlcmd or mssql-scripter from the command line:

mssql-scripter -S localhost -d MyDatabase -U sa --schema-and-data false \
  > sqlserver_schema.sql

Review the exported file for SQL Server-specific syntax:

• [Bracketed] identifiers — PostgreSQL uses double quotes
• IDENTITY(1,1) — Will become SERIAL or GENERATED ALWAYS AS IDENTITY
• NVARCHAR / NCHAR — PostgreSQL uses VARCHAR with UTF-8
• DATETIME2 / SMALLDATETIME — PostgreSQL uses TIMESTAMP
• UNIQUEIDENTIFIER — PostgreSQL uses UUID
• CLUSTERED / NONCLUSTERED indexes — PostgreSQL uses different index types
• WITH (PAD_INDEX = ON ...) — Storage clauses that PostgreSQL ignores

Step 2: Translate Schema to PostgreSQL

SQL Server and PostgreSQL have fundamentally different type systems, identifier rules, and default behaviors.

Option A: Automated Translation (Fastest)

Use the SQL Dialect Translator to convert your SQL Server schema export to PostgreSQL syntax. It handles type mapping, identity column conversion, and identifier quoting.

Option B: SchemaLens Diff (Recommended for Schema Redesigns)

If you're modernizing the schema as part of migration, paste your SQL Server schema and target PostgreSQL schema into SchemaLens. It shows structural differences and generates migration SQL.

Option C: Manual Translation

For small schemas, manually rewrite CREATE TABLE statements using the type mapping table below.

Step 3: Migrate Your Data

After translating the schema and creating tables in PostgreSQL, move the data.

Option 1: Azure Database Migration Service (DMS) — Azure SQL Only

If migrating from Azure SQL Database or Azure SQL Managed Instance, use Azure DMS. It handles schema conversion, data movement, and cutover with minimal downtime.

Option 2: pg_dump / pg_restore with Foreign Data Wrapper

For on-premise SQL Server, use the tds_fdw foreign data wrapper to connect PostgreSQL directly to SQL Server:

-- In PostgreSQL
CREATE EXTENSION tds_fdw;

CREATE SERVER sqlserver_server
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername 'sqlserver_host', port '1433', database 'MyDB');

CREATE USER MAPPING FOR postgres
  SERVER sqlserver_server
  OPTIONS (username 'sa', password 'password');

-- Import foreign schema
IMPORT FOREIGN SCHEMA dbo
  FROM SERVER sqlserver_server
  INTO public;

Option 3: Custom ETL with Python

For complex transformations, use Python with pyodbc and SQLAlchemy:

import pandas as pd
from sqlalchemy import create_engine

sqlserver = create_engine('mssql+pyodbc://user:pass@dsn')
postgres = create_engine('postgresql://user:pass@host/db')

# Read from SQL Server
df = pd.read_sql('SELECT * FROM dbo.Users', sqlserver)

# Transform if needed (e.g., convert UNIQUEIDENTIFIER to UUID string)
df['id'] = df['id'].astype(str)

# Write to PostgreSQL
df.to_sql('users', postgres, if_exists='append', index=False)

Option 4: SSIS → PostgreSQL ODBC

Use SQL Server Integration Services (SSIS) with a PostgreSQL ODBC driver. This is common in enterprise environments that already have SSIS packages.

Step 4: Validate and Test

Enterprise migrations require rigorous validation:

Schema Validation

• Compare table and column counts
• Verify all indexes were recreated (note: clustered indexes become regular indexes in PostgreSQL)
• Check foreign key constraints
• Validate default values and sequences
• Ensure triggers and stored procedures were rewritten in PL/pgSQL

Data Validation

• Row count comparison per table
• Checksum sampling on critical tables
• NULL constraint verification
• Date range validation (SQL Server DATETIME min: 1753-01-01, PostgreSQL TIMESTAMP min: 4713 BC)

Application Testing

• Run full regression test suite against PostgreSQL
• Performance benchmark key queries (execution plans will differ)
• Verify connection pooling behavior (PgBouncer recommended)

Use SchemaLens for Diff Validation

Export both schemas and diff them:

-- SQL Server (via SSMS or sqlcmd)
-- Generate Scripts → Schema Only

-- PostgreSQL
pg_dump --schema-only -U postgres mydb > postgres_schema.sql

Then paste both into SchemaLens to catch any remaining differences.

Common Type Mapping Issues

SQL Server Type	PostgreSQL Type	Notes
INT IDENTITY(1,1)	SERIAL	Use BIGSERIAL for BIGINT identity
BIGINT IDENTITY(1,1)	BIGSERIAL	Direct equivalent
BIT	BOOLEAN	SQL Server BIT can be 0, 1, or NULL
TINYINT	SMALLINT	PostgreSQL has no unsigned types
SMALLINT	SMALLINT	No change needed
INT	INTEGER	No change needed
BIGINT	BIGINT	No change needed
FLOAT	DOUBLE PRECISION	Or REAL for 4-byte float
REAL	REAL	No change needed
DECIMAL(p,s)	NUMERIC(p,s)	No change needed
MONEY	NUMERIC(19,4)	PostgreSQL has MONEY but it's discouraged
SMALLMONEY	NUMERIC(10,4)	No direct equivalent
VARCHAR(n)	VARCHAR(n)	No change needed
NVARCHAR(n)	VARCHAR(n)	PostgreSQL uses UTF-8 natively
CHAR(n)	CHAR(n)	No change needed
NCHAR(n)	CHAR(n)	PostgreSQL uses UTF-8 natively
TEXT	TEXT	No change needed
NTEXT	TEXT	Deprecated in SQL Server anyway
VARCHAR(MAX)	TEXT	PostgreSQL TEXT is unlimited
NVARCHAR(MAX)	TEXT	PostgreSQL TEXT is unlimited
DATETIME	TIMESTAMP	Or TIMESTAMPTZ with timezone
DATETIME2	TIMESTAMP	Higher precision than DATETIME
SMALLDATETIME	TIMESTAMP	Less precision, same mapping
DATETIMEOFFSET	TIMESTAMPTZ	Timezone-aware timestamp
DATE	DATE	No change needed
TIME	TIME	No change needed
UNIQUEIDENTIFIER	UUID	Use gen_random_uuid() as default
BINARY(n)	BYTEA	Fixed-length binary
VARBINARY(n)	BYTEA	Variable-length binary
IMAGE	BYTEA	Deprecated in SQL Server
VARBINARY(MAX)	BYTEA	For large binary objects
XML	XML	PostgreSQL has native XML type
SQL_VARIANT	JSONB or TEXT	No direct equivalent; redesign recommended

Migration Pitfalls to Avoid

• Forgetting about schemas vs databases — SQL Server databases are roughly equivalent to PostgreSQL schemas within a single database cluster. You may need to consolidate multiple SQL Server databases into one PostgreSQL database with multiple schemas.
• Ignoring collation — Case-insensitive collation in SQL Server (`CI`) is not the default in PostgreSQL. You may need citext extension or explicit lower()/upper() in queries.
• Assuming TOP = LIMIT — SELECT TOP 10 becomes SELECT ... LIMIT 10. The syntax is different and TOP with ORDER BY behaves slightly differently.
• NULL handling in concatenation — SQL Server: NULL + 'text' = NULL. PostgreSQL: NULL || 'text' = NULL. Same behavior, but CONCAT() in SQL Server treats NULL as empty string.
• Transaction behavior — SQL Server has implicit transactions in some contexts. PostgreSQL requires explicit BEGIN/COMMIT unless auto-commit is on.
• Cross-database queries — SELECT * FROM OtherDB.dbo.Table doesn't work in PostgreSQL. Use dblink or postgres_fdw for cross-database access.
• Temp tables — SQL Server #temp tables are session-scoped. PostgreSQL temporary tables (CREATE TEMP TABLE) behave similarly but have different visibility rules.
• Error handling — T-SQL TRY...CATCH becomes PL/pgSQL BEGIN...EXCEPTION. Syntax and behavior differ.

Frequently Asked Questions

How long does a SQL Server to PostgreSQL migration take?

For a 50-100 table schema with under 100GB of data, plan for 1-2 weeks including testing. Large enterprise schemas (1000+ tables) with TB-scale data can take 1-3 months. The schema translation is typically 20% of the effort; data migration, stored procedure rewriting, and application testing consume the rest.

Can we migrate without downtime?

Yes, using logical replication or change data capture (CDC). Tools like Debezium can stream SQL Server changes to PostgreSQL in real-time. Azure DMS also supports online migration for Azure SQL Database.

What about SQL Server Agent jobs?

SQL Server Agent jobs must be rewritten. In PostgreSQL, use pg_cron extension for scheduled tasks, or external schedulers like Linux cron, Airflow, or Dagster.

How do we handle SQL Server Reporting Services (SSRS)?

SSRS doesn't exist in PostgreSQL. Options include: Metabase, Apache Superset, Grafana, or commercial tools like Tableau and Power BI (which connect to PostgreSQL natively).

Is Azure Database for PostgreSQL a good target?

Yes, if you're already in Azure. It's fully managed PostgreSQL with built-in backups, monitoring, and scaling. However, self-managed PostgreSQL on Azure VMs or AKS gives you more control over extensions and configuration.

Do we need to rewrite all stored procedures?

Most likely yes. T-SQL and PL/pgSQL are different languages. Simple procedures may translate automatically, but complex logic with cursors, dynamic SQL, or CLR integration will need manual rewriting. Consider moving business logic to the application layer where possible.