search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Migrating From SQL Server to Postgres with Ora2pg Is very Slow #1776

Open ijedoya68 opened 2 months ago

ijedoya68 commented 2 months ago

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 commented 1 month ago

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