从MySQL切换到PostgreSQL - 提示、技巧和注意事项？

Question

我在考虑从MySQL转换到PostgreSQL。

对于使用PostgreSQL，你有什么技巧、诀窍和需要注意的地方？

MySQL用户应该注意什么？

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

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

- Toby Hede

---

仅供参考：这实际上与Oracle和Sun的新闻无关。我有一个需要多个数据库的Rails应用程序，并考虑使用PostgreSQL模式。 - Toby Hede

请参见：https://dev59.com/fHRB5IYBdhLWcg3wFz8g - vartec

Answer 1

我刚刚自己经历了这个问题，目前仍在进行中...

• 大小写敏感的文本
• 缺少INSERT IGNORE和REPLACE
• 几乎需要显式转换类型
• 没有反引号
• LOAD DATA INFILE（COPY接近，但不足以替代）
• 将autoincrement更改为SERIAL
• 虽然在MySQL中是不好的形式，在Postgres中，INNER JOIN没有ON子句是不可能发生的，使用CROSS JOIN或类似语法
• COUNT(*) 可能非常慢
• 数据库是用字符集编码的，而不是表格
• 您可以拥有多个数据库，具有多个模式（MySQL实际上只有一个数据库和多个模式）
• 分区方式不同
• MySQL中的interval与Postgres中的interval（用于时间间隔）不同
• 隐式列重命名，Postgres需要AS
• 无法同时更新多个表
• Postgres函数功能强大。因此，不存在CALL proc();语法；将proc()重写为函数，并使用SELECT proc();。

- rfusca

---

@OMG Ponies：在现代PG版本中，您不必显式使用序列来获取自动 ID。serial 和 bigserial 类型隐藏了所有语义，但在底层仍然是序列及其所有功能。它们创建一个自动命名的序列，并将列的默认值设置为 nextval。 - rfusca

我来自Oracle，所以我更喜欢序列。序列/大序列功能是在8.x引入的吗？ - OMG Ponies

@OMG Ponies：手册中早在7.1版本就有了。serial/bigserial实际上只是一个“魔术”技巧。在初始创建之后，它会显示为整数/大整数和序列。 - rfusca

你是说 Count(*) 很慢，但是比如 Count(id) 就能相当快地工作？还是说 PostgreSQL 的 Count() 总体上就很慢？ - Mr_Chimp

@Mr_Chimp 在Postgres中，count()函数由于MVCC的原因必须扫描磁盘上的所有行，所以速度较慢。在9.2中，由于可以在某些情况下使用索引仅扫描，因此可以加快速度，但事实是通常情况下count()函数比MySQL中的要慢。然而，权衡之处在于，你获得的读者和写者不会相互等待，这对某些工作负载可能是巨大的优势。 - I GIVE CRAP ANSWERS

有关 count(*) 性能提升的信息，请参见此处和此处。 - deFreitas

Answer 2

这将是一项巨大的任务，因为您需要测试整个代码库-每个查询都要测试，无论在哪里，以检查以下内容：

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

从操作上来看，您需要考虑以下几点：

• 备份/还原
• 磁盘空间利用率
• 内存利用率
• 一次性数据迁移-可能是一个大型/耗时的任务
• 如果失败，回滚计划
• 监视-如何监视您的MySQL，这些方法是否可以被适应
• （如果相关）-复制

在考虑此类迁移之前，您肯定需要进行大量性能测试。

对于大多数非微不足道的应用程序而言，这些成本使得切换到其他数据库太昂贵了。请仔细权衡利弊，考虑到上述巨大成本。

我会感到惊讶，如果在非微不足道的应用程序中，此过程需要少于三个月的时间，在此期间，您将无法继续进行常规开发。

- MarkR

---

这是一个新项目，因此在这个阶段迁移没有任何费用。但这些都是值得考虑的极好建议。 - Toby Hede

Answer 3

您可以尝试阅读PostgreSQL gotchas，其中包含最常见的问题。此外，PostgreSQL文档也相当不错，建议您也要仔细阅读。

另外，在pgsql wiki上有从MySQL转换到PostgreSQL的相关内容。

- janneb

Answer 4

我找到了这个脚本，它可以连接你的MySQL数据库和PostgreSQL数据库，然后将内容直接转移。对我来说，它非常好用。 https://github.com/philipsoutham/py-mysql2pgsql 安装方式：

$ pip install py-mysql2pgsql

运行

$ py-mysql2pgsql

在任意文件夹中，它会为您创建一个模板设置文件(mysql2pgsql.yml)，您可以在其中编辑并输入数据库的详细信息。

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

$ pip install argparse

当您填写完数据库详细信息后，只需再次运行即可。

$ py-mysql2pgsql

在与设置文件相同的文件夹中，完成这个操作非常简单。没有任何内容显示在屏幕上，但是我的数据库之后被完全复制了。

- Dahlo

---

我刚刚发现我无法在我的论坛中添加任何帖子或评论（使用数据库的软件），但是通过将postgresql数据库导出到文件，删除并创建数据库，然后导入文件来解决了这个问题。谁知道，也许我第一次导入时只是拥有错误的用户权限，因为我是在我的测试数据库中导入的。 - Dahlo

Answer 5

在进行转换之前，请通过以下方式将MySQL设置为ANSI严格模式：--transaction-isolation=SERIALIZABLE --sql-mode=ANSI

确保您没有使用MyIsam表。

MySQL允许进行很多不应该进行的转换；pg需要进行转换。

您的存储过程、函数和触发器将需要重新编写。pg为这些提供了多种语言选择，但您必须安装这些语言；这并不像MySQL那样用户友好。 pg只允许在选择列表中包含分组依据或聚合的列；如果您这样做，MySQL会通过选择组中的第一个值来欺骗。

MySQL添加了许多扩展功能：不等于运算符可以是C语言中的“!=”，它允许“&&”作为“and”的同义词，“||”作为“or”等。特别地，pg使用“||”表示字符串连接。

基本上，pg相当严格地遵循ANSI标准，而MySQL则不是。我强烈建议在转换到pg之前尽可能使您的MySQL严格遵循ANSI标准，然后在运行应用程序时检查任何警告。

- tpdi

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语句即可。

- Ivka