使用OGG for PG微服务快速双向同步RDS数据库（双主）
Tags： OGGOGG微服务PGPostgreSQL双主双向同步
👉 本文共约2800个字，系统预计阅读时间或需11分钟。
类似文章
OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。相关文章可以参考：
- 使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
- OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
- otter用于跨云RDS for mysql之间配置双主实时同步：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
- OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.xmmup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
使用OGG for PG微服务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | -- 创建专用网络 docker network create --subnet=172.72.6.0/24 pg-network -- OGG微服务 docker pull lhrbest/ogg213mapg:v1.0 docker rm -f lhrogg213mapg docker run -d --name lhrogg213mapg -h lhrogg213mapg \ --net=pg-network --ip 172.72.6.25 \ -p 9390:3389 -p 19000-19005:9000-9005 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/ogg213mapg:v1.0 \ /usr/sbin/init -- PG A docker rm -f lhrpga docker run -d --name lhrpga -h lhrpga \ -p 64320:5432 --net=pg-network --ip 172.72.6.20 \ -e POSTGRES_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ postgres:13.4 -- PG B docker rm -f lhrpgb docker run -d --name lhrpgb -h lhrpgb \ -p 64321:5432 --net=pg-network --ip 172.72.6.21 \ -e POSTGRES_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ postgres:13.4 -- 2个PG库需要配置 psql -U postgres -h 192.168.66.35 -p 64320 psql -U postgres -h 192.168.66.35 -p 64321 alter system set wal_level='logical'; alter system set max_replication_slots = 10 ; create database lhrdb; \c lhrdb create schema ogg; docker restart lhrpga lhrpgb -- OGGMA cat > /ogg213c/ogg_ma/odbc.ini <<"EOF" [ODBC Data Sources] PGDSN1=DataDirect 13 PostgreSQL Wire Protocol PGDSN2=DataDirect 13 PostgreSQL Wire Protocol [ODBC] IANAAppCodePage=106 InstallDir=/ogg213c/ogg_ma [PGDSN1] Driver=/ogg213c/ogg_ma/lib/GGpsql25.so #Driver=/usr/lib64/psqlodbcw.so Description=DataDirect 13 PostgreSQL Wire Protocol Database=lhrdb HostName=172.72.6.20 PortNumber=5432 LogonID=postgres Password=lhr [PGDSN2] Driver=/ogg213c/ogg_ma/lib/GGpsql25.so #Driver=/usr/lib64/psqlodbcw.so Description=DataDirect 13 PostgreSQL Wire Protocol Database=lhrdb HostName=172.72.6.21 PortNumber=5432 LogonID=postgres Password=lhr EOF |
访问：http://192.168.66.35:19000 ，用户名：oggadmin，密码：lhr
创建身份证明
或直接访问：http://192.168.66.35:19001/
创建检查点表
由于我们要配置双主，所以，这里的PG1和PG2都需要配置检查点表，否则只在目标端配置检查点表即可。
数据初始化
首先将PGA和PGB的lhrdb库的数据初始化.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- pga建表并插入数据 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \ --pgsql-host=172.72.6.20 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=10000 --tables=10 --threads=80 \ --events=999999999 --time=60 prepare -- pgb只是建表，注意：table-size sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \ --pgsql-host=172.72.6.21 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=0 --tables=10 --threads=80 \ --events=999999999 --time=60 prepare |
当然，我们可以不使用OGG进行数据的初始化，也可以使用数据库本身的工具进行初始化，例如：
1 2 3 | pg_dump -d lhrdb > lhrdb.dmp psql -U postgres -d lhrdb -h 172.72.6.21 < lhrdb.dmp |
这里演示从OGG进行数据的初始化。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
添加表的TRANDATA 信息
2个库的public下的表都需要添加。
从PGA配置初始提取
参数内容：
1 2 3 4 5 6 | EXTRACT ext0 SETENV(PGCLIENTENCODING = "UTF8") SETENV(ODBCINI="/ogg213c/ogg_ma/odbc.ini") SOURCEDB PGDSN1 USERIDALIAS PG1, DOMAIN OGGMA EXTFILE ./dirdat/e0 , PURGE TABLE public.*; |
数据已经导出到本地。
将数据导入到PGB
参数：
1 2 3 4 5 | REPLICAT rep0 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg213c/ogg_ma/odbc.ini") TARGETDB PGDSN2 USERIDALIAS PG2, DOMAIN OGGMA MAP public.*, TARGET public.*; |
可以看到，数据都已经抽取到目标端了。
配置复制槽
在配置PostgreSQL实时同步之前，需要先配置复制槽。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | C:\Users\lhrxxt>adminclient Oracle GoldenGate Administration Client for Oracle Version 21.3.0.0.1 OGGCORE_21.3.0.0.1_PLATFORMS_211007.1817 Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved. Windows x64 (optimized) on Oct 7 2021 21:05:36 Operating system character set identified as GBK. OGG (not connected) 1> CONNECT http://192.168.66.35:19001 deployment deploy213 as oggadmin password lhr OGG (http://192.168.66.35:19001 deploy213) 2> dblogin useridalias PG1 DOMAIN OGGMA Successfully logged into database. OGG (http://192.168.66.35:19001 deploy213 as PG1@lhrdb) 3> REGISTER EXTRACT ext1 2021-12-02T14:22:17Z INFO OGG-25355 Successfully created replication slot 'ext1_eaa1c3d574a94c47' for Extract group 'EXT1' in database 'lhrdb'. OGG (http://192.168.66.35:19001 deploy213 as PG1@lhrdb) 4> dblogin useridalias PG2 DOMAIN OGGMA Successfully logged into database. OGG (http://192.168.66.35:19001 deploy213 as PG2@lhrdb) 6> REGISTER EXTRACT ext2 2021-12-02T14:23:36Z INFO OGG-25355 Successfully created replication slot 'ext2_5521571981698d81' for Extract group 'EXT2' in database 'lhrdb'. OGG (http://192.168.66.35:19001 deploy213 as PG2@lhrdb) 7> info all Program Status Group Type Lag at Chkpt Time Since Chkpt REPLICAT RUNNING REP0 NONINTEGRATED 00:00:00 00:00:06 OGG (http://192.168.66.35:19001 deploy213 as PG2@lhrdb) 8> CONNECT http://192.168.66.35:19000 deployment deploy213 as oggadmin password lhr OGG (http://192.168.66.35:19000 deploy213) 9> info all Program Status Group Type Lag at Chkpt Time Since Chkpt ADMINSRVR RUNNING DISTSRVR RUNNING PMSRVR RUNNING RECVSRVR RUNNING |
若不配置复制槽，会报错：OGG-25374
Oracle GoldenGate Capture for PostgreSQL, EXT1.prm: The replication slot 'ext1_eaa1c3d574a94c47' for group 'EXT1' does not exist in the database 'lhrdb'.
配置PGA到PGB的实时同步
创建extract进程
若是报错：OGG-15409 Alias 'PG1' not found in credential store domain 'OracleGoldenGate'.
可以忽略，或者参考创建身份证明，在域OracleGoldenGate中创建PG1和PG2。
参数：
1 2 3 4 5 6 7 8 | extract ext1 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg213c/ogg_ma/odbc.ini" ) SOURCEDB PGDSN1 USERIDALIAS PG1, DOMAIN OGGMA exttrail ./dirdat/e1 IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ogg.ckpt table public.*; |
创建replicate进程
参数：
1 2 3 4 5 6 | replicat rep1 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg213c/ogg_ma/odbc.ini" ) SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8") TARGETDB PGDSN2 USERIDALIAS PG2, DOMAIN OGGMA map public.*, target public.*; |
配置B到A的实时同步
创建extract进程
参数：
1 2 3 4 5 6 7 8 | extract ext2 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg213c/ogg_ma/odbc.ini" ) SOURCEDB PGDSN2 USERIDALIAS PG2, DOMAIN OGGMA exttrail ./dirdat/e2 IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ogg.ckpt table public.*; |
创建replicate进程
参数：
1 2 3 4 5 6 | replicat rep2 SETENV(PGCLIENTENCODING = "UTF8") SETENV(ODBCINI="/ogg213c/ogg_ma/odbc.ini") SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8") TARGETDB PGDSN1 USERIDALIAS PG1, DOMAIN OGGMA map public.*, target public.*; |
配置完成后的效果
压测同步并查看检测数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib -- 在PGA压测 sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ --pgsql-host=172.72.6.20 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=1000 --tables=10 --threads=20 \ --events=999999999 --time=60 --report-interval=10 \ --db-ps-mode=disable --forced-shutdown=1 run -- 在PGB压测 sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ --pgsql-host=172.72.6.21 --pgsql-port=5432 \ --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ --table-size=1000 --tables=10 --threads=20 \ --events=999999999 --time=60 --report-interval=10 \ --db-ps-mode=disable --forced-shutdown=1 run |
在PGA侧加压
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | [root@docker35 ~]# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib [root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ > --pgsql-host=172.72.6.20 --pgsql-port=5432 \ > --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ > --table-size=1000 --tables=10 --threads=20 \ > --events=999999999 --time=60 --report-interval=10 \ > --db-ps-mode=disable --forced-shutdown=1 run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 20 Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 61 seconds Initializing worker threads... Threads started! [ 10s ] thds: 20 tps: 276.96 qps: 5884.44 (r/w/o: 4146.04/1108.42/629.97) lat (ms,95%): 215.44 err/s: 17.99 reconn/s: 0.00 [ 20s ] thds: 20 tps: 314.00 qps: 6681.87 (r/w/o: 4709.65/1257.81/714.41) lat (ms,95%): 150.29 err/s: 21.60 reconn/s: 0.00 [ 30s ] thds: 20 tps: 289.10 qps: 6186.99 (r/w/o: 4362.26/1159.82/664.91) lat (ms,95%): 150.29 err/s: 22.90 reconn/s: 0.00 [ 40s ] thds: 20 tps: 284.20 qps: 6040.41 (r/w/o: 4256.21/1137.30/646.90) lat (ms,95%): 132.49 err/s: 19.40 reconn/s: 0.00 [ 50s ] thds: 20 tps: 207.20 qps: 4382.53 (r/w/o: 3091.15/822.69/468.69) lat (ms,95%): 179.94 err/s: 13.60 reconn/s: 0.00 [ 60s ] thds: 20 tps: 267.65 qps: 5700.71 (r/w/o: 4015.83/1074.59/610.28) lat (ms,95%): 167.44 err/s: 19.20 reconn/s: 0.00 SQL statistics: queries performed: read: 245868 write: 65645 other: 37381 total: 348894 transactions: 16413 (269.81 per sec.) queries: 348894 (5735.37 per sec.) ignored errors: 1149 (18.89 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.8276s total number of events: 16413 Latency (ms): min: 10.27 avg: 73.47 max: 2213.35 95th percentile: 161.51 sum: 1205833.27 Threads fairness: events (avg/stddev): 820.6500/22.37 execution time (avg/stddev): 60.2917/0.38 |
可以看到数据是完全同步的！
在PGB侧加压
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | [root@docker35 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \ > --pgsql-host=172.72.6.21 --pgsql-port=5432 \ > --pgsql-user=postgres --pgsql-password=lhr --pgsql-db=lhrdb \ > --table-size=1000 --tables=10 --threads=20 \ > --events=999999999 --time=60 --report-interval=10 \ > --db-ps-mode=disable --forced-shutdown=1 run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 20 Report intermediate results every 10 second(s) Initializing random number generator from current time Forcing shutdown in 61 seconds Initializing worker threads... Threads started! [ 10s ] thds: 20 tps: 235.78 qps: 5027.44 (r/w/o: 3544.39/944.82/538.23) lat (ms,95%): 257.95 err/s: 15.89 reconn/s: 0.00 [ 20s ] thds: 20 tps: 256.61 qps: 5544.15 (r/w/o: 3914.04/1036.44/593.67) lat (ms,95%): 227.40 err/s: 22.78 reconn/s: 0.00 [ 30s ] thds: 20 tps: 181.34 qps: 3826.18 (r/w/o: 2694.53/723.94/407.71) lat (ms,95%): 831.46 err/s: 10.91 reconn/s: 0.00 [ 40s ] thds: 20 tps: 222.26 qps: 4656.12 (r/w/o: 3276.83/882.37/496.92) lat (ms,95%): 240.02 err/s: 11.95 reconn/s: 0.00 [ 50s ] thds: 20 tps: 196.54 qps: 4139.82 (r/w/o: 2914.00/785.87/439.96) lat (ms,95%): 292.60 err/s: 11.34 reconn/s: 0.00 [ 60s ] thds: 20 tps: 357.50 qps: 7705.61 (r/w/o: 5437.29/1442.80/825.52) lat (ms,95%): 116.80 err/s: 30.87 reconn/s: 0.00 SQL statistics: queries performed: read: 217896 write: 58206 other: 33054 total: 309156 transactions: 14526 (241.82 per sec.) queries: 309156 (5146.58 per sec.) ignored errors: 1038 (17.28 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 60.0658s total number of events: 14526 Latency (ms): min: 8.17 avg: 82.55 max: 3373.88 95th percentile: 227.40 sum: 1199186.82 Threads fairness: events (avg/stddev): 726.3000/19.53 execution time (avg/stddev): 59.9593/0.07 |
查看性能数据
其它内容不再截图。
双向同步测试完成，完美！
