(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)
Top comments (0)