一、DM端操作步骤

1.1 准备工作

达梦端安装目录及配置文件目录：/home/dmdba/dmhs/bin

1.1.1 设置环境变量

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
export LD_LIBRARY_PATH=/dm8/bin:/home/dmdba/dmhs/bin
export LD_LIBRARY_PATH=/dm8/bin:/home/dmdba/dmhs/bin:home/dmdba/dmhs/bin/lib

1.1.2 配置归档及开启逻辑附加日志

修改dm.ini参数，开启归档，重启数据库使参数生效.
ARCH_INI参数值设置为1
归档文件例子：（如果是主备集群请参考主备集群手册）
[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /opt/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE = 512
ARCH_SPACE_LIMIT = 0
开启逻辑附加日志RLOG_APPEND_LOGIC参数的值设置为1

1.1.3 安装HS 软件执行/home/dmdba/dmhs/scripts/ddl_sql_dm8.sql 文件

创建同步测试用户TEST
上传OCI文件至数据库安装目录bin目录下：
/home/dmdba/dmdbms/bin

1.1.4 DM端的dmhs的配置文件

数据库使用的是dmhs目录下的bin目录 dmhs.hs 文件配置：
<?xml version=“1.0” encoding=“GB2312” standalone=“no”?>
<dmhs>
<base>
<lang>en</lang>
<mgr_port>5345</mgr_port>
<chk_interval>3</chk_interval>
<ckpt_interval>60</ckpt_interval>
<siteid>2</siteid>
<version>2.0</version>
</base>
<exec>
<recv>
<mgr_port>5345</mgr_port><!-- 接收管理端口–>
<data_port>5346</data_port> <!-- 接收数据端口–>
</recv>
<db_type>DM8</db_type>
<db_server>192.168.24.122</db_server>
<db_user>SYSDBA</db_user> <!-- 数据库用户名–>
<db_pwd>SYSDBA</db_pwd>
<db_port>15236</db_port>
<exec_policy>2</exec_policy>
<exec_thr>4</exec_thr> <!–执行线程参数–>
<toggle_case>2</toggle_case> <!–小写转为大写–>
<seq_sync_mode>1</seq_sync_mode> <!–序列同步参数–>
<trxid_tables>5</trxid_tables>
<ddl_continue>1</ddl_continue>
<save_point_times>4294967294</save_point_times>
<clear_trx_file>1</clear_trx_file>
<trx_max_file>5</trx_max_file>
</exec>
<cpt>
<db_type>dm8</db_type>
<db_server>192.168.24.122</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>15236</db_port>
<idle_time>300</idle_time>
<ddl_mask>op:table:index:sequence:view:SCHEMA:USER</ddl_mask>
<char_code>PG_UTF8</char_code>
<arch>
<clear_interval>600</clear_interval>
<clear_flag>0</clear_flag>
</arch>
<send>
<ip>192.168.24.123</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<net_pack_size>256</net_pack_size>
<net_turns>0</net_turns>
<crc_check>0</crc_check>
<trigger>0</trigger>
<constraint>0</constraint>
<identity>0</identity>
<filter>
<enable>
<item>TEST.</item>
</enable>
<disable/>
</filter>
<map>
<item>TEST. == public.*</item>
</map>
</send>
</cpt>
</dmhs>

二、PG端操作步骤

2.1、部署PGSQL数据库

下载数据库包
https://www.postgresql.org/ftp/source/v11.1/ ###PG数据库包下载路径
https://www.postgresql.org/ftp/odbc/versions/src/ ### PG-ODBC下载路径

2.1.1 安装PG端依赖

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

2.1.2 解压并编译

[root@localhost ~]# mkdir /pgsql
[root@localhost ~]# tar -zxvf postgresql-11.1.tar.gz -C /pgsql/
[root@weekend02 postgresql-11.1]# ./configure --prefix=/pgsql/postgresql
[root@weekend02 postgresql-11.1]# make && make install

2.1.3 创建用户组postgres并创建用户postgres

[root@weekend02 postgresql-11.1]# groupadd postgres
[root@weekend02 postgresql-11.1]# useradd -g postgres postgres
[root@weekend02 postgresql-11.1]# passwd postgres
输入用户密码：qwer1234
[root@weekend02 postgresql-11.1]# id postgres
uid=501(postgres) gid=501(postgres) 组=501(postgres)
[root@weekend02 postgresql-11.1]# cd /pgsql/postgresql
[root@weekend02 postgresql]# mkdir data
[root@weekend02 postgresql]# chown postgres:postgres data

2.1.4 添加环境变量

cd /home/postgres
vi .bash_profile
export PGHOME=/pgsql/postgresql
export PGDATA=/pgsql/postgresql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin
vim /etc/profile

export PGHOME=/pgsql/postgresql
export PGDATA=/pgsql/postgresql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin

[root@weekend02 postgres]# source .bash_profile /etc/profile

2.1.5使用postgres用户initdb初使用化数据库

[postgres@localhost ~]$ initdb --encoding=UTF8
[postgres@localhost ~]$ cd /pgsql/postgresql/data/
[postgres@localhost data]$ ls
base pg_ident.conf pg_serial pg_tblspc postgresql.auto.conf
global pg_logical pg_snapshots pg_twophase postgresql.conf
pg_commit_ts pg_multixact pg_stat PG_VERSION
pg_dynshmem pg_notify pg_stat_tmp pg_wal
pg_hba.conf pg_replslot pg_subtrans pg_xact
[postgres@localhost data]$

配置服务：修改/pgsql/postgresql/data目录下的两个文件
postgresql.conf ###配置PostgreSQL数据库服务器的相应的参数
pg_hba.conf ###配置对数据库的访问权限

[postgres@weekend02 data]$ vi postgresql.conf

listen_addresses = ‘’ # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to ‘localhost’; use '’ for all
# (change requires restart)
port = 5432 # (change requires restart)
wal_level = replica # minimal, replica, or logical
archive_mode = on #on打开归档，off关闭归档
archive_command = ‘test ! -f /opt/postgresql/data/12_bak/%f && cp %p /opt/postgresql/data/12_bak/%f’ #归档路经，12_bak为新建存放归档目录需手动创建；

2.1.6 设置PostgreSQL开机自启动

[postgres@weekend02 pgsql]$ cd /pgsql/postgresql-11.1/contrib/start-scripts
[postgres@weekend02 start-scripts]$ ls
freebsd linux macos
[root@weekend02 start-scripts]# chmod a+x linux
[root@weekend02 start-scripts]# cp linux /etc/init.d/postgresql
修改/etc/init.d/postgresql文件的两个变量
prefix设置为postgresql的安装路径：/pgsql/postgresql
PGDATA设置为postgresql的数据目录路径：/pgsql/postgresql/data

设置开机启动：
[root@weekend02 init.d]# chkconfig --add postgresql
查看端口：
[root@weekend02 sysconfig]# ss -antup |grep 5432

[root@weekend02 init.d]# service postgresql start
Starting PostgreSQL: ok

2.1.7 设置pg端用户，密码以及数据库

postgres=# create user pgtest password ‘123456’;
CREATE ROLE
postgres=# ALTER ROLE pgtest SUPERUSER;
ALTER ROLE
postgres=# create database test;
CREATE DATABASE
postgres=# \q
建测试数据库TEST
test创建用户pgtest 并授予test为超级管理用户
postgres=# ALTER ROLE pgtest SUPERUSER;
ALTER ROLE
同时授予权限：grant ALL PRIVILEGES on all tables in schema public to test;

2.1.8 创建pg端辅助表

执行路径：/home/dmdba/dmhs/scripts/ddl_sql_pg.sql
执行前需修改ddl_sql_pg.sql中的以下两项
dname varchar(64) := ‘test’;–请把这个XXX改成实际所在库的库名！！
create table public.“DMHS_DDL_LOG” 若使用的不是public需要替换为使用的用户

2.1.9 安装ODBC

解压编译odbc
[root@localhost ~]# tar -zxcf unixODBC-2.3.9.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/unixODBC-2.3.9/
编译：
[root@localhost ~]#./configure
[root@localhost ~]# make
[root@localhost ~]# make install
odbc配置文件
[postgres@localhost ~]$ cat /usr/local/etc/odbcinst.ini
[PostgreSQL ANSI(x64)]
Description=PostgreSQL driver for Linux
Driver=/usr/local/pgodbc92/lib/psqlodbcw.so
Setup=/usr/local/pgodbc92/lib/psqlodbcw.so
UsageCount=1

[MySQL ODBC 8.0 Driver]
Driver=/usr/local/lib/libmyodbc8a.so
UsageCount=2

#[PostgreSQL1]
#Description=PostgreSQL driver for Linux
#Driver=/usr/local/pgodbc92/lib/psqlodbcw.so
#Setup=/usr/local/pgodbc92/lib/psqlodbcw.so
#UsageCount=1
[postgres@localhost ~]$

[postgres@localhost ~]$ cat /usr/local/etc/odbc.ini
[PG92]
Description = PostgreSQLODBC
Driver = PostgreSQL ANSI(x64)
Database = test
Servername = localhost
UserName = pgtest
Password = 123456
Port = 5432
ReadOnly = 0

[MYSQL]
Description = MYSQL ODBC DSN
Driver =MySQL ODBC 8.0 Driver
SERVER = 192.168.24.121
PORT = 3306
UID = test
PWD = mysql123456

#[PG93]
#Description = PostgreSQLODBC
#Driver = PostgreSQL1
#Database = pg
#Servername = localhost
#UserName = pg
#Password = pg123
#Port = 5432
#ReadOnly = 0

[postgres@localhost ~]$

su - postgres
[postgres@localhost ~]$ isql -v PG92 pgtest 123456

2.1.10 安装Postgresql ODBC驱动

[root@localhost ~]# tar -zxvf psqlodbc-9.02.0000.tar.gz -C /usr/local/
[root@localhost psqlodbc-9.02.0100]# ./configure --prefix=/usr/local/pgodbc10 --with-libpq=/usr/pgsql-10 --includedir=/usr/pgsql-10/include
[root@localhost psqlodbc-9.02.0100]# make
[root@localhost psqlodbc-9.02.0100]# make install
注释：–with-libpq PG的安装目录
–prefix 驱动指定的安装目录

2.2 PG端部署HS软件

2.2.1 PG端hs配置文件信息

<?xml version=“1.0” encoding=“GB2312”?>
<dmhs>
<base>
<siteid>1</siteid>
<mgr_port>5345</mgr_port>
<chk_interval>3</chk_interval>
<ckpt_interval>60</ckpt_interval>
<lang>en</lang>
<version>2.0</version>
</base>
<exec>
<recv>
<mgr_port>5345</mgr_port> <!-- 接收管理端口–>
<data_port>5346</data_port> <!-- 接收数据端口–>
</recv>
<db_type>postgres</db_type>
<db_server>127.0.0.1</db_server>
<db_user>pgtest</db_user> <!–数据库用户名–>
<db_pwd>123456</db_pwd>
<driver>/usr/local/pgodbc92/lib/psqlodbcw.so</driver>
<db_name>test</db_name> <!–同步数据库–>
<db_port>5432</db_port>
<level>0</level>
<ddl_continue>1</ddl_continue>
<case_sensitive>0</case_sensitive>
<enable_rowid>0</enable_rowid>
<exec_mode>0</exec_mode>
<exec_thr>16</exec_thr> <!–执行线程参数–>
<toggle_case>0</toggle_case> <!–对象转为大写–>
<exec_policy>0</exec_policy>
<commit_policy>0</commit_policy>
<enable_merge>0</enable_merge>
<affect_row>1</affect_row>
<seq_sync_mode>1</seq_sync_mode> <!–序列同步参数–>
<trxid_tables>5</trxid_tables>
<!–save_point_times>4294967294</save_point_times–>
<clear_trx_file>1</clear_trx_file>
<trx_max_file>5</trx_max_file>
</exec>
<cpt>
<db_type>postgres</db_type>
<db_server>localhost</db_server>
<db_user>pgtest</db_user>
<db_pwd>123456</db_pwd>
<db_name>test</db_name>
<db_port>5432</db_port>
<idle_time>300</idle_time>
<!–driver>/usr/local/pgodbc92/lib/psqlodbcw.so</driver–>
<ddl_mask>op:obj</ddl_mask>
<char_code>PG_UTF8</char_code>
<constraint>1</constraint>
<arch>
<clear_interval>600</clear_interval>
<clear_flag>0</clear_flag>
</arch>
<send>
<ip>192.168.24.122</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<net_pack_size>256</net_pack_size>
<net_turns>0</net_turns>
<crc_check>0</crc_check>
<trigger>0</trigger>
<constraint>0</constraint>
<identity>0</identity>
<filter>
<enable>
<item>public.</item>
</enable>
<disable>
</disable>
</filter>
<map>
<item>public.==TEST.*</item>
</map>
</send>
</cpt>
</dmhs>

三、同步测试

3.1、PG和达梦 端启动exec模块

3.2、PG端和达梦端COPY和启动CPT模块

3.3、PG端创建表

3.4、达梦端查看

达梦端创建表

CREATE TABLE “TEST”.“TABLE_1”
(
“COLUMN_1” CHAR(10),
“COLUMN_2” CHAR(10),
“COLUMN_3” CHAR(10),
“COLUMN_4” CHAR(10),
“COLUMN_5” CHAR(10)) STORAGE(ON “MAIN”, CLUSTERBTR) ;

PG端查看

如果在COPY时候报如下错误

错误原因为：ODBC配置文件中得odbcinst.ini中 名字改为PostgreSQL ANSI(x64)