oracle迁移至Postgresql最佳实践

一、本文背景

去O 去O 去O，大势所趋，尽管O库还是那么强大。oracle迁移到pg ，可参考Oracle to Postgres Conversion

ora2pg 不错，还有pg的 fdw ，这些都不错，加上自己再写一些触发器，也可以做到无缝迁移，但是这样的工作量是相当大的，如果用于生产迁移。需要的准备工作和测试，最少半年以上，而且工作相当繁琐。

我们测试了很多，包括项目中一直用的pyspark，这东西很强，做etl有点大材小用。最后用ora2pg迁移数据结构，包括表结构、存储过程、函数等；用老耿的 osync进行初始化；用oggoracle产品支持列表和pg自身的logical replication pg逻辑复制进行实时同步。哈哈，还是和o有关的。

多说一句，为设么要用pg的逻辑复制。以为ogg只支持到pg11，我们需要用pg12.那么pg11到pg12有两种方式，一个是通过逻辑复制将pg11数据实时同步到pg12 ，一个是通过pg_upgrade将pg11升级到pg12

二、问题分析最佳实践

这里讲讲我们的一些测试过程和遇到的问题。

2.1 ogg的坑

严格来说不能怪ogg，因为开始没有去看官方的支持列表

如果迁移到pg11.3及以下的版本，那么ogg会相当给力。因为ogg目前对pg的支持只有ogg11.2，并且pg最高版本不能超过pg11.3 。11.3以后，pg的数据字典有变化，参考pg11的pg_attrdef 和 pg12的pg_attrdef 可知在新版本12少了字段adsrc。

2.2 如何查问题

在配置pg端配置ogg的时候，进行 capture tabledef public.test
测试时总是提示 table not found。检查了大小写、权限等都没有问题。
最后怀疑是ogg的bug（尽管这不是bug，而是ogg for pg12 还没有进行研发）。
我们开始了bug 验证过程。

使用linux 自带工具 strace 查看 ogg 的 capture 过程

cd data/ogg #进入ogg安装目录
strace -o trc.out ./ggsc  # 进入 ogg命令行

最后通过查看 trc.out可知ogg 通过执行一些sql 去查pg的数据，但是trace内容捕获不到具体sql。

我们通过pg 后台查询当前正在执行的sql，找到下面这样的SQL。

SELECT g as cat,n.nspname as schema,c.relname as name,a.attname as colname,a.atttypid as datatype,(case q.typname when 'bpchar' then 'character' when 'varchar' then 'character varying' when 'int2' then 'smallint' when 'int4' then 'integer' when 'int8' then 'bigint' when 'float4' then 'real' when 'float8' then 'double precision' else q.typname end) as typname,(case when q.typname='numeric' then (case when a.atttypmod=-1 then -1 else a.atttypmod/65536 end) when a.atttypmod>0 and (q.typname='bpchar' or q.typname='varchar') then a.atttypmod-4 else a.atttypmod end) as colsize,(case when q.typname='numeric' then (case when a.atttypmod=-1 then -1 else a.atttypmod/65536+1+(((a.atttypmod-4)%65536)>0)::int4 end) else a.attlen end) as buflen,(case when q.typname='numeric' then (case when a.atttypmod<=0 then a.atttypmod else a.atttypmod%65536-4 end) when q.typname='timestamp' or q.typname='time' then 6 else null end) as digits,null as radix,(CASE WHEN a.attnotnull THEN 0 ELSE 1 END) as nullable,d.description,pg_get_expr(f.adbin, f.adrelid) as default as default,null as sqltype,null as dtsub,null as octlen,a.attnum as ordinal,(CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END) as is_nullable FROM current_database() g,pg_catalog.pg_type q,pg_catalog.pg_namespace n,pg_catalog.pg_class c,pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_description d ON (a.attrelid=d.objoid AND a.attnum=d.objsubid) LEFT JOIN pg_catalog.pg_attrdef f ON (a.attrelid=f.adrelid AND a.attnum=f.adnum) WHERE a.attnum>0 AND c.oid=a.attrelid AND n.oid=c.relnamespace AND a.atttypid=q.oid AND c.relkind in ('r','v') AND has_schema_privilege(n.oid, 'USAGE') AND  n.nspname LIKE 'ogg' AND  c.relname LIKE 'ggtest' order by 1,2,3,17

该sql 去pg（测试时版本为pg12）中执行发现报错，pg_attrdef 找不到字段 adsrc

2.3 如何解决问题

问题找到了，那么就确定了这个”Bug"。接下来是如何解决。
有两种办法，一个是给ogg打补丁，一个是给pg "打补丁"。最后我们通过修改数据字典pg_catalog.pg_attrdef添加一个字段 adsrc，解决了该问题。

如何修改pg数据字典
1、修改PG参数 allow_system_table_mods='on'
并重启PG
2、alter table pg_catalog.pg_attrdef add column adsrc text ;

OK,如果时间在此刻停止，一切将是那么的完美。不过..... continue....

2.4 Bug的延伸

通过修改pg的数据字典骗过了ogg，但是骗人是不对的，虽然ogg不是人，也给我们带来了巨大的麻烦。
后续在做其它测试时，比如大批量的pg_dump ，pg_restore时，pg会崩溃，从而中断了后续进程。

server process (PID 34299) was terminated by signal 11: Segmentation fault

开始误认为是OOM，后来知道OOM是9，而signal 为11 一般是bug
通过分析 pg 的 core dump ，确认是Bug 。现在刚刚开始搞pg，还没有解决的这个能力。后面需要学习下如何分析，如何提交bug，如果解决。关于这个知识点，可参考德哥的[PostgreSQL+MySQL 联合解决方案课程 - 汇总视频、课件] (https://github.com/digoal/blog/blob/master/202001/20200118_02.md)中的第14课

三、OGG for Postgresql

可参考oracle OMS 文档 1544137.1
此处不做详细说明，只列出大纲。

注意：
源端oracle要开启最小日志捕获 alter database add supplemental log data;
源端需要开启归档
源端ogg 要进行 add trandata tableName
目标端禁用触发器、外键约束

3.1 配置环境

oracle 源端的环境变量配置
略

oracle 源端创建ogg 用户
略

postgresql目标端的环境配置

export GGATE=/data/ogg
export PGHOME=/usr/pgsql-12
export PGDATA=/data/pgsql/12/data/
export PATH=$PGHOME/bin:$HOME/bin:$GGATE:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$GGATE/lib:$LD_LIBRARY_PATH
export ODBCINI=$GGATE/odbc.ini
alias ggsci='rlwrap ggsci'

postgresql 目标端创建 ogg用户

CREATE ROLE ogg superuser PASSWORD 'ogg' login;

3.2 源端安装ogg

略

3.3 源端配置ogg

进入ogg环境创建子目录

$./ggsci
ogg>create subdirs

3.4 源端测试数据源登录

ogg>dblogin userid ogg, password ogg
ogg>list test.*
ogg>capture tabledef test.test

3.5 目标端安装ogg

略

3.6 目标端安装ogg

略

3.7 目标配置ogg

ogg>create subdirs

配置ODBC数据源

[ODBC Data Sources]
postgre=DataDirect 12 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=106
InstallDir=/data/ogg
[gg_postgres]
Driver=/data/ogg/lib/GGpsql25.so
Description=DataDirect 12 PostgreSQL Wire Protocol
Database=testdb
HostName=localhost
PortNumber=5432
LogonID=ogg
Password=ogg

3.8 目标端测试数据源登录

此处的 gg_postgres 是前面 ODBC数据源中配置的【gg_postgres】

ogg>dblogin sourcedb gg_postgres userid postgres
ogg>list test.test
ogg>capture tabledef test.test

至此，ogg的安装配置算是测试通过。后面进行数据同步的配置

3.9 、配置同步策略

3.9.1 配置文件定义

在源端生成数据文件定义

./ggsci > edit param defgen
DEFSFILE ./dirdef/GGDEF.def
USERID ogg, password ogg
table test.test  ;

生成定义文件

$ ./defgen paramfile ./dirprm/defgen.prm

拷贝文件到目标库

$scp ./dirdef/GGDEF.def  ogg@target_ip:/data/ogg/dirdef/

3.9.2 源端创建 extract

源端创建 extract

EXTRACT suser
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg, PASSWORD ogg
exttrail ./dirdat/us
TABLE test.test;

源端添加 extract

add extract suser, tranlog, begin now
add exttrail ./dirdat/us, extract suser, megabytes 100

源端启动

start suser

3.9.3 源端创建pump

pump也是一种extract ，是为了防止直接将ogg捕获的数据变化的文件传输到目标端过程中因为网络中断而发生数据损坏的问题。extract 捕获数据后放到源端本地，pump 通过读取extract捕获的数据，然后传输到目标端，这样如果网络中断，pump可在网络恢复后继续传输(自动恢复传输需要配置mgr autostart选项)
添加一个pump ，可以让extract 把捕获的数据先放在本地，防止网络断掉数据丢失。

源端创建 pump
目标端ip地址：192.168.20.155

extract puser
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg, PASSWORD ogg
RMTHOST 192.168.20.155, MGRPORT 7809
RMTTRAIL  ./dirdat/us
TABLE test.test;

源端添加pump

add extract puser,exttrailsource ./dirdat/us
add rmttrail ./dirdat/us,extract puser,megabytes 100

源端启动pump

start puser

3.9.4 目标端创建replicat

目标端创建 relicat

REPLICAT ruser
SOURCEDEFS ./dirdef/GGDEF.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/data/ogg/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB GG_Postgres, USERID ogg, PASSWORD ogg
DISCARDFILE ./dirrpt/disk_user.dsc, purge
MAP test.test, TARGET test.test;

目标端添加 replicat

add replicat ruser,  NODBCHECKPOINT, exttrail ./dirdat/us

目标端启动 replicat
此处的scn 是oracle 导出数据时指定数据一致性时用到的scn
也就是说在源端oracle 数据导出时，需要指定一个一致时间点 scn
如果没有老耿的 osync 工具，那么需要先通过oracle的 expdp 导出一个一致性数据（指定一个scn），然后恢复到另外一个oracle 数据库，再使用ora2pg 等其它工具将数据同步到 pg

start ruser,aftercsn 10103524728

至此，同步完成。后续主要停掉oracle那边的应用，开启pg这边的应用即可完成迁移，几乎无感迁移。

若有收获，就赞赏下吧