How to migrate MSSQL Server DB to PostgreSQL DB?
Tags: mssql, postgres, migration, database
Published: 2022-09-21 21:01:20


(1) Install pgloader utility:

```
sudo apt-get install -y pgloader
```

(2) Create pgloader configuraton file:

```
cat pgloader.conf
load database
from
mssql://<mssql_db_user>:<mssql_db_pwd>@<mssql_db_host>/<mssql_db_name>
into postgresql://<pg_db_user>:<pg_db_pwd>@<pg_db_host>/<pg_db_name>;
```

(3) Run the pgloader:

```
pgloader pgloader.conf
```

(4) Check pgloader trace file:

```
dmi@dmi-VirtualBox:~/my_pgloader$ pgloader pgloader.conf
2022-05-04T15:58:02.012000Z LOG pgloader version "3.6.1"
2022-05-04T15:58:02.311000Z LOG Migrating from #<MSSQL-CONNECTION
mssql://SA@192.168.0.77:1433/some_mssqldb {10068ED983}>
2022-05-04T15:58:02.312000Z LOG Migrating into #<PGSQL-CONNECTION
pgsql://postgres@192.168.0.77:5432/some_pgdb {10068EECA3}>
Max connections reached, increase value of TDS_MAX_CONN
Max connections reached, increase value of TDS_MAX_CONN
2022-05-04T15:58:03.341000Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 1 0.694s
Create Schemas 0 0 0.028s
Create SQL Types 0 0 0.013s
Create tables 0 2 0.055s
Set Table OIDs 0 1 0.006s
----------------------- --------- --------- --------- --------------
dbo.my_table 0 3 0.0 kB 0.016s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.013s
Index Build Completion 0 0 0.000s
Reset Sequences 0 0 0.030s
Primary Keys 0 0 0.000s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 3 0.0 kB 0.043s
```

(5) Examine source and destination DB objects.

Examine source [ MS SQL Server ]:

```
dmi@dmi-VirtualBox:~/my_pgloader$ sqlcmd -S 192.168.0.77 -U SA
Password:
1> use mydb
2> go
Changed database context to 'mydb'.
1> select * from my_table
2> go
id name
-----------
-------------------------------------------------------------------------------
---------------------
1 One
2 Two
3 Three
(3 rows affected)
1>
```

Examine destination [ PostgreSQL ]:

```
postgres=# \d dbo.*
Table "dbo.my_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
postgres=#
postgres=#
postgres=#
postgres=# \d dbo.*
Table "dbo.my_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
postgres=# select * from dbo.my_table;
id | name
----+-------
1 | One
2 | Two
3 | Three
(3 rows)
postgres=#
```

(6) How to migrate a database from MSSQL to PostgreSQL to a
public schema?

Create pgloader.conf file:

```
cat pgloader.conf
load database
from mssql://SA:mypwd@192.168.0.77/mydb
into postgresql://postgres:mypwd@192.168.0.77/mydb
ALTER SCHEMA 'dbo' RENAME TO 'public';
```

Run the pgloader:

```
pgloader pgloader.conf
2022-05-04T16:32:01.012000Z LOG pgloader version "3.6.1"
2022-05-04T16:32:01.083000Z LOG Migrating from #<MSSQL-CONNECTION
mssql://SA@192.168.0.77:1433/mydb {10068ED8B3}>
2022-05-04T16:32:01.084000Z LOG Migrating into #<PGSQL-CONNECTION
pgsql://postgres@192.168.0.77:5432/mydb {10068EEBD3}>
Max connections reached, increase value of TDS_MAX_CONN
Max connections reached, increase value of TDS_MAX_CONN
2022-05-04T16:32:01.288000Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 1 0.057s
Create Schemas 0 0 0.000s
Create SQL Types 0 0 0.007s
Create tables 0 2 0.022s
Set Table OIDs 0 1 0.004s
----------------------- --------- --------- --------- --------------
public.my_table 0 3 0.0 kB 0.015s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.015s
Index Build Completion 0 0 0.001s
Reset Sequences 0 0 0.012s
Primary Keys 0 0 0.000s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 3 0.0 kB 0.028s
```

Connect to the destination DB:

```
psql -h 192.168.0.77 -d mydb -U postgres -W
Password:
psql (12.10 (Ubuntu 12.10-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression:
off)
Type "help" for help.
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | my_table | table | mydb
(1 row)
select * from my_table;
id | name
----+-------
1 | One
2 | Two
3 | Three
(3 rows)
```

![How to migrate MSSQL Server DB to PostgreSQL DB?](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lyv8qx9awbc1zivtrer9.png)