从SQL Server逐渐迁移到PostgreSQL [英] Gradually migrate from SQL Server to PostgreSQL

问题描述

公司有许多在SQL Server上运行的应用程序。数据库有点混乱。

Company has numerous applications running on SQL Server. The database is a bit of a mess.

目标是逐步从SQL Server迁移到PostgreSQL（不可选择其他SQL Server实例）

The goal is to gradually move from SQL Server to PostgreSQL (another SQL Server instance is not an option)

一种理想的情况是，如果新的应用程序可以连接到PostgreSQL，创建新的表结构，但仍然能够使用/与来自旧版SQL Server的数据进行交互（不可以选择将应用程序连接到两个数据库服务器）。

An ideal scenario would be if new applications could connect to PostgreSQL, create a new table structure, but still be able to use/interact with data from legacy SQL Server (an app connecting to two database servers is not an option).

外国数据包装器似乎不是一种选择，因为该技术非常不成熟，对于PostgreSQL，外部表是只读的。

Foreign data wrappers do not seem to be an option, since the technology is very immature and in case of PostgreSQL, the foreign tables are read-only.

另一个疯狂的想法是从SQL Server实例连接到PostgreSQL，新的应用程序将连接到SQL Server，但使用PostgreSQL的外部数据库。该外部数据库（我想是）可以访问主机的数据库对象。到了某个时候，开发人员会将所有新应用程序从SQL Server切换到PostgreSQL。

Another wild idea is to connect from SQL Server instance to PostgreSQL, new applications would connect to SQL Server, but use PostgreSQL's foreign database. That foreign database ( I guess ) would have access to host's database objects. And at one point developers would switch all the new apps from SQL Server to PostgreSQL.

当然可以尝试同步数据了。

And of course there is the possibility to try and sync the data.

哪个会是最好的选择？

推荐答案

您建议的所有方法都是痛苦和迁移失败。如果您尝试使用这种方法，人们将对PostgreSQL多么糟糕，缓慢和不可靠感到厌恶。想要保留SQL Server的人将是一个伟大的政治举措，但不是迁移到PostgreSQL的好方法。

Everything you suggest is a recipe for pain and failed migrations. People will rant and rave about how awful, slow and unreliable PostgreSQL is if you try to use this approach. It'd be a great political move by someone who wanted to keep SQL Server, but not a good way to migrate to PostgreSQL.

有一个读/写外部数据包装器即将推出更新的Pg版本，但最初仅支持其他PostgreSQL服务器。由于需要转换sqlstate和错误消息，搜索条件等等，因此支持MS SQL将会困难很多，因此任何包装器无疑都将受到很大的限制，并且性能不佳。就像您说的那样，此时FDW支持还太不成熟。

There's a read/write foreign data wrapper coming for newer Pg versions, but it'll initially only support other PostgreSQL servers. Supporting MS SQL would be a lot harder due to the need to translate sqlstates and error messages, search conditions, and more, so any wrapper would no doubt be quite limited and have less than great performance. As you say, FDW support is too immature at this point anyway.

尝试执行如下所示的混合操作会丢失很多东西：

There are just so many things you lose by trying to do a hybrid like this:

• 不强制执行外键完整性

• No foreign key integrity enforcement

两侧的数据类型可能表现不尽相同，因此数据在一侧可以正常，而在另一侧则可以。考虑时间戳/日期。

Data types on each side might not behave 100% the same so data could be OK on one side and not on the other. Think timestamps/dates.

有效的联接将需要极其复杂的外部数据包装器-因此通常会发生的情况是整个表将被获取然后联接反对本地。性能会很糟糕。

Efficient joins would require an extremely sophisticated foreign data wrapper - so what'll usually happen is that the whole table will get fetched then joined against locally. Performance will be terrible.

在执行除最琐碎的任务之外的任何其他操作时，编写查询都会成为噩梦。函数名称各不相同，等等。

Writing queries becomes a nightmare when you're doing anything but the most trivial task. Function names differ, etc.

您失去或削弱了许多ACID属性，并且/或者必须使用两阶段提交，这会降低性能。

You lose or weaken many ACID properties and/or must use two phase commit, which sucks for performance.

严重的是，不要这样做。

Seriously, don't do this.

同步数据库可能甚至更糟-除非是一种方法，否则它将成为丢失更新，重新出现删除的行甚至更糟的秘诀。双向同步非常困难。

Syncing the DBs is probably even worse - unless it's one way, it's going to be a recipe for lost updates, deleted rows reappearing, and worse. Two-way sync is extremely hard.

开始使您的应用能够同时在两台服务器上运行，从而为移动做好准备。一次。准备好要在Pg上运行的应用后，请开始使用迁移后的实时数据进行一些负载测试和可靠性测试。 然后考虑迁移，但已制定计划，如果发现最后一刻会迫使您延迟的问题，那么该举动就会逆转。

Start preparing your apps for a move by making them able to run on both servers, but only one at a time. Once you've got the app ready to run on Pg, start doing some load testing and reliability testing with a migrated copy of the live data. then think about migrating, but have plans for how to reverse the move if you find last minute problems that force you to delay.

如果如果要向应用程序中添加全新的部件，如果它们根本不与数据库中的其他数据进行交互，则将它们包含在Pg中可能是合理的。但是，这几乎是不可能的，当您告诉系统管理员您现在需要跨两个独立数据库的原子快照时，您的系统管理员仍然会讨厌您。

If you're adding entirely new parts to the app it might be reasonable to have them in Pg if they don't interact with the other data in the DB at all. That's pretty unlikely, though, and your sysadmins will still hate you when you tell them that you now need an atomic snapshot across two separate databases...

这篇关于从SQL Server逐渐迁移到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！