pgloader SQLSERVER -＞ PostgreSQL 配置文件样例

pgloader 是什么？安装和基本用户法可以去其他同道的blog上去看，这里不占用网络空间了。刚开始用官方的文档读起还是很费劲的，所以把常用的配置例子放在这里。

官方文档：https://pgloader.readthedocs.io/en/latest/index.html

迁移 SQLServer 与迁移 MySQL 基本相同, 默认的 WITH 选项包含: no truncate, create tables, include drop, create indexes, reset sequences, foreign keys, downcase identifiers, uniquify index names, create schemas(仅SQLServer)

注意最后的分号不能省

完整用法

load database
     from mssql://sa:123456@127.0.0.1/hsoatest
     into postgresql://oa:password@192.168.0.70:11018/testdb

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

  SET maintenance_work_mem to '128MB',
      work_mem to '12MB',
      search_path to 'sakila, public, "$user"'

 CAST type bigint when (= precision 20) to bigserial drop typemod,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer

 MATERIALIZE VIEWS film_list, staff_list

 -- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'
 -- EXCLUDING TABLE NAMES MATCHING ~<ory>
 -- DECODING TABLE NAMES MATCHING ~/messed/, ~/encoding/ AS utf8
 -- ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 -- ALTER TABLE NAMES MATCHING ~/_list$/ SET SCHEMA 'mv'

 ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/
  SET SCHEMA 'mv'

 ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40')

 ALTER SCHEMA 'sakila' RENAME TO 'pagila'

 BEFORE LOAD DO
   $$ create schema if not exists pagila; $$,
   $$ create schema if not exists mv;     $$,
   $$ alter database sakila set search_path to pagila, mv, public; $$;

只导入结构

load database
     from mssql://sa:123456@127.0.0.1/hsoatest
     into postgresql://oa:password@192.168.0.70:11018/testdb
WITH schema only
;

只导入数据

load database
     from mssql://sa:123456@127.0.0.1/hsoatest
     into postgresql://oa:password@192.168.0.70:11018/testdb
WITH data only
;

单表导入

load database
     from mssql://sa:123456@127.0.0.1/hsoatest
     into postgresql://oa:password@192.168.0.70:11018/testdb
         including only table names like 'FlowBasicData' in schema 'dbo'
;

排除表

load database
     from mssql://sa:123456@127.0.0.1/hsoatest_sgwData
     into postgresql://oa:password@192.168.0.70:11018/testdb
excluding table names like 'GlobalAccount' in schema 'dbo'
;