OGG用于PG数据库之间双主实时同步

AiDB

发布于 2023-04-26 11:56:10

2.6K0

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

架构

环境准备

-- 创建专用网络
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

-- 配置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

-- 配置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

测试同步

insert into test values(1);

select * from test;

重新同步

stop *
alter ext1,begin now
alter ext2,begin now
alter rep1,begin now
alter rep2,begin now
start *
info all

压测

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进程正常，说明双向同步没问题

结论

1、OGG for PG可以远程捕获和投递

2、OGG FOR PG可以用于云RDS for PG之间的同步。

3、需要配置的参数：

wal_level='logical'
max_replication_slots=10

4、PG数据库需要通过ODBC连接

参考

使用OGG微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
otter用于跨云RDS之间配置双主实时同步：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
OGG用于跨云RDS之间配置双主实时同步–OGG远程捕获和投递：https://www.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html

本文结束。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-09-28，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库