dimitri
pgloader replaces dollar character by underscore in tables, indexes names
-
[x] pgloader --version <3.6.2>
-
[ ] did you test a fresh compile from the source tree? No
-
[x] did you search for other similar issues?
-
[x] how can I reproduce the bug?
--
--
LOAD DATABASE
FROM mysql://migtestuser:[email protected]:3306/DZUTA0KURM01J
INTO postgresql://kurmi:[email protected]:5432/kurmie
alter schema 'DZUTA0KURM01J' rename to 'public'
WITH schema only , reset no sequences
;
I am using the command file to execute the pgloader migration.
On Mysql
On Postgres
As you can see, the table has been renamed. The dollar character $ has been replaced by underscore _. Is there any workaround to avoid this behavior ?
- [x] pgloader output you obtain
2021-12-08T19:33:34.004000Z LOG report summary reset
table name errors rows bytes total time
---------------------- --------- --------- --------- --------------
fetch meta data 0 2184 1.520s
Create Schemas 0 0 0.000s
Create SQL Types 0 0 0.005s
Create tables 0 726 1.175s
Set Table OIDs 0 363 0.053s
---------------------- --------- --------- --------- --------------
---------------------- --------- --------- --------- --------------
Index Build Completion 0 1287 11.938s
Create Indexes 0 1287 24.604s
Primary Keys 0 360 0.200s
Create Foreign Keys 0 534 0.619s
Create Triggers 0 0 0.000s
Install Comments 0 0 0.000s
---------------------- --------- --------- --------- --------------
Total import time ✓ 0 37.361s
-
[ ] data that is being loaded, if relevant Not really just no error appears
-
[x] How the data is different from what you expected, if relevant The table s$callcenterservcf6e8cckkl has been renamed by s_callcenterservcf6e8cckkl
Ran into this same issue. unfortunately for me, the $ is in the column name.
2022-01-31T13:58:59.025000-08:00 LOG pgloader version "3.6.3~devel"
2022-01-31T13:58:59.166000-08:00 LOG Migrating from #<MSSQL-CONNECTION mssql://serviceaccount@sqlbox:1433/db {10083CD8D3}>
2022-01-31T13:58:59.166000-08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://serviceaccount@localhost:5432/archive {10083CF093}>
KABOOM!
FATAL error: pgloader failed to find column "schema"."table"."cp_1" in target table "\"schema\".\"table\""
An unhandled error condition has been signalled:
pgloader failed to find column "schema"."table"."cp_1" in target table "\"schema\".\"table\""
What I am doing here?
pgloader failed to find column "schema"."table"."cp_1" in target table "\"schema\".\"table\""
Even if I create the table myself in postgres, protecting the name in quotes during creation:
"CP$1" text NULL,
and then using pgloader's
WITH CREATE NO TABLES, intepretation on the ms sql side seems to be that $ are _ when importing data, so the correctly named column is not found on the postgres end.
Temporary fix is naming the column CP_1 and then changing them name back to CP$1.
Not sustainable for continuous migration though.
