How to Migrate from SQL Server to PostgreSQL on Ubuntu Linux

Key Takeaways

• Assess SQL Server workloads before starting PostgreSQL migration planning carefully.
• Choose PostgreSQL migration tools based on database size and complexity.
• Validate converted schemas, data, queries, and applications before PostgreSQL cutover carefully.
• Plan the SQL Server to PostgreSQL cutover to minimize business downtime.

Migrating from Microsoft SQL Server to PostgreSQL on Ubuntu Linux can become complex when differences in database architecture, SQL syntax, data types, stored procedures, jobs, and application dependencies start affecting the migration process. Simply moving tables and data is rarely enough for a reliable production migration.

A successful SQL Server to PostgreSQL migration requires careful assessment, schema conversion, data migration, code updates, application testing, validation, and performance tuning. SQL Server-specific features such as T-SQL procedures, SQL Server Agent jobs, identity behavior, functions, collations, and proprietary queries often need to be reviewed or converted for PostgreSQL.

Tools such as pgloader, AWS Schema Conversion Tool (SCT), AWS Database Migration Service (DMS), BCP/COPY, and commercial migration platforms can automate parts of the process. 

How Do You Migrate SQL Server to PostgreSQL?

Migrating SQL Server to PostgreSQL requires a systematic approach because the two databases use different SQL dialects (T-SQL vs. PL/pgSQL), data types, and architecture. The practical process can be organized into three phases: assess and prepare, migrate and convert, then validate and cut over.

Phase 1: Assess and Prepare

Start by understanding exactly what exists in SQL Server before moving anything.

Inventory:

• Databases, schemas, tables, views, indexes, and constraints.
• Stored procedures, functions, triggers, and SQL Server Agent jobs.
• Application dependencies and connection methods.
• Database size, growth rate, and expected downtime.
• SQL Server-specific features that have no direct PostgreSQL equivalent.

Create a backup and document a rollback plan before beginning the migration.

Small note: Do not estimate migration effort based on database size alone. A 20 GB database with thousands of T-SQL dependencies can require more conversion work than a much larger database built primarily around standard SQL.

Phase 2: Convert and Migrate

Prepare PostgreSQL on Ubuntu, convert the schema, and migrate the data.

A typical mapping looks like this:

Phase 3: Validate and Cut Over

After loading the data, compare the source and destination before switching production traffic.

Validate:

• Row counts.
• Key constraints and relationships.
• Indexes.
• Sequences or identity values.
• Representative application queries.
• Stored procedures and business logic.
• Performance of critical transactions.

Once validation is complete, freeze writes on SQL Server, perform the final synchronization, switch the application to PostgreSQL, and monitor the new environment.

Why Migrate from SQL Server to PostgreSQL? 

Organizations migrate from Microsoft SQL Server to PostgreSQL to modernize their data infrastructure, eliminate restrictive vendor practices, and drastically reduce expenses. While SQL Server is a capable enterprise database, PostgreSQL has evolved into a highly competitive, enterprise-grade alternative. 

Lower Database Licensing Dependency

PostgreSQL does not require the same proprietary database licensing model as Microsoft SQL Server. That can change the economics of an environment, particularly where database licensing represents a significant portion of infrastructure expenditure. However, migration has its own costs, including engineering time, testing, infrastructure, training, support, and application changes.

Linux and Cloud Compatibility

PostgreSQL is widely used on Linux and can run on Ubuntu directly, in containers, or through managed cloud database services. This makes PostgreSQL a practical option for organizations standardizing on Linux-based infrastructure.

Extensibility and JSONB

PostgreSQL provides native JSON and JSONB capabilities, indexes, extensions, and a broad ecosystem. JSONB can be particularly useful when applications need to query semi-structured data alongside relational data. Performance, however, depends on the workload, indexing strategy, query patterns, and data model.

What Are the Main SQL Server to PostgreSQL Migration Challenges? 

Migrating from Microsoft SQL Server to PostgreSQL is a strategic move to eliminate licensing fees and leverage open-source scalability, but it presents several complex technical roadblocks. The main migration challenges stem from structural, behavioral, and procedural differences between the two relational database engines. 

T-SQL and PL/pgSQL Are Different

T-SQL code cannot simply be renamed or copied into PostgreSQL. Variables, exception handling, temporary tables, functions, procedures, cursors, system functions, and transaction behavior may need to be rewritten.

For example, an SQL Server identity definition might look like this:

CREATE TABLE customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100)
);
A PostgreSQL equivalent could use:
CREATE TABLE customers (
    customer_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

The exact definition should be chosen based on the application's requirements and the PostgreSQL version.

Identity and Sequence Differences

Identity columns and sequences need particular attention after migration. If existing data is loaded into PostgreSQL, verify that the associated sequence or identity mechanism will generate the correct next value. Otherwise, new inserts can produce duplicate-key errors.

Collation and Case Behavior

String comparison, identifiers, sorting, and case behavior can differ between SQL Server and PostgreSQL. Applications that depend on case-insensitive comparisons or SQL Server-specific collation behavior should be tested explicitly.

SQL Server Agent Jobs

SQL Server Agent jobs do not migrate automatically as PostgreSQL jobs do. Scheduled tasks need to be recreated using an appropriate scheduling mechanism such as Linux cron, pg_cron, or an external orchestration platform.

Schema and Data Type Mismatches

Schema and data type mismatches are a major SQL Server to PostgreSQL migration challenge. SQL Server and PostgreSQL use different data types, constraints, and schema conventions. Teams must carefully map each SQL Server data type to its PostgreSQL equivalent to prevent data loss, conversion errors, and application issues.

Which SQL Server to PostgreSQL Migration Tools Should You Use? 

When choosing an SQL Server to PostgreSQL migration tool, your selection depends entirely on your budget, whether you require a graphical interface (GUI), and whether you need continuous data synchronization. The most effective tools for migrating your database schema and data are categorized below by their primary use case

For a straightforward migration, pgloader can reduce manual work. For complex enterprise environments, a combination of schema conversion, replication, testing, and application refactoring may be more appropriate. 

Can You Migrate SQL Server to PostgreSQL on Ubuntu? 

Yes, you can absolutely migrate SQL Server to PostgreSQL on Ubuntu. Because both databases can run natively or via containers on Linux, Ubuntu serves as an excellent, highly efficient environment to execute the migration. The process generally involves two main components: migrating the database schema (structure) and migrating the actual table data.

A common architecture is

SQL Server on Windows → migration/conversion process → PostgreSQL on Ubuntu → application testing → production cutover

Keeping the source and destination environments available simultaneously makes parallel testing and rollback planning easier. The Ubuntu environment should be prepared with appropriate CPU, memory, storage, networking, security, PostgreSQL configuration, monitoring, and backup capabilities before production migration

How Do You Validate a SQL Server to PostgreSQL Migration?

You validate an SQL Server to PostgreSQL migration by comparing data accuracy, database structure, business logic, application behavior, and performance between the source and target environments. Start with row counts and representative records, then verify constraints, indexes, sequences, queries, and critical application workflows before production cutover.

1. Begin by comparing important tables between SQL Server and PostgreSQL: Check row counts, representative records, relationships, constraints, indexes, and sequences to identify missing, duplicated, or incorrectly converted data.

2. Verify essential application reads, inserts, updates, and deletes: Test common user workflows to ensure applications can retrieve, create, modify, and remove data correctly after migrating to PostgreSQL.

3. Confirm reports return accurate results after migration: Run important reports and analytical queries against PostgreSQL and compare their results with the SQL Server environment.

4. Ensure transactions commit, roll back, and maintain data integrity: Test multi-step operations to confirm PostgreSQL handles successful transactions, failures, rollbacks, and concurrent operations as expected.

5. Verify users retain correct authentication and database permissions: Test application logins, PostgreSQL roles, permissions, access restrictions, and privileged operations to ensure users can access only authorized resources.

6. Confirm automated jobs execute correctly on PostgreSQL: Test migrated scheduled tasks, recurring processes, notifications, maintenance jobs, and other automated workflows after replacing SQL Server Agent dependencies.

7. Test converted procedures and functions for equivalent behavior: Execute important T-SQL conversions and PostgreSQL functions with representative inputs to confirm calculations, error handling, outputs, and business rules remain correct.

8. Run equivalent workloads against both databases before production cutover: Parallel testing can reveal differences in query results, application behavior, performance, transactions, and business logic that simple row-count validation may miss.

How Do You Optimize PostgreSQL After Migration?

Do not immediately copy SQL Server configuration values into PostgreSQL. PostgreSQL has its own memory, storage, connection, planner, and maintenance settings.

After migration:

1. Run VACUUM ANALYZE where appropriate to update statistics and support query planning.
2. Review indexes based on actual PostgreSQL query patterns.
3. Identify slow queries using PostgreSQL monitoring tools.
4. Configure connection pooling when the application creates many database connections.
5. Review memory and workload-specific PostgreSQL settings.

Performance tuning should be based on measurements from the new environment rather than assumptions from SQL Server.

What Should the SQL Server to PostgreSQL Cutover Plan Include?

An SQL Server to PostgreSQL cutover plan must minimize downtime and ensure data integrity during the final switch. It should include the following five core elements:

• Final Data Synchronization: Establish a precise window to stop application writes, run a final delta sync using tools like AWS Database Migration Service, and perform schema and row-count validation.
• Application Reconfiguration: Update connection strings, environment variables, and database drivers (switching from .NET/SqlClient to Npgsql or JDBC to PgJDBC) across all application environments.
• Rollback Strategy: Define a clear "point of no return" and maintain a reverse replication pipeline or backup plan to quickly restore operations on the original SQL Server if critical failures occur post-switch.
• Performance Verification: Run automated smoke tests, validate critical API endpoints, and monitor PostgreSQL system metrics (like CPU, memory, and active connections) immediately after pointing traffic to the new database.
• Post-Cutover Optimization: Execute database maintenance tasks right after the migration, specifically running ANALYZE or VACUUM to update the query planner statistics and ensure optimal query performance.

How Much Does SQL Server to PostgreSQL Migration Cost?

SQL Server to PostgreSQL migration costs depend on database size, migration tooling, manual effort, and the complexity of the SQL Server environment. Smaller databases may require only a few days of manual work, while larger environments can require several weeks and additional tooling. The following estimates compare manual migration effort with common migration tools and potential annual license savings:

What Affects SQL Server to PostgreSQL Migration Cost?

Migration cost is influenced by more than database size. Schema complexity, stored procedures, SQL Server Agent jobs, application dependencies, testing requirements, downtime constraints, and the amount of manual code conversion can significantly affect the overall effort.

Pre-Migration Assessment Checklist

A pre-migration assessment identifies the database objects, dependencies, data volume, application requirements, downtime constraints, and rollback requirements before migration begins. This assessment helps determine the appropriate migration approach, tools, resources, and expected effort.

Frequently Asked Questions

1. Can T-SQL stored procedures be migrated automatically?

Some T-SQL can be converted automatically, but complex stored procedures usually require manual review and testing. Conversion tools can help identify and transform compatible constructs, while SQL Server-specific logic may need to be rewritten in PL/pgSQL.

2. What is the easiest way to migrate SQL Server to PostgreSQL?

For a relatively straightforward database, pgloader can simplify schema and data migration. More complex environments may require a combination of schema conversion, data migration, change replication, code refactoring, and application testing.

3. Can I migrate SQL Server data to PostgreSQL without downtime?

Potentially, yes, depending on the architecture and migration tooling. A migration using ongoing change replication can reduce the final downtime window, but the exact approach depends on the source workload, target architecture, data volume, and application behavior.

4. How long does an SQL Server to PostgreSQL migration take?

There is no reliable timeline based only on database size. A small database with complex stored procedures and application dependencies can require significant engineering, while a larger database with simple schemas may be easier to migrate.

5. What happens to SQL Server Agent jobs after migration?

SQL Server Agent jobs need to be recreated in the PostgreSQL/Linux environment. Depending on the workload, teams can use cron, pg_cron, application schedulers, or enterprise orchestration tools.

6. Is PostgreSQL compatible with Ubuntu Linux?

Yes. PostgreSQL is designed to run on Linux, including Ubuntu. Teams can install PostgreSQL directly on Ubuntu or deploy it through containers or managed database services.

7. How to Import SQL into PostgreSQL?

Import an SQL file into PostgreSQL using psql by specifying the target database and file. For example, run psql -d my_database -f backup.sql. SQL Server files may require conversion first because T-SQL syntax is not directly compatible with PostgreSQL.

Ready to migrate from SQL Server to PostgreSQL?

Geopits Advisory helps enterprises plan and execute SQL Server to PostgreSQL migrations with a structured, low-risk approach. Assess your SQL Server environment, identify compatibility requirements, choose the right PostgreSQL migration tools, and build a phased migration plan. Contact GeopIT Advisory to start your migration assessment.