从 PostgreSQL 迁移到 MariaDB

这是相对困难的迁移——PG 和 MySQL/MariaDB 是两种不同流派。建议评估"是否真的要迁"——很多场景下 PG 才是更好选择。

何时迁、何时不迁

迁的合理理由：

团队全是 MySQL 背景
周边生态（ORM、备份工具）都围绕 MySQL/MariaDB 建设
想用 MariaDB 独有特性（ColumnStore）
想统一全公司用 MariaDB 系

别迁的理由：

用了 PG 的 PostGIS、JSONB 复杂查询、ARRAY 类型
用了 LISTEN/NOTIFY、materialized view、partial index
用了 RLS (Row Level Security)
用了 logical replication 做精细数据流

类型映射

PostgreSQL	MariaDB
`SMALLSERIAL`	`SMALLINT AUTO_INCREMENT`
`SERIAL`	`INT AUTO_INCREMENT`
`BIGSERIAL`	`BIGINT AUTO_INCREMENT`
`TEXT`	`TEXT` 或 `LONGTEXT`
`VARCHAR(n)`	`VARCHAR(n)`
`BOOLEAN`	`BOOLEAN` (= `TINYINT(1)`)
`JSON` / `JSONB`	`JSON`
`TIMESTAMP WITH TIME ZONE`	`DATETIME` + 自管时区
`INTERVAL`	无直接对应，用秒数 / 字符串
`UUID`	MariaDB 10.7+ 有 `UUID`，老版本用 `BINARY(16)`
`BYTEA`	`BLOB` / `VARBINARY`
`ARRAY`	无直接对应，用 JSON 数组
`ENUM`	`ENUM`
`HSTORE`	JSON
`INET` / `CIDR`	`VARCHAR(45)` 自己解析
`POINT` / `POLYGON`	MariaDB Spatial 也有，但函数名不同
`tsvector`	`FULLTEXT KEY ... WITH PARSER ngram`

工具：pgloader

最成熟的 PG → MySQL/MariaDB 迁移工具。

# Ubuntu
sudo apt install pgloader

# 配置文件 pg2mariadb.load
LOAD DATABASE
  FROM postgresql://user:pass@pg-host/source_db
  INTO mysql://user:pass@maria-host/target_db

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

SET MySQL PARAMETERS
  net_read_timeout = '120',
  net_write_timeout = '120'

CAST type uuid to varchar(36),
     type jsonb to json drop typemod,
     type tsvector to longtext drop typemod

ALTER SCHEMA 'public' RENAME TO 'source_db'

BEFORE LOAD DO
  $$ SET FOREIGN_KEY_CHECKS = 0; $$

AFTER LOAD DO
  $$ SET FOREIGN_KEY_CHECKS = 1; $$ ;

跑：

pgloader pg2mariadb.load

必改的 SQL

`RETURNING`

PG 标配；MariaDB 10.5+ 也支持 INSERT … RETURNING，但 UPDATE/DELETE RETURNING 直到 11.x 才完整：

-- PG: 通用
UPDATE users SET active = TRUE WHERE id = 1 RETURNING *;

-- MariaDB 11.0+
UPDATE users SET active = TRUE WHERE id = 1 RETURNING id, name;
-- 老版本：先 UPDATE 再 SELECT

`ON CONFLICT` vs `ON DUPLICATE KEY UPDATE`

-- PG
INSERT INTO users (email, name) VALUES (?, ?)
  ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- MariaDB
INSERT INTO users (email, name) VALUES (?, ?)
  ON DUPLICATE KEY UPDATE name = VALUES(name);
-- 11.x 起也支持
INSERT INTO users (email, name) VALUES (?, ?)
  ON DUPLICATE KEY UPDATE name = NEW.name;

`DISTINCT ON`（MariaDB 没有）

-- PG
SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC;

-- MariaDB 用窗口函数
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t WHERE rn = 1;

`generate_series`

-- PG
SELECT * FROM generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day');

-- MariaDB 用递归 CTE（见 newbie/cte）
WITH RECURSIVE d AS (
  SELECT DATE '2026-01-01' AS day
  UNION ALL SELECT day + INTERVAL 1 DAY FROM d WHERE day < '2026-01-31'
) SELECT day FROM d;

数组列

-- PG
SELECT * FROM products WHERE 'apple' = ANY(tags);

-- MariaDB（tags 是 JSON 数组）
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"apple"');

Window 函数

两边语法基本一致，能直接迁。

CTE

也基本一致。

必改的运维

Sequences

PG 用 sequences，MariaDB 用 AUTO_INCREMENT。pgloader 已自动转换。

Schema vs Database

PG 一个 database 可有多个 schema；MariaDB 没有 schema 概念，schema ≈ database。pgloader 用 ALTER SCHEMA 把 public 改成 db 名。

Triggers / Functions

PL/pgSQL 不兼容。MariaDB 用 SQL/PSM。简单触发器可手动改写：

-- PG
CREATE FUNCTION fn() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

-- MariaDB
DELIMITER //
CREATE TRIGGER tr BEFORE UPDATE ON tbl
FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END //
DELIMITER ;