How to Migrate SQL Server to PostgreSQL

Migrating from MS SQL Server to PostgreSQL involves several steps, including schema conversion, data migration, and application modifications. This whitepaper contains step-by-step guide in the migration process.

Analyze Your Database

Before starting migration assess the following:

• Check schema complexity (tables, indexes, constraints, stored procedures).
• Identify data types that need conversion (e.g., DATETIME in SQL Server vs. TIMESTAMP in PostgreSQL).
• Examine triggers, views, and stored procedures (T-SQL vs. PL/pgSQL differences).
• SQL Server specific items (cross-database queries, SQL Server Agent Jobs, T-SQL queries)

Convert the Schema

Here are manual or semi-automated options of schema conversion:

• Use SSMA or pgLoader to generate a PostgreSQL-compatible schema.
• Manually adjust:
  • Data types (see Safe data types mapping below)
  • Primary & foreign keys, constraints, and indexes.
  • Sequences for identity columns (SERIAL or GENERATED ALWAYS AS IDENTITY).
  • Stored procedures and triggers (rewrite T-SQL to PL/pgSQL).
  • Safe data types mapping

Safe data types mapping

If you are using a dedicated commercial converter, it handles schema conversion with respect to safe types mapping as a part of database migration.

Example of schema conversion:

SQL Server:

CREATE TABLE Employees (

ID INT IDENTITY(1,1) PRIMARY KEY,

Name NVARCHAR(100),

HireDate DATETIME DEFAULT GETDATE(),

IsActive BIT DEFAULT 1

);

PostgreSQL:

CREATE TABLE Employees (

ID SERIAL PRIMARY KEY,

Name TEXT,

HireDate TIMESTAMP DEFAULT NOW(),

IsActive BOOLEAN DEFAULT TRUE

);

Migrate Data

pgLoader is the preferred tool for semi-automated migration as it handles bulk migration efficiently:

pgloader ms.sql connection_string postgresql://user:password@host/dbname

Alternative is to use SSMA or CSV export/import for small datasets:

BULK INSERT TableName FROM ‘data.csv’ WITH (FORMAT = ‘CSV’);

psql -h host -U user -d dbname -c “\copy TableName FROM ‘data.csv’ CSV HEADER;”

One more alternative is to use comprehensive commercial tools automating all major phases of migration including data transfer.

Choose a Migration Tool

There are multiple free and paid tools that can help in particular phase of SQL Server to PostgreSQL migration. Schema conversion tools:

• pgLoader – Automates schema and data migration.
• SQL Server Migration Assistant (SSMA) for PostgreSQL – Microsoft’s tool for schema conversion.

Data Migration Tools:

• pgLoader – Best for large datasets; automates schema, data, and index migration.
• Pentaho Data Integration (PDI) – ETL tool for complex migrations.
• DBeaver – Open-source database tool with migration support.

The tools above are suitable for semi-automated migration of small and medium size databases. For large and complicated migration projects it is recommended to use dedicated automation tools. One of these tools is MSSQL-to-PostgreSQL converter developed by Intelligent Converters.

Key features of MSSQL-to-PostgreSQL:

• Schemas, data, indexes, sequences and views are migrated with respect to safe types mapping
• MS SQL (all versions), Azure SQL, on-premises PostgreSQL (starting from v9.0), Heroku, Azure, Amazon RDS are supported
• ODBC or any other middleware is not required
• Option to filter data for migration using SELECT-queries
• Edit tables feature (modify name, type, default values for every column)

The product is supplied with free demo version for evaluation. Demo version migrates not more than 50 rows per table, does not migrate foreign keys and views

Modify the Application

• Check SQL Queries: PostgreSQL does not support certain SQL Server-specific functions (TOP, IDENTITY, GO batch separator).
• Rewrite stored procedures, views, and triggers in PL/pgSQL.
• Update ORM settings if using frameworks like Django, Hibernate, or Entity Framework.

Examples of what needs to be converted into PostgreSQL equivalents:

Validate

Validation is a very significant phase of database migration ensuring that schema and data are correctly transferred without loss, duplication, or corruption. Other goals of post-migration validation are to verify that queries, indexing and execution plans work efficiently in PostgreSQL.

Here are key steps of post-migration validation:

• Compare table structures (check if columns, types, constraints, and indexes match)
• Compare record counts in both databases (SELECT COUNT(*) FROM Employees)
• Compare row data using checksum functions
• Randomly sample and compare individual records
• Validate queries, indexes, and performance (use PostgreSQL EXPLAIN ANALYZE)
• Test application compatibility (verify reports, dashboards, and background jobs; perform load testing to compare query execution times)