合 OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递
👉 本文共约584个字，系统预计阅读时间或需3分钟。
架构
环境准备
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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | -- 创建专用网络 docker network create --subnet=172.72.6.0/24 pg-network -- 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 -- 安装ogg docker rm -f lhroggforpg docker run -d --name lhroggforpg -h lhroggforpg \ --net=pg-network --ip 172.72.6.25 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker cp /soft/213000_ggs_Linux_x64_PostgreSQL_64bit.zip lhroggforpg:/soft/ docker exec -it lhroggforpg bash mkdir /ogg unzip /soft/213000_ggs_Linux_x64_PostgreSQL_64bit.zip tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C /ogg/ rpm -e postgresql-libs-9.2.24-7.el7_9.x86_64 --nodeps yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install -y postgresql13-libs* cat >> /root/.bashrc <<"EOF" export LD_LIBRARY_PATH=/ogg/lib:/usr/pgsql-13/lib/:/usr/lib64:/usr/lib:$LD_LIBRARY_PATH export ODBCINI=/ogg/odbc.ini export PATH=$PATH:/ogg/ alias ogg='rlwrap ggsci' EOF source /root/.bashrc ogg create subdirs -- ODBC cat > /ogg/odbc.ini <<"EOF" [ODBC Data Sources] LHRPGDSN=DataDirect 13 PostgreSQL Wire Protocol [ODBC] IANAAppCodePage=106 InstallDir=/ogg [PGDSN1] Driver=/ogg/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=/ogg/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 psql -U postgres -h 192.168.66.35 -p 64321 psql -U postgres -h 192.168.66.35 -p 64320 create database lhrdb; \c lhrdb create table test(id int primary key); create schema ogg; -- 需要重启库 alter system set wal_level='logical'; select pg_reload_conf(); docker restart lhrpga lhrpgb -- mgr cat > /ogg/dirprm/mgr.prm <<"EOF" port 7809 EOF start mgr |
双主
A->B
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 | -- 配置extract cat > /ogg/dirprm/ext1.prm <<"EOF" extract ext1 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg/odbc.ini" ) SOURCEDB PGDSN1, userid postgres, password lhr exttrail ./dirdat/e1 IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ogg.ckpt table public.*; EOF DBLOGIN SOURCEDB PGDSN1 USERID postgres PASSWORD lhr REGISTER EXTRACT ext1 ADD TRANDATA public.* ADD TRANDATA ogg.ckpt add ext ext1, tranlog, begin now add exttrail ./dirdat/e1, ext ext1 -- 配置replication cat > /ogg/dirprm/rep1.prm <<"EOF" replicat rep1 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg/odbc.ini" ) SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8") TARGETDB PGDSN2, userid postgres, password lhr map public.*, target public.*; EOF DBLOGIN SOURCEDB PGDSN2 USERID postgres PASSWORD lhr add checkpointtable ogg.ckpt add rep rep1, exttrail ./dirdat/e1, checkpointtable ogg.ckpt |
B->A
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 | -- 配置extract cat > /ogg/dirprm/ext2.prm <<"EOF" extract ext2 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg/odbc.ini" ) SOURCEDB PGDSN2, userid postgres, password lhr exttrail ./dirdat/e2 IGNOREREPLICATES TRANLOGOPTIONS FILTERTABLE ogg.ckpt table public.*; EOF DBLOGIN SOURCEDB PGDSN2 USERID postgres PASSWORD lhr REGISTER EXTRACT ext2 ADD TRANDATA public.* ADD TRANDATA ogg.ckpt add ext ext2, tranlog, begin now add exttrail ./dirdat/e2, ext ext2 -- 配置replication cat > /ogg/dirprm/rep2.prm <<"EOF" replicat rep2 SETENV(PGCLIENTENCODING = "UTF8" ) SETENV(ODBCINI="/ogg/odbc.ini" ) SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8") TARGETDB PGDSN1, userid postgres, password lhr map public.*, target public.*; EOF DBLOGIN SOURCEDB PGDSN1 USERID postgres PASSWORD lhr add checkpointtable ogg.ckpt ADD TRANDATA public.* ADD TRANDATA ogg.ckpt add rep rep2, exttrail ./dirdat/e2, checkpointtable ogg.ckpt |
测试同步
1 2 3 4 | insert into test values(1); select * from test; |
重新同步
1 2 3 4 5 6 7 8 | stop * alter ext1,begin now alter ext2,begin now alter rep1,begin now alter rep2,begin now start * info all |
压测
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 | 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 pg_dump -d lhrdb > lhrdb.dmp psql -U postgres -d lhrdb -h 172.72.6.21 < lhrdb.dmp DBLOGIN SOURCEDB PGDSN1 USERID postgres PASSWORD lhr DBLOGIN SOURCEDB PGDSN2 USERID postgres PASSWORD lhr ADD TRANDATA public.* 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=10000 --tables=10 --threads=20 \ --events=999999999 --time=60 --report-interval=10 \ --db-ps-mode=disable --forced-shutdown=1 run 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=10000 --tables=10 --threads=20 \ --events=999999999 --time=60 --report-interval=10 \ --db-ps-mode=disable --forced-shutdown=1 run -- 4个OGG进程正常，说明双向同步没问题 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
