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:
如果Postgresql和SQL Server上的数据库中有相同的数据库模式(表、主键、索引和触发器是相同的)，那么将数据从一个数据库移动到另一个数据库的最佳方式是什么?目前我们有一个内部的。net程序，通过两个ODBC连接完成以下工作:
- read a row from source database table 1
- 从源数据库表1中读取一行
- construct an insert statement
- 构造一个insert语句
- write a row into destination database table 1
- 将一行写入目标数据库表1
- Go to 1 if there are more rows in the table
- 如果表中有更多的行，则为1。
- Move to next table in database and go to 1
- 移到数据库中的下一个表，转到1
Needless to say: this is a very slow process and I would be interested if there was a better/faster solution to this?
不用说:这是一个非常缓慢的过程，如果有更好/更快的解决方案，我会很感兴趣的。
2 个解决方案
#1
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).
如果是“一次性”迁移，就会有一个SQL Server的工具，它允许您在数据库之间移动数据(我现在不在Windows机器上，所以不能告诉您它的名称——类似于导入/导出工具)。
If it's an ongoing synchronisation, you can look at the MS Sync framework, which plays nice with SQL Server and Postgres.
如果是正在进行的同步，您可以查看MS Sync框架，它与SQL Server和Postgres配合得很好。
#2
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.
答案是批量出口和批量装载。通过使用PostgreSQL https://www.postgresql.org/docs/current/static/sql-copy.html中的复制命令以CSV格式从表中转储数据，然后使用SQLServer导入CSV文件中的批量插入到SQLServer中，您可以更快地完成这一操作。一个经验法则是利用并行处理过程。检查是否可以将数据加载到CSV并与SQL Server并行，如果有许多表，那么还可以在单独的表的级别上进行并行。顺便说一下，逐行加载或迁移数据是最慢的方法之一。
#1
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).
如果是“一次性”迁移，就会有一个SQL Server的工具，它允许您在数据库之间移动数据(我现在不在Windows机器上，所以不能告诉您它的名称——类似于导入/导出工具)。
If it's an ongoing synchronisation, you can look at the MS Sync framework, which plays nice with SQL Server and Postgres.
如果是正在进行的同步，您可以查看MS Sync框架，它与SQL Server和Postgres配合得很好。
#2
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.
答案是批量出口和批量装载。通过使用PostgreSQL https://www.postgresql.org/docs/current/static/sql-copy.html中的复制命令以CSV格式从表中转储数据，然后使用SQLServer导入CSV文件中的批量插入到SQLServer中，您可以更快地完成这一操作。一个经验法则是利用并行处理过程。检查是否可以将数据加载到CSV并与SQL Server并行，如果有许多表，那么还可以在单独的表的级别上进行并行。顺便说一下，逐行加载或迁移数据是最慢的方法之一。