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 databasefrom mssql://sa:123456@127.0.0.1/hsoatestinto postgresql://oa:password@192.168.0.70:11018/testdbWITH include drop, create tables, create indexes, reset sequences,workers = 8, concurrency = 1,multiple readers per thread, rows per range = 50000SET 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 integerMATERIALIZE 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 databasefrom mssql://sa:123456@127.0.0.1/hsoatestinto postgresql://oa:password@192.168.0.70:11018/testdb
WITH schema only
;

只导入数据

load databasefrom mssql://sa:123456@127.0.0.1/hsoatestinto postgresql://oa:password@192.168.0.70:11018/testdb
WITH data only
;

单表导入

load databasefrom mssql://sa:123456@127.0.0.1/hsoatestinto postgresql://oa:password@192.168.0.70:11018/testdbincluding only table names like 'FlowBasicData' in schema 'dbo'
;

排除表

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