PostgreSQL: Oracle_fdw 数据迁移之高效

最近在做一个 Oracle 迁移 PostgreSQL 项目 ,数据迁移初步计划用 PostgreSQL 的外部表 oracle_fdw 来做,oracle,pg 测试环境搭好后,今天做了下测试,发现 Oracle_fdw 迁移数据效率挺高,22 GB 数据,迁移花了 55 分钟左右,下面是迁移的步骤:

环境信息

服务器配置信息
DELL R610
8 核 24 GB
硬盘:两块 SCSI 300 GB ( RAID 1)

版本信息

1
2
3
OS: Red Hat Enterprise Linux Server release 5.6  
PG: PostgreSQL 9.2beta2
Oracle: 10.2.0.4.0

PG 部分参数配置

1
2
3
4
5
shared_buffers = 1024MB  
synchronous_commit = off
autovacuum = off
checkpoint_segments = 128
checkpoint_timeout = 60 min

备注:为了提高写入速度,数据导入过程中关闭 autovacuum。

Oracle 参数配置

1
2
3
4
5
sga_target=5G  
pga_aggregate_target=1797M
db_file_multiblock_read_count=16
TEMP 表空间: 10GB
UNDO 表空间: 8GB

关于 oracle_fdw 安装
pg 上需要安装 oracle_fdw 模块,关于 oracle_fdw 的安装,本文略,具体可以参考之前写的 blog: https://postgres.fun/20120303174557.html

Oracle 库表信息

Oracle 需要抽取的表的表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
create table francs.TBL_FDW_TEST  
(
USER_ID NUMBER(10) not null,
STATE NUMBER(10) not null,
USER_NAME VARCHAR2(64),
BIND_MOBILE VARCHAR2(20),
BIND_EMAIL VARCHAR2(64),
PASSWD VARCHAR2(64) not null,
LOGIN_TIME DATE,
IMSI VARCHAR2(20),
IMEI VARCHAR2(20),
LOGIN_COUNT NUMBER(10) not null,
LAST_STIME DATE,
UPTAG NUMBER(10) not null
);

alter table francs.TBL_FDW_TEST add primary key (USER_ID);
alter table francs.TBL_FDW_TEST
add constraint UK_AUTH_USERNAME unique (USER_NAME)
using index
tablespace TBS_francs_IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

create index francs.IDX_TBL_FDW_TEST_LOGIN_TIME on francs.TBL_FDW_TEST (LOGIN_TIME);

PostgreSQL 表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table TBL_FDW_TEST (  
USER_ID NUMERIC(10) not null,
STATE NUMERIC(10) not null,
USER_NAME VARCHAR(64) null,
BIND_MOBILE VARCHAR(20) null,
BIND_EMAIL VARCHAR(64) null,
PASSWD VARCHAR(64) not null,
LOGIN_TIME timestamp(0) without time zone,
IMSI VARCHAR(20) null,
IMEI VARCHAR(20) null,
LOGIN_COUNT NUMERIC(10) not null,
LAST_STIME timestamp(0) without time zone,
UPTAG NUMERIC(10) not null,
constraint PK_AUTH_SKYID primary key (USER_ID),
constraint UK_AUTH_USERNAME unique (USER_NAME)
);

CREATE INDEX IDX_TBL_FDW_TEST_LOGIN_TIME ON TBL_FDW_TEST USING btree (LOGIN_TIME);

外部表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
francs=> \det ft_TBL_FDW_TEST  
List of foreign tables
Schema | Table | Server
--------+------------------+------------
francs | ft_TBL_FDW_TEST | oracle_srv
(1 row)

francs-> \d ft_TBL_FDW_TEST
Foreign table "francs.ft_TBL_FDW_TEST"
Column | Type | Modifiers | FDW Options
-------------+--------------------------------+-----------+-------------
USER_ID | numeric(10,0) | not null |
state | numeric(10,0) | not null |
user_name | character varying(64) | |
bind_mobile | character varying(20) | |
bind_email | character varying(64) | |
passwd | character varying(64) | not null |
login_time | timestamp(0) without time zone | |
imsi | character varying(20) | |
imei | character varying(20) | |
login_count | numeric(10,0) | not null |
last_stime | timestamp(0) without time zone | |
uptag | numeric(10,0) | not null |
Server: oracle_srv
FDW Options: (schema 'francs', "table" 'TBL_FDW_TEST')

备注:PG中建立相应的外部表 ft_TBL_FDW_TEST ,表结构和pg目标表 TBL_FDW_TEST 结构一致,当然外部表有些限制,比如不能有 default 值等。

迁移脚本

ORACLE 库中取最大 user_id

1
2
3
4
SQL> select max(sky_id) from francs.TBL_USER_auth;
MAX(SKY_ID)
-----------
295799801

insert_auth_1.sql 脚本

1
2
\timing  
insert into TBL_USER_AUTH select * from ft_TBL_USER_AUTH where USER_ID < 100000000;

insert_auth_2.sql 脚本

1
2
\timing  
insert into TBL_USER_AUTH select * from ft_TBL_USER_AUTH whereUSER_ID >= 100000000 and SKY_ID < 150000000;

insert_auth_3.sql 脚本

1
2
\timing  
insert into TBL_FDW_TEST select * from ft_TBL_FDW_TEST where USER_ID >= 150000000 and USER_ID < 200000000;

insert_auth_4.sql 脚本

1
2
\timing  
insert into TBL_FDW_TEST select * from ft_TBL_FDW_TEST where USER_ID >= 200000000 and USER_ID < 250000000;

insert_auth_5.sql 脚本

\timing  
insert into TBL_FDW_TEST select * from ft_TBL_FDW_TEST where USER_ID >= 250000000 and USER_ID < 300000000;

备注:由于在 PostgreSQL 中单个查询只能利用到一个 CPU 核,而不能像 Oracle 库中的 启用并行查询。所以这里逻辑上写了多个 sql,根据主键 USER_ID 进行划分,上面分成了五个脚本。

同时后台执行上面五个脚本,最后执行完的脚本的执行时间为 55 分钟左右。55 分钟只是数据迁移时间,索引的创建时间并没有包括,索引的创建时间较长,尤其是主键和 unique index 的添加耗时更长。

总结

22 GB 的数据从 Oracle 库中迁移到 PG 库只花费了 55 分钟左右,这个速度已经比较快了,oracle_fdw 模块是 Oracle 转 PG的一大利器,使用得好可以大大提高 O 转 P 的效率。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%