TheLazy.Dev
Portfolio GitHub

Blog

mssql |postgres |migration |database

How to migrate MSSQL Server DB to PostgreSQL DB?

dm8ry

Dmitry Romanoff

Posted on September 21, 2022

How to migrate MSSQL Server DB to PostgreSQL DB?

(1) Install pgloader utility:



sudo apt-get install -y pgloader
Enter fullscreen mode Exit fullscreen mode

(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>;
Enter fullscreen mode Exit fullscreen mode

(3) Run the pgloader:



pgloader pgloader.conf
Enter fullscreen mode Exit fullscreen mode

(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
Enter fullscreen mode Exit fullscreen mode

(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>
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

(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';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

How to migrate MSSQL Server DB to PostgreSQL DB?

💖 💪 🙅 🚩
dm8ry
Dmitry Romanoff

Posted on September 21, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

How to migrate MSSQL Server DB to PostgreSQL DB?
mssql How to migrate MSSQL Server DB to PostgreSQL DB?

September 21, 2022