目标端环境：
[BEGIN] 2016/2/23 9:58:18
[root@node2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)
[root@node2 ~]# arch
x86_64
安装postgresql ,yum 方式：
[root@node2 ~]# rpm -qa | grep postgresql
postgresql-9.2.7-1.el7.x86_64
postgresql-docs-9.2.7-1.el7.x86_64
postgresql-server-9.2.7-1.el7.x86_64
postgresql-libs-9.2.7-1.el7.x86_64
[root@node2 ~]# yum list | grep postgresql
postgresql.x86_64 9.2.7-1.el7 @anaconda/7.1
postgresql-docs.x86_64 9.2.7-1.el7 @anaconda/7.1
postgresql-libs.x86_64 9.2.7-1.el7 @anaconda/7.1
postgresql-server.x86_64 9.2.7-1.el7 @anaconda/7.1
postgresql.i686 9.2.7-1.el7 Server
postgresql-contrib.x86_64 9.2.7-1.el7 Server
postgresql-devel.i686 9.2.7-1.el7 Server
postgresql-devel.x86_64 9.2.7-1.el7 Server
postgresql-jdbc.noarch 9.2.1002-5.el7 Server
postgresql-libs.i686 9.2.7-1.el7 Server
postgresql-odbc.x86_64 09.03.0100-2.el7 Server
postgresql-plperl.x86_64 9.2.7-1.el7 Server
postgresql-plPython.x86_64 9.2.7-1.el7 Server
postgresql-pltcl.x86_64 9.2.7-1.el7 Server
postgresql-test.x86_64 9.2.7-1.el7 Server
qt-postgresql.i686 1:4.8.5-8.0.1.el7 Server
qt-postgresql.x86_64 1:4.8.5-8.0.1.el7 Server
[root@node2 ~]# yum -y install postgresql-*
已加载插件：langpacks
Server | 3.6 kB 00:00:00
软件包 postgresql-9.2.7-1.el7.x86_64 已安装并且是最新版本
软件包 postgresql-libs-9.2.7-1.el7.x86_64 已安装并且是最新版本
软件包 postgresql-docs-9.2.7-1.el7.x86_64 已安装并且是最新版本
软件包 postgresql-server-9.2.7-1.el7.x86_64 已安装并且是最新版本
正在解决依赖关系
--> 正在检查事务
---> 软件包 postgresql-contrib.x86_64.0.9.2.7-1.el7 将被 安装
--> 正在处理依赖关系 libossp-uuid.so.16()(64bit)，它被软件包 postgresql-contrib-9.2.7-1.el7.x86_64 需要
---> 软件包 postgresql-devel.x86_64.0.9.2.7-1.el7 将被 安装
---> 软件包 postgresql-jdbc.noarch.0.9.2.1002-5.el7 将被 安装
---> 软件包 postgresql-odbc.x86_64.0.09.03.0100-2.el7 将被 安装
---> 软件包 postgresql-plperl.x86_64.0.9.2.7-1.el7 将被 安装
---> 软件包 postgresql-plpython.x86_64.0.9.2.7-1.el7 将被 安装
---> 软件包 postgresql-pltcl.x86_64.0.9.2.7-1.el7 将被 安装
---> 软件包 postgresql-test.x86_64.0.9.2.7-1.el7 将被 安装
--> 正在检查事务
---> 软件包 uuid.x86_64.0.1.6.2-26.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
==================================================================================================================================================
Package 架构 版本 源 大小
==================================================================================================================================================
正在安装:
postgresql-contrib x86_64 9.2.7-1.el7 Server 538 k
postgresql-devel x86_64 9.2.7-1.el7 Server 943 k
postgresql-jdbc noarch 9.2.1002-5.el7 Server 446 k
postgresql-odbc x86_64 09.03.0100-2.el7 Server 267 k
postgresql-plperl x86_64 9.2.7-1.el7 Server 83 k
postgresql-plpython x86_64 9.2.7-1.el7 Server 92 k
postgresql-pltcl x86_64 9.2.7-1.el7 Server 56 k
postgresql-test x86_64 9.2.7-1.el7 Server 1.7 M
为依赖而安装:
uuid x86_64 1.6.2-26.el7 Server 54 k
事务概要
==================================================================================================================================================
安装 8 软件包 (+1 依赖软件包)
总下载量：4.1 M
安装大小：18 M
Downloading packages:
--------------------------------------------------------------------------------------------------------------------------------------------------
总计 16 MB/s | 4.1 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : postgresql-devel-9.2.7-1.el7.x86_64 1/9
正在安装 : uuid-1.6.2-26.el7.x86_64 2/9
正在安装 : postgresql-contrib-9.2.7-1.el7.x86_64 3/9
正在安装 : postgresql-test-9.2.7-1.el7.x86_64 4/9
正在安装 : postgresql-plperl-9.2.7-1.el7.x86_64 5/9
正在安装 : postgresql-jdbc-9.2.1002-5.el7.noarch 6/9
正在安装 : postgresql-plpython-9.2.7-1.el7.x86_64 7/9
正在安装 : postgresql-odbc-09.03.0100-2.el7.x86_64 8/9
正在安装 : postgresql-pltcl-9.2.7-1.el7.x86_64 9/9
验证中 : postgresql-test-9.2.7-1.el7.x86_64 1/9
验证中 : postgresql-contrib-9.2.7-1.el7.x86_64 2/9
验证中 : postgresql-pltcl-9.2.7-1.el7.x86_64 3/9
验证中 : postgresql-odbc-09.03.0100-2.el7.x86_64 4/9
验证中 : uuid-1.6.2-26.el7.x86_64 5/9
验证中 : postgresql-plpython-9.2.7-1.el7.x86_64 6/9
验证中 : postgresql-jdbc-9.2.1002-5.el7.noarch 7/9
验证中 : postgresql-devel-9.2.7-1.el7.x86_64 8/9
验证中 : postgresql-plperl-9.2.7-1.el7.x86_64 9/9
已安装:
postgresql-contrib.x86_64 0:9.2.7-1.el7 postgresql-devel.x86_64 0:9.2.7-1.el7 postgresql-jdbc.noarch 0:9.2.1002-5.el7
postgresql-odbc.x86_64 0:09.03.0100-2.el7 postgresql-plperl.x86_64 0:9.2.7-1.el7 postgresql-plpython.x86_64 0:9.2.7-1.el7
postgresql-pltcl.x86_64 0:9.2.7-1.el7 postgresql-test.x86_64 0:9.2.7-1.el7
作为依赖被安装:
uuid.x86_64 0:1.6.2-26.el7
完毕！
确认安装成功：
[root@node2 ~]# rpm -qa | grep postgresql
postgresql-9.2.7-1.el7.x86_64
postgresql-docs-9.2.7-1.el7.x86_64
postgresql-jdbc-9.2.1002-5.el7.noarch
postgresql-pltcl-9.2.7-1.el7.x86_64
postgresql-odbc-09.03.0100-2.el7.x86_64
postgresql-contrib-9.2.7-1.el7.x86_64
postgresql-plperl-9.2.7-1.el7.x86_64
postgresql-test-9.2.7-1.el7.x86_64
postgresql-server-9.2.7-1.el7.x86_64
postgresql-plpython-9.2.7-1.el7.x86_64
postgresql-libs-9.2.7-1.el7.x86_64
postgresql-devel-9.2.7-1.el7.x86_64
[root@node2 ~]#
[root@node2 ~]#
[root@node2 ~]#
[root@node2 ~]#
启动postgresql:
[root@node2 ~]# service postgresql status
Redirecting to /bin/systemctl status postgresql.service
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled)
Active: inactive (dead)
[root@node2 ~]# whoami
root
启动报错：
[root@node2 ~]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... mkdir: cannot create directory '/var/lib/pgsql/data/pg_log': File exists
failed, see /var/lib/pgsql/initdb.log
[root@node2 ~]# cat /var/lib/pgsql/initdb.log
runuser: may not be used by non-root users
initdb: 目录"/var/lib/pgsql/data"已存在，但不是空的
如果您想创建一个新的数据库系统, 请删除或清空
目录 "/var/lib/pgsql/data" 或者运行带参数的 initdb
而不是 "/var/lib/pgsql/data".
属于此数据库系统的文件宿主为用户 "postgres".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "zh_CN.utf8"进行初始化.
默认的数据库编码已经相应的设置为 "UTF8".
initdb: 无法为本地化语言环境"zh_CN.utf8"找到合适的文本搜索配置
缺省的文本搜索配置将会被设置到"simple"
[root@node2 ~]# service postgresql start
Redirecting to /bin/systemctl start postgresql.service
Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.
[root@node2 ~]# cd /var/lib/pgsql/data
[root@node2 data]# ls -l
总用量 0
drwx------ 2 postgres postgres 6 2月 22 20:57 pg_log
[root@node2 data]# cd pg_log
[root@node2 pg_log]# ls
[root@node2 pg_log]# cd ../
[root@node2 data]# ls
pg_log
排查问题，删除data目录后问题解决：
[root@node2 data]# cd ../
[root@node2 pgsql]# ls -l
总用量 4
drwx------. 2 postgres postgres 6 5月 5 2014 backups
drwx------. 3 postgres postgres 19 2月 22 20:57 data
-rw------- 1 postgres postgres 636 2月 22 21:00 initdb.log
-rw------- 1 postgres postgres 0 2月 20 05:07 logfile
[root@node2 pgsql]# pwd
/var/lib/pgsql
[root@node2 pgsql]# pwd
/var/lib/pgsql
[root@node2 pgsql]# rm -rf data
[root@node2 pgsql]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK
[root@node2 pgsql]# service postgresql status
Redirecting to /bin/systemctl status postgresql.service
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled)
Active: failed (Result: exit-code) since 一 2016-02-22 21:01:22 EST; 1min 31s ago
Process: 4800 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)
2月 22 21:01:22 node2 systemd[1]: Starting PostgreSQL database server...
2月 22 21:01:22 node2 postgresql-check-db-dir[4800]: "/var/lib/pgsql/data" is missing or empty.
2月 22 21:01:22 node2 postgresql-check-db-dir[4800]: Use "postgresql-setup initdb" to initialize the database cluster.
2月 22 21:01:22 node2 postgresql-check-db-dir[4800]: See /usr/share/doc/postgresql-9.2.7/README.rpm-dist for more information.
2月 22 21:01:22 node2 systemd[1]: postgresql.service: control process exited, code=exited status=1
2月 22 21:01:22 node2 systemd[1]: Failed to start PostgreSQL database server.
2月 22 21:01:22 node2 systemd[1]: Unit postgresql.service entered failed state.
[root@node2 pgsql]# service postgresql start
Redirecting to /bin/systemctl start postgresql.service
[root@node2 pgsql]# service postgresql status
Redirecting to /bin/systemctl status postgresql.service
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled)
Active: active (running) since 一 2016-02-22 21:03:08 EST; 9s ago
Process: 5049 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 5042 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 5052 (postgres)
CGroup: /system.slice/postgresql.service
├─5052 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─5053 postgres: logger process
├─5055 postgres: checkpointer process
├─5056 postgres: writer process
├─5057 postgres: wal writer process
├─5058 postgres: autovacuum launcher process
└─5059 postgres: stats collector process
2月 22 21:03:07 node2 systemd[1]: Starting PostgreSQL database server...
2月 22 21:03:08 node2 systemd[1]: Started PostgreSQL database server.
[root@node2 pgsql]#
[root@node2 pgsql]#
[root@node2 pgsql]#
[root@node2 pgsql]#
root 用户启动报错，切换到postgres用户启动，yum 方式安装pg 默认为创建此用户：
[root@node2 pgsql]# psql
psql: 致命错误: 角色 "root" 不存在
[root@node2 pgsql]# cat /etc/passwd | grep post
postgres:x:26:26 ostgreSQL Server:/var/lib/pgsql:/bin/bash
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
[root@node2 pgsql]# su - postgres
上一次登录：一 2月 22 20:56:37 EST 2016pts/0 上
-bash-4.2$ pg_ctl status
pg_ctl: 正在运行服务器进程(PID: 5052)
/usr/bin/postgres "-D" "/var/lib/pgsql/data" "-p" "5432"
登录postgresql:
-bash-4.2$ psql
psql (9.2.7)
输入 "help" 来获取帮助信息.
postgres=# \l
资料库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
postgres=# \d
找不到关联。
postgres=# \c postgres
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# \d
找不到关联。
postgres=# \q
配置postgresql 登录访问控制：
-bash-4.2$ cd /var/lib/p
pcp/ pgsql/ pki/ plymouth/ polkit-1/ postfix/ pulse/
-bash-4.2$ cd /var/lib/pgsql/data/
-bash-4.2$ ls -l
总用量 48
drwx------ 5 postgres postgres 38 2月 22 21:02 base
drwx------ 2 postgres postgres 4096 2月 22 21:03 global
drwx------ 2 postgres postgres 17 2月 22 21:02 pg_clog
-rw------- 1 postgres postgres 4232 2月 22 21:02 pg_hba.conf
-rw------- 1 postgres postgres 1636 2月 22 21:02 pg_ident.conf
drwx------ 2 postgres postgres 31 2月 22 21:03 pg_log
drwx------ 4 postgres postgres 34 2月 22 21:02 pg_multixact
drwx------ 2 postgres postgres 17 2月 22 21:03 pg_notify
drwx------ 2 postgres postgres 6 2月 22 21:02 pg_serial
drwx------ 2 postgres postgres 6 2月 22 21:02 pg_snapshots
drwx------ 2 postgres postgres 24 2月 22 21:14 pg_stat_tmp
drwx------ 2 postgres postgres 17 2月 22 21:02 pg_subtrans
drwx------ 2 postgres postgres 6 2月 22 21:02 pg_tblspc
drwx------ 2 postgres postgres 6 2月 22 21:02 pg_twophase
-rw------- 1 postgres postgres 4 2月 22 21:02 PG_VERSION
drwx------ 3 postgres postgres 58 2月 22 21:02 pg_xlog
-rw------- 1 postgres postgres 19782 2月 22 21:02 postgresql.conf
-rw------- 1 postgres postgres 57 2月 22 21:03 postmaster.opts
-rw------- 1 postgres postgres 91 2月 22 21:03 postmaster.pid
-bash-4.2$ vi pg_hba.conf
-bash-4.2$ grep trust pg_hba.conf
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
host all all 192.168.1.0/24 trust
配置数据库监听范围：
-bash-4.2$ vi postgresql.conf
-bash-4.2$ grep listen_address postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -a
. base pg_clog pg_ident.conf pg_multixact pg_serial pg_stat_tmp pg_tblspc PG_VERSION postgresql.conf postmaster.pid
.. global pg_hba.conf pg_log pg_notify pg_snapshots pg_subtrans pg_twophase pg_xlog postmaster.opts
-bash-4.2$ cd
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ ls -a
. .. backups .bash_history .bash_profile .cache .config data initdb.log logfile .psql_history
默认.bash_profile 配置：
-bash-4.2$ cat .bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/data
export PGDATA
创建goldengate 软件安装目录，并调整权限及属主：
-bash-4.2$ exit
登出
[root@node2 pgsql]# df -h
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/ol-root 46G 12G 35G 25% /
devtmpfs 1.8G 0 1.8G 0% /dev
tmpfs 1.8G 80K 1.8G 1% /dev/shm
tmpfs 1.8G 9.0M 1.8G 1% /run
tmpfs 1.8G 0 1.8G 0% /sys/fs/cgroup
/dev/loop0 4.0G 4.0G 0 100% /mnt
/dev/sda1 497M 152M 345M 31% /boot
[root@node2 pgsql]# mkdir -p /u02/pg/ogg
[root@node2 pgsql]# rm /u01/pg/ogg
[root@node2 /]# chown -R postgres:postgres /u02/
[root@node2 /]# chmod -R 755 /u02/
[root@node2 /]# cd /u02/pg/ogg
goldengate 软件root用户上传至此目录，并调整权限：
[root@node2 ogg]# ls -l
总用量 729540
-rw-r--r-- 1 root root 747048960 2月 22 21:23 ggs_Linux_x64_PostgreSQL_64bit.tar
[root@node2 ogg]# chmod 755 ggs_Linux_x64_PostgreSQL_64bit.tar
[root@node2 ogg]# chown postgres:postgres ggs_Linux_x64_PostgreSQL_64bit.tar
postgres 用户解压goldengate软件：
[root@node2 ogg]# su - postgres
上一次登录：一 2月 22 21:13:47 EST 2016pts/0 上
-bash-4.2$ cd /u02/pg/ogg
-bash-4.2$ ls -l
总用量 729540
-rwxr-xr-x 1 postgres postgres 747048960 2月 22 21:23 ggs_Linux_x64_PostgreSQL_64bit.tar
-bash-4.2$ tar -xf ggs_Linux_x64_PostgreSQL_64bit.tar
-bash-4.2$ ls -l
总用量 1338584
-rw-r----- 1 postgres postgres 426 10月 15 2010 bcpfmt.tpl
-rw-r----- 1 postgres postgres 1725 10月 15 2010 bcrypt.txt
-rwxr-xr-x 1 postgres postgres 8607397 12月 11 20:15 cachefiledump
-rwxr-xr-x 1 postgres postgres 8772006 12月 11 20:13 checkprm
-rwxr-x--- 1 postgres postgres 9569747 12月 11 20:07 convchk
-rwxr-xr-x 1 postgres postgres 15068810 12月 11 20:11 convprm
-rw-r----- 1 postgres postgres 159 10月 15 2010 db2cntl.tpl
-rw-r--r-- 1 postgres postgres 2500 8月 20 2015 DDGG.LIC
-rwxr-x--- 1 postgres postgres 20356711 12月 11 20:21 defgen
drwxr-x--- 2 postgres postgres 23 12月 11 18:06 dirout
drwxr-x--- 5 postgres postgres 43 12月 11 18:06 dirwww
-rwxr-x--- 1 postgres postgres 16947932 12月 11 19:42 emsclnt
-rwxr-x--- 1 postgres postgres 86303787 12月 11 20:53 extract
-rw-r----- 1 postgres postgres 1968 10月 15 2010 freeBSD.txt
-rwxr-x--- 1 postgres postgres 17163882 12月 11 19:39 ggcmd
-rw-r----- 1 postgres postgres 1847888 12月 11 18:16 ggMessage.dat
-rw-r----- 1 postgres postgres 39326056 12月 11 18:15 ggparam.dat
-rwxr-x--- 1 postgres postgres 43473146 12月 11 19:36 ggsci
-rwxr-xr-x 1 postgres postgres 747048960 2月 22 21:23 ggs_Linux_x64_PostgreSQL_64bit.tar
drwxr-xr-x 11 postgres postgres 4096 12月 12 02:27 help
-rw-r----- 1 postgres postgres 278351 10月 21 17:56 help.txt
-rwxr-x--- 1 postgres postgres 498096 12月 11 19:42 keygen
drwxr-x--- 2 postgres postgres 4096 12月 12 02:27 lib
-rwxr-x--- 1 postgres postgres 126863 10月 17 2014 libantlr3c.so
-rwxr-x--- 1 postgres postgres 2109463 7月 13 2015 libdb-6.1.so
-rwxr-x--- 1 postgres postgres 15314827 12月 11 18:26 libgglog.so
-rwxr-x--- 1 postgres postgres 11469665 12月 11 19:08 libggnnzitp.so
-rwxr-x--- 1 postgres postgres 50885388 12月 11 18:57 libggparam.so
-rwxr-x--- 1 postgres postgres 1971854 12月 11 18:29 libggperf.so
-rwxr-x--- 1 postgres postgres 3546231 12月 11 18:29 libggrepo.so
-rwxr-x--- 1 postgres postgres 22827292 10月 17 2014 libicudata.so.48
-rwxr-x--- 1 postgres postgres 22827292 10月 17 2014 libicudata.so.48.1
-rwxr-x--- 1 postgres postgres 2527729 10月 17 2014 libicui18n.so.48
-rwxr-x--- 1 postgres postgres 2527729 10月 17 2014 libicui18n.so.48.1
-rwxr-x--- 1 postgres postgres 1809818 10月 17 2014 libicuuc.so.48
-rwxr-x--- 1 postgres postgres 1809818 10月 17 2014 libicuuc.so.48.1
-rwxr-x--- 1 postgres postgres 4925371 10月 17 2014 libxerces-c.so.28
-rw-r----- 1 postgres postgres 1668 10月 15 2010 libxml2.txt
drwxr-xr-x 3 postgres postgres 18 12月 12 02:27 locale
-rwxr-x--- 1 postgres postgres 27839750 12月 11 19:49 logdump
-rwxr-x--- 1 postgres postgres 30678806 12月 11 19:22 mgr
-r--r----- 1 postgres postgres 94588 12月 12 02:27 notices.txt
-rwxr-x--- 1 postgres postgres 8964249 12月 11 19:53 oggerr
-rw-r----- 1 postgres postgres 1272 12月 28 2010 prvtclkm.plb
-rwxr-x--- 1 postgres postgres 87907271 12月 11 21:23 replicat
-rwxr-x--- 1 postgres postgres 8980284 12月 11 19:51 retrace
-rwxr-x--- 1 postgres postgres 16136022 12月 11 19:57 reverse
-rwxr-x--- 1 postgres postgres 29973717 12月 11 20:05 server
-rw-r----- 1 postgres postgres 248 10月 15 2010 sqlldr.tpl
-rw-r----- 1 postgres postgres 759 10月 15 2010 tcperrs
-rw-r----- 1 postgres postgres 37455 3月 4 2015 ucharset.h
drwxr-x--- 7 postgres postgres 116 12月 11 18:06 UserExitExamples
-rw-r----- 1 postgres postgres 30782 10月 2 17:49 usrdecs.h
-rw-r----- 1 postgres postgres 1476 10月 15 2010 zlib.txt
-bash-4.2$ cd
-bash-4.2$ vi .bash_profile
-bash-4.2$ source .bash_profile
-bash-4.2$ cat .bash_profile
报错：
bash: ggsci: 未找到命令...
-bash-4.2$ strace ggsci
strace: Can't stat 'ggsci': No such file or directory
-bash-4.2$ ./ggsci
./ggsci: error while loading shared libraries: libGGicu25.so: cannot open shared object file: No such file or directory
-bash-4.2$ pwd
/u02/pg/ogg
-bash-4.2$ exit
登出
[root@node2 ogg]# find / -name "libGGicu25.so"
/u02/pg/ogg/lib/libGGicu25.so
原因：
.bash_profile 环境变量缺少导致：
[root@node2 ogg]# su - postgres
上一次登录：一 2月 22 21:24:17 EST 2016pts/0 上
重新编辑.bash_profile 文件：
-bash-4.2$ vi .bash_profile
-bash-4.2$ source .bash_profile
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ cat .bash_profile
[ -f /etc/profile ] && source /etc/profile
export GGHOME=/u02/pg/ogg
PGDATA=/var/lib/pgsql/data
export PGDATA
export LD_LIBRARY_PATH=/u02/pg/ogg/lib LD_LIGRARY_PATH
export PATH=/u01/pg/oggPATH
-bash-4.2$ cd $GGHOME
-bash-4.2$ ggsci
.
-bash-4.2$ exit
登出
配置目标端相关进程：
mgr 端口调整为7810，因为目标端前期配置有MYSQL TO MYSQL 单向复制，占用了7809端口：
[root@node2 ogg]# su - postgres
上一次登录：一 2月 22 21:37:56 EST 2016pts/0 上
-bash-4.2$ cd $GGHOME
-bash-4.2$ ggsci
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> edit params mgr
ERROR: Directory /u02/pg/ogg/dirprm does not exist yet (use CREATE SUBDIRS).
GGSCI (node2) 2> create subdirs
Creating subdirectories under current directory /u02/pg/ogg
Parameter files /u02/pg/ogg/dirprm: created
Report files /u02/pg/ogg/dirrpt: created
Checkpoint files /u02/pg/ogg/dirchk: created
Process status files /u02/pg/ogg/dirpcs: created
SQL script files /u02/pg/ogg/dirsql: created
Database definitions files /u02/pg/ogg/dirdef: created
Extract data files /u02/pg/ogg/dirdat: created
Temporary files /u02/pg/ogg/dirtmp: created
Credential store files /u02/pg/ogg/dircrd: created
Masterkey wallet files /u02/pg/ogg/dirwlt: created
Dump files /u02/pg/ogg/dirdmp: created
GGSCI (node2) 3> edit params mgr
GGSCI (node2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (node2) 5> start mgr
Manager started.
GGSCI (node2) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (node2) 7> shell tail -100 ggserr.log
2016-02-22 21:40:05 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): edit params mgr.
2016-02-22 21:40:18 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): create subdirs.
2016-02-22 21:40:30 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): edit params mgr.
2016-02-22 21:40:40 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 21:40:43 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): start mgr.
2016-02-22 21:40:43 WARNING OGG-01877 Oracle GoldenGate Manager, mgr.prm: Missing explicit accessrule for server collector.
2016-02-22 21:40:43 INFO OGG-00960 Oracle GoldenGate Manager, mgr.prm: Access granted (rule #8).
2016-02-22 21:40:43 INFO OGG-00983 Oracle GoldenGate Manager, mgr.prm: Manager started (port 7810).
2016-02-22 21:40:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
GGSCI (node2) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
登录postgresql数据库，创建测试用表：
GGSCI (node2) 9> exit
-bash-4.2$ psql
psql (9.2.7)
输入 "help" 来获取帮助信息.
postgres-# \q
-bash-4.2$ createdb test
-bash-4.2$ psql
psql (9.2.7)
输入 "help" 来获取帮助信息.
postgres=# \l
资料库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
(4 行记录)
postgres=# \c test
您现在已经连线到数据库 "test",用户 "postgres".
test=# create table t1 (id int primary key,name varchar(20));
注意: CREATE TABLE / PRIMARY KEY 将要为表 "t1" 创建隐含索引 "t1_pkey"
CREATE TABLE
test=# \d
关联列表
架构模式 | 名称 | 型别 | 拥有者
----------+------+--------+----------
public | t1 | 资料表 | postgres
(1 行记录)
test=# select * from public.t1;
id | name
----+------
(0 行记录)
test=#
test=#
修改数据库用户postgres密码为oracle:
test=# \password postgres
输入新的密码：
再次键入：
test=# \q
-bash-4.2$ cd
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ exit
登出
编辑postgresql 目标端ODBC数据源
[root@node2 ogg]# su - postgres
上一次登录：一 2月 22 21:38:39 EST 2016pts/0 上
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ vi .bash_profile
-bash-4.2$ source .bash_profile
-bash-4.2$ cat .bash_profile
[ -f /etc/profile ] && source /etc/profile
export GGHOME=/u02/pg/ogg
PGDATA=/var/lib/pgsql/data
export PGDATA
export LD_LIBRARY_PATH=/u02/pg/ogg/libLD_LIGRARY_PATH
export PATH=/u02/pg/oggPATH
export ODBCINI=/u02/pg/ogg/odbc.ini
-bash-4.2$ cd /u02/pg/ogg/
-bash-4.2$ vi odbc.ini
-bash-4.2$ pwd
/u02/pg/ogg
-bash-4.2$ cat odbc.ini
[ODBC Data Sources]
GG_Postgres=DataDirect 9.2.7 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=106
InstallDir=/u02/pg/ogg
[GG_Postgres]
Driver=/u02/pg/ogg/lib/GGpsql25.so
Description=DataDirect 9.2.7 PostgreSQL Wire Protocol
Database=test
HostName=192.168.1.223
PortNumber=5432
LogonID=postgres
Password=oracle
-bash-4.2$ cd $GGHOME
-bash-4.2$ ggsci
source .bash_profile 是调整生效：
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
测试数据源连接：
出现问题：
GGSCI (node2) 2> dblogin sourcedb gg_postgres userid postgres
Password:
2016-02-22 22:00:00 WARNING OGG-00552 Database operation failed: Couldn't connect to gg_postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
ERROR: Database operation failed: Couldn't connect to gg_postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.Failed to open data source gg_postgres for user postgres.
GGSCI (node2) 3> dblogin sourcedb GG_Postgres userid postgres
Password:
2016-02-22 22:01:13 WARNING OGG-00552 Database operation failed: Couldn't connect to GG_Postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
ERROR: Database operation failed: Couldn't connect to GG_Postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.Failed to open data source GG_Postgres for user postgres.
GGSCI (node2) 4> exit
-bash-4.2$ netstat -tunlp | grep post
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 5052/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 5052/postgres
-bash-4.2$ ggsci
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (node2) 2> dblogin sourcedb gg_postgres userid postgres
Password:
2016-02-22 22:02:40 WARNING OGG-00552 Database operation failed: Couldn't connect to gg_postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
ERROR: Database operation failed: Couldn't connect to gg_postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.Failed to open data source gg_postgres for user postgres.
GGSCI (node2) 1> dblogin sourcedb gg_postgres userid postgres
Password:
2016-02-22 22:06:16 WARNING OGG-00552 Database operation failed: Couldn't connect to gg_postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
ERROR: Database operation failed: Couldn't connect to gg_postgres. ODBC error: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.Failed to open data source gg_postgres for user postgres.
GGSCI (node2) 2> dblogin sourcedb gg_postgres userid postgres
Password:
2016-02-22 22:08:37 INFO OGG-03036 Database character set identified as UTF-8. Locale: zh_CN.
2016-02-22 22:08:37 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (node2 as postgres@gg_postgres) 3> exit
数据库访问控制导致，处理方式如下：
-bash-4.2$ ########调整内容如下：
-bash-4.2$ pwd
/u02/pg/ogg
-bash-4.2$ cd data
-bash: cd: data: 没有那个文件或目录
-bash-4.2$ cd /var/lib/pgsql/data/
-bash-4.2$ grep trust pg_hba.conf
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
host all all 127.0.0.1/32 trust
host all all 192.168.1.0/24 trust
-bash-4.2$ ####然后重启启动postgresql再次尝试dblogin sourcedb gg_postgres userid postgres 连接成功
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ pwd
/var/lib/pgsql/data
-bash-4.2$ cd $GGHOME
-bash-4.2$ ggsci
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
goldengate for mysql to postgresql 源端配置操作：
源端数据库状态及用户配置，这里简单说明，详细操作参考 goldengate for mysql to mysql 文档：
[BEGIN] 2016/2/23 11:11:39
[mysql@node1 ~]$ exit
登出
[root@node1 ~]# clear
[root@node1 ~]# su - mysql
上一次登录：一 2月 22 22:11:26 EST 2016pts/0 上
[mysql@node1 ~]$ service mysqld status
Redirecting to /bin/systemctl status mysqld.service
mysqld.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
Active: active (running) since 一 2016-02-22 20:47:29 EST; 1h 24min ago
Process: 2103 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Main PID: 2097 (mysqld_safe)
CGroup: /system.slice/mysqld.service
├─2097 /bin/sh /usr/bin/mysqld_safe
└─2485 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mysqld.log...
[mysql@node1 ~]$ rpm -qa | grep mysql
mysql-community-libs-5.6.23-3.el7.x86_64
mysql-community-common-5.6.23-3.el7.x86_64
akonadi-mysql-1.9.2-4.0.1.el7.x86_64
mysql-connector-odbc-5.2.5-6.0.1.el7.x86_64
qt-mysql-4.8.5-8.0.1.el7.x86_64
mysql-community-server-5.6.23-3.el7.x86_64
mysql-community-client-5.6.23-3.el7.x86_64
[mysql@node1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATHHOME/.local/binHOME/bin
export GGHOME=/u01/mysql/ogg
export PATH=/u01/mysql/oggPATH
export LD_LIBRARY_PATH=/home/mysql/ggsLD_LIBRARY_PATH
export PATH
[mysql@node1 ~]$
[mysql@node1 ~]$
[mysql@node1 ~]$
现有进程为MYSQL TO MYSQL 进程：
[mysql@node1 ~]$ cd $GGHOME
[mysql@node1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT_MY 00:00:00 01:19:42
EXTRACT STOPPED PM_MY 00:00:00 01:19:37
GGSCI (node1) 2> start mgr
Manager started.
GGSCI (node1) 3> info
ERROR: Invalid command.
GGSCI (node1) 4> ]info all
ERROR: Invalid command.
GGSCI (node1) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_MY 00:00:00 01:20:05
EXTRACT STOPPED PM_MY 00:00:00 01:20:00
GGSCI (node1) 6> view params ext_my
extract ext_my
setenv (MYSQL_HOME="/var/lib/mysql")
tranlogoptions altlogdest /var/lib/mysql/binary-log.index
sourcedb sourcedb@node1:3306,userid root,password oracle
exttrail /u01/mysql/ogg/dirdat/m2
dynamicresolution
gettruncates
table sourcedb.TCUSTMER;
table sourcedb.TCUSTORD;
GGSCI (node1) 7> view params pm_my
extract pm_my
rmthost 192.168.1.223,mgrport 7809
rmttrail /u01/mysql/ogg/dirdat/m2
passthru
gettruncates
table sourcedb.TCUSTMER;
table sourcedb.TCUSTORD;
GGSCI (node1) 8>
GGSCI (node1) 8>
GGSCI (node1) 8> exit
源端mysql 数据库创建测试表：
[mysql@node1 ogg]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sourcedb |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t1 (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.31 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.01 sec)
mysql> desc test.t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> exit
配置抽取进程：
[mysql@node1 ogg]$ pwd
/u01/mysql/ogg
[mysql@node1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_MY 00:00:00 01:30:01
EXTRACT STOPPED PM_MY 00:00:00 01:29:57
GGSCI (node1) 2> edit params ext_pg
GGSCI (node1) 10> view params ext_pg
extract ext_pg
setenv (MYSQL_HOME="/var/lib/mysql")
tranlogoptions altlogdest /var/lib/mysql/binary-log.index
sourcedb sourcedb@node1:3306,userid root,password oracle
rmthost 192.168.1.223,mgrport 7810
rmttrail /u02/pg/ogg/dirdat/ep
table test.t1;
GGSCI (node1) 16> add extract ext_pg,tranlog,begin now
EXTRACT added.
GGSCI (node1) 17> add exttrail /u02/pg/ogg/dirdat/ep,extract ext_pg
EXTTRAIL added.
GGSCI (node1) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_MY 00:00:00 01:41:03
EXTRACT STOPPED EXT_PG 00:00:00 00:00:14
EXTRACT STOPPED PM_MY 00:00:00 01:40:59
启动ext_pg进程：
GGSCI (node1) 19> start ext_pg
Sending START request to MANAGER ...
EXTRACT EXT_PG starting
GGSCI (node1) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_MY 00:00:00 01:41:22
EXTRACT RUNNING EXT_PG 00:00:00 00:00:33
EXTRACT STOPPED PM_MY 00:00:00 01:41:18
查看进程状态：
GGSCI (node1) 21> info extract ext_pg
EXTRACT EXT_PG Last Started 2016-02-22 22:34 Status RUNNING
Checkpoint Lag 00:00:36 (updated 00:00:04 ago)
Process ID 11945
VAM Read Checkpoint 2016-02-22 22:33:42.331162
异构环境创建数据定义文件参数文件：
GGSCI (node1) 22> edit params defgen
GGSCI (node1) 23> view params defgen
DEFSFILE /u01/mysql/ogg/dirdef/GGTEST.def
sourcedb sourcedb@node1:3306,userid root,password oracle
TABLE test.t1;
GGSCI (node1) 24> exit
生产数据定义参数文件：
[mysql@node1 ogg]$ ./defgen paramfile ./dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 17:21:39
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-02-22 22:38:53
***********************************************************************
Operating System Version:
Linux
Version #2 SMP Wed Feb 11 14:18:22 PST 2015, Release 3.8.13-55.1.6.el7uek.x86_64
Node: node1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 12082
***********************************************************************
** Running with the following parameters **
***********************************************************************
DEFSFILE /u01/mysql/ogg/dirdef/GGTEST.def
sourcedb sourcedb@node1:3306,userid root,password ***
TABLE test.t1;
Retrieving definition for test.t1.
Definitions generated for 1 table in /u01/mysql/ogg/dirdef/GGTEST.def.
[mysql@node1 ogg]$
[mysql@node1 ogg]$
[mysql@node1 ogg]$ cat /u01/mysql/ogg/dirdef/GGTEST.def
*+- Defgen version 5.0, Encoding UTF-8
*
* Definitions created/modified 2016-02-22 22:38
*
* Field descriptions for each column entry:
*
* 1 Name
* 2 Data Type
* 3 External Length
* 4 Fetch Offset
* 5 Scale
* 6 Level
* 7 Null
* 8 Bump if Odd
* 9 Internal Length
* 10 Binary Length
* 11 Table Length
* 12 Most Significant DT
* 13 Least Significant DT
* 14 High Precision
* 15 Low Precision
* 16 Elementary Item
* 17 Occurs
* 18 Key Column
* 19 Sub Data Type
* 20 Native Data Type
* 21 Character Set
* 22 Character Length
* 23 LOB Type
* 24 Partial Type
*
Database type: MYSQL
Character set ID: ISO-8859-1
National character set ID: UTF-16
Locale: en_US
Case sensitivity: 11 11 11 22 22 11 11 11 11 11 11 11 11 11 11 11
TimeZone: US/Eastern
*
Definition for table test.t1
Record length: 32
Syskey: 0
Columns: 2
id 132 11 0 0 0 1 0 4 4 4 0 0 0 0 1 0 1 0 3 -1 0 0 0
name 64 20 7 0 0 1 0 20 20 0 0 0 0 0 1 0 0 0 253 1032 0 0 0
End of definition
拷贝数据定义参数文件至postgresql端，由于是root用户拷贝，注意调整权限：
[mysql@node1 ogg]$ pwd
/u01/mysql/ogg
[mysql@node1 ogg]$ scp dird
dirdat/ dirdef/ dirdmp/
[mysql@node1 ogg]$ scp dirdef/GGTEST.def 192.168.1.223:/u02/pg/ogg/dirdef
The authenticity of host '192.168.1.223 (192.168.1.223)' can't be established.
ECDSA key fingerprint is 2f:42:0a:ff:d2:43:ef:f1:8d:6c:1d:72:51:42:ee:5b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.223' (ECDSA) to the list of known hosts.
mysql@192.168.1.223's password:
scp: /u02/pg/ogg/dirdef/GGTEST.def: Permission denied
[mysql@node1 ogg]$ su
密码：
[root@node1 ogg]# scp dirdef/GGTEST.def 192.168.1.223:/u02/pg/ogg/dirdef
root@192.168.1.223's password:
GGTEST.def 100% 1188 1.2KB/s 00:00
[root@node1 ogg]# ssh node2
root@node2's password:
Last login: Mon Feb 22 22:37:58 2016 from 192.168.1.1
[root@node2 ~]# cd /u02/pg/ogg/dirdef
[root@node2 dirdef]# ls -l
总用量 4
-rw-r----- 1 root root 1188 2月 22 22:41 GGTEST.def
[root@node2 dirdef]# chmod 777 GGTEST.def
[root@node2 dirdef]# chown postgres:postgres GGTEST.def
[root@node2 dirdef]# ls -l
总用量 4
-rwxrwxrwx 1 postgres postgres 1188 2月 22 22:41 GGTEST.def
[root@node2 dirdef]# exit
登出
Connection to node2 closed.
[root@node1 ogg]#
[root@node1 ogg]#
[root@node1 ogg]#
[root@node1 ogg]# exit
exit
[mysql@node1 ogg]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 2> exit
配置postgresql目标端复制进程：
-bash-4.2$ pwd
/u02/pg/ogg
-bash-4.2$ ggsci
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (node2) 2> edit params rep_pg
GGSCI (node2) 3> view params rep_pg
REPLICAT rep_pg
SOURCEDEFS /u02/pg/ogg/dirdef/GGTEST.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/u02/pg/ogg/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB GG_Postgres, USERID postgres, PASSWORD oracle
DISCARDFILE /u02/pg/ogg/dirrpt/diskg.dsc, purge
MAP test.t1, TARGET public.t1, COLMAP (id=id,name=name);
GGSCI (node2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (node2) 5> add replicat rep_pg,NODBCHECKPOINT,extrail /u02/pg/ogg/dirdat/ep
ERROR: Invalid parameter specified for ADD REPLICAT.
GGSCI (node2) 6> add replicat rep_pg,NODBCHECKPOINT,exttrail /u02/pg/ogg/dirdat/ep
REPLICAT added.
GGSCI (node2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP_PG 00:00:00 00:00:03
启动replicat 进程并查看状态及日志，确认无报错及警告：
GGSCI (node2) 8> start rep_pg
Sending START request to MANAGER ...
REPLICAT REP_PG starting
GGSCI (node2) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_PG 00:00:00 00:00:09
GGSCI (node2) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_PG 00:00:00 00:00:00
GGSCI (node2) 11> shell tail -500 ggserr.log
2016-02-22 21:40:05 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): edit params mgr.
2016-02-22 21:40:18 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): create subdirs.
2016-02-22 21:40:30 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): edit params mgr.
2016-02-22 21:40:40 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 21:40:43 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): start mgr.
2016-02-22 21:40:43 WARNING OGG-01877 Oracle GoldenGate Manager, mgr.prm: Missing explicit accessrule for server collector.
2016-02-22 21:40:43 INFO OGG-00960 Oracle GoldenGate Manager, mgr.prm: Access granted (rule #8).
2016-02-22 21:40:43 INFO OGG-00983 Oracle GoldenGate Manager, mgr.prm: Manager started (port 7810).
2016-02-22 21:40:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 21:41:27 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 21:59:10 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
gres.
rror: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
gres.
rror: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
2016-02-22 22:02:27 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
gres.
rror: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
gres.
rror: SQLSTATE 08001 native database error 0. [Oracle][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number.
gres.
2016-02-22 22:08:37 INFO OGG-03036 Oracle GoldenGate Command Interpreter: Database character set identified as UTF-8. Locale: zh_CN.
2016-02-22 22:08:37 INFO OGG-03037 Oracle GoldenGate Command Interpreter: Session character set identified as UTF-8.
2016-02-22 22:11:19 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
.
2016-02-22 22:34:13 INFO OGG-00960 Oracle GoldenGate Manager, mgr.prm: Access granted (rule #1).
RVER CPU -1 PRI -1 TIMEOUT 300 PARAMS ).
2016-02-22 22:34:13 INFO OGG-00960 Oracle GoldenGate Manager, mgr.prm: Access granted (rule #5).
2016-02-22 22:34:14 INFO OGG-01677 Oracle GoldenGate Collector: Waiting for connection (started dynamically).
2016-02-22 22:34:14 INFO OGG-00963 Oracle GoldenGate Manager, mgr.prm: Command received from SERVER on host [::1]:57690 (REPORT 13987 7819).
2016-02-22 22:34:14 INFO OGG-00960 Oracle GoldenGate Manager, mgr.prm: Access granted (rule #1).
2016-02-22 22:34:14 INFO OGG-00974 Oracle GoldenGate Manager, mgr.prm: Manager started collector process (Port 7819).
2016-02-22 22:34:14 INFO OGG-01228 Oracle GoldenGate Collector: Timeout in 300 seconds.
2016-02-22 22:34:19 INFO OGG-01229 Oracle GoldenGate Collector: Connected to node1:58730.
2016-02-22 22:34:19 INFO OGG-01226 Oracle GoldenGate Collector: Socket buffer size set to 43690 (flush size 27985).
2016-02-22 22:34:19 INFO OGG-01669 Oracle GoldenGate Collector: Opening /u02/pg/ogg/dirdat/ep000000000 (byte -1, current EOF 0).
2016-02-22 22:43:28 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 22:43:45 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): edit params rep_pg.
2016-02-22 22:47:50 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
il /u02/pg/ogg/dirdat/ep.
ail /u02/pg/ogg/dirdat/ep.
2016-02-22 22:49:12 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 22:49:16 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): start rep_pg.
ICAT REP_PG ).
2016-02-22 22:49:16 INFO OGG-00960 Oracle GoldenGate Manager, mgr.prm: Access granted (rule #6).
2016-02-22 22:49:16 INFO OGG-00975 Oracle GoldenGate Manager, mgr.prm: REPLICAT REP_PG starting.
2016-02-22 22:49:17 INFO OGG-00995 Oracle GoldenGate Delivery, rep_pg.prm: REPLICAT REP_PG starting.
2016-02-22 22:49:17 INFO OGG-03059 Oracle GoldenGate Delivery, rep_pg.prm: Operating system character set identified as UTF-8.
2016-02-22 22:49:17 INFO OGG-02695 Oracle GoldenGate Delivery, rep_pg.prm: ANSI SQL parameter syntax is used for parameter parsing.
2016-02-22 22:49:17 INFO OGG-02095 Oracle GoldenGate Delivery, rep_pg.prm: Successfully set environment variable PGCLIENTENCODING=UTF8.
i.
L32UTF8.
efinition file, is ISO-8859-1.
2016-02-22 22:49:17 INFO OGG-02095 Oracle GoldenGate Delivery, rep_pg.prm: Successfully set environment variable PGCLIENTENCODING=UTF8.
i.
L32UTF8.
2016-02-22 22:49:17 INFO OGG-03036 Oracle GoldenGate Delivery, rep_pg.prm: Database character set identified as UTF-8. Locale: zh_CN.
2016-02-22 22:49:17 INFO OGG-03037 Oracle GoldenGate Delivery, rep_pg.prm: Session character set identified as UTF-8.
2016-02-22 22:49:17 INFO OGG-01815 Oracle GoldenGate Delivery, rep_pg.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u02/pg/ogg/dirtmp.
heckpoint table for this Replicat.
2016-02-22 22:49:17 INFO OGG-00996 Oracle GoldenGate Delivery, rep_pg.prm: REPLICAT REP_PG started.
:49:17.216686.
d because trail file /u02/pg/ogg/dirdat/ep000000000 contains table definitions.
2016-02-22 22:49:18 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
2016-02-22 22:49:21 INFO OGG-00987 Oracle GoldenGate Command Interpreter: GGSCI command (postgres): info all.
GGSCI (node2) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_PG 00:00:00 00:00:08
GGSCI (node2) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_PG 00:00:00 00:00:02
GGSCI (node2) 14> exit
-bash-4.2$ clear
mysql 源端插入数据，并提交测试数据同步：
[root@node1 ~]# su - mysql
上一次登录：一 2月 22 22:12:00 EST 2016pts/0 上
[mysql@node1 ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select count(*) from test.t1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql> insert into test.t1 values (1,'test');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test.t1 values (2,'name');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from test.t1;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql>
目标端数据统计：
[root@node2 ~]# su - postgres
上一次登录：一 2月 22 22:54:00 EST 2016pts/1 上
-bash-4.2$ psql
psql (9.2.7)
输入 "help" 来获取帮助信息.
postgres=# \l
资料库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 |
(4 行记录)
postgres=# select count(*) from public.t1;
错误: 关系 "public.t1" 不存在
第1行select count(*) from public.t1;
^
postgres=# \c test
您现在已经连线到数据库 "test",用户 "postgres".
test=# select count(*) from public.t1;
count
-------
0
(1 行记录)
test=# select count(*) from public.t1;
count
-------
2
(1 行记录)
test=# select * from public.t1;
id | name
----+------
1 | test
2 | name
(2 行记录)
test=#
查看个进程事务统计：
cd $GGHOME
ggsci
GGSCI (node1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_MY 00:00:00 01:49:56
EXTRACT RUNNING EXT_PG 00:00:00 00:00:08
EXTRACT STOPPED PM_MY 00:00:00 01:49:51
GGSCI (node1) 2> stats ext_pg
Sending STATS request to EXTRACT EXT_PG ...
Start of Statistics at 2016-02-22 23:02:24.
Output to /u02/pg/ogg/dirdat/ep:
Extracting from test.t1 to test.t1:
*** Total statistics since 2016-02-22 22:56:13 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2016-02-22 22:56:13 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2016-02-22 22:56:13 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2016-02-22 22:56:13 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
-bash-4.2$ pwd
/u02/pg/ogg
-bash-4.2$ ggsci
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_PG 00:00:00 00:00:08
GGSCI (node2) 2> info rep_pg,detail
REPLICAT REP_PG Last Started 2016-02-22 22:49 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Process ID 14390
Log Read Checkpoint File /u02/pg/ogg/dirdat/ep000000000
First Record RBA 1301
Current Log BSN value: (requires database login)
Last Committed Transaction CSN value: (requires database login)
Extract Source Begin End
/u02/pg/ogg/dirdat/ep000000000 * Initialized * First Record
/u02/pg/ogg/dirdat/ep000000000 * Initialized * First Record
Current directory /u02/pg/ogg
Report file /u02/pg/ogg/dirrpt/REP_PG.rpt
Parameter file /u02/pg/ogg/dirprm/rep_pg.prm
Checkpoint file /u02/pg/ogg/dirchk/REP_PG.cpr
Process file /u02/pg/ogg/dirpcs/REP_PG.pcr
Error log /u02/pg/ogg/ggserr.log
GGSCI (node2) 3>
GGSCI (node2) 3>
GGSCI (node2) 3> stats rep_pg
Sending STATS request to REPLICAT REP_PG ...
Start of Statistics at 2016-02-22 23:02:48.
Replicating from test.t1 to public.t1:
*** Total statistics since 2016-02-22 22:56:16 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2016-02-22 22:56:16 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2016-02-22 22:56:16 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2016-02-22 22:56:16 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
[END] 2016/2/23 12:27:25
设置postgresql 开机自启动，RHEL 7.1 64位
[root@node2 ogg]# chkconfig postgresql on
注意：正在将请求转发到“systemctl enable postgresql.service”。
ln -s '/usr/lib/systemd/system/postgresql.service' '/etc/systemd/system/multi-user.target.wants/postgresql.service'
[root@node2 ogg]# systemctl enable postgresql.service
[root@node2 ogg]# chkconfig --list
注意：该输出结果只显示 SysV 服务，并不包含原生 systemd 服务。SysV 配置数据可能被原生 systemd 配置覆盖。
如果您想列出 systemd 服务,请执行 'systemctl list-unit-files'。
欲查看对特定 target 启用的服务请执行
'systemctl list-dependencies [target]'。
netconsole 0:关 1:关 2:关 3:关 4:关 5:关 6:关
network 0:关 1:关 2:开 3:开 4:开 5:开 6:关
rhnsd 0:关 1:关 2:开 3:开 4:开 5:开 6:关
[root@node2 ogg]# systemctl list-unit-files | grep postgresql
postgresql.service enabled
[root@node2 ogg]# systemctl status postgresql.service
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
Active: active (running) since 一 2016-02-22 22:08:23 EST; 2h 23min ago
Main PID: 12250 (postgres)
CGroup: /system.slice/postgresql.service
├─12250 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─12251 postgres: logger process
├─12253 postgres: checkpointer process
├─12254 postgres: writer process
├─12255 postgres: wal writer process
├─12256 postgres: autovacuum launcher process
└─12257 postgres: stats collector process
2月 22 22:08:22 node2 systemd[1]: Starting PostgreSQL database server...
2月 22 22:08:23 node2 systemd[1]: Started PostgreSQL database server.
[root@node2 ogg]#
|
|
京ICP备12024223-8号
不良信息举报电话：13718043309
转播
分享
淘帖
