You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.7devel
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.7devel"
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(schema_id) 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.CONSTRAINT_NAME, '.', '_') 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
The text was updated successfully, but these errors were encountered:
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
develdevel"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
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(schema_id) 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.CONSTRAINT_NAME, '.', '_') 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
The text was updated successfully, but these errors were encountered: