📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
- 前言
- 📣 1.环境部署
- ✨ 1.1 架构规划
- ✨ 1.2 网络规划
- 📣 2.OGG for postgrSQL 安装
- ✨ 2.1 解压安装
- ✨ 2.2 安装lib
- ✨ 2.3 环境变量
- ✨ 2.4 OGG初始化
- ✨ 2.5 ODBC驱动
- 📣 3.主从同步
- ✨ 3.1 配置extract
- ✨ 3.2.配置replication
- ✨ 3.3 进程启动
前言
本文详细阐述了利用OGG实现PostgreSQL实时同步的全过程。📣 1.环境部署
✨ 1.1 架构规划
✨ 1.2 网络规划
| 主机名 | IP | 操作系统 | 内存/空间 | 角色 | 说明 |
|---|---|---|---|---|---|
| postgresql01 | 172.18.12.50 | centos7.8 | 2c/18G 400G | 源端 | postgresql 14版本 |
| postgresql02 | 172.18.12.51 | centos7.8 | 2c/18G 400G | 目标库 | postgresql 14版本 |
| Ogg21c | 172.18.12.52 | centos7.8 | 2c/18G 400G | OGG | OGG for postgrSQL |
📣 2.OGG for postgrSQL 安装
✨ 2.1 解压安装
安装
mkdir /ogg
unzip /opt/213000_ggs_Linux_x64_PostgreSQL_64bit.zip
[root@oggforpg /]# unzip /opt/213000_ggs_Linux_x64_PostgreSQL_64bit.zip
Archive: /opt/213000_ggs_Linux_x64_PostgreSQL_64bit.zip
inflating: ggs_Linux_x64_PostgreSQL_64bit.tar
inflating: OGG-21.3.0.0-README.txt
inflating: oracle-goldengate-release-notes_21.3.pdf
[root@oggforpg /]# tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C /ogg/
✨ 2.2 安装lib
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql14-libs*
Installed:
postgresql14-libs.x86_64 0:14.10-1PGDG.rhel7
✨ 2.3 环境变量
cat >> /root/.bashrc <<"EOF"
export OGG_HOME=/ogg
export LD_LIBRARY_PATH=/ogg/lib:/usr/pgsql-14/lib/:/usr/lib64:/usr/lib:$LD_LIBRARY_PATH
export ODBCINI=/ogg/odbc.ini
export PATH=$PATH:$OGG_HOME
#alias ogg='rlwrap ggsci'
alias ggsci='cd $OGG_HOME;ggsci'
EOF
source /root/.bashrc
✨ 2.4 OGG初始化
[root@oggforpg ~]# ogg
Oracle GoldenGate Command Interpreter for PostgreSQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), PostgreSQL on Aug 4 2021 20:27:55
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggforpg) 1> create subdirs
Creating subdirectories under current directory /root
Parameter file /ogg/dirprm: created.
Report file /ogg/dirrpt: created.
Checkpoint file /ogg/dirchk: created.
Process status files /ogg/dirpcs: created.
SQL script files /ogg/dirsql: created.
Database definitions files /ogg/dirdef: created.
Extract data files /ogg/dirdat: created.
Temporary files /ogg/dirtmp: created.
Credential store files /ogg/dircrd: created.
Master encryption key wallet files /ogg/dirwlt: created.
Dump files /ogg/dirdmp: created.
-- mgr
cat > /ogg/dirprm/mgr.prm <<"EOF"
port 7809
dynamicportlist 2032-2040
purgeoldextracts ./dirdat/*, usecheckpoints,minkeephours 24
AUTORESTART ER *, RETRIES 3, WAITMINUTES 2,RESETMINUTES 10
EOF
✨ 2.5 ODBC驱动
cat > /ogg/odbc.ini <<"EOF"
[ODBC Data Sources]
postgre=DataDirect 14 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=106 ##106代表的是UTF-8
InstallDir=/ogg
[PGDSN1]
Driver=/ogg/lib/GGpsql25.so
#Driver=/usr/lib64/psqlodbcw.so
Description=DataDirect 14 PostgreSQL Wire Protocol
Database=jemdb
HostName=172.18.12.50
PortNumber=5432
LogonID=ogguser
Password=123456
TransactionErrorBehavior=2
[PGDSN2]
Driver=/ogg/lib/GGpsql25.so
#Driver=/usr/lib64/psqlodbcw.so
Description=DataDirect 14 PostgreSQL Wire Protocol
Database=jemdb
HostName=172.18.12.51
PortNumber=5432
LogonID=ogguser
Password=123456
TransactionErrorBehavior=2
EOF
##登录测试,密码123456
psql -U postgres -h 172.18.12.50 -p 5432
psql -U postgres -h 172.18.12.51 -p 5432
📣 3.主从同步
✨ 3.1 配置extract
cat > /ogg/dirprm/ext1.prm <<“EOF”
extract ext1
SETENV(PGCLIENTENCODING = “UTF8” )
SETENV(ODBCINI=“/ogg/odbc.ini” )
SOURCEDB PGDSN1, userid ogguser, password 123456
exttrail ./dirdat/e1
IGNOREREPLICATES
TRANLOGOPTIONS FILTERTABLE ogg.ckpt
table public.*;
EOF
DBLOGIN SOURCEDB PGDSN1 USERID ogguser PASSWORD 123456
REGISTER EXTRACT ext1
ADD TRANDATA public.*
add checkpointtable ogg.ckpt
ADD TRANDATA ogg.ckpt
add ext ext1, tranlog, begin now
add exttrail ./dirdat/e1, ext ext1
✨ 3.2.配置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 ogguser, password 123456
map public., target public.;
EOF
DBLOGIN SOURCEDB PGDSN2 USERID ogguser PASSWORD 123456
add checkpointtable ogg.ckpt
add rep rep1, exttrail ./dirdat/e1, checkpointtable ogg.ckpt
✨ 3.3 进程启动
start EXT!
start REP1
GGSCI (oggforpg as ogguser@PGDSN2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:07
REPLICAT RUNNING REP1 00:00:00 00:00:02
