Migrate data from SQL Server to PostgreSQL. Open source tools vs. DBConvert.
In this article, we will compare several ways of converting 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 some 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 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.
Migrating from SQL Server to PostgreSQL.
Essentially, both SQL Server and PostgreSQL are relational databases. But why is there a problem with migrations at all?
Data Type Mapping.
Some MSSQL datatypes do not match directly to PostgreSQL data types, so you need to change it to the analogous PostgreSQL datatype.
Please refer to the following table for the differences between MSSQL and Postgres data types.
| Type | Microsoft SQL Server | PostgreSQL |
|---|---|---|
| 64-bit integer | BIGINT | BIGINT |
| Fixed length byte string | BINARY(n) | BYTEA |
| 1, 0 or NULL | BIT | BOOLEAN |
| Fixed length char string, 1 <= n <= 8000 | CHAR(n) | CHAR(n) |
| Variable length char string, 1 <= n <= 8000 | VARCHAR(n) | VARCHAR(n) |
| Variable length char string, <= 2GB | VARCHAR(max) | TEXT |
| Variable length Unicode UCS-2 string | NVARCHAR(n) | VARCHAR(n) |
| Variable length Unicode UCS-2 data, <= 2GB | NVARCHAR(max) | TEXT |
| Variable length character data, <= 2GB | TEXT | TEXT |
| Variable length Unicode UCS-2 data, <= 2GB | NTEXT | TEXT |
| Variable length byte string , 1 <= n <= 8000 | VARBINARY(n) | BYTEA |
| Variable length byte string , <= 2GB | VARBINARY(max) | BYTEA |
| Double precision floating point number | DOUBLE PRECISION | DOUBLE PRECISION |
| 32 bit integer | INTEGER | INTEGER |
| Fixed point number | NUMERIC(p,s) | NUMERIC(p,s) |
| Date includes year, month and day | DATE | DATE |
| Date and Time with fraction | DATETIME | TIMESTAMP(3) |
| Date and Time with fraction and time zone | DATETIMEOFFSET(p) | TIMESTAMP(p) WITH TIME ZONE |
| Date and Time | SMALLDATETIME | TIMESTAMP(0) |
| 8 bit unsigned integer, 0 to 255 | TINYINT | SMALLINT |
| 16 byte GUID(UUID) data | UNIQUEIDENTIFIER | CHAR(16) |
| Automatically updated binary data | ROWVERSION | BYTEA |
| 32 bit currency amount | SMALLMONEY | MONEY |
| Variable length binary data, <= 2GB | IMAGE | BYTEA |
There are many more incompatibilities between MS SQL and PostgreSQL Server but we will not cover them in this article.
Open source tools for database migration between SQL Server and PostgreSQL.
Manual data mapping 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.
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.
pgloader pros:
- pgloader loads data from various sources like MS SQL, SQLite, MySQL, CSV into PostgreSQL.
- It is licensed under The PostgreSQL Licence and free to use.
- pgloader is a cross-platform software.
- Docker image is available.
Sqlserver2pgsql is written in Perl. This is another open source migration tool to automate the conversion of Microsoft SQL Server database to PostgreSQL database.
- It converts a SQL Server schema to a PostgreSQL schema
- 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.:
- 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.
- 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.
- 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.
- After making a copy of your source database to target DB, keep your databases in Sync with Update, Insert, and Drop synchronization features.
- The DBSync application performs bidirectional replication between SQL Server and PostgreSQL (where two different databases simultaneously replicate changes from each other).
- 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.