特别说明

1. 因项目需要支持mysql和postgresql支持，迁移数据库过程持续了2天，特做此记录，可能描述不重复，内容不准确，还请指教。
2. 因环境及网络限制，本次使用虚拟机进行操作，如果你能直接连接你的数据库，可忽略文章内的宿主机及虚拟机相关词汇。
3. 本文章可能会因为项目的推进进一步完善，也期待你的补充。

pgloader安装

pgloader可自行下载，Linux原生也有，但是我在迁移过程中试了centosstream、cents7、红帽、Ubuntu等操作系统，遇到各种问题，最后在docker安装成功了，所以本次也是在docker进行。

镜像地址可自行查找下载，我使用的镜像地址如下：

https://ali.qhftdx.cn/resource/wangxch/blog/pgloader.tar

注意：本镜像地址我也是从其它地方下载到的，只提供工具使用，下载后你需要docker load -i pgloader.tar命令加载镜像后使用。

环境信息

运行方式

方式一：命令行直跑（最简单，推荐）

docker run --rm \
  -e PGPASSWORD='123456' \
  -e MYSQL_PWD='456789' \
  dimitri/pgloader:latest \
  pgloader \
  "mysql://tobacco_ys@192.168.1.2:9306/testmysqldb" \
  "postgresql://postgres@192.168.1.2:5432/testmysqldb"

密码通过环境变量传入，不走 URL，避免特殊字符（@ # %）解析出错。

方式二：.load 文件（可加并发、类型映射等高级选项）

1. 创建 migrate.load

cat > migrate_test.load << 'EOF'
LOAD DATABASE
  FROM mysql://tobacco_ys@192.168.1.2:9306/testmysqldb
  INTO postgresql://postgres@192.168.1.2:5432/testmysqldb
  WITH include drop, create tables, create indexes, reset sequences, workers = 1
  CAST type tinyint to smallint drop typemod
  CAST type smallint to smallint drop typemod
  CAST type int to integer drop typemod
  CAST type bigint to bigint drop typemod
  CAST type datetime to timestamp
  CAST type timestamp to timestamptz;
EOF

⚠️ 注意：.load 文件 WITH 子句末尾必须加分号 ;，否则报 ESRAP-PARSE-ERROR。

2. 执行迁移

docker run --rm \
  -e PGPASSWORD='123456' \
  -e MYSQL_PWD='456789' \
  -v $(pwd)/migrate.load:/tmp/migrate.load \
  dimitri/pgloader:latest \
  pgloader /migrate.load

踩坑记录

迁移完成后必做：重置自增序列

1. 移到 public

本次迁移后默认不在public模式，需要迁移到public模式。在 postgresql 里直接执行：

-- 移表
DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'gc_ies_vic'
  LOOP
    EXECUTE 'ALTER TABLE gc_ies_vic.' || r.tablename || ' SET SCHEMA public';
  END LOOP;
END 
$$;

-- 移序列
DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'gc_ies_vic'
  LOOP
    EXECUTE 'ALTER SEQUENCE gc_ies_vic.' || r.sequence_name || ' SET SCHEMA public';
  END LOOP;
END 
$$;

-- 删空 schema
DROP SCHEMA gc_ies_vic;

2. 重置序列

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT 
      t.table_name,
      c.column_name,
      pg_get_serial_sequence('public.' || t.table_name, c.column_name) as seq_name
    FROM information_schema.tables t
    JOIN information_schema.columns c ON t.table_name = c.table_name AND t.table_schema = c.table_schema
    WHERE t.table_schema = 'public'
      AND c.column_default LIKE 'nextval%'
  LOOP
    IF r.seq_name IS NOT NULL THEN
      EXECUTE 'SELECT setval(''' || r.seq_name || ''', COALESCE((SELECT MAX(' || r.column_name || ') FROM public.' || r.table_name || '), 1));';
    END IF;
  END LOOP;
END 
$$;

3. 验证

-- 表都在 public 下了
SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';

-- 随便看张表
SELECT * FROM public.ccsp_customer_blacklist LIMIT 5;

4. 序列设为 MAX(id)+1

pgloader 迁移完不会自动把 PG 的序列设为 MAX(id)+1，不重置的话后续 INSERT 会报主键冲突。

-- 对每张有 SERIAL / IDENTITY 的表执行
SELECT setval(
  pg_get_serial_sequence('表名', 'id'),
  COALESCE((SELECT MAX(id) FROM 表名), 1),
  true
);

5. 批量生成所有表的重置 SQL

SELECT 'SELECT setval(pg_get_serial_sequence(''' || tablename || ''', ''id''), COALESCE((SELECT MAX(id) FROM ' || tablename || '), 1), true);'
FROM pg_tables
WHERE schemaname = 'public';

把结果复制出来逐条执行即可。

6. 验证迁移结果

-- PG 侧检查表数量和行数
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- 对比 MySQL 侧
-- 在 MySQL 里: SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='gc_ies_vic';

其它

postgresql数据库大小查询

SELECT pg_size_pretty(pg_database_size('gc_ies_vic'));

SELECT count(*) FROM information_schema.tables WHERE table_schema='public';

数据库锁死处理

SELECT pid, usename, state, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = 'gc_ies_vic';

-- 终止 gc_ies_vic 库上所有连接（除了你自己这个）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'gc_ies_vic' AND pid <> pg_backend_pid();

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.query AS blocked_query,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.query AS blocking_query,
       blocking_activity.state AS blocking_state
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

DROP DATABASE gc_ies_vic;
CREATE DATABASE gc_ies_vic;

pg对字段大小写敏感，可按实际项目情况在转移数据库的时候是否将全部字段转化为小写

# 强制保留大小写
pgloader --with "quote identifiers" mysql://xxx pgsql://xxx

# 强制转小写（默认）
pgloader --with "downcase identifiers" mysql://xxx pgsql://xxx