1

If we have the same database schema in a database on Postgresql and SQL Server (table, primary keys, indexes and triggers are the same) what would be the best way to move data from one database to another? Currently we have one in-house .NET program that does the following through two ODBC connections:

  1. read a row from source database table 1
  2. construct an insert statement
  3. write a row into destination database table 1
  4. Go to 1 if there are more rows in the table
  5. Move to next table in database and go to 1

Needless to say: this is a very slow process and I would be interested if there was a better/faster solution to this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dragan Matic
  • 329
  • 1
  • 3
  • 10
  • http://stackoverflow.com/questions/3498877/how-do-i-move-a-database-from-one-server-to-another-in-pgsql?answertab=votes#tab-top Good answered. – Tejas Tank Dec 20 '13 at 13:23
  • I would try to tweak the [`pg_dump`](https://www.postgresql.org/docs/current/static/app-pgdump.html) output. – Clodoaldo Neto Jul 22 '16 at 12:15

2 Answers2

2

If it's a "one off" migration, there's a tool you get with SQL Server which allows you to move data around between databases (I'm not on a Windows machine right now, so can't tell you what it's called - something like import/export tool).

If it's an ongoing synchronisation, you can look at the MS Sync framework, which plays nice with SQL Server and Postgres.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

The answer is bulk export and bulk loading. You can go much faster by using the copy command in PostgreSQL https://www.postgresql.org/docs/current/static/sql-copy.html to dump data from the tables in the CSV format and then use the bulk insert in SQLServer Import CSV file into SQL Server. A rule of thumb is to harness parallelism for the process. Check if you can load the data ins CSV in parallel to SQL Server and if you have many tables then you can also have a parallelism on the level of separate tables. By the way, loading or migrating data row by row is one of the slowest ways.

Community
  • 1
  • 1
ady
  • 1,108
  • 13
  • 19