使用pgloader从MySQL迁移至PostgreSQL遇目标schema未找到错误

问题场景 #

在同一台Linux机器上部署MySQL和PostgreSQL，使用pgloader迁移MySQL数据库naqaba_portal到PostgreSQL数据库pg_gsc_v2，迁移脚本如下：

LOAD DATABASE
    FROM mysql://master:password@127.0.0.1:3306/naqaba_portal
    INTO postgresql://postgres:password@127.0.0.1:5432/pg_gsc_v2

WITH include no drop, create tables, create indexes, reset sequences, data only;

运行脚本后触发错误：

root@WOQOOF:~# pgloader migrate.load
2024-07-06T08:07:50.008000+08:00 LOG pgloader version "3.6.2"
2024-07-06T08:07:50.100003+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://move@127.0.0.1:3306/naqaba_portal {1007FAB923}>
2024-07-06T08:07:50.100003+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@127.0.0.1:5432/pg_gsc_v2 {1007FAC643}>
KABOOM!
FATAL error: pgloader failed to find anything in schema "naqaba_portal" in target catalog.
An unhandled error condition has been signalled:
   pgloader failed to find anything in schema "naqaba_portal" in target catalog.

已确认PostgreSQL中已创建目标数据库pg_gsc_v2和schemanaqaba_portal，服务正常且权限配置完整，但问题仍存在。

解决方案 #

1. 显式指定目标schema的搜索路径 #

pgloader默认会将MySQL数据库名映射为PostgreSQL的schema名，但如果目标连接未指定搜索路径，可能无法找到该schema。修改迁移脚本的INTO连接串，添加搜索路径参数：

LOAD DATABASE
    FROM mysql://master:password@127.0.0.1:3306/naqaba_portal
    INTO postgresql://postgres:password@127.0.0.1:5432/pg_gsc_v2?options=-csearch_path%3Dnaqaba_portal

WITH include no drop, create tables, create indexes, reset sequences;

注意：移除了data only参数，该参数会跳过表创建步骤，与create tables冲突，导致pgloader找不到预创建的表（若未手动建表）。

2. 检查schema名称的大小写一致性 #

PostgreSQL对标识符大小写敏感（未加引号时自动转为小写），确认创建schema的语句是：

CREATE SCHEMA naqaba_portal;

如果创建时使用了引号包裹的大小写混合名称（比如CREATE SCHEMA "Naqaba_Portal";），需要在pgloader脚本中用引号指定schema，或调整schema名称与MySQL数据库名大小写完全一致。

3. 手动验证PostgreSQL的schema可用性 #

登录PostgreSQL命令行，执行以下命令确认schema存在且当前用户有权限访问：

psql -U postgres -d pg_gsc_v2

然后执行：

-- 查看所有schema
SELECT schema_name FROM information_schema.schemata;
-- 确认当前用户对naqaba_portal的权限
SELECT * FROM information_schema.table_privileges WHERE grantee = 'postgres' AND table_schema = 'naqaba_portal';
-- 查看当前搜索路径
SHOW search_path;

如果search_path中没有naqaba_portal，可临时设置：

SET search_path TO naqaba_portal, public;

或永久修改用户的搜索路径：

ALTER ROLE postgres SET search_path TO naqaba_portal, public;

4. 拆分建表与数据加载步骤 #

如果必须保留data only参数，需先单独执行建表步骤，再加载数据：

1. 建表脚本：

LOAD DATABASE
    FROM mysql://master:password@127.0.0.1:3306/naqaba_portal
    INTO postgresql://postgres:password@127.0.0.1:5432/pg_gsc_v2?options=-csearch_path%3Dnaqaba_portal

WITH include no drop, create tables, create indexes, reset sequences, schema only;

2. 数据加载脚本：

LOAD DATABASE
    FROM mysql://master:password@127.0.0.1:3306/naqaba_portal
    INTO postgresql://postgres:password@127.0.0.1:5432/pg_gsc_v2?options=-csearch_path%3Dnaqaba_portal

WITH include no drop, data only;

内容的提问来源于stack exchange，提问作者Tahir Rasheed