将PostgreSQL数据库迁移到SQL Server数据库的最简单方法[英] Easiest way to migrate a PostgreSQL database into an SQL Server one

本文是小编为大家收集整理的关于将PostgreSQL数据库迁移到SQL Server数据库的最简单方法的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个 PostgreSQL 数据库,我想将它迁移到 SQL Server —— 架构和数据.我很穷,所以我不想付任何钱.我也很懒,所以我不想做太多的工作.目前我正在逐表做这个,大约有100个表要做.这是非常乏味的.

有什么技巧可以满足我的要求吗?

推荐答案

我相信你可能会因为从 PostgreSQL 生成一个简单的 SQL 脚本(理论上)几乎可以再次运行而非常容易而被否决数据库管理系统.如果一个人是一个普通的 PostgreSQL 用户,它听起来就像一个愚蠢的问题.

这不公平,因为事实证明这实际上是一个中等难度的问题(尽管更多是由于 SQL Server 的奇怪语法和接口,而不是 PostgreSQL 的任何失败).

您应该能够在此 Serverfault 页面的已接受答案中找到一些有用的信息:http://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005.

如果您可以在没有数据的情况下转换架构,则可以使用此命令缩短数据步骤:

pg_dump --data-only --column-inserts your_db_name > data_load_script.sql

这个加载会很慢,但是 --column-inserts 选项会为每一行数据生成最通用的 INSERT 语句并且应该是兼容的.

编辑:关于转换架构的建议如下:

我将从转储架构开始,但删除与所有权或权限有关的任何内容.这应该足够了:

pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql

编辑此文件以添加"BEGIN TRANSACTION;"行到开头和"回滚交易";到最后.现在您可以加载它并在 SQL Server 的查询窗口中运行它.如果遇到任何错误,请确保转到文件底部,突出显示 ROLLBACK 语句并运行它(在突出显示语句时按 F5).

基本上,您必须解决每个错误,直到脚本干净地运行.然后您可以将"ROLLBACK TRANSACTION"更改为"COMMIT TRANSACTION"并最后一次运行.

不幸的是,我无法帮助您解决可能会看到的错误,因为我从未从 PostgreSQL 转到 SQL Server,只是反过来.但是,有些事情我认为会成为问题(显然,不是详尽的清单):

  • PostgreSQL 通过使用 DEFAULT 将 NOT NULL INTEGER 字段链接到 SEQUENCE 来自动增加字段.在 SQL Server 中,这是一个 IDENTITY 列,但它们并不完全相同.我不确定它们是否等效,但如果您的原始模式充满了"id"字段,您可能会遇到一些麻烦.我不知道 SQL Server 是否有 CREATE SEQUENCE,所以您可能需要删除它们.
  • 数据库函数/存储过程不会在 RDBMS 平台之间进行转换.您需要删除所有 CREATE FUNCTION 语句并手动翻译算法.
  • 注意数据文件的编码.我是 Linux 人,所以我不知道如何在 Windows 中验证编码,但您需要确保 SQL Server 期望的内容与您从 PostgreSQL 导入的文件相同._pg_dump_ 有一个选项 --encoding= 可以让你设置一个特定的编码.我似乎记得 Windows 倾向于对 Unicode 使用两字节的 UTF-16 编码,而 PostgreSQL 使用 UTF-8.由于 UTF-16 输出,我在从 SQL Server 到 PostgreSQL 的过程中遇到了一些问题,因此值得研究.
  • PostgreSQL 数据类型 TEXT 只是一个没有最大长度的 VARCHAR.在 SQL Server 中,TEXT 很复杂(并且已弃用).原始架构中声明为 TEXT 的每个字段都需要针对适当的 SQL Server 数据类型进行审核.
  • SQL Server 为 UNICODE 数据提供了额外的数据类型.我对它不够熟悉,无法提出建议.我只是指出这可能是个问题.

本文地址:https://www.itbaoku.cn/post/597527.html

问题描述

I have a PostgreSQL database that I want to move to SQL Server -- both schema and data. I am poor so I don't want to pay any money. I am also lazy, so I don't want to do very much work. Currently I'm doing this table by table, and there are about 100 tables to do. This is extremely tedious.

Is there some sort of trick that does what I want?

推荐答案

I believe that you may have gotten down-votes due to amazing ease of generating a simple SQL script from PostgreSQL that can (theoretically) be run again just about any DBMS. If one is a regular PostgreSQL user, it sounds like a dumb question.

That's not fair since it turns out this is actually a moderately hard problem (although more due to SQL Server's odd syntax and interface than any failing of PostgreSQL).

You should be able to find some useful information in the accepted answer in this Serverfault page: http://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005.

If you can get the schema converted without the data, you may be able to shorten the steps for the data by using this command:

pg_dump --data-only --column-inserts your_db_name > data_load_script.sql

This load will be quite slow, but the --column-inserts option generates the most generic INSERT statements possible for each row of data and should be compatible.

EDIT: Suggestions on converting the schema follows:

I would start by dumping the schema, but removing anything that has to do with ownership or permissions. This should be enough:

pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql

Edit this file to add the line "BEGIN TRANSACTION;" to the beginning and "ROLLBACK TRANSACTION;" to the end. Now you can load it and run it in a query window in SQL Server. If you get any errors, make sure you go to the bottom of the file, highlight the ROLLBACK statement and run it (by hitting F5 while the statement is highlighted).

Basically, you have to resolve each error until the script runs through cleanly. Then you can change the "ROLLBACK TRANSACTION" to "COMMIT TRANSACTION" and run one final time.

Unfortunately, I cannot help with which errors you may see as I have never gone from PostgreSQL to SQL Server, only the other way around. Some things that I would expect to be an issue, however (obviously, NOT an exhaustive list):

  • PostgreSQL does auto-increment fields by linking a NOT NULL INTEGER field to a SEQUENCE using a DEFAULT. In SQL Server, this is an IDENTITY column, but they're not exactly the same thing. I'm not sure if they are equivalent, but if your original schema is full of "id" fields, you may be in for some trouble. I don't know if SQL Server has CREATE SEQUENCE, so you may have to remove those.
  • Database functions / Stored Procedures do not translate between RDBMS platforms. You'll need to remove any CREATE FUNCTION statements and translate the algorithms manually.
  • Be careful about encoding of the data file. I'm a Linux person, so I have no idea how to verify encoding in Windows, but you need to make sure that what SQL Server expects is the same as the file you are importing from PostgreSQL. _pg_dump_ has an option --encoding= that will let you set a specific encoding. I seem to recall that Windows tends to use two-byte, UTF-16 encoding for Unicode where PostgreSQL uses UTF-8. I had some issue going from SQL Server to PostgreSQL due to UTF-16 output so it would be worth researching.
  • The PostgreSQL datatype TEXT is simply a VARCHAR without a max length. In SQL Server, TEXT is... complicated (and deprecated). Each field in your original schema that are declared as TEXT will need to be reviewed for an appropriate SQL Server data type.
  • SQL Server has extra data types for UNICODE data. I'm not familiar enough with it to make suggestions. I'm just pointing out that it may be an issue.