Migrating From SQL Server to Postgres with Ora2pg Is very Slow

[ijedoya68]

I tried using the ora2pg tool to migrate from an on-prem SQL Server database to the GCP Cloud SQL but the migration is very slow running with a single thread. I have 1000+ tables to migrate and it takes hours to run through a single table. However, it looks like you cannot use the -P or -j options for SQL Server the process simply crashes with the error message:

DBD::ODBC::st fetchall_arrayref failed: [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (SQL-08S01) [Microsoft][ODBC Driver 18 for SQL Server]Communication link failure (SQL-08S01) at /usr/local/share/perl5/Ora2Pg.pm line 15527. DBI st handle 0x55a4cf36cc60 cleared whilst still active at /usr/local/share/perl5/Ora2Pg.pm line 15527. FLAGS 0x580495: COMSET Active Warn RaiseError PrintWarn LongTruncOk ERR 1 ERRSTR '[Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (SQL-08S01) [Microsoft][ODBC Driver 18 for SQL Server]Communication link failure (SQL-08S01) [Microsoft][ODBC Driver 18 for SQL Server]Communication link failure (SQL-08S01) [Microsoft][ODBC Driver 18 for SQL Server]Communication link failure (SQL-08S01)' PARENT DBI::db=HASH(0x55a4d125f540) KIDS 0 (0 Active) LongReadLen 1047552 NUM_OF_FIELDS 27 NUM_OF_PARAMS 0

Any idea how I can run this in parallel or improve the performance of the migration, at current rate it looks like it will take a couple of weeks to migrate all 1000+ tables.

Any ideas of help are welcome.

My next try will be to see if I can split the DB up and run multiple instance with different config, but that will not solve the very slow rate of transfer.

[darold]

Yes this is the problem with the ODBC driver, you can try to use the tds_fdw foreign data wrapper to import data.