最近在做一个 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 的效率。
原创文章，作者：254126420，如若转载，请注明出处：https://blog.ytso.com/237879.html
