从 MySQL 迁移到 PostgreSQL

Migrating from MySQL to PostgreSQL

提问by Steve M

We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons.

我们目前正在将 MySQL 用于我们正在构建的产品，并且渴望尽快迁移到 PostgreSQL，主要是出于许可原因。

Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improvements/losses, major hurdles in converting SQL and stored procedures, etc.

有没有其他人做过这样的举动？我们的数据库是应用程序的命脉，最终将存储数 TB 的数据，所以我很想知道性能改进/损失的经验，转换 SQL 和存储过程的主要障碍等。

Edit: Just to clarify to those who have asked why we don't like MySQL's licensing. We are developing a commercial product which (currently) depends on MySQL as a database back-end. Their license states we need to pay them a percentage of our list price per installation, and not a flat fee. As a startup, this is less than appealing.

编辑：只是向那些询问我们为什么不喜欢 MySQL 许可的人澄清一下。我们正在开发一个商业产品，它（目前）依赖 MySQL 作为数据库后端。他们的许可证规定，我们需要按每次安装的标价向他们支付一定比例的费用，而不是固定费用。作为一家初创公司，这并不吸引人。

回答by Micha? Rudnicki

Steve, I had to migrate my old application the way around, that is PgSQL->MySQL. I must say, you should consider yourself lucky ;-) Common gotchas are:

史蒂夫，我不得不迁移我的旧应用程序，即 PgSQL->MySQL。我必须说，你应该认为自己很幸运 ;-) 常见的问题是：

• SQL is actually pretty close to language standard, so you may suffer from MySQL's dialect you already know
• MySQL quietly truncates varchars that exceed max length, whereas Pg complains - quick workaround is to have these columns as 'text' instead of 'varchar' and use triggers to truncate long lines
• double quotes are used instead of reverse apostrophes
• boolean fields are compared using IS and IS NOT operators, however MySQL-compatible INT(1) with = and <> is still possible
• there is no REPLACE, use DELETE/INSERT combo
• Pg is pretty strict on enforcing foreign keys integrity, so don't forget to use ON DELETE CASCADE on references
• if you use PHP with PDO, remember to pass a parameter to lastInsertId() method - it should be sequence name, which is created usually this way: [tablename]_[primarykeyname]_seq

• SQL实际上非常接近语言标准，所以你可能会受到你已经知道的MySQL方言的影响
• MySQL 悄悄地截断超过最大长度的 varchars，而 Pg 抱怨 - 快速解决方法是将这些列作为“文本”而不​​是“varchar”并使用触发器来截断长行
• 使用双引号代替反撇号
• 布尔字段使用 IS 和 IS NOT 运算符进行比较，但是 MySQL 兼容的 INT(1) 与 = 和 <> 仍然是可能的
• 没有 REPLACE，使用 DELETE/INSERT 组合
• Pg 在强制执行外键完整性方面非常严格，所以不要忘记在引用上使用 ON DELETE CASCADE
• 如果您将 PHP 与 PDO 一起使用，请记住将参数传递给 lastInsertId() 方法 - 它应该是序列名称，通常是这样创建的：[tablename]_[primarykeyname]_seq

I hope that helps at least a bit. Have lots of fun playing with Postgres!

我希望这至少有一点帮助。玩 Postgres 玩得很开心！

回答by Grant Johnson

I have done a similar conversion, but for different reasons. It was because we needed better ACID support, and the ability to have web users see the same data they could via other DB tools (one ID for both).

我做了类似的转换，但出于不同的原因。这是因为我们需要更好的 ACID 支持，以及让 Web 用户看到他们可以通过其他数据库工具看到的相同数据的能力（两者都有一个 ID）。

Here are the things that bit us:

以下是困扰我们的事情：

1. MySQL does not enforce constraints as strictly as PostgreSQL.
2. There are different date handling routines. These will need to be manually converted.
3. Any code that does not expect ACID compliance may be an issue.

1. MySQL 不像 PostgreSQL 那样严格执行约束。
2. 有不同的日期处理例程。这些将需要手动转换。
3. 任何不期望符合 ACID 的代码都可能是一个问题。

That said, once it was in place and tested, it was much nicer. With correct locking for safety reasons and heavy concurrent use, PostgreSQL performed better than MySQL. On the things where locking was not needed (read only) the performance was not quite as good, but it was still faster than the network card, so it was not an issue.

也就是说，一旦它就位并经过测试，它就会好得多。出于安全原因和大量并发使用的正确锁定，PostgreSQL 的表现优于 MySQL。在不需要锁定（只读）的情况下，性能不是很好，但它仍然比网卡快，所以这不是问题。

Tips:

提示：

• The automated scripts in the contrib directory are a good starting point for your conversion, but will need to be touched a little usually.
• I would highly recommend that you use the serializable isolation level as a default.
• The pg_autodoc tool is good to really see your data structures and help find any relationships you forgot to define and enforce.

• contrib 目录中的自动化脚本是转换的一个很好的起点，但通常需要稍微接触一下。
• 我强烈建议您使用可序列化隔离级别作为默认值。
• pg_autodoc 工具非常适合真正查看您的数据结构并帮助找到您忘记定义和强制执行的任何关系。

回答by Patrick Desjardins

We did a move from a MySQL3 to PostgreSQL 8.2 then 8.3. PostgreSQL has the basic of SQL and a lot more so if your MYSQL do not use fancy MySQL stuff you will be OK.

我们从 MySQL3 迁移到 PostgreSQL 8.2，然后是 8.3。PostgreSQL 有 SQL 的基础知识，还有很多，所以如果你的 MYSQL 不使用花哨的 MySQL 东西，你会没事的。

From my experience, our MySQL database (version 3) doesn't have Foreign Key... PostgreSQL lets you have them, so we had to change that... and it was a good thing and we found some mistake.

根据我的经验，我们的 MySQL 数据库（版本 3）没有外键……PostgreSQL 允许您拥有它们，所以我们不得不改变它……这是一件好事，但我们发现了一些错误。

The other thing that we had to change was the coding (C#) connector that wasn't the same in MySQL. The MySQL one was more stable than the PostgreSQL one. We still have few problems with the PostgreSQL one.

我们必须更改的另一件事是与 MySQL 中不同的编码 (C#) 连接器。MySQL 比 PostgreSQL 更稳定。我们对 PostgreSQL 的问题仍然很少。