Use OGG for PG microservice to quickly synchronize RDS databases in both directions (Dual Master)

Similar articles

OGG has both the traditional classic architecture and the latest microservices. Both of them can remotely capture and apply data and zero intrusion to the database server. The traditional classic architecture is a pure command-line mode. The latest microservice architecture is a graphical interface operation, and almost all operations can be carried out on the interface. For relevant articles, please refer to:

Using OGG for PG microservices

-- Create a private network
docker network create --subnet=172.72.6.0/24 pg-network


-- OGG Microservices
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 individual PG The library needs to be configured
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

visit: http://192.168.66.35:19000  ， User name: oggadmin, password: lhr

Create proof of identity

Or direct access: http://192.168.66.35:19001/

Create checkpoint table

Since we need to configure dual master, both PG1 and PG2 here need to configure the checkpoint table, otherwise we can only configure the checkpoint table at the target end.

Data initialization

First, initialize the lhrdb library data of PGA and PGB

-- pga Create table and insert data
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 Just create a table. Note: 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

Of course, we can initialize the data without using OGG or using the tools of the database itself, for example:

pg_dump -d lhrdb > lhrdb.dmp

psql -U postgres -d lhrdb -h 172.72.6.21 < lhrdb.dmp

Here is a demonstration of data initialization from OGG.

Add TRANDATA information for table

The tables under public of the two libraries need to be added.

Initial extraction from PGA configuration

Parameter content:

EXTRACT ext0
SETENV(PGCLIENTENCODING = 'UTF8')
SETENV(ODBCINI='/ogg213c/ogg_ma/odbc.ini')
SOURCEDB PGDSN1 USERIDALIAS PG1, DOMAIN OGGMA
EXTFILE ./dirdat/e0 , PURGE
TABLE public.*;

Data has been exported locally.

Import data into PGB

Parameters:

REPLICAT rep0
SETENV(PGCLIENTENCODING = 'UTF8' )
SETENV(ODBCINI='/ogg213c/ogg_ma/odbc.ini')
TARGETDB PGDSN2 USERIDALIAS PG2, DOMAIN OGGMA
MAP public.*, TARGET public.*;

You can see that the data has been extracted to the target end.

Configure replication slots

Before configuring PostgreSQL real-time synchronization, you need to configure the replication slot.

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

If the replication slot is not configured, an error will be reported: OGG-25374
Oracle GoldenGate Capture for PostgreSQL, EXT1.prm: The replication slot 'ext1_eaa1c3d574a94c47' for group 'EXT1' does not exist in the database 'lhrdb'.

Configure real-time synchronization from PGA to PGB

Create extract process

If an error is reported: OGG-15409 Alias' PG1 'not found in credential store domain' oraclegolden gate '

You can ignore it, or refer to creating an identity certificate to create PG1 and PG2 in the domain oraclegolden gate.

Parameters:

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.*;

Create replicate process

Parameters:

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.*;

Configure real-time synchronization from B to A

Create extract process

Parameters:

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.*;

Create replicate process

Parameters:

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.*;

Effect after configuration

Synchronize the pressure measurement and view the test data

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib

-- stay PGA Pressure measurement
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



-- stay PGB Pressure measurement
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

Pressurize on PGA side

[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

You can see that the data is completely synchronized!

Pressurize on PGB side

[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

View performance data

http://192.168.66.35:19004/

No more screenshots of other contents.

Two way synchronization test completed, perfect!

Keywords: MySQL Oracle PostgreSQL c3d

Added by skulk on Wed, 08 Dec 2021 01:57:34 +0200

Popular Keywords

©2024 Programming VIP