pgsql连接mysql pgsql连接oracle

转载

温柔一刀 2023-12-16 00:12:10

文章标签 pgsql连接mysql 数据库 oracle postgresql bc 文章分类 MySQL 数据库

OGG工具实现Postgresql同步到Oracle数据库

网络上找了一大圈没有找到Postgresql同步到Oracle数据库的案例。自己根据网络上找到的一些案例素材和其他异构数据库的模式搭建部署了一套使用OGG从Postgresql同步数据到Oracle的环境，供参考学习。如有疑问请指教。

下面直接上干货。

场景如下：

数据库类型	角色
Postgresql10.3	源端
Oracle11.2.0.4	目标端
OGG 21.3	同步工具
tbl_test	需要同步的表
testdb	需要同步的数据库

1、安装并配置postgresql数据库

# useradd pgadmin
# chown pgadmin:pgadmin postgresql-10.3.tar.gz 
# chmod 775 postgresql-10.3.tar.gz 

# mkdir /opt/pgadmin/postgresql -p
# chown pgadmin:pgadmin /opt/pgadmin -R
# chmod 775 /opt/pgadmin -R 

安装依赖包
# yum install zlib-devel readline-devel

编译配置以及安装
# ./configure --prefix=/opt/pgadmin/postgresql --with-wal-blocksize=16 --with-blocksize=16

安装 
# make 
# make install

编译安装contrib
cd /soft/pg103/pg103/contrib
make 
make install 

创建数据存放目录
su - pgadmin 
mkdir /opt/pgadmin/pg_data


配置环境变量
su - pgadmin 
vi .bash_profile
export PGHOME=/opt/pgadmin/postgresql
export PGDATA=/opt/pgadmin/pg_data
export LD_LIBRARY_PATH=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH:$HOME/bin

初始化数据库
su - pgadmin
$ initdb 

$ initdb 
The files belonging to this database system will be owned by user "pgadmin".
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 /opt/pgadmin/pg_data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
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 /opt/pgadmin/pg_data -l logfile start


启动数据库
pg_ctl -D /opt/pgadmin/pg_data -l logfile start

配置启动脚本
vi .bash_profile 
alias pg_start='pg_ctl -D /opt/pgadmin/pg_data -l logfile start'
alias pg_status='pg_ctl -D /opt/pgadmin/pg_data -l logfile status'
alias pg_stop='pg_ctl -D /opt/pgadmin/pg_data -l logfile stop'

配置远程连接
PostgreSQL安装后，貌似默认是只接受本地机器连接访问。如果想在其他主机上访问PostgreSQL数据库服务器，就需要进行相应的配置。以下是我配置远程连接PostgreSQL数据库方式：
修改配置文件：（PostgreSQL安装路径下的data，也是安装时data的默认路径）data目录下的 pg_hba.conf 和 postgresql.conf。
1. pg_hba.conf 配置PostgreSQL数据库的访问权限。
找到“# IPv4 local connections:“后，回车另起一行，添加参数行如下，保存。
host all all 0.0.0.0/0 trust
其中0.0.0.0/0表示运行任意ip地址访问
2. postgresql.conf 配置PostgreSQL数据库服务器的相应的参数。
找到“listen_addresses“参数后，设置listen_addresses = ‘*’，保存。
3. 设置好后重启服务即可被远程连接


主库批量生成测试数据
创建测试库
create database testdb;
create table tbl_test (id int, info text, c_time timestamp);
insert into tbl_test select generate_series(1000,1001),md5(random()::text),clock_timestamp();

主库创建数据库同步用户
create user ggadmin with password 'Bai_yun123';
ALTER USER ggadmin WITH REPLICATION;
ALTER USER ggadmin WITH SUPERUSER;
GRANT USAGE ON SCHEMA public TO ggadmin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ggadmin;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tbl_test TO ggadmin;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ggadmin;
GRANT ALL PRIVILEGES ON DATABASE testdb TO ggadmin;
GRANT CONNECT ON DATABASE testdb TO ggadmin;
GRANT CREATE ON DATABASE testdb TO ggadmin;
create user ogguser superuser login password 'ogguserP2021';

##到此，数据库安装配置完成

2、安装OGG FOR postgresql

创建OGG安装目录
# mkdir /ogg213
# chmod 775 /ogg213
# chown pgadmin:pgadmin /ogg213

设置环境变量
export GG_HOME=/ogg213
export PGHOME=/opt/pgadmin/postgresql
export PGDATA=/opt/pgadmin/pg_data
export LD_LIBRARY_PATH=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export OGG_HOME=/ogg213
export LD_LIBRARY_PATH=$OGG_HOME/lib:$PGHOME/lib:/ogg213/lib

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

解压安装
su - pgadmin
$ unzip 213000_ggs_Linux_x64_PostgreSQL_64bit.zip
$ tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar -C /ogg213

创建OGG目录
$ cd /ogg213
$ ./ggsci
GGSCI (db01) 1> create subdirs  

Creating subdirectories under current directory /ogg213

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


##至此，OGG软件安装完成。

3、配置OGG登录访问Postgresql数据库

创建DSN
ogg for pg使用的是ODBC来连接数据库，因此需要指明ODBC dirver

配置驱动程序
$ cat /etc/odbcinst.ini 
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1


配置ODBC.INI连接文件 
vi /etc/odbc.ini
#####################################参考内容#####################################
[ODBC Data Sources]										//定义的数据库别名，后面的【postgre】部分与之对应
GG_Postgres=DataDirect 10.6 PostgreSQL Wire Protocol		

[ODBC]
IANAAppCodePage=106										//字符集，应该与数据库的字符集对应，106代表UTF-8
InstallDir=/data/ogg									//ogg安装目录

[GG_Postgres]
Driver=/data/ogg/lib/GGpsql25.so						//OGG安装包里有内置ODBC驱动
Description=DataDirect 10.6 PostgreSQL Wire Protocol	//介绍
Database=postgres										//表空间
HostName=localhost										//一般是本地地址
PortNumber=5432											//端口
LogonID=postgres										//数据库用户名
Password=postgres										//数据库密码
#####################################参考内容#####################################

cat /etc/odbc.ini 

[ODBC Data Sources]
alp=DataDirect 10.3 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=106
InstallDir=/ogg213

[testdb]
Driver=/ogg213/lib/GGpsql25.so
Description=DataDirect 10.3 PostgreSQL Wire Protocol
Database=testdb
HostName=127.0.0.1
PortNumber=5432
LogonID=ggadmin
Password=Bai_yun123
TransactionErrorBehavior=2

测试登录成功.
GGSCI (db01) 1> dblogin sourcedb testdb userid ggadmin password Bai_yun123

2023-03-16 10:11:46  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.UTF-8.

2023-03-16 10:11:46  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.

4、开始配置进程参数

主库配置
配置全局参数
GGSCI (db01 as ggadmin@alp) 10> view param ./GLOBALS

GGSCHEMA ggadmin
CHECKPOINTTABLE GGADMIN.CHECKPOINTPG

配置MGR进程
GGSCI (db01) 1> edit param mgr

PORT 7810
DYNAMICPORTLIST 7810-7820
AUTORESTART EXTRACT *, WAITMINUTES 2, RESETMINUTES 5
PURGEOLDEXTRACTS ./DIRDAT/*, USECHECKPOINTS,MINKEEPHOURS 24
AUTORESTART ER *, RETRIES 3, WAITMINUTES 2,RESETMINUTES 10
ACCESSRULE, PROG *, IPADDR 192.168.56.*, ALLOW


配置抽取进程
首先注册抽取进程，生成一个复制槽
dblogin sourcedb testdb userid ggadmin password Bai_yun123
REGISTER EXTRACT EALP


GGSCI (db01) 1> dblogin sourcedb testdb userid ggadmin password Bai_yun123

2023-03-16 11:00:00  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.UTF-8.

2023-03-16 11:00:00  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.

GGSCI (db01 as ggadmin@testdb) 2> REGISTER EXTRACT EALP

2023-03-16 11:00:11  INFO    OGG-25355  Successfully created replication slot 'ealp_388c9ed6a1f9e474' for Extract group 'EALP' in database 'testdb'.


编辑抽取进程参数
GGSCI (db01) 1> edit param ealp

EXTRACT EALP
SETENV(PGCLIENTENCODING = "UTF8")
SETENV(ODBCINI="/etc/odbc.ini" )
sourcedb alp  userid ggadmin password Bai_yun123
EXTTRAIL /ogg213/dirdat/E1
DISCARDFILE /ogg213/dirrpt/ealp.dsc,append, megabytes 4096
DISCARDROLLOVER AT 05:30 ON MONDAY
TABLE PUBLIC.*;


ADD EXT EALP, TRANLOG , BEGIN NOW
ADD EXTTRAIL /OGG213/DIRDAT/E1, EXT EALP


##删除重建 如果需要
DELETE EXTTRAIL /ogg213/dirrpt/E1, EXTRACT EALP
##删除一个旧的复制槽，取消注册之前需要先删除旧的抽取进程
UNREGISTER EXTRACT ealp with DATABASE alp

配置投递进程
GGSCI (db01) 1> edit param palp

EXTRACT palp
RMTHOST 192.168.56.100, MGRPORT 7810
RMTTRAIL /ogg213/dirdat/E1
PASSTHRU
TABLE PUBLIC.*;


add extract palp, exttrailsource /ogg213/dirdat/E1
add rmttrail /ogg213/dirdat/E1,extract palp


配置异构文件
GGSCI (db01) 1> edit param defalp

defsfile /ogg213/dirdef/defalp.prm
sourcedb alp userid ggadmin password Bai_yun123
table public.*;

./defgen paramfile dirprm/defalp.prm
将生成的def 传送到目标端


增加checkpoint table ,增加表的最小化日志
dblogin sourcedb testdb userid ggadmin password Bai_yun123
add checkpointtable ggadmin.CHECKPOINTPG
ADD TRANDATA public.tbl_test ALLCOLS

GGSCI (db01) 1> dblogin sourcedb testdb userid ggadmin password Bai_yun123

2023-03-16 10:11:46  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.UTF-8.

2023-03-16 10:11:46  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.

GGSCI (db01 as ggadmin@alp) 4> ADD TRANDATA public.tbl_test ALLCOLS

Logging of supplemental log data is enabled for table public.tbl_test. REPLICA IDENTITY was DEFAULT and is changed to FULL


目标库应用进程配置
GGSCI (global) 19> view param ./GLOBALS

CHECKPOINTTABLE GGADMIN.GGCHECK


GGSCI (global) 20> view param mgr

PORT 7810
DYNAMICPORTLIST 7810-7820
AUTORESTART EXTRACT *, WAITMINUTES 2, RESETMINUTES 5
PURGEOLDEXTRACTS ./DIRDAT/*, USECHECKPOINTS,MINKEEPHOURS 24
AUTORESTART ER *, RETRIES 3, WAITMINUTES 2,RESETMINUTES 10
ACCESSRULE, PROG *, IPADDR 192.168.56.*, ALLOW


GGSCI (global) 21> view param ralp

replicat ralp
SETENV (TNS_ADMIN = "/u01/app/oracle/product/11.2.0/db_1/network/admin")
userid ggadmin@orcl,password Bai_yun123
sourcedefs /ogg213/dirdef/defalp.prm OVERRIDE
reperror default, discard
discardfile /ogg213/dirrpt/ralp.dsc, append, megabytes 4096
DISCARDROLLOVER AT 05:30 ON MONDAY
--map public.*,target alp.*;
map public.tbl_test,target alp.tbl_test;


增加应用进程
dblogin userid ggadmin@orcl password Bai_yun123

add checkpointtable ggadmin.ggcheck
add replicat ralp,exttrail /ogg213/dirdat/E1,checkpointtable ggadmin.ggcheck 


如果遇到权限的问题请执行如下命令
grant flashback any table to testdb;
grant flashback on testdb.TBL_TEST to testdb;
exec dbms_goldengate_auth.grant_admin_privilege('ggadmin');

5、配置初始化数据进程

主库配置初始化进程 einialp

EXTRACT EINIALP
SETENV(PGCLIENTENCODING = "UTF8")
SETENV(ODBCINI="/etc/odbc.ini" )
sourcedb testdb userid ggadmin password Bai_yun123
RMTHOST 192.168.56.100, MGRPORT 7810
RMTTASK REPLICAT, GROUP RINIALP
TABLE public.*;


add extract EINIALP, sourceistable

备库配置初始化进程 rinialp
	
REPLICAT RINIALP
SETENV (TNS_ADMIN = "/u01/app/oracle/product/11.2.0/db_1/network/admin")
userid ggadmin@orcl,password Bai_yun123
sourcedefs /ogg213/dirdef/defalp.prm OVERRIDE
DISCARDFILE /ogg213/dirrpt/RINIALP.DSC, PURGE
map public.*,target alp.*;

add replicat riniaccs,specialrun

6、测试验证

主库DML操作
testdb=# select * from tbl_test where id=58;
 id |               info               |           c_time           
----+----------------------------------+----------------------------
 58 | 488c66e98d1efeb2752ee595d450125c | 2023-03-17 09:53:46.360224
(1 row)

testdb=# update tbl_test set info='pg2oracle' where id=58;
UPDATE 1
testdb=# select * from tbl_test where id=58;
 id |   info    |           c_time           
----+-----------+----------------------------
 58 | pg2oracle | 2023-03-17 09:53:46.360224
(1 row)

testdb=# delete from tbl_test where id=58;
DELETE 1


备库确认

SQL> select * from tbl_test where id=58;

        ID INFO                                  C_TIME
---------- ------------------------------------- -----------------------------
        58 pg2oracle                             17-MAR-23 09.53.46.360224 AM

SQL> select * from tbl_test where id=58;

no rows selected

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。