博客已全面迁移至 PostgreSQL

我最近把博客的后端从 MySQL 切到了 PostgreSQL。原因并不是单一的“谁更好”，而是基于我自己需求的综合判断：

• 标准 SQL 支持更好：比如需要用到复杂查询、窗口函数、递归 CTE（WITH RECURSIVE）时，PostgreSQL 的语法和优化更可靠，写查询也更顺手。
• 内建高级数据类型：jsonb、数组类型、地理信息（PostGIS）和全文检索等，很多功能可以直接用 DB 层实现，减少应用层 hack。
• 扩展与自定义能力强：可以自定义类型、函数、索引方法（GIN/BRIN）、安装扩展（pg_trgm、pgcrypto）来扩展功能。
• 事务与一致性：MVCC + 完备的事务隔离，对并发写入和回读一致性更有保障。
• 生态和稳定性：社区成熟、工具链（备份、复制、监控）很好，长期维护更省心。

说直白一点：当你需要“更强的查询能力”或“用 DB 做更多事情”时，Postgres 往往更合适。

迁移前的准备

迁移前把常见坑尽量处理掉，减少跑回头路的时间：

1. 确认 PostgreSQL 已安装并能访问（本地或远程）

   • Debian/Ubuntu 举例：sudo apt install postgresql
   • 检查服务：sudo systemctl status postgresql
2. 创建数据库与账号（示例）

## 切换到 postgres 用户执行
sudo -u postgres psql

-- 在 psql 里：
CREATE USER blog WITH PASSWORD 'your_password_here';
CREATE DATABASE blog OWNER blog ENCODING 'UTF8';
\q

注意：确保数据库编码是 UTF8，否则中文/Emoji 可能出问题。

3. 检查连接权限（pg_hba.conf）
   如果 PHP-FPM/应用和 DB 在不同主机，需要在 postgresql.conf/pg_hba.conf 中允许远程连接并重载 PostgreSQL：

## pg_hba.conf 中添加（示例）
host    blog    blog    192.0.2.0/24    md5

然后重载：sudo systemctl reload postgresql

4. 准备好 Navicat（或其他迁移工具）并能同时连接到 MySQL 和 PostgreSQL。
   如果数据量很大，提前评估磁盘空间、目标表的 fillfactor、索引重建所需时间等。

用 Navicat 迁移的详细步骤

Navicat 的“数据传输”工具很方便，但有些选项需要注意，下面按顺序写清楚：

1. 先导出结构（可选但推荐）

   • 在 Navicat 中把 MySQL 的表结构导出来，检查字段类型。Navicat 会做类型映射，但我会先只迁移结构（不传数据），确认没问题再传数据。
   • 重点检查：自增列、unsigned 类型、tinyint(1)（很多是布尔）、BLOB/TEXT、datetime/timestamp、默认值、字符集/校对规则。

2. 字段类型映射常见问题

   • INT / BIGINT -> integer / bigint（Postgres 没有 unsigned；若 MySQL 使用 unsigned 且数值可能超出 signed 上限，要改用 bigint）。
   • TINYINT(1) 通常代表布尔，建议映射为 boolean。
   • AUTO_INCREMENT -> SERIAL 或 GENERATED AS IDENTITY（Navicat 通常会处理）。
   • TEXT/BLOB -> text/bytea（注意 bytea 的读取方式不同）。
   • DATETIME/TIMESTAMP -> timestamp without time zone（注意时区处理）。
   • JSON -> jsonb（优先选 jsonb，查询性能好）。

3. 先传结构再传数据（稳妥顺序）

   • 推荐顺序：结构 -> 数据 -> 索引/约束/外键。理由是数据导入时禁用外键可以提升速度并避免因为导入顺序造成的约束错误。Navicat 有选项“先传数据/后创建约束”，如果不确定可以手动分三步走。

4. 数据传输设置（在 Navicat 的“数据传输”里）

   • 源：MySQL，目标：PostgreSQL。
   • 选择“结构 + 数据”（或先结构再数据）。
   • 检查“字段映射”和“高级选项”，确保 Navicat 会把 AUTO_INCREMENT 转为 Postgres 的序列，或手动修改。
   • 对大表可以开启“批量插入”或调整批量大小。
   • 如果数据包含二进制字段（file、avatar 等），注意 Navicat 对 bytea/BLOB 的处理是否正确。

5. 执行迁移并观察日志

   • 迁移时观察 Navicat 的日志窗口，出现警告或错误时不要直接忽略（常见是类型不兼容、字段长度溢出、默认值表达式不同）。
   • 完成后逐表对比行数：SELECT COUNT(*) FROM table;（MySQL 与 Postgres 上分别检查）。

迁移后必须做的调整

迁移完成后别急着把旧库删了，逐项检查并修复：

1. 修正序列（auto-increment）
   MySQL 的自增并不会自动给 PostgreSQL 的序列设置到最大值。若不修正，下一次插入可能冲突。修复方法（举例）：

-- 先查序列名
SELECT pg_get_serial_sequence('your_table','id');

-- 把序列设置到当前最大 id
SELECT setval(pg_get_serial_sequence('your_table','id'), (SELECT COALESCE(MAX(id),0) FROM your_table));

如果表很多，可以写脚本或用下面的 DO 块（在测试环境先试）：

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN
    SELECT table_name, column_name
    FROM information_schema.columns
    WHERE column_default LIKE 'nextval(%' AND table_schema='public'
  LOOP
    EXECUTE format('SELECT setval(pg_get_serial_sequence(%L, %L), COALESCE(MAX(%I),0)) FROM %I',
                   r.table_name, r.column_name, r.column_name, r.table_name);
  END LOOP;
END$$;

（执行前建议手动检查几张关键表，保证安全）

2. 权限与表属主

   • 确保 Typecho 使用的 DB 用户拥有需要的权限：连接、对 schema 的使用、对表的增删改查权限。
     示例：

GRANT CONNECT ON DATABASE blog TO blog;
\c blog
GRANT USAGE ON SCHEMA public TO blog;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blog;

• 若需要把目标表的 owner 改为该用户：ALTER TABLE table_name OWNER TO blog;

3. 字符集、排序规则问题

   • 确保数据库是 UTF8，否则中文有乱码。
   • MySQL 的 collation/排序和 PostgreSQL 有差异，某些 ORDER BY 的结果可能不一致，必要时在查询里显式指定 COLLATE 或在应用层调整。

4. SQL 兼容性修正

   • MySQL 的 IFNULL() -> PostgreSQL 用 COALESCE()。
   • MySQL 的 LIMIT x, y 在 Postgres 是 LIMIT y OFFSET x。
   • MySQL 用反引号 ` 标识符，Postgres 普通不需要，但如果有混合大小写、保留字，需改为双引号或改名。
   • Typecho 自带的 SQL（如果有写原生 SQL）可能需要微调，但如果使用 ORM/Typecho 的 DB 接口，兼容性通常较好。

5. 全文检索 / 搜索功能重做（如果用到了）

   • MySQL 的全文索引和 Postgres 的全文索引实现不同，若博客依赖全文搜索，建议用 tsvector + GIN 索引或 pg_trgm 来优化。
6. 执行 VACUUM/ANALYZE
   迁移完后运行：

VACUUM ANALYZE;

让查询规划器知道各表统计信息，避免早期慢查询。

Typecho 的配置修改

Typecho 默认用 MySQL，要让它用 PostgreSQL，需要把 DB 实例改成 Pdo_Pgsql。把你原来的 MySQL 配置替换为下面这段（按实际填入 host/user/password）：

<?php
/**
 * Typecho 配置文件（PostgreSQL）
 */

$db = new Typecho_Db('Pdo_Pgsql', 'typecho_');
$db->addServer(array (
  'host'     => 'localhost',      // 数据库主机（如果是远程填 IP）
  'user'     => 'blog',           // PostgreSQL 用户名
  'password' => '你的密码',       // 用户密码
  'dbname'   => 'blog',           // 数据库名
  'port'     => 5432,             // PostgreSQL 端口
), Typecho_Db::READ | Typecho_Db::WRITE);
Typecho_Db::set($db);

注意事项：

• PHP 必须启用 pdo_pgsql 扩展。Debian/Ubuntu 示例：

  sudo apt install php-pgsql
  sudo systemctl restart php8.1-fpm   # 或者 apache2

• 修改完 config.inc.php 后，重启 PHP-FPM / Nginx，使配置生效。
• Typecho 的表前缀（上例 typecho_）保持原样，避免与旧数据库冲突（若你同时保留 MySQL，则注意区分）。

性能与运维注意点

• 开启 autovacuum（默认开启），用于自动回收死行。视写量调整 autovacuum_* 参数。
• 定期 VACUUM ANALYZE：尤其是大改表后。
• 索引策略：Postgres 对索引选择依赖统计信息，迁移后如果某些查询慢，使用 EXPLAIN ANALYZE 找瓶颈，并考虑建立 GIN、BRIN（大表）、或复合索引。
• 用好扩展：需要模糊匹配可装 pg_trgm；对 JSON 查询使用 GIN + jsonb_path_ops。
• 备份策略：使用 pg_dump / pg_dumpall 做逻辑备份，pg_basebackup 做物理备份；定期演练恢复。

常见问题与排查

• 连接失败：检查 pg_hba.conf、postgresql.conf（listen_addresses）、防火墙和端口。
• 登录权限不足：确认用户拥有对应数据库和 schema 的权限，必要时 ALTER DEFAULT PRIVILEGES。
• 字符编码乱码：确认源库的字符集（MySQL 多数是 utf8mb4），导入到 UTF8 Postgres。如果乱码，可能需要在导出时指定编码转换。
• 自增 ID 冲突：忘记修序列会导致插入失败，参见“修正序列”部分。
• SQL 语法不兼容：应用层出现 SQL 错误，逐条改成 Postgres 支持的写法（IFNULL -> COALESCE，LIMIT OFFSET 等）。
• 全文/搜索不一致：迁移后需要重建全文索引用 Postgres 的实现方式。

我的小结

• 使用 Navicat 这样的图形工具能把大部分重复性工作交给工具处理，但不要全然信任自动映射——重点字段（自增、unsigned、JSON/BLOB、默认值）要人工确认。
• 先迁移结构再迁移数据，再重建约束/索引，能最小化错误和回滚成本。
• 迁移完成后务必修正序列并运行 VACUUM ANALYZE，这样能避免很多“导入后性能差或唯一键冲突”的尴尬。
• 在应用层，优先让 Typecho 使用 PDO 的 Postgres 驱动（pdo_pgsql），大部分 Typecho 插件/功能能正常工作，但如果站点有自写 SQL，还是需要人工检查与调整。