Since developers are preferring open-source databases now-a-days, PostgreSQL can be a good option where you can migrate your database. Open-source databases can be beneficial for developers and enterprises in many terms.
While this post is about migrating databases specifically from SQL Server(MSSQL) to PostgreSQL, I won't talk much about the differences between them. To see the differences between them, click here. Also, the steps mentioned in the article is for Windows 10.
How to Migrate?
There are different ways through which you can migrate your database from MSSQL to Postgres. It can be done manually by creating same database schema in Postgres as you have in MSSQL or you can use any tool that migrates the data automatically.
Migrating the database schema manually can be a way. You can generate scripts in MSSQL Server Management Studio and execute those scripts in Postgres' SQL Shell or PgAdmin. This will create the database schema in Postgres and then you'll have to export the data but there can be some errors produced due to different datatype compatibility in both MSSQL and Postgres.
Using a migrating tool can save much time and work as it will generate the scripts itself and create the schema in Postgres. There are different migration tools available:
- EnterpriseDB Migration Toolkit
- Sqlserver2pgsql
Migrating Database Schema manually
To migrate the database manually, you can generate scripts for either a table or a complete database and execute those scripts in PostgreSQL .
To generate scripts for a database in SQL Server, follow the steps mentioned below:
Step 1: Open MS SQL Server Management Studio. Connect to the server. Go to the database that needs to be migrated. Right click on the database > Tasks > Generate Scripts.
Step 2: Choose the objects for which the scripts have to be generated. You can either choose to script the entire database and all the database objects or specific database objects.
Step 3: Set the Scripting Option. You can open the scripts in a new query window or you can generate the scripts in a single file or one object script in one file.
Step 4: Review your objects and settings and Finish. The scripts will be generated.
After the scripts are generated, execute those scripts in either PostgreSQL' SQL Shell or PgAdmin. The PostgreSQL database schema will be generated.
Migrating Database using Migration Toolkit
Migrating database by generating scripts can be time consuming. Using a migration tool can save a lot of time and effort. There are some migration tools available which can be used to migrate the database easily. Here, I will use EnterpriseDB's Migration Toolkit.
You can either download EnterpriseDB's Migration Toolkit from their website or you can open Application Stack Builder on your system and install it.
Steps to install EnterpriseDB's Migration Toolkit
Step 1: Open the Application Stack Builder. Stack Builder is downloaded when you download PostgreSQL.
Step 2: Select the PostgreSQL server where you want to install the Migration Toolkit. Make sure you're connected to the Internet.
Step 3: The wizard will show the applications that can be installed. Go to Categories > Registration-required and trial products > EnterpriseDB Tools. Check the Migration Toolkit and click on Next.
Step 4: You'll be asked to Select you Download directory. Select your desired folder where you want to download and click on Next. The Migration Toolkit will be downloaded and you'll be asked to install it. After installing, the Migration Toolkit is ready to be used.
Building the toolkit.properties File
The toolkit.properties file is needed to provide the configuration and connection information of the source and target databases.
Step 1: Open the toolkit.properties file. On Windows, the file will be located in:
C:\Program Files\edb\mtk\etc
Step 2: Modify the file with any editor of your choice. I personally prefer VS Code. The file should contain the following information:
SRC_DB_URL : specifies the connection information of the source database.
SRC_DB_USER : specifies the username for the source database.
SRC_DB_PASSWORD : specifies the password for the source database.
TARGET_DB_URL : specifies the connection information of the target database.
TARGET_DB_USER : specifies the username for the target database.
TARGET_DB_PASSWORD : specifies the password for the target database.
The URL of the database must be in the given format:
{TARGET_DB_URL|SRC_DB_URL}=jdbc:edb://host:port/database_id
Here's a sample toolkit.properties file with SQL Server as source database and PostgreSQL as target database.
For additional information on configuring the toolkit.properties file and for migrating databases other than SQL Server, refer Building the toolkit.properties File
Invoking the Migration Toolkit
After the toolkit.properties file is configured, we are ready to migrate the database using Migration Toolkit. We will invoke the Migration Toolkit using the executable runMTK.bat file. On Windows, you can find the executable file in:
C:\Program Files\edb\mtk\bin
To invoke the Migration Toolkit, open the command line and navigate to the executable file and invoke the following command:
> .\runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres [options, …] <schema_name>
You can migrate multiple schemas at a time by mentioning the schema names using comma-delimited list such as:
> .\runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres [options, …] <schema_name1>,<schema_name2>,<schema_name3>
If you don't mention the -sourcetype and -targettype parameter in the command, by default the Migration Toolkit expects the source database to be Oracle and the target database to be EDB Postgres Advanced Server.
After invoking the Migration Toolkit, the database schema along with the data will be migrated from SQL Server to PostgreSQL. You can verify the tables and its data in Postgres.
Things to keep in mind while migrating
Syntax: Before migrating databases, you must know the syntax differences between your source and target database. I this case, its MSSQL and Postgres.
Datatype Mismatch: Both SQL Server and PostgreSQL has different datatypes.
While migrating manually, you have to take care with it. You can write scripts to convert the datatypes of SQL Server to desired PostgreSQL datatypes or you can manually convert the datatypes after generating the CREATE scripts and before executing it in Postgres.
Using Migration Toolkit, the datatypes will be converted by the toolkit. But there can be some errors while inserting the data by it. Since Migration Toolkit migrates the schema along with the data, after converting the datatypes, while inserting the data, some data in specific column with specific datatype will not be converted to desired datatype by the Migration Toolkit.
e.g.- We don't have boolean datatype in SQL Server but we have boolean in PostgreSQL. Boolean data is stored as bit datatype in SQL Server and the data will be inserted as 1 for 'true' and 0 for 'false'. The Migration Toolkit will convert the bit datatype to boolean datatype but while inserting 1 and 0 in that, it will throw error as the PostgreSQL expects boolean data to be either true or false.
To fix this, you can write scripts to convert 1 to true and 0 to false using CASE statements.
System-defined Functions Mismatch: MSSQL and PostgreSQL provide different functions for same thing which needs to be taken care of while migrating.
e.g.- We have GETDATE() to get the current date in MSSQL but in PostgreSQL, we have NOW() for that.
To get the UTC date, we have GETUTCDATE() in MSSQL but in Postgres we need to write the following query:
SELECT NOW() AT time zone 'utc';
We have NEWID() in MSSQL that creates a unique value of type uniqueidentifier but in Postgres, we have uuid_generate_v4() for this which comes as extension.
We have ISNULL(expression, value) that returns a specified value if the expression is NULL in MSSQL but in Postgres, we have COALESCE(expression, value).
If you want to use functions with the same name as MSSQL in Postgres, you can create a user-defined function with the same name and write the Postgres' equivalent function query inside it.
There are many other functions that are different in both which needs to be taken care of.
Case-sensitivity: In Postgres, if we mention any tablename or columnname inside double quotes(""), it is taken as case-sensitive.
e.g.-
SELECT firstname FROM candidate;
is equivalent to
SELECT FirstName FROM Candidate;
but both are different to
SELECT "FirstName" FROM "Candidate";
String Concatenation: When we try to concatenate data from two or more columns, in MSSQL, we can write the query as:
SELECT firstname + ' ' + lastname AS candidatename FROM candidate;
But in Postgres, we write as:
SELECT firstname || ' ' || lastname AS candidatename FROM candidate;
Stored Procedures: The stored procedures in Postgres doesn't work same as in MSSQL.
e.g.- In MSSQL stored procedure, if we want to return a query that contains multiple columns, we will just write the query inside the procedure but in Postgres, we have to write all the in and out parameters.
We can return multiple records through stored procedures in MSSQL, but in Postgres, we cannot return multiple records using stored procedure. To do this, we have to create a function with return type as TABLE and write the query inside it.
There are other incompatibilities too which are not mentioned in this article. The mentioned are the basic things which we need to keep in mind while migrating.
Conclusion
Migrating to PostgreSQL can be beneficial as it is open source and has complex datatypes but MSSQL has its own features. Choosing between the databases completely depends on the use case. If migrating to PostgreSQL, we have to take care of the datatypes, syntax and other things which is mentioned in the article while migrating.
If you have reached this far, hopefully, this quick overview helps you ease migration of your databases. Thank You for giving this a read!!
Top comments (5)
You mention that Migration Toolkit converts all the datatypes for you; with regards to bit to boolean conversion, that's pretty much the exact type of challenge that I would have expected Migration Toolkit to be capable of handling.
Are you aware of any other tools that can handle the conversion process more completely?
You might want to check out Ispirer Toolkit for your migration task. I've used it before, and it has data mapping features. You can also take advantage of a trial to see how it performs in your specific migration project.
edit i was able to make this work by using the following cmd:
runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres -targetSchema public -schemaOnly -allTables dbo
HOWEVER, all column names were imported into postgres in lower case but they were a combination of upper and lower case in sql server. Is there any option that prevents this from happening?
disregard
hello, can anyone help me with the cmd to initiate this process? i'm not clear on what I need to enter for ? I've tried using the database name and have also tried just "dbo", but i'm getting error codes MTK-10050 and MTK-10001, respectively.
Great article on using native tools to make the transfer.
I developed an open-source tool called SQLpipe that may make it easier to make smaller transfers. Here is a step by step tutorial on transferring data from SQL Server to PostgreSQL: sqlpipe.com/transfer-data-from-sql...
sadly, does not allow 'dbo' schema.
you need to have sqlserver and postgres jars downloaded and copied into C:\Program Files\edb\mtk\lib
you need to execute the command as Administrator