v2.0
MogDB为源库
支持版本
数据库版本: 不低于 2.0
依赖插件: wal2json
注: MogDB为源库,不支持PostgreSQL、SQL Server、DB2对象同步,请设置跳过同步对象,通道高级参数skipMigrationObject: ON
数据库设置
安装插件 wal2json
具体请参考: wal2json
修改源库 WAL 参数
修改源库参数配置文件 postgresql.conf
,参数文件位于数据库 data 目录下。
通过 gsql 工具登录 MogDB 数据库,查询数据目录位置:
gsql -d postgres -U $USER -c 'show data_directory'
修改以上命令查询到的数据目录中的 postgresql.conf 文件
# 注意替换 <data_directory> 为实际查询到的数据目录位置
vi <data_directory>/postgresql.conf
在 postgresql.conf 中找到如下参数,并修改为对应的参数值:
- wal_level=logical # 使用logical decoding读取预写日志
- wal_sender_timeout = 0s # 超时时间自定义
修改源库 HBA 配置
在数据库数据目录(<data_directory>)中,找到 pg_hba.conf
配置文件,在其中添加如下配置以便允许复制。
注意: mdb_user 为复制使用的用户,实际环境中若用户名有变更,则需要实际实际情况修改。
# 注意替换 <data_directory> 为实际查询到的数据目录位置
cat >> <data_directory>/pg_hba.conf <"EOF"
# Add for MDB replication
host replication mdb_user 0.0.0.0/0 sha256
EOF
设置用户权限
创建 mdb_user 用户,用于登录和复制源端 MogDB 数据,实际环境中用户名与密码可按实际需求自行修改。
其中:
- login 权限用于登录数据库
- replication 权限用于同步增量数据
- sysadmin 权限用于对象初始化和全量数据同步
create user mdb_user with login replication sysadmin PASSWORD 'Enmo@123';
打开表的 REPLICA IDENTITY 模式
针对需要被同步的所有表,都需要打开 REPLICA IDENTITY 模式,具体命令如下:
注意: 以下仅为命令示例,实际上需要对被同步的所有表,都设置 REPLICA IDENTITY。
ALTER TABLE public.customers REPLICA IDENTITY FULL;
开启心跳配置
发送心跳消息使连接器能够将最新检索到的LSN发送到数据库slot,从而允许数据库回收不再需要的WAL文件所使用的磁盘空间。
开启心跳需要在通道高级参数中,设置heartbeatIntervalMs为正整数,并在源节点执行以下命令。
注意: 以下仅为命令示例,表名heartbeat需与高级参数heartbeatTableName参数值保持一至。
CREATE TABLE heartbeat (ts TIMESTAMP WITH TIME ZONE);
INSERT INTO heartbeat (ts) VALUES (NOW());
数据类型映射
源库 | 目标库 | 源库类型 | 目标库类型 |
---|---|---|---|
MogDB | Oracle | bit | RAW |
MogDB | Oracle | varbit | RAW |
MogDB | Oracle | bytea | BLOB |
MogDB | Oracle | uuid | CLOB |
MogDB | Oracle | json | CLOB |
MogDB | Oracle | jsonb | CLOB |
MogDB | Oracle | xml | CLOB |
MogDB | Oracle | float4 | BINARY_FLOAT |
MogDB | Oracle | float8 | BINARY_DOUBLE |
MogDB | Oracle | oid | NUMBER |
MogDB | Oracle | int1 | NUMBER |
MogDB | Oracle | int2 | NUMBER |
MogDB | Oracle | int4 | NUMBER |
MogDB | Oracle | int8 | NUMBER |
MogDB | Oracle | money | NUMBER |
MogDB | Oracle | numeric | NUMERIC |
MogDB | Oracle | bpchar | CHAR |
MogDB | Oracle | char | CHAR |
MogDB | Oracle | name | VARCHAR2 |
MogDB | Oracle | varchar | VARCHAR2 |
MogDB | Oracle | bool | BLOB |
MogDB | Oracle | text | CLOB |
MogDB | Oracle | date | DATE |
MogDB | Oracle | interval | INTERVAL DAY TO SECOND |
MogDB | Oracle | time | TIMESTAMP |
MogDB | Oracle | timetz | TIMESTAMP WITH TIME ZONE |
MogDB | Oracle | timestamp | TIMESTAMP |
MogDB | Oracle | timestamptz | TIMESTAMP WITH TIME ZONE |
MogDB | Oracle | point | CLOB |
MogDB | Oracle | lseg | BLOB |
MogDB | Oracle | box | BLOB |
MogDB | Oracle | path | BLOB |
MogDB | Oracle | polygon | BLOB |
MogDB | Oracle | circle | BLOB |
MogDB | Oracle | cidr | BLOB |
MogDB | Oracle | inet | CLOB |
MogDB | Oracle | macaddr | CLOB |
MogDB | Oracle | tsquery | BLOB |
MogDB | Oracle | tsvector | BLOB |
MogDB | Oracle | aclitem | BLOB |
MogDB | Oracle | cid | BLOB |
MogDB | Oracle | gtsvector | BLOB |
MogDB | Oracle | int2vector | BLOB |
MogDB | Oracle | oidvector | BLOB |
MogDB | Oracle | oidvector_extend | BLOB |
MogDB | Oracle | refcursor | BLOB |
MogDB | Oracle | regclass | BLOB |
MogDB | Oracle | regconfig | BLOB |
MogDB | Oracle | regdictionary | BLOB |
MogDB | Oracle | regoper | BLOB |
MogDB | Oracle | regoperator | BLOB |
MogDB | Oracle | regproc | BLOB |
MogDB | Oracle | regprocedure | BLOB |
MogDB | Oracle | regtype | BLOB |
MogDB | Oracle | tid | BLOB |
MogDB | Oracle | txid_snapshot | BLOB |
MogDB | Oracle | xid | BLOB |
MogDB | Oracle | smallserial | NUMBER |
MogDB | Oracle | int4range | CLOB |
MogDB | Oracle | int8range | CLOB |
MogDB | Oracle | tsrange | CLOB |
MogDB | Oracle | daterange | CLOB |
MogDB | Oracle | ltree | CLOB |
MogDB | Oracle | citext | CLOB |
MogDB | Oracle | macaddr8 | CLOB |
MogDB | Oracle | geometry | CLOB |
MogDB | Oracle | geography | CLOB |
MogDB | Oracle | hstore | CLOB |
MogDB | Oracle | enum | VARCHAR2 |
MogDB | Oracle | serial | NUMBER |
MogDB | Oracle | numrange | CLOB |
MogDB | Oracle | json | JSON |
MogDB | Oracle | nvarchar2 | NVARCHAR2 |
MogDB | Oracle | abstime | BLOB |
MogDB | Oracle | reltime | BLOB |
MogDB | Oracle | smalldatetime | DATE |
MogDB | Oracle | raw | BLOB |
MogDB | Oracle | interval day to hour | INTERVAL DAY TO SECOND |
MogDB | Oracle | interval day to minute | INTERVAL DAY TO SECOND |
MogDB | Oracle | interval day to second | INTERVAL DAY TO SECOND |
MogDB | Oracle | interval hour to minute | INTERVAL DAY TO SECOND |
MogDB | Oracle | interval hour to second | INTERVAL DAY TO SECOND |
MogDB | Oracle | interval minute to second | INTERVAL DAY TO SECOND |
MogDB | Oracle | interval year to month | INTERVAL YEAR TO MONTH |
MogDB | MySQL | bit | binary |
MogDB | MySQL | varbit | longblob |
MogDB | MySQL | bytea | longblob |
MogDB | MySQL | uuid | varchar |
MogDB | MySQL | json | json |
MogDB | MySQL | jsonb | json |
MogDB | MySQL | xml | longtext |
MogDB | MySQL | float4 | double |
MogDB | MySQL | float8 | double |
MogDB | MySQL | int1 | smallint |
MogDB | MySQL | int2 | smallint |
MogDB | MySQL | int4 | int |
MogDB | MySQL | int8 | bigint |
MogDB | MySQL | money | decimal |
MogDB | MySQL | numeric | decimal |
MogDB | MySQL | bpchar | char |
MogDB | MySQL | char | char |
MogDB | MySQL | name | varchar |
MogDB | MySQL | varchar | varchar |
MogDB | MySQL | bool | boolean |
MogDB | MySQL | text | longtext |
MogDB | MySQL | date | date |
MogDB | MySQL | interval | varchar |
MogDB | MySQL | time | time |
MogDB | MySQL | timetz | time |
MogDB | MySQL | timestamp | datetime |
MogDB | MySQL | timestamptz | datetime |
MogDB | MySQL | point | varchar |
MogDB | MySQL | cidr | varchar |
MogDB | MySQL | inet | varchar |
MogDB | MySQL | macaddr | varchar |
MogDB | MySQL | tsquery | longtext |
MogDB | MySQL | tsvector | longtext |
MogDB | MySQL | enum | enum |
MogDB | MySQL | nvarchar2 | varchar |
MogDB | MySQL | abstime | tinyblob |
MogDB | MySQL | reltime | tinyblob |
MogDB | MySQL | smalldatetime | datetime |
MogDB | MySQL | raw | blob |
字符集映射
源库 | 目标库 | 源库字符集 | 目标库字符集 |
---|---|---|---|
MogDB | Oracle | BIG5 | ZHT16BIG5 |
MogDB | Oracle | EUC_JP | JA16EUC |
MogDB | Oracle | EUC_JIS_2004 | JA16EUCTILDE |
MogDB | Oracle | EUC_TW | ZHT32EUC |
MogDB | Oracle | GB18030 | ZHS32GB18030 |
MogDB | Oracle | GBK | ZHS16GBK |
MogDB | Oracle | ISO_8859_5 | CL8ISO8859P5 |
MogDB | Oracle | ISO_8859_6 | AR8ISO8859P6 |
MogDB | Oracle | ISO_8859_7 | EL8ISO8859P7 |
MogDB | Oracle | ISO_8859_8 | IW8ISO8859P8 |
MogDB | Oracle | JOHAB | KO16KSCCS |
MogDB | Oracle | KOI8R | CL8KOI8R |
MogDB | Oracle | KOI8U | CL8KOI8U |
MogDB | Oracle | LATIN1 | WE8ISO8859P1 |
MogDB | Oracle | LATIN2 | EE8ISO8859P2 |
MogDB | Oracle | LATIN3 | SE8ISO8859P3 |
MogDB | Oracle | LATIN4 | NEE8ISO8859P4 |
MogDB | Oracle | LATIN5 | WE8ISO8859P9 |
MogDB | Oracle | LATIN6 | NE8ISO8859P10 |
MogDB | Oracle | LATIN7 | BLT8ISO8859P13 |
MogDB | Oracle | LATIN8 | CEL8ISO8859P14 |
MogDB | Oracle | LATIN9 | WE8ISO8859P15 |
MogDB | Oracle | SJIS | JA16SJIS |
MogDB | Oracle | SHIFT_JIS_2004 | JA16SJISTILDE |
MogDB | Oracle | SQL_ASCII | US7ASCII |
MogDB | Oracle | UHC | KO16MSWIN949 |
MogDB | Oracle | UTF8 | AL32UTF8 |
MogDB | Oracle | WIN866 | RU8PC866 |
MogDB | Oracle | WIN1250 | EE8MSWIN1250 |
MogDB | Oracle | WIN1251 | CL8MSWIN1251 |
MogDB | Oracle | WIN1252 | WE8MSWIN1252 |
MogDB | Oracle | WIN1253 | EL8MSWIN1253 |
MogDB | Oracle | WIN1254 | TR8MSWIN1254 |
MogDB | Oracle | WIN1255 | IW8MSWIN1255 |
MogDB | Oracle | WIN1256 | AR8MSWIN1256 |
MogDB | Oracle | WIN1257 | BLT8MSWIN1257 |
MogDB | Oracle | WIN1258 | VN8MSWIN1258 |
MogDB | MySQL | UTF8 | UTF8 |
MogDB | MySQL | GBK | GBK |
MogDB | MySQL | BIG5 | BIG5 |
MogDB | MySQL | LATIN1 | LATIN1 |
MogDB | MySQL | LATIN2 | LATIN2 |
MogDB | MySQL | SJIS | SJIS |
MogDB | MySQL | EUC_KR | EUCKR |
MogDB | MySQL | KOI8U | KOI8U |
MogDB | MySQL | LATIN5 | LATIN5 |
MogDB | MySQL | LATIN7 | LATIN7 |
MogDB | MySQL | GB18030 | GB2312 |
MogDB | postgresql | BIG5 | BIG5 |
MogDB | postgresql | EUC_CN | EUC_CN |
MogDB | postgresql | EUC_JP | EUC_JP |
MogDB | postgresql | EUC_JIS_2004 | EUC_JIS_2004 |
MogDB | postgresql | EUC_KR | EUC_KR |
MogDB | postgresql | EUC_TW | EUC_TW |
MogDB | postgresql | GB18030 | GB18030 |
MogDB | postgresql | GBK | GBK |
MogDB | postgresql | Windows936 | Windows936 |
MogDB | postgresql | ISO_8859_5 | ISO_8859_5 |
MogDB | postgresql | ISO_8859_6 | ISO_8859_6 |
MogDB | postgresql | ISO_8859_7 | ISO_8859_7 |
MogDB | postgresql | ISO_8859_8 | ISO_8859_8 |
MogDB | postgresql | JOHAB | JOHAB |
MogDB | postgresql | KOI8R | KOI8R |
MogDB | postgresql | KOI8U | KOI8U |
MogDB | postgresql | LATIN1 | LATIN1 |
MogDB | postgresql | LATIN2 | LATIN2 |
MogDB | postgresql | LATIN3 | LATIN3 |
MogDB | postgresql | LATIN4 | LATIN4 |
MogDB | postgresql | LATIN5 | LATIN5 |
MogDB | postgresql | LATIN6 | LATIN6 |
MogDB | postgresql | LATIN7 | LATIN7 |
MogDB | postgresql | LATIN8 | LATIN8 |
MogDB | postgresql | LATIN9 | LATIN9 |
MogDB | postgresql | LATIN10 | LATIN10 |
MogDB | postgresql | MULE_INTERNAL | MULE_INTERNAL |
MogDB | postgresql | SJIS | SJIS |
MogDB | postgresql | SHIFT_JIS_2004 | SHIFT_JIS_2004 |
MogDB | postgresql | SQL_ASCII | SQL_ASCII |
MogDB | postgresql | UHC | UHC |
MogDB | postgresql | UTF8 | UTF8 |
MogDB | postgresql | WIN866 | WIN866 |
MogDB | postgresql | WIN874 | WIN874 |
MogDB | postgresql | WIN1250 | WIN1250 |
MogDB | postgresql | WIN1251 | WIN1251 |
MogDB | postgresql | WIN1252 | WIN1252 |
MogDB | postgresql | WIN1253 | WIN1253 |
MogDB | postgresql | WIN1254 | WIN1254 |
MogDB | postgresql | WIN1255 | WIN1255 |
MogDB | postgresql | WIN1256 | WIN1256 |
MogDB | postgresql | WIN1257 | WIN1257 |
MogDB | postgresql | WIN1258 | WIN1258 |