PG端创建测试用户及数据库

创建测试数据库TEST，设置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创建用户pgtest 并授予test为超级管理用户
postgres=# ALTER ROLE pgtest SUPERUSER;
同时授予权限：grant ALL PRIVILEGES on all tables in schema public to test;
执行路径：/home/dmdba/dmhs/scripts/ddl_sql_pg.sql
执行前需修改ddl_sql_pg.sql中的以下两项
dname varchar(64) := ‘test’;–请把这个XXX改成实际所在库的库名！！
create table public.“DMHS_DDL_LOG” 若使用的不是public需要替换为使用的用户
执行ddl_sql_pg.sql
[postgres@localhost ~]$ psql -U pgtest -d test -f /home/dmdba/dmhs/scripts/ddl_sql_pg.sql

PG安装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

OBC配置文件如下：

[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=
[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

安装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 驱动指定的安装目录

PG端部署HS软件（部署过程此处省略）

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>

DM端安装HS软件

达梦端安装目录及配置文件目录：/home/dmdba/dmhs/bin
设置环境变量：
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM7_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

数据库配置归档：
修改达梦端的dm.ini参数，开启归档，重启数据库使参数生效
1、ARCH_INI参数值设置为1
2、开启逻辑附加日志RLOG_APPEND_LOGIC参数的值设置为1
3、归档文件例子
[ ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /opt/dmdbms/data/DAMENG/arch
ARCH_FILE_SIZE = 512
ARCH_SPACE_LIMIT = 0
创建同步测试用户TEST
上传OCI文件至数据库安装目录bin目录下：（再将OCI连接到HS安装目录下）
/home/dmdba/dmdbms/bin

DM端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>
</dmhs>

达梦端启动服务与exec模块

PG端启动CPT模块COPY数据字典

PG端创建测试表插入数据

达梦端查看