环境

• OS version: Red Hat Enterprise Linux Server release 6.5
• Oracle version: 19.3.0.0.0
• PostgreSQL version: 12.3
• ora2pg: v21.0

ps: 为了方便同步，在PG库中创建 scott 用户和模式。

目录结构

scott/
├── conf
│   ├── table_meta.conf
│   └── table_sync.conf
├── log
├── oracle
└── sql
    └── create_table.sql

获取表结构

先来看一下 ora2pg 配置文件的内容

[root@localhost conf]# cat table_meta.conf 
PG_VERSION 12
ORACLE_HOME /usr/lib/oracle/12.2/client64/
#Set Oracle database connection (data source, user, password)
ORACLE_DSN dbi:Oracle:host=192.168.228.77;service_name=orcl;port=1521
ORACLE_USER system
ORACLE_PWD sys_1234
SCHEMA scott
DEBUG 1
ORA_INITIAL_COMMAND
EXPORT_SCHEMA 0
CREATE_SCHEMA 1
COMPILE_SCHEMA 0
NLS_LANG    AMERICAN_AMERICA.UTF8
TYPE  TABLE  
OUTPUT  /root/shx/ora2pg/scott/sql/create_table.sql

Oracle 中 scott 的对象

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ ----------------------------
SCOTT                PK_DEPT                        INDEX
SCOTT                EMP                            TABLE
SCOTT                PK_EMP                         INDEX
SCOTT                BONUS                          TABLE
SCOTT                SALGRADE                       TABLE
SCOTT                DEPT                           TABLE

接下来通过上面的配置文件来获取oracle端的元数据：

[root@localhost ora2pg]# cd scott/conf/
[root@localhost conf]# ora2pg -c table_meta.conf 
[2021-03-28 17:01:08] Ora2Pg version: 21.0
[2021-03-28 17:01:08] Trying to connect to database: dbi:Oracle:host=192.168.228.77;service_name=orcl;port=1521
[2021-03-28 17:01:08] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
[2021-03-28 17:01:08] Looking forward functions declaration in schema SCOTT.
[2021-03-28 17:01:08] Retrieving table information...
[2021-03-28 17:01:32] Retrieving index information...
[2021-03-28 17:01:47] Retrieving columns information...
[2021-03-28 17:02:02] Retrieving comments information...
[2021-03-28 17:02:07] Retrieving foreign keys information...
[2021-03-28 17:02:27] Retrieving unique keys information...
[2021-03-28 17:02:45] Retrieving check constraints information...
[2021-03-28 17:02:54] [1] Scanning table BONUS (1 rows)...
[2021-03-28 17:02:54] [2] Scanning table DEPT (1 rows)...
[2021-03-28 17:02:54] [3] Scanning table EMP (1 rows)...
[2021-03-28 17:02:54] [4] Scanning table SALGRADE (1 rows)...
[2021-03-28 17:02:59] Exporting tables...
[2021-03-28 17:02:59] Dumping table BONUS...
[2021-03-28 17:02:59] Dumping table DEPT...
[2021-03-28 17:02:59] Dumping table EMP...
[2021-03-28 17:02:59] Dumping table SALGRADE...
[2021-03-28 17:02:59] Dumping RI EMP...
[2021-03-28 17:02:59] Fixing function calls in output files...

由上面的结果可以看出，ora2pg 会检索 oracle 库中的 table, index, cloumns, mommets and constraints，但是有些表在同步过程中可能会因为外键约束的影响，导致数据同步失败，因此如果同步的表有外键约束，最好先删掉，数据同步完成后再创建。

PostgreSQL 中创建表

[root@localhost sql]# psql -h 192.168.228.76 -p 5555 -d postgres -U scott -W -f create_table.sql 
Password: 
SET
SET
SET
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE

同步

同步配置文件内容

ORACLE_HOME /usr/lib/oracle/12.2/client64/
ORACLE_DSN dbi:Oracle:host=192.168.228.77;sid=orcl;port=1521
ORACLE_USER system
ORACLE_PWD sys_1234
SCHEMA scott
CREATE_SCHEMA 1
TRUNCATE_TABLE 1
STOP_ON_ERROR 0
TYPE  COPY
PG_VERSION 12.3
PG_DSN dbi:Pg:dbname=postgres;host=192.168.228.76;port=5555
PG_SCHEMA scott
PG_USER scott
PG_PWD tiger
FILE_PER_TABLE 1
JOBS 2

开始同步…

[root@localhost conf]# ora2pg -c table_sync.conf 
[========================>] 4/4 tables (100.0%) end of scanning.       
[>                        ] 0/1 rows (0.0%) Table BONUS (0 sec., 0 recs/sec)
DBD::Pg::db do failed: ERROR:  cannot truncate a table referenced in a foreign key constraintess.
DETAIL:  Table "emp" references "dept".
HINT:  Truncate table "emp" at the same time, or use TRUNCATE ... CASCADE. at /usr/local/share/perl5/Ora2Pg.pm line 3598.
FATAL: ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "emp" references "dept".
HINT:  Truncate table "emp" at the same time, or use TRUNCATE ... CASCADE.
Aborting export...

报错，就是上面所说的外键导致的报错，在同步的配置文件中TRUNCATE_TABLE 1 使得在表同步之前会先 truncate PG 库中的表，因此违反了外键约束。

删除掉外键约束…

[root@localhost ~]# psql -h 192.168.228.76 -p 5555 -d postgres -U scott -c "alter table emp drop constraint fk_deptno;" -W
Password: 
ALTER TABLE

重新开始同步…

[root@localhost conf]# ora2pg -c table_sync.conf 
[========================>] 4/4 tables (100.0%) end of scanning.       
[>                        ] 0/1 rows (0.0%) Table BONUS (1 sec., 0 recs/sec)
[========================>] 4/1 rows (400.0%) Table DEPT (0 sec., 4 recs/sec)                    
[========================>] 14/1 rows (1400.0%) Table EMP (0 sec., 14 recs/sec)                   
[========================>] 5/1 rows (500.0%) Table SALGRADE (0 sec., 5 recs/sec)                  
Fixing function calls in output files...ows (575.0%) - (1 sec., avg: 23 recs/sec), SALGRADE in progress.
[========================>] 23/4 rows (575.0%) on total estimated data (1 sec., avg: 23 tuples/sec)  

同步成功。

最后再把外键约束加上：

[root@localhost ~]# psql -h 192.168.228.76 -p 5555 -d postgres -U scott -c "ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;" -W
Password: 
ALTER TABLE

查看表的数量

postgres=> select * from pg_tables where tableowner='scott';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 scott      | bonus     | scott      |            | f          | f        | f           | f
 scott      | dept      | scott      |            | t          | f        | t           | f
 scott      | emp       | scott      |            | t          | f        | t           | f
 scott      | salgrade  | scott      |            | f          | f        | f           | f
(4 rows)

其他

目前大部分表的同步利用这个工具都可以完成，但是还有部分情况不能顺利同步。

另外，同步完成后Oracle和PG的行数对比还没想好利用什么方式进行对比。