从MySQL迁到PostgreSQL踩过的七类坑

  1.  Home
  2. Text  
    3. Share to：

前阵子折腾一个小站的数据层，本来用 MySQL 跑得好好的，因为想试试「用 PostgreSQL 做全文检索 + JSONB 存半结构化数据」这条路，就把一整套东西搬了过去。搬的过程比想象中顺利，pgloader 一把就跑完了，业务代码改了几个函数名就上线了。真正出问题的是第三周：网站开始间歇性 502，日志里全是 terminating connection due to administrator command，还有一行 canceling statement due to statement timeout。

查下来是两件事凑在一起：一是几个「上千万行的统计查询」在 MySQL 上跑 8 秒，迁到 PostgreSQL 上跑了 40 秒，撞了 statement_timeout；二是连接数一直往上涨，最后撞了 max_connections。这两个问题都不是语法迁移能暴露的，而是执行模型与并发模型不一样。这篇就把这一路的坑按顺序写清楚，给准备从 MySQL 迁到 PostgreSQL 的个人站长一个参考。

为什么个人站也会考虑 PostgreSQL

先说不必要的情况。如果你的站就是「文章 + 评论 + 标签 + 一点统计」，MySQL 8 完全够用，迁移的成本回收不了，别折腾。我之所以迁，是因为三个具体需求：

  • 全文检索：中文用 zhparser 或 pg_jieba，比 MySQL 的 ngram 分词更可控，还能给不同字段加权、做摘要高亮；
  • 复杂聚合与窗口函数：站内要做「每个标签近三十天排名变化」，这类查询在 PostgreSQL 的 LATERAL 与窗口函数里写起来短很多；
  • JSONB：半结构化的采集元数据、主题配置、A/B 实验参数，放在 JSONB 列里可以直接建 GIN 索引查询，不用再开一堆 EAV 表。

这三个都是「用得上的功能」，不是为了新而新。如果你的需求里没有类似的点，跳过这篇文章更省事。

第一坑：字符集与排序规则（collation）的隐式差异

MySQL 的默认排序规则是 utf8mb4_0900_ai_ci，大小写不敏感、重音不敏感。PostgreSQL 默认用 os 的 locale，在 Debian 上常是 C.UTF-8，而 C 排序是字节序，大小写敏感。这意味着原来在 MySQL 上 WHERE slug = 'Hello' 能命中 hello，迁过去就命中不了。

-- 查看库与列的排序规则
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = current_database();
\d+ posts

两种处理方式。想保留 MySQL 那种「大小写不敏感」的语义，建库时用 ICU collation：

CREATE DATABASE site
  WITH ENCODING 'UTF8'
  LC_COLLATE 'zh-x-icu'
  LC_CTYPE 'zh-x-icu'
  TEMPLATE template0;

注意 TEMPLATE template0 必须写，否则会因为 template1 的 collation 冲突报错。用 ICU 的地方要确认 SHOW lc_collate 真的变了，而不是以为写了就生效。

另一种做法是让应用层自己处理：所有 slug 统一 lower() 之后入库，查询时也用 lower()，再加一个表达式索引保证能走索引：

CREATE INDEX idx_posts_slug_lower ON posts (lower(slug));

我选的是第二种。原因是排序规则一旦定下来，之后想改要重建整个库（pg_dump + 新库 + 导回），成本比在应用层多做一次 lower() 高得多。这个决定最好在迁移第一天就做。

第二坑：句柄参数占位符与 NULL 语义

PHP 的 PDO 在 MySQL 上默认是「模拟预处理」（ATTR_EMULATE_PREPARES=true），到 PostgreSQL 上服务端真正做预处理，这时类型推断会严格很多。最常见的报错是：

ERROR: could not determine data type of parameter $1

原因是 WHERE deleted_at IS NULL OR deleted_at = ? 这种写法里，如果 ? 传的是字符串 null，PostgreSQL 无法推断类型。解决办法有两个：一是用 PDO::PARAM_NULL 明确传 NULL，二是写 ?::timestamptz 强制造型。

另一个差异是 MySQL 的 '' 和 NULL 在某些老代码里被当作等价处理（尤其配合 NOT NULL DEFAULT '' 的列），而 PostgreSQL 里 '' 是一个合法的空字符串，NOT NULL 列可以存空串，但 IS NULL 判断不会命中。数据迁过去之后统计报表「有值」的数量对不上，多半就是这个原因。

SELECT
  count(*) FILTER (WHERE url = '')    AS empty_str,
  count(*) FILTER (WHERE url IS NULL) AS null_val
FROM posts;

FILTER 是 PostgreSQL 特有的聚合过滤语法，比 SUM(CASE WHEN ...) 读起来清楚，迁移之后这类核查会频繁用到。

第三坑：LIMIT 加 ORDER BY 的执行计划差别

这是让我踩了 40 秒查询的地方。MySQL 在 ORDER BY created_at DESC LIMIT 20 上非常擅长用索引倒序扫描，取到 20 行就停。PostgreSQL 通常也能做到，但如果 ORDER BY 里的列不在索引的「最左位置」，或者有个 WHERE 条件让优化器误判选择性，它可能会选择「全量排序 + 取前 20」，成本就完全不同。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM posts
WHERE status = 'publish'
ORDER BY created_at DESC
LIMIT 20;

如果看到的执行计划里出现了 Sort Method: external merge Disk: 200MB，那就是排序溢出了 work_mem。这时有两个选择：一是调大 work_mem（但要小心，它是每个排序节点可用的，并发上去会吃光内存），二是建一个恰好匹配查询的复合索引：

CREATE INDEX CONCURRENTLY idx_posts_status_created
  ON posts (status, created_at DESC);

CONCURRENTLY 不能省。PostgreSQL 的普通 CREATE INDEX 会持有 SHARE 锁，阻塞所有写入；CONCURRENTLY 不阻塞写，虽然慢一倍多、失败会留下 INVALID 索引（需要 DROP INDEX 后重建），但在线上是唯一可接受的选项。

顺便说一个 MySQL 转过来最容易忽略的点：PostgreSQL 没有 FORCE INDEX 的等价写法。要影响优化器，可以临时 SET enable_seqscan = off，但那是全局会话级的且只说明「尽量避免」，不是强制。正确做法还是把统计信息弄准：

ANALYZE posts;
-- 需要更高精度时，针对特定列加大统计目标
ALTER TABLE posts ALTER COLUMN status SET STATISTICS 1000;
ANALYZE posts;

第四坑：连接数模型完全不一样

MySQL 的连接是「一个查询一个线程」，线程有开销但相对轻。PostgreSQL 是一个连接一个进程，每个进程有独立的内存空间和地址空间，开销大得多。所以 PostgreSQL 上 max_connections 的默认值 100 不是保守，而是有依据的：超过几百个连接之后，进程切换本身就会拖慢全机。

PHP-FPM 每个 worker 一条持久连接，pm.max_children=50 就是 50 个连接常驻。如果有多个池（比如前台和后台分池），加起来很容易到 150~200。这时如果还有几个 CLI 脚本（备份、定时任务）各开连接，撞上限就是必然。

-- 现在谁在连着、在干什么
SELECT pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       now() - backend_start AS conn_age,
       now() - query_start   AS query_age
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY query_age DESC NULLS LAST
LIMIT 20;

state = 'idle in transaction' 是最需要警惕的状态：它说明应用开启了事务但没有提交或回滚，事务里持有的锁一直不放，还可能阻塞 vacuum。个人站上这种连接通常来自「try 里抛异常、catch 里只打日志没 rollback」的写法。

解决连接数最有效的办法是上连接池。我的选择是 PgBouncer 的 transaction 模式：它在每个事务结束后就把连接还给数据库，所以 500 个客户端只需要 20 个真实连接。transaction 模式的代价是「会话级状态不保留」——SET 的变量、临时表、advisory lock（会话级）都会失效。如果你用了 SET search_path 或者临时表，就得改用 session 模式，那就救不了连接数了。

[databases]
site = host=127.0.0.1 port=5432 dbname=site

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
server_idle_timeout = 60

几个要点：listen_addr 一定要写 127.0.0.1，PgBouncer 本身没有加密，暴露在公网等于把数据库裸露。auth_file 权限设 600。server_idle_timeout 设 60 秒是为了应对「中间有防火墙掐空闲连接」——这和 MySQL 那边 wait_timeout 要小于链路超时是同一个道理。

第五坑：VACUUM 与「表膨胀」是必修课

MySQL 的 InnoDB 删除行之后空间留在表里复用，可以通过 OPTIMIZE 重建。PostgreSQL 用的是 MVCC 加多版本存储，UPDATE 和 DELETE 都会留下死元组，靠 VACUUM 回收。如果 autovacuum 跟不上，表会膨胀到十倍，查询变慢、磁盘告警，这些问题会一起出现。

个人站最常见的膨胀原因不是高并发，而是大批量 UPDATE：比如给全站文章批量改一个字段，几十万行的更新会在瞬间产出几十万死元组，autovacuum 可能要跑几十分钟才追上，期间查询计划全乱。

-- 哪些表死元组最多、上次 vacuum 是什么时候
SELECT relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

dead_pct 超过 20% 且 n_dead_tup 上万，就该手动跑一次 VACUUM (ANALYZE) 表名。注意 VACUUM 不回收磁盘空间（只标记可复用），要真正把文件缩小得用 VACUUM FULL，但它会持有排他锁、期间整表不可读写，个人站只能在维护窗口做，且必须留足磁盘（它要重写整表）。

另外有一个容易忽略的配置：autovacuum_vacuum_scale_factor 默认 0.2，意思是「死元组超过表的 20% 才触发」。这个比例对百万行的大表意味着 20 万死元组才会启动，太迟了。对大表单独调小：

ALTER TABLE posts SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

第六坑：备份方式的改变

MySQL 有 mysqldump --single-transaction 这样的热备手段，PostgreSQL 对应的工具是 pg_dump，默认就是一致性快照（不需要额外参数）。但 pg_dump 有一个对个人站影响很大的点：它默认只导出单个数据库，而 PostgreSQL 的「用户」「角色」属于实例级，不在库的 dump 里。恢复到一个新实例时，角色不存在会导致权限语句全部失败。

# 单库备份（-Fc 自定义格式，支持并行恢复）
pg_dump -h 127.0.0.1 -U site -Fc -f /backup/site-$(date +%F).dump site

# 角色单独备份
pg_dumpall --roles-only -U postgres -f /backup/roles-$(date +%F).sql

# 恢复（-j 4 并行，-Fc 才有）
pg_restore -h 127.0.0.1 -U site -d site_new -j 4 /backup/site-2026-10-24.dump

还有一个细节：pg_dump 的运行版本不能比服务端新太多（新版客户端兼容老服务端，反过来不行）。Debian 上如果装了多个版本，要显式用 /usr/lib/postgresql/16/bin/pg_dump 这样的完整路径，否则 PATH 里那个可能不认识服务端的新特性，报 server version mismatch。

备份完一定要验证。我的做法是每个月在测试机上真恢复一次，然后跑三条 SQL 核对行数：

SELECT 'posts' AS t, count(*) FROM posts
UNION ALL SELECT 'comments', count(*) FROM comments
UNION ALL SELECT 'users', count(*) FROM users;

同时 pg_restore -l 列一下 dump 里有没有遗漏的表——如果 dump 是 --table 参数遗留的产物，很容易少表而不自知。

第七坑：迁移本身的做法

如果确定要迁，我的顺序是这样，尽量避免「一次大爆炸」：

第一步，结构迁移单独做。不要用 pgloader 直接把表和数据一起搬，因为 MySQL 的 int unsigned、enum、zerofill 映射过去往往不理想。手写 DDL 更可控，尤其主键与字符集这两处。

第二步，数据分批导。几百万行的表用 COPY 最快，但要注意 COPY 遇到一行坏数据就整批失败，所以先小批量试，或者在源端用 WHERE id BETWEEN 分批：

# 先在目标端建表，再从 CSV 导入
\copy posts (id, title, body, created_at) FROM '/tmp/posts_1.csv' WITH (FORMAT csv, HEADER true)

第三步，双跑验证。上线前至少让新旧库同时接收写入几天，用脚本对比关键表的行数与几个聚合值。差异一定会有（时间戳精度、NULL 与空串、自增序列），逐个确认是「预期差异」还是「bug」。

第四步，序列要重置。这是迁移后最常见的「第二天就炸」的问题：手动插入了带 id 的数据，但序列还停在 1，之后第一条普通插入就撞主键。

SELECT setval(pg_get_serial_sequence('posts', 'id'),
              (SELECT max(id) FROM posts));

所有有自增主键的表都要跑一遍。我建了个临时查询把库里所有序列都列出来批量核对：

SELECT c.relname AS tbl,
       a.attname AS col,
       pg_get_serial_sequence(quote_ident(c.relname), a.attname) AS seq
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind = 'r' AND a.attnum > 0
  AND pg_get_serial_sequence(quote_ident(c.relname), a.attname) IS NOT NULL
ORDER BY 1;

第五步，回滚预案。数据库迁过去之后，应用层如果出问题，回滚不是「把配置改回来」那么简单——迁移期间产生的新数据在 PostgreSQL 里。所以要么在切换窗口内保持只读，要么准备好反向同步脚本。我的做法是切换当天上午把站切到维护页（503），完成迁移后半小时内验证通过就放开，超过两小时就回滚，因为这段时间没有新数据，回滚成本是零。

总结：该迁不该迁的判断

迁完两个月，感受是值得的，但主要是因为全文检索和 JSONB 这两个具体需求，而不是「PostgreSQL 更好」。如果你的需求是：

  • 数据量在千万行以内、以简单 CRUD 为主 → 别迁，收益覆盖不了成本；
  • 需要中文全文检索、复杂聚合、半结构化字段查询 → 值得迁；
  • 团队只有你一个人且不熟 PostgreSQL 的运维（VACUUM、连接池、备份） → 先在自己机器上跑三个月再决定；
  • 有复杂的 MySQL 特有语法（大量存储过程、ON DUPLICATE KEY UPDATE、GROUP_CONCAT）→ 迁移成本会翻倍，慎重。

最后补一句个人体会：迁移这类「底层换掉」的事情，最贵的从来不是技术，而是心理上的「它已经跑起来了所以应该没问题」。我这次的两个故障都发生在第三周，都是那种「小流量测不出来、累积几天才爆」的类型。所以不管迁什么，留一条「关键指标的日常曲线」比做一次完美的迁移更重要。

Last modification：October 24th, 2026 at 09:26 pm
© 允许规范转载

相关文章

Leave a Comment