I have created a configuration file and called it "ms.load" and put the following configuration in it:
load database
from mssql://sa:xxxxxxx@10.85.10.30:1433/sonarqube
into postgresql://sonar:sonar@localhost:5432/sonarqube
including only table names like 'GlobalAccount' in schema 'dbo'
set work_mem to '16MB', maintenance_work_mem to '512 MB'
before load do $$ drop schema if exists dbo cascade;
$$;
Then i have created a shell script file to execute the pgloader configration file and called it "mssqltopostgresql.sh" with the following line in it:
pgloader -d -v -L ~/pgloader.log ~/ms.load
when I run it i get the following
./mssqltopostgresql.sh
Executing the phloader command
pgloader version 3.6.7~devel
compiled with SBCL 2.1.11.debian
sb-impl::default-external-format :UTF-8
tmpdir: #P"/tmp/pgloader/"
2024-04-21T13:24:04.000000Z NOTICE Starting pgloader, log system is ready.
2024-04-21T13:24:04.016000Z INFO Starting monitor
2024-04-21T13:24:04.020000Z LOG pgloader version "3.6.7~devel"
2024-04-21T13:24:04.028000Z INFO Parsed command:
--# pgloader mssql://sa:xxxxxxxx@10.85.10.30/sonarqube pgsql://sonar:sonar@localhost/sonarqube
load database
from mssql://sa:xxxxxxx@10.85.10.30:1433/sonarqube
into postgresql://sonar:sonar@localhost:5432/sonarqube
including only table names like 'GlobalAccount' in schema 'dbo'
set work_mem to '16MB', maintenance_work_mem to '512 MB'
before load do $$ drop schema if exists dbo cascade;
$$;
2024-04-21T13:24:04.064000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://sonar@localhost:5432/sonarqube {1007FD66D3}>
2024-04-21T13:24:04.064000Z DEBUG SET client_encoding TO 'utf8'
2024-04-21T13:24:04.064000Z DEBUG SET work_mem TO '16MB'
2024-04-21T13:24:04.064000Z DEBUG SET maintenance_work_mem TO '512 MB'
2024-04-21T13:24:04.064000Z DEBUG SET application_name TO 'pgloader'
2024-04-21T13:24:04.072000Z NOTICE Executing SQL block for before load
2024-04-21T13:24:04.088000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://sonar@localhost:5432/sonarqube {1007FD66D3}>
2024-04-21T13:24:04.088000Z DEBUG SET client_encoding TO 'utf8'
2024-04-21T13:24:04.088000Z DEBUG SET work_mem TO '16MB'
2024-04-21T13:24:04.088000Z DEBUG SET maintenance_work_mem TO '512 MB'
2024-04-21T13:24:04.088000Z DEBUG SET application_name TO 'pgloader'
2024-04-21T13:24:04.092000Z DEBUG BEGIN
2024-04-21T13:24:04.092000Z SQL drop schema if exists dbo cascade;
2024-04-21T13:24:04.096000Z LOG Migrating from #<MSSQL-CONNECTION mssql://sa@10.85.10.30:1433/sonarqube {1007FD5A53}>
2024-04-21T13:24:04.096000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://sonar@localhost:5432/sonarqube {1007FD66D3}>
Max connections reached, increase value of TDS_MAX_CONN
2024-04-21T13:24:04.140000Z SQL MSSQL: sending query: -- params: dbname
-- table-type-name
-- including
-- filter-list-to-where-clause including
-- excluding
-- filter-list-to-where-clause excluding
select c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE,
CASE
WHEN c.COLUMN_DEFAULT LIKE '((%' AND c.COLUMN_DEFAULT LIKE '%))' THEN
CASE
WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'newid()' THEN 'GENERATE_UUID'
WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) LIKE 'convert(%varchar%,getdate(),%)' THEN 'today'
WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'getdate()' THEN 'CURRENT_TIMESTAMP'
WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'sysdatetimeoffset()' THEN 'CURRENT_TIMESTAMP'
WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) LIKE '''%''' THEN SUBSTRING(c.COLUMN_DEFAULT,4,len(c.COLUMN_DEFAULT)-6)
ELSE SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4)
END
WHEN c.COLUMN_DEFAULT LIKE '(%' AND c.COLUMN_DEFAULT LIKE '%)' THEN
CASE
WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'newid()' THEN 'GENERATE_UUID'
WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) LIKE 'convert(%varchar%,getdate(),%)' THEN 'today'
WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'getdate()' THEN 'CURRENT_TIMESTAMP'
WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'sysdatetimeoffset()' THEN 'CURRENT_TIMESTAMP'
WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) LIKE '''%''' THEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4)
ELSE SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2)
END
ELSE c.COLUMN_DEFAULT
END,
c.IS_NULLABLE,
COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity'),
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_PRECISION_RADIX,
c.NUMERIC_SCALE,
c.DATETIME_PRECISION,
c.CHARACTER_SET_NAME,
c.COLLATION_NAME
from INFORMATION_SCHEMA.COLUMNS c
join INFORMATION_SCHEMA.TABLES t
on c.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = t.TABLE_NAME
where c.TABLE_CATALOG = 'sonarqube'
and t.TABLE_TYPE = 'BASE TABLE'
and ((c.table_schema = 'dbo' and c.table_name LIKE 'GlobalAccount'))
order by c.table_schema, c.table_name, c.ordinal_position;
2024-04-21T13:24:04.148000Z SQL MSSQL: sending query: -- params: including
-- filter-list-to-where-clause including
-- excluding
-- filter-list-to-where-clause excluding
select schema_name(schemaid) as SchemaName,
o.name as TableName,
REPLACE(i.name, '.', '') as IndexName,
co.[name] as ColumnName,
i.is_unique,
i.is_primary_key,
i.filter_definition
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id
and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id
and co.column_id = ic.column_id
where schema_name(schema_id) not in ('dto', 'sys')
and ((schema_name(schema_id) = 'dbo' and o.name LIKE 'GlobalAccount'))
order by SchemaName,
o.[name],
i.[name],
ic.is_included_column,
ic.key_ordinal;
2024-04-21T13:24:04.152000Z SQL MSSQL: sending query: -- params: dbname
-- including
-- filter-list-to-where-clause including
-- excluding
-- filter-list-to-where-clause excluding
SELECT
REPLACE(KCU1.CONSTRAINTNAME, '.', '') AS 'CONSTRAINT_NAME'
, KCU1.TABLE_SCHEMA AS 'TABLE_SCHEMA'
, KCU1.TABLE_NAME AS 'TABLE_NAME'
, KCU1.COLUMN_NAME AS 'COLUMN_NAME'
, KCU2.TABLE_SCHEMA AS 'UNIQUE_TABLE_SCHEMA'
, KCU2.TABLE_NAME AS 'UNIQUE_TABLE_NAME'
, KCU2.COLUMN_NAME AS 'UNIQUE_COLUMN_NAME'
, RC.UPDATE_RULE AS 'UPDATE_RULE'
, RC.DELETE_RULE AS 'DELETE_RULE'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
AND KCU1.TABLE_CATALOG = 'sonarqube'
AND KCU1.CONSTRAINT_CATALOG = 'sonarqube'
AND KCU1.CONSTRAINT_SCHEMA NOT IN ('dto', 'sys')
AND KCU1.TABLE_SCHEMA NOT IN ('dto', 'sys')
AND KCU2.TABLE_SCHEMA NOT IN ('dto', 'sys')
and ((kcu1.table_schema = 'dbo' and kcu1.table_name LIKE 'GlobalAccount'))
ORDER BY KCU1.CONSTRAINT_NAME, KCU1.ORDINAL_POSITION;
2024-04-21T13:24:04.180000Z ERROR MSSQL ERROR: %dbsqlexec fail
2024-04-21T13:24:04.180000Z LOG You might need to review the FreeTDS protocol version in your freetds.conf file, see http://www.freetds.org/userguide/choosingtdsprotocol.htm
2024-04-21T13:24:04.180000Z LOG report summary reset
table name errors read imported bytes total time read write
before load 0 1 1 0.020s
fetch meta data 0 0 0 0.000s
2024-04-21T13:24:04.200000Z INFO Stopping monitor
I have taken the the queries and run them on the MSSQL client only the last query failed because of the small letters on the following line:
"and ((kcu1.table_schema = 'dbo' and kcu1.table_name LIKE 'GlobalAccount'))"
after I changed it in the MSSQL client to:
"AND ((KCU1.TABLE_SCHEMA = 'dbo' AND KCU1.TABLE_NAME LIKE 'GlobalAccount'))"
the query successfully executed.
So, how to correct this issue in the version of pgloader that i have?
Please note that i have installed pgloader using the following command:
sudo apt install pgloader
this is running on an ubuntu server "VERSION="22.04.4 LTS (Jammy Jellyfish)""
This is my pgloader version:
pgloader version "3.6.7~devel"
compiled with SBCL 2.1.11.debian
I have created a configuration file and called it "ms.load" and put the following configuration in it:
load database from mssql://sa:xxxxxxx@10.85.10.30:1433/sonarqube into postgresql://sonar:sonar@localhost:5432/sonarqube
including only table names like 'GlobalAccount' in schema 'dbo'
set work_mem to '16MB', maintenance_work_mem to '512 MB'
before load do $$ drop schema if exists dbo cascade; $$;
Then i have created a shell script file to execute the pgloader configration file and called it "mssqltopostgresql.sh" with the following line in it: pgloader -d -v -L ~/pgloader.log ~/ms.load
when I run it i get the following
./mssqltopostgresql.sh Executing the phloader command pgloader version 3.6.7~devel compiled with SBCL 2.1.11.debian sb-impl::default-external-format :UTF-8 tmpdir: #P"/tmp/pgloader/" 2024-04-21T13:24:04.000000Z NOTICE Starting pgloader, log system is ready. 2024-04-21T13:24:04.016000Z INFO Starting monitor 2024-04-21T13:24:04.020000Z LOG pgloader version "3.6.7~devel" 2024-04-21T13:24:04.028000Z INFO Parsed command: --# pgloader mssql://sa:xxxxxxxx@10.85.10.30/sonarqube pgsql://sonar:sonar@localhost/sonarqube load database from mssql://sa:xxxxxxx@10.85.10.30:1433/sonarqube into postgresql://sonar:sonar@localhost:5432/sonarqube
including only table names like 'GlobalAccount' in schema 'dbo'
set work_mem to '16MB', maintenance_work_mem to '512 MB'
before load do $$ drop schema if exists dbo cascade; $$;
2024-04-21T13:24:04.064000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://sonar@localhost:5432/sonarqube {1007FD66D3}> 2024-04-21T13:24:04.064000Z DEBUG SET client_encoding TO 'utf8' 2024-04-21T13:24:04.064000Z DEBUG SET work_mem TO '16MB' 2024-04-21T13:24:04.064000Z DEBUG SET maintenance_work_mem TO '512 MB' 2024-04-21T13:24:04.064000Z DEBUG SET application_name TO 'pgloader' 2024-04-21T13:24:04.072000Z NOTICE Executing SQL block for before load 2024-04-21T13:24:04.088000Z DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://sonar@localhost:5432/sonarqube {1007FD66D3}> 2024-04-21T13:24:04.088000Z DEBUG SET client_encoding TO 'utf8' 2024-04-21T13:24:04.088000Z DEBUG SET work_mem TO '16MB' 2024-04-21T13:24:04.088000Z DEBUG SET maintenance_work_mem TO '512 MB' 2024-04-21T13:24:04.088000Z DEBUG SET application_name TO 'pgloader' 2024-04-21T13:24:04.092000Z DEBUG BEGIN 2024-04-21T13:24:04.092000Z SQL drop schema if exists dbo cascade; 2024-04-21T13:24:04.096000Z LOG Migrating from #<MSSQL-CONNECTION mssql://sa@10.85.10.30:1433/sonarqube {1007FD5A53}> 2024-04-21T13:24:04.096000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://sonar@localhost:5432/sonarqube {1007FD66D3}> Max connections reached, increase value of TDS_MAX_CONN 2024-04-21T13:24:04.140000Z SQL MSSQL: sending query: -- params: dbname -- table-type-name -- including -- filter-list-to-where-clause including -- excluding -- filter-list-to-where-clause excluding select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, CASE WHEN c.COLUMN_DEFAULT LIKE '((%' AND c.COLUMN_DEFAULT LIKE '%))' THEN CASE WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'newid()' THEN 'GENERATE_UUID' WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) LIKE 'convert(%varchar%,getdate(),%)' THEN 'today' WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'getdate()' THEN 'CURRENT_TIMESTAMP' WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) = 'sysdatetimeoffset()' THEN 'CURRENT_TIMESTAMP' WHEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) LIKE '''%''' THEN SUBSTRING(c.COLUMN_DEFAULT,4,len(c.COLUMN_DEFAULT)-6) ELSE SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) END WHEN c.COLUMN_DEFAULT LIKE '(%' AND c.COLUMN_DEFAULT LIKE '%)' THEN CASE WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'newid()' THEN 'GENERATE_UUID' WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) LIKE 'convert(%varchar%,getdate(),%)' THEN 'today' WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'getdate()' THEN 'CURRENT_TIMESTAMP' WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) = 'sysdatetimeoffset()' THEN 'CURRENT_TIMESTAMP' WHEN SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) LIKE '''%''' THEN SUBSTRING(c.COLUMN_DEFAULT,3,len(c.COLUMN_DEFAULT)-4) ELSE SUBSTRING(c.COLUMN_DEFAULT,2,len(c.COLUMN_DEFAULT)-2) END ELSE c.COLUMN_DEFAULT END, c.IS_NULLABLE, COLUMNPROPERTY(object_id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity'), c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_PRECISION_RADIX, c.NUMERIC_SCALE, c.DATETIME_PRECISION, c.CHARACTER_SET_NAME, c.COLLATION_NAME
where c.TABLE_CATALOG = 'sonarqube' and t.TABLE_TYPE = 'BASE TABLE' and ((c.table_schema = 'dbo' and c.table_name LIKE 'GlobalAccount'))
order by c.table_schema, c.table_name, c.ordinal_position; 2024-04-21T13:24:04.148000Z SQL MSSQL: sending query: -- params: including -- filter-list-to-where-clause including -- excluding -- filter-list-to-where-clause excluding select schema_name(schemaid) as SchemaName, o.name as TableName, REPLACE(i.name, '.', '') as IndexName, co.[name] as ColumnName, i.is_unique, i.is_primary_key, i.filter_definition
where schema_name(schema_id) not in ('dto', 'sys') and ((schema_name(schema_id) = 'dbo' and o.name LIKE 'GlobalAccount'))
order by SchemaName, o.[name], i.[name], ic.is_included_column, ic.key_ordinal; 2024-04-21T13:24:04.152000Z SQL MSSQL: sending query: -- params: dbname -- including -- filter-list-to-where-clause including -- excluding -- filter-list-to-where-clause excluding SELECT REPLACE(KCU1.CONSTRAINTNAME, '.', '') AS 'CONSTRAINT_NAME' , KCU1.TABLE_SCHEMA AS 'TABLE_SCHEMA' , KCU1.TABLE_NAME AS 'TABLE_NAME' , KCU1.COLUMN_NAME AS 'COLUMN_NAME' , KCU2.TABLE_SCHEMA AS 'UNIQUE_TABLE_SCHEMA' , KCU2.TABLE_NAME AS 'UNIQUE_TABLE_NAME' , KCU2.COLUMN_NAME AS 'UNIQUE_COLUMN_NAME' , RC.UPDATE_RULE AS 'UPDATE_RULE' , RC.DELETE_RULE AS 'DELETE_RULE'
WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION AND KCU1.TABLE_CATALOG = 'sonarqube' AND KCU1.CONSTRAINT_CATALOG = 'sonarqube' AND KCU1.CONSTRAINT_SCHEMA NOT IN ('dto', 'sys') AND KCU1.TABLE_SCHEMA NOT IN ('dto', 'sys') AND KCU2.TABLE_SCHEMA NOT IN ('dto', 'sys')
ORDER BY KCU1.CONSTRAINT_NAME, KCU1.ORDINAL_POSITION; 2024-04-21T13:24:04.180000Z ERROR MSSQL ERROR: %dbsqlexec fail 2024-04-21T13:24:04.180000Z LOG You might need to review the FreeTDS protocol version in your freetds.conf file, see http://www.freetds.org/userguide/choosingtdsprotocol.htm 2024-04-21T13:24:04.180000Z LOG report summary reset table name errors read imported bytes total time read write
fetch meta data 0 0 0 0.000s
2024-04-21T13:24:04.200000Z INFO Stopping monitor
I have taken the the queries and run them on the MSSQL client only the last query failed because of the small letters on the following line: "and ((kcu1.table_schema = 'dbo' and kcu1.table_name LIKE 'GlobalAccount'))" after I changed it in the MSSQL client to: "AND ((KCU1.TABLE_SCHEMA = 'dbo' AND KCU1.TABLE_NAME LIKE 'GlobalAccount'))"
the query successfully executed.
So, how to correct this issue in the version of pgloader that i have?
Please note that i have installed pgloader using the following command: sudo apt install pgloader
this is running on an ubuntu server "VERSION="22.04.4 LTS (Jammy Jellyfish)"" This is my pgloader version: pgloader version "3.6.7~devel" compiled with SBCL 2.1.11.debian