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

Switching from MySQL to PostgreSQL - tips, tricks and gotchas?

提问by Toby Hede

I am contemplating a switch from MySQL to PostgreSQL.

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

What are your tips, tricks and gotchas for working with PostgreSQL?

您使用 PostgreSQL 的技巧、窍门和陷阱是什么？

What should a MySQLer look out for?

MySQLer 应该注意什么？

See also: How different is PostgreSQL to MySQL?
See also: Migrate from MySQL to PostgreSQL

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

Note - I don't think this is a duplicate. In particular the type of answers are quite diffferent and the responses here have much more implementation detail, which is what I was looking for

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

回答by rfusca

Just went through this myself, well I still am...

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

• Case sensitive text
• Lack of INSERT IGNOREand REPLACE
• Explicit casting needed almost everywhere
• No backticks
• LOAD DATA INFILE(COPYis close, but not close enough)
• Change autoincrementto SERIAL
• Although bad form in MySQL, in Postgres, an INNER JOINwithout an ONclause can't happen, use CROSS JOINor the like
• COUNT(*)can be crazy slow
• Databases are encoded with character sets, not tables
• You can have multiple databases, with multiple schemas (MySQL really has just one database and multiple schema)
• Partitioning is different
• MySQL intervalvs. Postgres interval(for time intervals)
• Implicit column renaming, Postgres requires AS
• Cannot update multiple tables at the same time in Postgres
• Postgres functions are powerful. So there is no CALL proc();; rewrite proc()as a function and SELECT proc();.

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

回答by MarkR

It is going to be a massive task as you'll have to test your entire code-base - every single query, anywhere, for

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

• Syntax
• Correct behaviour (i.e. returns the same results)
• Performance - e.g. are there any performance regressions / improvements, and can you handle them?
• Error handling - they do not behave the same under error conditions, maybe your code was relying on specific error codes

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

Operationally you will need to look at:

在操作上，您需要查看：

• Backup/restore
• Disc space utilisation
• Memory utilisation
• One-off data migration - could be a big / time consuming task
• Rollback plan for if it fails
• Monitoring - how are you monitoring your MySQL, and can those methods be adapted
• (If relevant) - replication

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

You will definitely have to do major amounts of performance testing before considering such a move.

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

These costs make moving to a different database too expensive for most nontrivial apps. Consider the benefits VERYcarefully against the vast, vast costs of doing all of the above.

对于大多数重要的应用程序来说，这些成本使得迁移到不同的数据库过于昂贵。相对于执行上述所有操作的巨大成本，非常仔细地考虑收益。

I would be surprised if it takes you less than three months, in a nontrivial application, during which time you won't be able to continue regular development.

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

回答by janneb

You could try PostgreSQL gotchasthat contains the most common issues. Generally, the PostgreSQL documentation is pretty good too, so keep that under your pillow as well.

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

Also, Converting from MySQL to PostgreSQLon the pgsql wiki.

此外，在 pgsql wiki 上从 MySQL 转换为 PostgreSQL。

回答by Dahlo

I found this script that will connect to your MySQL database and your PostgreSQL database and just transfer the contents. It worked like a charm for me.

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

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

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

Installed by

安装者

$ pip install py-mysql2pgsql

Run

跑

$ py-mysql2pgsql

in any folder, and it will create a template settings file for you (mysql2pgsql.yml) that you can edit and enter your databases' details in.

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

I had to install argparse for it to work.

我必须安装 argparse 才能让它工作。

$ pip install argparse

When your database details are filled in, just run it again

填写完数据库详细信息后，再次运行它

$ py-mysql2pgsql

in the same folder as the settings file, and wham, you are done. It didn't print anything to the screen, but my database was fully copied afterwards.

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

回答by tpdi

Before converting, set your MySQL to ANSI-strictness by starting the server with: --transaction-isolation=SERIALIZABLE --sql-mode=ANSI

在转换之前，通过使用以下命令启动服务器，将 MySQL 设置为 ANSI-strictness：--transaction-isolation=SERIALIZABLE --sql-mode=ANSI

Make sure you're not using MyIsam tables.

确保您没有使用 MyIsam 表。

MySQL allows a lot of conversions it shouldn't; pg will require a cast.

MySQL 允许很多它不应该的转换；pg 将需要演员表。

Your stored procs, functions, and triggers will have to be re-written. pg gives you a choice of languages for these, but you have to install the languages; it's not as user friendly as MySQL.

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

pg will only allow in a select list columns that are in a group by or are aggregates; MySQL will cheat by selecting the first value in the group if you do this.

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

MySQL adds a bunch of extensions: the not-equal operator can be !=as in C, it allows '&&' as a synonym for 'and', '||' for 'or' etc. In particular, pg uses '||' to mean string catenation.

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

Basically, pg is pretty strictly ANSI, MySQL isn't. I'd strongly suggest getting your MySQL to as strict an ANSI compliance as possible before converting to pg, then checking for any warnings when you run your applications.

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

回答by Ivka

Aside of moving database structure, where you cannot avoid manual adjustments...

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

The most reliable method of transferring data(table by table, provided that the structures are the same):

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

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

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

Have been trying every other approach recently (like mysqldump with tons of options + sed etc.), but nothing worked as nice as this.

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

This approach also allows for some flexibility when structure is changed along the way - just write an appropriate SELECT.

这种方法还允许在沿途更改结构时具有一定的灵活性 - 只需编写适当的 SELECT。