Migrating Database from SQL Server(MSSQL) to PostgreSQL
Tags: postgres
Published: 2021-08-12 13:09:21


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](https://www.enterprisedb.com/blog/microsoft-sql-server-mssql-vs-postgresql-comparison-details-what-differences). 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:
1. EnterpriseDB Migration Toolkit
2. 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**.
![SSMS Generate Scripts](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cu28urmu420xs8hd13vh.png)

**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.
![Choose database objects to script](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kx2rmaw8s0v19ywn3qpr.png)

**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.
![Scripting options](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/swkip7nqmemk0r9u7aef.png)

**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](https://www.enterprisedb.com/products/oracle-to-postgresql-migration-toolkit-move-from).

You can either download EnterpriseDB's Migration Toolkit from their [website](https://www.enterprisedb.com/products/oracle-to-postgresql-migration-toolkit-move-from) 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.
![Stack Builder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j8txevbnu1x9zw7izb7f.jpg)

**Step 2:** Select the PostgreSQL server where you want to install the Migration Toolkit. Make sure you're connected to the Internet.
![Stack Builder Welcome Wizard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/msbdpkjujy55ak63bxtc.png)

**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**.
![Migration Toolkit to be installed](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r9a3rdvlco3lzkf76ri2.png)

**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.
![toolkit.properties](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w5r1v87arrgv4jkq2zos.png)

For additional information on configuring the toolkit.properties file and for migrating databases other than SQL Server, refer [Building the toolkit.properties File](https://www.enterprisedb.com/edb-docs/d/edb-postgres-migration-toolkit/user-guides/user-guide/53.0.2/building_toolkit.properties_file.html#defining-a-postgresql-url)

####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:

```sql
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.-** ```sql
SELECT firstname FROM candidate;
```

is equivalent to 

```sql
SELECT FirstName FROM Candidate;
```

but both are different to

```sql
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:

```sql
SELECT firstname + ' ' + lastname AS candidatename FROM candidate;
```

But in Postgres, we write as:

```sql
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!!