利用FDW进行ORACLE到Postgresql的数据迁移

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/postgres/instantclient_11_2:/usr/local/pgsql/lib
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/pgsql/bin
ORACLE_HOME=/home/postgres/instantclient_11_2
PGDATA=/data
export PATH ORACLE_HOME LD_LIBRARY_PATH PGDATA

$ make
$ make install

create extension oracle_fdw;
postgres=# \dx
                        List of installed extensions
    Name | Version |   Schema | Description
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.1     | public | foreign data wrapper for Oracle access -->说明创建成功
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

CREATE SERVER spclora FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '10.26.5*.**:1521/spcltbk');
--创建server spclora 连接到oracle数据库
GRANT USAGE ON FOREIGN SERVER spclora TO userinfo_prod;
--将server授权给用户userinfo_prod
\c userinfodb userinfo_prod
--使用userinfo_prod切换到userinfodb
CREATE USER MAPPING FOR userinfo_prod SERVER spclora OPTIONS (user 'pgsync', password 'oracle');
--创建到oracle的映射，pgsync/oracle是oracle数据库的用户名和密码

create FOREIGN table t_***_userinfo_fdw
(
  phone*** VARCHAR(24) not null,
  ser***tus int4 not null,
  createtime timestamp with time zone default now() not null,
  ups***time  timestamp with time zone default now() not null,
  op**a*or      int4 not null,
  che**me timestamp with time zone,
  lo**id       int4,
  modu**code VARCHAR(20),
  modi***time  timestamp with time zone default now() not null
)SERVER spclora OPTIONS (schema 'SP***DP', table 'T_USERINFO_MV',prefetch '10240');

至此，通过访问外部表，即可访问ORACLE数据库对应表，上例建表语句中的options表示oracle的表信息SP***DP.T_USERINFO_MV。pretetch，表示从oracle预取的行数，默认是200,建议配置到最大10240,根据我们的实战经验，配置该参数后，数据迁移的速度至少提高50%。

1. 由于在原表中以省份ID区分各省数据，列数据选择性较低，以省份过滤查询时，部分省份数据无法使用索引，全表扫描会导致迁移的时间延长；而且本次迁移，业务上也进行了更改，所以迁移数据时只需要原表的部分列。鉴于此情况，我们在源端使用了物化视图对原表数据进行裁剪，PG外部表与物化视图进行对应，这样迁移时可以减少无效的读取IO，缩短迁移时间。
2. 修改外部表的prefetch参数为10240。
3. 将多个表数据迁移编写脚本实现手工并行。