利用OGG實(shí)現(xiàn)PostgreSQL實(shí)時(shí)同步的過程詳解
更新時(shí)間:2023年11月13日 09:26:55 作者:IT邦德
本文詳細(xì)闡述了利用OGG實(shí)現(xiàn)PostgreSQL實(shí)時(shí)同步的全過程,文章通過代碼示例和圖文結(jié)合講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的參考價(jià)值,需要的朋友可以參考下
1.環(huán)境部署
1.1 架構(gòu)規(guī)劃
1.2 網(wǎng)絡(luò)規(guī)劃
主機(jī)名 | IP | 操作系統(tǒng) | 內(nèi)存/空間 | 角色 | 說明 |
---|---|---|---|---|---|
postgresql01 | 172.18.12.50 | centos7.8 | 2c/18G 400G | 源端 | postgresql 14版本 |
postgresql02 | 172.18.12.51 | centos7.8 | 2c/18G 400G | 目標(biāo)庫 | 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驅(qū)動(dòng)
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 ##登錄測(cè)試,密碼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 進(jìn)程啟動(dòng)
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
到此這篇關(guān)于利用OGG實(shí)現(xiàn)PostgreSQL實(shí)時(shí)同步的過程詳解的文章就介紹到這了,更多相關(guān)OGG實(shí)現(xiàn)PostgreSQL實(shí)時(shí)同步內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
postgresql 實(shí)現(xiàn)多表關(guān)聯(lián)刪除
這篇文章主要介紹了postgresql 實(shí)現(xiàn)多表關(guān)聯(lián)刪除操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01詳解如何定位postgreSQL數(shù)據(jù)庫中未被使用過的索引
在生產(chǎn)環(huán)境上,由于不規(guī)范的優(yōu)化措施,數(shù)據(jù)庫中可能存在大量的索引,并且相當(dāng)一部分的索引重未被使用過,今天帶大家如何找出這些索引,本文給大家介紹了定位postgreSQL數(shù)據(jù)庫中未被使用過的索引的方法,需要的朋友可以參考下2024-03-03PostgreSQL截取字符串到指定字符位置詳細(xì)示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL截取字符串到指定字符位置的相關(guān)資料,PostgreSQL數(shù)據(jù)庫拼接字符串函數(shù)是一種非常重要的函數(shù),使用它可以方便地將不同的字符串進(jìn)行拼接操作,從而得到我們需要的結(jié)果,需要的朋友可以參考下2023-07-07