如何将PostgreSQL数据库迁移到SQLServer中? [英] How to migrate a PostgreSQL database into a SQLServer one?
问题描述
我有一个要迁移到SQL Server的PostgreSQL数据库-模式和数据.我很穷,所以我不想付任何钱.我也很懒,所以我不想做很多工作.目前,我正在逐表执行此操作,大约有100个表需要执行.这非常繁琐.
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?
推荐答案
您应该能够在此Serverfault页面的已接受答案中找到一些有用的信息:
You should be able to find some useful information in the accepted answer in this Serverfault page: https://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
此加载将非常慢,但是--column-inserts
选项会为每行数据生成最通用的INSERT语句,并且应该兼容.
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.
有关转换架构的建议如下:
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
编辑此文件,将BEGIN TRANSACTION;
行添加到开头,将ROLLBACK TRANSACTION;
行添加到结尾.现在,您可以加载它并在SQL Server的查询窗口中运行它.如果遇到任何错误,请确保转到文件底部,突出显示ROLLBACK语句并运行它(在突出显示该语句的同时按F5键).
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).
基本上,您必须解决每个错误,直到脚本干净运行为止.然后,您可以将ROLLBACK TRANSACTION
更改为COMMIT TRANSACTION
并最后运行一次.
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.
不幸的是,我从来没有从PostgreSQL迁移到SQL Server,就从相反的角度来看,我无法帮助您解决可能出现的错误.不过,我希望有些事情会成为问题(显然,这不是详尽的清单):
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通过使用
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在PostgreSQL使用UTF-8的情况下倾向于将两字节UTF-16编码用于Unicode.由于UTF-16输出,从SQL Server到PostgreSQL我遇到了一些问题,因此值得研究. - PostgreSQL数据类型
TEXT
只是一个没有最大长度的VARCHAR
.在SQL Server中,TEXT
很复杂(不建议使用).原始架构中声明为TEXT
的每个字段都需要检查以获取适当的SQL Server数据类型. - SQL Server为
UNICODE
数据提供了额外的数据类型.我对它不够熟悉,无法提出建议.我只是指出这可能是个问题.
- PostgreSQL does auto-increment fields by linking a
NOT NULL INTEGER
field to aSEQUENCE
using aDEFAULT
. In SQL Server, this is anIDENTITY
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 hasCREATE 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 aVARCHAR
without a max length. In SQL Server,TEXT
is... complicated (and deprecated). Each field in your original schema that are declared asTEXT
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.
这篇关于如何将PostgreSQL数据库迁移到SQLServer中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!