PGLoader导入数据到public schema（外一则）

发布 by 猛禽 需要2分钟读完

问题

最近用PGLoader从MySQL向PostgreSQL导数据时碰到的问题。默认情况下导入后PGLoader会在PG库里创建一个与库名相同的schema，并将数据存放在这里，而不是放在默认的public schema里，所以如果不加search path的话，导入的数据会找不到。

第一次用命令行导入后真是一脸囧，显示明明导入成功，就是找不到数据，放狗搜了好久才找到原因，官方还没有解决方案……

试了很多方法，比如直接修改schema不行，因为public已存在：

ALTER SCHEMA 'dbname' RENAME TO 'public';

在命令文件里使用下述命令也不行：

ALTER TABLE NAMES MATCHING ~/dbname.*$/ SET SCHEMA 'public';

因为一则match不了包含schema的表名，二则它只匹配第一个结果（参考这里的说明），不是所有结果，除非把所有表名都列出来……

解决方案

官方唯一可用的方案简单粗暴，使用如下命令文件：

LOAD DATABASE
     FROM      mysql://mysql_username:mysql_passwd@localhost/yourdb
     INTO postgresql:///yourdb

  SET PostgreSQL PARAMETERS
      search_path to 'yourdb, public'

保存为mysql.load，然后调用执行：

pgloader mysql.load

就是把yourdb这个schema加入数据库的搜索路径，这样就可以看到了。

如果还是喜欢把表都放在public，可以把所有的表的schema改掉，可惜这事不是一个语句可以搞定的（参考这个贴的几个方法改写）：

DO 
$$ DECLARE
   old_schema NAME = 'wpdb';
   l_sql TEXT;
BEGIN
  SET LOCAL search_path = old_schema;
  FOR l_sql IN
    SELECT format('ALTER TABLE %I.%I SET SCHEMA public', table_schema, table_name)
      FROM information_schema.tables WHERE table_schema=old_schema
  LOOP
    EXECUTE l_sql;
  END LOOP;
END; $$;

大写字段名的问题

默认从MySQL导入后大写的字段名都变成了小写，后来找到这里提供了一个解决方案，就是使用quote identifiers参数，给字段名加上引号。

但是导入数据又出错了：Can't init COPY to xxx...。后来找到这里说是3.4.1版的问题，但是ubuntu18.04只有3.4.1，只好编译安装新版本解决（详见外一则）。

新的配置文件是这样的：

LOAD DATABASE
     FROM      mysql://mysql_username:mysql_passwd@localhost/yourdb
     INTO postgresql:///yourdb

  WITH quote identifiers

  SET PostgreSQL PARAMETERS
      search_path to 'yourdb, public'

sequence名称问题

在从MySQL里导入Wordpress数据时发现一个问题，就是Wordpress默认的sequence名字为<table_name>_seq，但是PGLoader导入后变成了<table_name>_<field_name>_seq，导致Wordpress不能正常工作。

需要加上：

ALTER SEQUENCE IF EXISTS "wp_commentmeta_meta_id_seq" RENAME TO wp_commentmeta_seq
ALTER SEQUENCE IF EXISTS "wp_comments_comment_ID_seq" RENAME TO wp_comments_seq
ALTER SEQUENCE IF EXISTS "wp_links_link_id_seq" RENAME TO wp_links_seq
ALTER SEQUENCE IF EXISTS "wp_options_option_id_seq" RENAME TO wp_options_seq
ALTER SEQUENCE IF EXISTS "wp_postmeta_meta_id_seq" RENAME TO wp_postmeta_seq
ALTER SEQUENCE IF EXISTS "wp_posts_ID_seq" RENAME TO wp_posts_seq
ALTER SEQUENCE IF EXISTS "wp_term_taxonomy_term_taxonomy_id_seq" RENAME TO wp_term_taxonomy_seq
ALTER SEQUENCE IF EXISTS "wp_termmeta_meta_id_seq" RENAME TO wp_termmeta_seq
ALTER SEQUENCE IF EXISTS "wp_terms_term_id_seq" RENAME TO wp_terms_seq
ALTER SEQUENCE IF EXISTS "wp_usermeta_umeta_id_seq" RENAME TO wp_usermeta_seq
ALTER SEQUENCE IF EXISTS "wp_users_ID_seq" RENAME TO wp_users_seq

（外一则）编译说明

首先需要安装lisp及相关依赖：

apt install sbcl postgresql-10-ip4r

然后下载源码并编译及测试（需要用postgres用户测试，否则权限不足）：

wget https://github.com/dimitri/pgloader/releases/download/v3.6.2/pgloader-bundle-3.6.2.tgz
tar -xvf pgloader-bundle-3.6.2.tgz
cd pgloader-bundle-3.6.2
make
sudo chown -R postgres *
sudo su - postgres
LANG=en_US.UTF-8 make test

测试通过即可使用新版pgloader了。

推送到[go4pro.org]