How to Migrate Data From SQL Server to PostgreSQL

Migrating data between different types of databases is not a trivial task. In this article, we will compare several ways of converting from SQL Server to PostgreSQL.

Microsoft SQL Server is a great database engine, but it has drawbacks in some cases.

Free Microsoft SQL Server Express can be used successfully for small databases.

Limitations of SQL Server Express:

• 1GB maximum memory used by the database engine.
• 10GB maximum database size.
• 1MB maximum buffer cache.
• CPU the lesser of one (1) socket or four (4) cores (number of SQL user connections NOT limited).

For large databases, you may need to get the paid version of the MSSQL Server, starting with the Standard edition license. The cost of the SQL Server depends on the number of users and the size of the database.

If you can’t even afford a standard SQL Server license, PostgreSQL is an alternative.

PostgreSQL is completely free, so the license cost is the main advantage of PostgreSQL over Microsoft SQL Server.

Open-source Tools for Database Migration Between SQL Server and PostgreSQL

Manual data migration is cumbersome. A good database migration tool should prompt the user to select objects to migrate, such as tables, indexes, primary and foreign key constraints.

Fortunately, there are tools out there that can automate boring manual work.

1. pgloader is a well-known open-source tool that imports data from SQL Server into PostgreSQL using the COPY command, loads data, indexes, and foreign keys, and converts data to PostgreSQL as intended.

1. pgloader loads data from various sources like MS SQL, SQLite, MySQL, CSV into PostgreSQL.
2. It is licensed under The PostgreSQL Licence and free to use.
3. pgloader is a cross-platform software.
4. Docker image is available.

2. Sqlserver2pgsql is written in Perl. This is another open-source migration tool to automate the conversion of the Microsoft SQL Server database to the PostgreSQL database.

1. It converts a SQL Server schema to a PostgreSQL schema
2. If desired, it can create a Pentaho Data Integrator (Kettle) console to migrate all data from SQL Server to PostgreSQL.

Disadvantages of Some Open-source Tools

• The tools mentioned above are command-line utilities, so they don’t have a graphical interface. For those who are intimidated by the terminal, they are not easy to use.
• These tools are limited to one-way migrations where PostgreSQL can be configured as a destination.

DBConvert Database Migration Software

DBConvert & DBSync for SQL Server and PostgreSQL are popular software tools that minimize the challenges of database conversion and synchronization between SQL Server and PostgreSQL databases.

DBConvert/DBSync for SQL Server and PostgreSQL Pros:

1. DBConvert tools are highly customizable and allow you to quickly and easily export data from Microsoft SQL Server to Postgres using a comprehensive graphical interface. DBConvert Applications guide you through several steps, from connecting to source and destination databases, configuring migration parameters, and scheduling subsequent runs.
2. In DBConvert products, the target database is presented in a tree, which significantly simplifies settings’ configuration. The proven advantage of DBConvert software is that non-professional users can effectively use it.
3. An automated validation system carefully checks the structure and relationships of your source database prior to migration, ensuring that none of your data is lost or damaged, and guarantees a risk-free and error-free data transfer.
4. After making a copy of your source database to target DB, keep your databases in Sync with Update, Insert, and Drop synchronization features.
5. The DBSync application performs bidirectional replication between SQL Server and PostgreSQL (where two different databases simultaneously replicate changes from each other).
6. In any combination, using SQL Server to PostgreSQL converter/sync tool, data migrations between the following databases are possible:

• Microsoft SQL Server,
• Windows Azure SQL Databases/ Azure SQL Data Warehouse,
• Google Cloud SQL for SQL Server,
• Google Cloud SQL for PostgreSQL,
• AWS RDS/ Aurora,
• Heroku Postgres.

Conclusion:

In this article, we’ve covered several ways of migrating data from SQL Server to PostgreSQL.

Pgloader and Sqlserver2pgsql are excellent solutions for Linux users and those who feel comfortable in the terminal. It does its job when migrating from many databases to PostgreSQL. But conversion is possible in ONE direction only.

DBConvert tools support migration and synchronization in both directions between SQL Server and PostgreSQL database types. Many more on-premises and cloud databases conversion is supported. DBSync is a perfect solution to keep databases in sync after initial migration continuously.