OGG-Postgres笔记

timedatectl set-timezone Asia/Shanghai

/etc/sysconfig/network-scripts
BOOTPROTO=“static”
IPADDR=192.168.52.128
NETMASK=255.255.255.0
GATEWAY=192.168.52.1

vi /etc/passwd

groupadd -g 1001 postgres
useradd -g postgres -m -u 1001 postgres
ls -l /home/postgres/

cd /
mkdir u01
chmod 777 u01

https://www.postgresql.org/download/linux/redhat/

yum install postgresql11-11.7-1PGDG.rhel7.x86_64.rpm postgresql11-contrib-11.7-1PGDG.rhel7.x86_64.rpm postgresql11-libs-11.7-1PGDG.rhel7.x86_64.rpm postgresql11-server-11.7-1PGDG.rhel7.x86_64.rpm

[root@localhost home]# psql -V
psql (PostgreSQL) 11.7

vi /etc/sudoers

Allow root to run any commands anywhere

root ALL=(ALL) ALL
postgres ALL=(ALL) NOPASSWD:ALL

[root@localhost u01]# su - postgres

[postgres@localhost ~]$ cd /u01
mkdir pgsql-data

cd
vi .bash_profile
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/pgsql-11/bin/

初始化
#sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

#pg_ctl stop -D /var/lib/pgsql/11/data/
#vi /usr/lib/systemd/system/postgresql-11.service
#systemctl daemon-reload

initdb -D /u01/pgsql-data

[postgres@localhost ~]$ which initdb
/usr/pgsql-11/bin/initdb
[postgres@localhost ~]$ initdb -D /u01/pgsql-data
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.

The database cluster will be initialized with locale “en_US.UTF-8”.
The default database encoding has accordingly been set to “UTF8”.
The default text search configuration will be set to “english”.

Data page checksums are disabled.

fixing permissions on existing directory /u01/pgsql-data … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default timezone … America/Los_Angeles
selecting dynamic shared memory implementation … posix
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok

WARNING: enabling “trust” authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
–auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D /u01/pgsql-data -l logfile start

pg_ctl start -l logfile -D /u01/pgsql-data
pg_ctl -D /u01/pgsql-data -l logfile start

访问数据库
psql

postgres-# \l+

//查看pg的时区与时间
testpdb=# select now();
now

2023-11-08 17:28:24.745838-08
(1 row)

//查看时区
testpdb=# show timezone;
TimeZone

America/Los_Angeles
(1 row)

select * from pg_timezone_names;

Asia/Shanghai

set time zone ‘Asia/Shanghai’;
SET
alter database testpdb set timezone= ‘Asia/Shanghai’;

//修改时区，注意此默认为session级别 postgres=# set time zone ‘PRC’; SET

用户级配置 alter role rolname set timezone=‘UTC’; 或者 alter role all set
timezone=‘UTC’;

数据库级配置 alter database testpdb set timezone= ‘Asia/Shanghai’;

全局配置（永久生效） //查看配置文件中时区设置，要想永久生效，此时需要修改配置文件 [root@localhost ~]$ grep
timezone postgresql.conf log_timezone = ‘US/Pacific’ timezone =
‘US/Pacific’

//修改完配置时重新加载 [root@localhost ~]$ pg_ctl reload -D /u01/pgsql-data
————————————————
CSDN博主「逝水-无痕」的原创文章
原文链接：https://blog.csdn.net/wangkai_123456/article/details/106333521

\q

################

https://www.oracle.com/middleware/technologies/goldengate-downloads.html

useradd -m oggp
mkdir -p /u01/oggp
chown oggp:root /u01/oggp

su - oggp
[oggp@localhost ~]$ ls -l /home/ggs_Linux_x64_PostgreSQL_64bit.tar
-rw-rw-rw-. 1 root root 381552640 Aug 5 2021 /home/ggs_Linux_x64_PostgreSQL_64bit.tar

cd /u01/oggp/
tar -xf /home/ggs_Linux_x64_PostgreSQL_64bit.tar
[oggp@localhost oggp]$ ls
bcpfmt.tpl dirsca help_pt_BR.txt libgcc_s.so.1 libPocoJSON.so.71 pmsrvr
bcrypt.txt emsclnt help.txt libgglog.so libPocoJWT.so.71 prvtclkm.plb
cachefiledump extract help_zh_CN.txt libggnnzitp.so libPocoNet.so.71 replicat
certstore freeBSD.txt help_zh_TW.txt libggparam.so libPocoNetSSL.so.71 retrace
checkprm ggcmd keygen libggperf.so libPocoUtil.so.71 server
convchk ggMessage.dat lib libggrepo.so libPocoXML.so.71 SQLDataTypes.h
convprm ggparam.dat libantlr3c.so libggssl.so libstdc++.so.6 sqlldr.tpl
crypto ggsci libboost_program_options-mt-x64.so.1 libggutil.so libudt.so tcperrs
db2cntl.tpl help_de.txt libboost_program_options-mt-x64.so.1.73 libicudata.so.65 libxerces-c-3.2.so ucharset.h
db_upgrade help_es.txt libboost_program_options-mt-x64.so.1.73.0 libicui18n.so.65 locale UserExitExamples
DDGG.LIC help_fr.txt libboost_system-mt-x64.so.1 libicuuc.so.65 logdump usrdecs.h
defgen help_it.txt libboost_system-mt-x64.so.1.73 liblmdb.so mgr version4j.jar
diretc help_ja.txt libboost_system-mt-x64.so.1.73.0 libPocoCrypto.so.71 notices.txt zlib.txt
dirout help_ko.txt libdb-6.1.so libPocoFoundation.so.71 oggerr
[oggp@localhost oggp]$

vi .bash_profile
export LD_LIBRARY_PATH=/usr/pgsql-11/lib:/u01/oggp/lib:$LD_LIBRARY_PATH
alias ggsci=‘cd /u01/oggp; ./ggsci’

source .bash_profile

ggsci

GGSCI (localhost.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/oggp

Parameter file /u01/oggp/dirprm: created.
Report file /u01/oggp/dirrpt: created.
Checkpoint file /u01/oggp/dirchk: created.
Process status files /u01/oggp/dirpcs: created.
SQL script files /u01/oggp/dirsql: created.
Database definitions files /u01/oggp/dirdef: created.
Extract data files /u01/oggp/dirdat: created.
Temporary files /u01/oggp/dirtmp: created.
Credential store files /u01/oggp/dircrd: created.
Master encryption key wallet files /u01/oggp/dirwlt: created.
Dump files /u01/oggp/dirdmp: created.

GGSCI (localhost.localdomain) 2> edit param mgr
–端口可以自定义

port 2031
dynamicportlist 2032-2040
purgeoldextracts ./dirdat/*, usecheckpoints,minkeephours 24
AUTORESTART ER *, RETRIES 3, WAITMINUTES 2,RESETMINUTES 10

GGSCI (localhost.localdomain) 3> start mgr
Manager started.

GGSCI (localhost.localdomain) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

#########################

[postgres@localhost ~]$ psql
psql (11.7)
Type “help” for help.

postgres=# create user ogguser superuser login password ‘ogguserP2023’;
CREATE ROLE
postgres=# create database testpdb encoding=utf8;
CREATE DATABASE
postgres=# \c testpdb
You are now connected to database “testpdb” as user “postgres”.
testpdb=# create table tb01(id int not null, name varchar(16), ts timestamp , primary key(id) );
CREATE TABLE
testpdb=# \d tb01
Table “public.tb01”
Column | Type | Collation | Nullable | Default
--------±----------------------------±----------±---------±--------
id | integer | | not null |
name | character varying(16) | | |
ts | timestamp without time zone | | |
Indexes:
“tb01_pkey” PRIMARY KEY, btree (id)

###ogg###################

vi odbc.ini

[ODBC Data Sources]
PGDSN=DataDirect 11.7 PostgreSQL Wire Protocol
postgres=DataDirect 11.7 PostgreSQL Wire Protocol
scott=DataDirect 11.7 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=106
InstallDir=/u01/oggp

[TESTPDB]
Driver=/u01/oggp/lib/GGpsql25.so
Description=DataDirect 11.7 PostgreSQL Wire Protocol
Database=testpdb
HostName=localhost
PortNumber=5432
LogonID=ogguser
Password=ogguserP2023
TransactionErrorBehavior=2

[oggp@localhost oggp]$ export ODBCINI=/u01/oggp/odbc.ini

vi .bash_profile
export ODBCINI=/u01/oggp/odbc.ini

否则 dblogin 报错
2023-11-09 09:12:42 WARNING OGG-00552 Database operation failed: Couldn’t connect to TESTPDB. ODBC error: SQLSTATE IM002 native database error 0. [DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the value of InstallDir in your odbc.ini.
Error: Database operation failed: Couldn’t connect to TESTPDB. ODBC error: SQLSTATE IM002 native database error 0. [DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the value of InstallDir in your odbc.ini.Failed to open data source TESTPDB for user ogguser

[oggp@localhost oggp]$ ggsci

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 UTF-8.

Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved.

GGSCI (localhost.localdomain) 1> dblogin sourcedb TESTPDB userid ogguser , password ogguserP2023

2023-11-08 15:13:11 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.

2023-11-08 15:13:11 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.

###############postgres##############

[postgres@localhost pgsql-data]$ cd /u01/pgsql-data
[postgres@localhost pgsql-data]$ cp postgresql.conf postgresql.conf.bak
[postgres@localhost pgsql-data]$ vi postgresql.conf

wal_level = logical

max_wal_senders = 10

max_replication_slots = 10

pg_ctl -D /u01/pgsql-data stop
pg_ctl -D /u01/pgsql-data -l logfile start

[postgres@localhost pg_wal]$ cd /u01/pgsql-data/pg_wal
[postgres@localhost pg_wal]$
[postgres@localhost pg_wal]$ ls -l
total 16384
-rw-------. 1 postgres postgres 16777216 Nov 8 15:20 000000010000000000000001
drwx------. 2 postgres postgres 6 Nov 8 13:59 archive_status
[postgres@localhost pg_wal]$ cd /u01/pgsql-data/pg_logical/
[postgres@localhost pg_logical]$ ls -l
total 4
drwx------. 2 postgres postgres 6 Nov 8 13:59 mappings
-rw-------. 1 postgres postgres 8 Nov 8 15:20 replorigin_checkpoint
drwx------. 2 postgres postgres 6 Nov 8 13:59 snapshots
[postgres@localhost pg_logical]$

#####oggp###########
[oggp@localhost oggp]$ ggsci

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 UTF-8.

Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved.

GGSCI (localhost.localdomain) 1> dblogin sourcedb TESTPDB userid ogguser , password ogguserP2023

2023-11-08 15:25:23 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.

2023-11-08 15:25:23 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.

GGSCI (localhost.localdomain as ogguser@TESTPDB) 2> register extract ext1

2023-11-08 15:25:37 INFO OGG-25355 Successfully created replication slot ‘ext1_e9ac74ca2ea28c8f’ for Extract group ‘EXT1’ in database ‘testpdb’.

##########postgres#######
vi /u01/pgsql-data/log/postgresql-Tue.log

2023-11-07 23:20:09.843 PST [62965] LOG: database system is ready to accept connections
2023-11-07 23:25:37.216 PST [63285] LOG: logical decoding found consistent point at 0/16CC6E0
2023-11-07 23:25:37.216 PST [63285] DETAIL: There are no running transactions.
2023-11-07 23:25:37.216 PST [63285] STATEMENT: select slot_name from pg_create_logical_replication_slot(‘ext1_e9ac74ca2ea28c8f’,‘test_decoding’)

#####oggp################
增量捕获
增加进程日志
ggsci

GGSCI (localhost.localdomain as ogguser@TESTPDB) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (localhost.localdomain as ogguser@TESTPDB) 4> add ext ext1, tranlog , begin now
Extract added.

GGSCI (localhost.localdomain as ogguser@TESTPDB) 5> add exttrail ./dirdat/e1, ext ext1
EXTTRAIL added.

GGSCI (localhost.localdomain as ogguser@TESTPDB) 6> edit param ext1

–添加如下内容
extract ext1
setenv(PGCLIENTENCODING = “UTF8”)
setenv(ODBCINI=“/u01/oggp/odbc.ini” )
sourcedb testpdb, userid ogguser, password ogguserP2023
exttrail ./dirdat/e1
discardfile ./dirrpt/ext1.dsc
table public.tb01;

GGSCI (localhost.localdomain as ogguser@TESTPDB) 7> start ext1

Sending START request to Manager …
Extract group EXT1 starting.

GGSCI (localhost.localdomain as ogguser@TESTPDB) 8> info ext1

Extract EXT1 Last Started 2023-11-08 15:54 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Process ID 65112
VAM Read Checkpoint 2023-11-08 15:50:29.599714

Replication Slot ext1_e9ac74ca2ea28c8f is active with PID 65122 in database testpdb
Slot Restart LSN 0/16CC6E0
Slot Flush LSN 0/16CC718
Current Log Position 0/16CC718

GGSCI (localhost.localdomain as ogguser@TESTPDB) 9> stats ext1, total

#######postgre#############

psql
\c testpdb
create table tb02 as select * from tb01 where 1>2;
\d

psql -U ogguser -d testpdb

###########oggp###############

ggsci

dblogin sourcedb TESTPDB userid ogguser , password ogguserP2023

add ext ie1, sourceistable
edit param ie1
–添加如下内容
extract ie1
setenv(PGCLIENTENCODING = “UTF8”)
setenv(ODBCINI=“/u01/oggp/odbc.ini” )
sourcedb testpdb, userid ogguser, password ogguserP2023
extfile ./dirdat/i1
table public.tb01;

add rep ir1, exttrail ./dirdat/i1, nodbcheckpoint
edit param ir1
–添加如下内容

replicat ir1
setenv(PGCLIENTENCODING = “UTF8”)
setenv(ODBCINI=“/u01/oggp/odbc.ini” )
targetdb testpdb, userid ogguser, password ogguserP2023
discardfile ./dirrpt/ir11.dsc
map public.tb01, target public.tb02;

–启动ir1进程

start ir1
info ir1

add rep ir2, exttrail ./dirdat/e1, nodbcheckpoint
edit param ir2
–添加如下内容

replicat ir2
setenv(PGCLIENTENCODING = “UTF8”)
setenv(ODBCINI=“/u01/oggp/odbc.ini” )
targetdb testpdb, userid ogguser, password ogguserP2023
discardfile ./dirrpt/ir21.dsc
map public.tb01, target public.tb02;

#####################
add extract pmpa, EXTTRAILSOURCE ./dirdat/e1
add rmttrail ./dirdat/rc, ext pmpa

edit params pmpa — 配置投递进程参数

extract pmpa
setenv(PGCLIENTENCODING = “UTF8”)
//setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
passthru
//–REPORT AT 01:59
//–reportrollover at 02:00
rmthost 172.16.0.40, mgrport 7809, compress
rmttrail ./dirdat/rc
dynamicresolution — 优化参数，当同步的表过多时，为一个表单独的建立一个记录存入磁盘中，加快OGG进程。
table public.tb01;
//table hzq.*; — 要同步的表，结尾必须要";".

add rep repa, exttrail ./dirdat/rc