利用OGG實現PostgreSQL實時同步的過程詳解
更新時間:2023年11月13日 09:26:55 作者:IT邦德
本文詳細闡述了利用OGG實現PostgreSQL實時同步的全過程,文章通過代碼示例和圖文結合講解的非常詳細,對大家的學習或工作有一定的參考價值,需要的朋友可以參考下
1.環(huán)境部署
1.1 架構規(guī)劃
1.2 網絡規(guī)劃
主機名 | 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 環(huán)境變量
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
到此這篇關于利用OGG實現PostgreSQL實時同步的過程詳解的文章就介紹到這了,更多相關OGG實現PostgreSQL實時同步內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!