从 MySQL 切换到 PostgreSQL - 提示、技巧和陷阱？

Question

我正在考虑从 MySQL 切换到 PostgreSQL。

使用 PostgreSQL 有哪些提示、技巧和陷阱？

MySQLer 应该注意什么？

另请参阅：PostgreSQL 与 MySQL 有什么不同？
另请参阅：从 MySQL 迁移到 PostgreSQL

注意 - 我不认为这是重复的。 特别是答案的类型非常不同，这里的答案有更多的实现细节，这正是我正在寻找的

Answer 1

我自己刚刚经历过这个，好吧，我仍然是......

• 区分大小写的文本
• 缺少INSERT IGNORE和REPLACE
• 几乎到处都需要显式转换
• 没有反引号
• LOAD DATA INFILE< /code> （COPY 很接近，但还不够接近）
• 将 autoincrement 更改为 SERIAL
• 虽然在 MySQL 中格式不好，但在 Postgres 中，一个 ON 子句的 >INNER JOIN 不可能发生，使用 CROSS JOIN 或类似的
• COUNT(*) 可能会很疯狂慢
• 数据库是用字符集编码的，而不是表
• 你可以有多个数据库，有多个模式（MySQL 实际上只有一个数据库和多个模式）
• 分区是不同的
• MySQL interval 与 Postgres interval< /code> （时间间隔）
• 隐式列重命名，Postgres 需要 AS
• 无法在 Postgres 中同时更新多个表
• Postgres 功能很强大。 所以没有CALL proc();; 将 proc() 重写为函数并 SELECT proc();。

Answer 2

这将是一项艰巨的任务，因为您必须测试整个代码库 - 每个查询，任何地方，

• 语法
• 正确的行为（即返回相同的结果）
• 性能 - 例如是否有任何性能回归/改进，以及你能应付他们吗？
• 错误处理 - 它们在错误条件下的行为不同，也许您的代码依赖于特定的错误代码

在操作上，您需要查看：

• 备份/恢复
• 磁盘空间利用率
• 内存利用率
• 一次性数据迁移 - 可能会很大/时间消耗任务
• 失败时的回滚计划
• 监控 - 您如何监控 MySQL，以及是否可以调整这些方法
• （如果相关） - 复制

在考虑这样的举措之前，您肯定必须进行大量的性能测试。

这些成本使得迁移到不同的数据库对于大多数重要的应用程序来说过于昂贵。 仔细考虑一下好处和执行上述所有操作的巨大成本。

如果您在一个不平凡的应用程序中花费不到三个月的时间，我会感到惊讶，在此期间您将无法继续常规开发。

Answer 3

您可以尝试包含最常见问题的 PostgreSQL 陷阱 。 一般来说，PostgreSQL 文档也非常好，所以也请把它放在枕头下。

另外，pgsql wiki 上的从 MySQL 转换为 PostgreSQL。

Answer 4

我发现这个脚本将连接到您的 MySQL 数据库和 PostgreSQL 数据库并仅传输内容。 它对我来说就像一种魅力。

https://github.com/philipsoutham/py-mysql2pgsql

通过

$ pip install py-mysql2pgsql

在任意文件夹中运行

$ py-mysql2pgsql

安装，它将创建一个模板设置文件（mysql2pgsql.yml），您可以编辑并在其中输入数据库的详细信息。

我必须安装 argparse 才能正常工作。

$ pip install argparse

填写完数据库详细信息后，只需

$ py-mysql2pgsql

在与设置文件相同的文件夹中再次运行它，然后，您就完成了。 它没有在屏幕上打印任何内容，但之后我的数据库被完全复制。

Answer 5

在转换之前，通过启动服务器将 MySQL 设置为 ANSI 严格性： --transaction-isolation=SERIALIZABLE --sql-mode=ANSI

确保您没有使用 MyIsam 表。

MySQL 允许大量不应该进行的转换； pg 需要演员阵容。

您的存储过程、函数和触发器将必须重新编写。 pg 为您提供了这些语言的选择，但您必须安装这些语言； 它不像 MySQL 那样用户友好。

pg 将只允许在选择列表中包含 group by 或聚合的列； 如果您这样做，MySQL 将通过选择组中的第一个值来作弊。

MySQL 添加了一堆扩展：不等于运算符可以是 != 就像 C 中一样，它允许 '&&' 作为“和”、“||”的同义词 表示“或”等。特别是，pg 使用“||” 表示字符串连接。

基本上，pg 是非常严格的 ANSI，而 MySQL 不是。 我强烈建议在转换为 pg 之前让您的 MySQL 尽可能严格地符合 ANSI，然后在运行应用程序时检查是否有任何警告。

Answer 6

除了移动数据库结构之外，你无法避免手动调整...

最可靠的传输数据方法（逐表传输，前提是结构相同）：

mysql --default-character-set=utf8 -e "SELECT * FROM mytable" > mytable.txt

psql
\copy mytable from '/path/to/mytable.txt' WITH NULL AS 'NULL';

最近一直在尝试其他方法（比如带有大量选项的 mysqldump + sed 等），但没有什么比这更好的了。

当结构沿途发生变化时，这种方法还提供了一定的灵活性 - 只需编写适当的 SELECT 即可。