PostgreSQL9 PPAS9流复制配置

PostgreSQL流复制集群搭建，这个是翻的陈年旧档主机：PPAS1 PPAS21
安装PPAS 9.0
[root@ppas1 ~]# setenforce 0
[root@ppas1 ~]# ppasmeta-9.0.4.14-linux-x64.run--全部安装[root@ppas1 ~]# vim /etc/bashrc
加入:
PATH=$PATH:/opt/PostgresPlus/9.0AS/binexport PATH
--Logout 并重新登陆系统
安装PPAS 9.0
[root@ppas2 ~]# setenforce 0
[root@ppas2 ~]# ppasmeta-9.0.4.14-linux-x64.run--全部安装
[root@ppas2 ~]# vim /etc/bashrc
加入:
PATH=$PATH:/opt/PostgresPlus/9.0AS/binexport PATH
--Logout 并重新登陆系统
2
建立archive 目录
[root@ppas1 ~]# mkdir /opt/ppas_arch
[root@ppas1 ~]# chown enterprisedb.enterprisedb /opt/ppas_arch
建立archive 目录
[root@ppas2 ~]# mkdir /opt/ppas_arch
[root@ppas2 ~]# chown enterprisedb.enterprisedb /opt/ppas_arch
3
[root@ppas1 ~]# mkdir /opt/PostgresPlus/9.0/.ssh
[root@ppas1 ~]# chown enterprisedb.enterprisedb /opt/PostgresPlus/9.0/.ssh/
[root@ppas1 ~]# ssh root@192.168.122.12 "mkdir .ssh"
[root@ppas1 ~]# su -enterprisedbbash-4.1$ ssh-keygen -t rsabash-4.1$ scp ~/.ssh/id_rsa.pub root@192.168.122.12:.ssh/
id_rsa_ppas1.pub
4
[root@ppas2 ~]# touch /root/.ssh/authorized_keys[root@ppas2 ~]# cat /root/.ssh/id_rsa_ppas1.pub >> /root/.ssh/
authorized_keys
5
bash-4.1$ ssh root@192.168.122.12Last login: Mon Jun 27 22:27:09 2011 from 192.168.122.2
[root@ppas1 ~]# logoutConnection to 192.168.122.12 closed.
bash-4.1$ logout[root@ppas1 ~]#
6
[root@ppas2 ~]# mkdir /opt/PostgresPlus/9.0/.ssh[root@ppas2 ~]# chown enterprisedb.enterprisedb /opt/PostgresPlus/9.0/.ssh/
[root@ppas2 ~]# ssh root@192.168.122.11 "mkdir .ssh"
[root@ppas2 ~]# su -enterprisedbbash-4.1$ ssh-keygen -t rsabash-4.1$ scp ~/.ssh/id_rsa.pub root@192.168.122.11:.ssh/
id_rsa_ppas2.pub
7
[root@ppas1 ~]# touch /root/.ssh/authorized_keys[root@ppas1 ~]# cat /root/.ssh/id_rsa_ppas2.pub >> /root/.ssh/
authorized_keys
8
bash-4.1$ ssh root@192.168.122.11Last login: Mon Jun 27 22:31:14 2011 from 192.168.122.1[root@ppas1 ~]# logoutConnection to 192.168.122.11 closed.
bash-4.1$ logout[root@ppas2 ~]#
9
配置PPAS的Stream Replication
[root@ppas1 ~]# vim /opt/PostgresPlus/9.0AS/data/postgresql.conf
wal_level = hot_standby
archive_mode = on
archive_command = "cp -i %p /opt/ppas_arch/%f < /dev/null"
max_wal_senders = 1
hot_standby = on
log_statement = "all" #只用于测试
[root@ppas1 ~]# echo "host replication enterprisedb192.168.122.11/32 trust" >> /opt/PostgresPlus/9.0AS/data/pg_hba.conf[root@ppas1 ~]# echo "host replication enterprisedb192.168.122.12/32 trust" >> /opt/PostgresPlus/9.0AS/data/pg_hba.conf[root@ppas1 ~]# echo "host all all 192.168.122.11/32 trust" >> /opt/PostgresPlus/9.0AS/data/pg_hba.conf
[root@ppas1 ~]# echo "host all all 192.168.122.12/32 trust" >> /opt/PostgresPlus/9.0AS/data/pg_hba.conf
10
[root@ppas2 ~]# /etc/init.d/ppas-9.0 stop
11
将PPAS1的数据全备到PPAS2
[root@ppas1 ~]# edb-psql -U enterprisedb edb -c "SELECTpg_start_backup（"label",true）;"
[root@ppas1 ~]# rsync -a /opt/PostgresPlus/9.0AS/data/
root@192.168.122.12:/opt/PostgresPlus/9.0AS/data/ --excludepostmaster.pid
[root@ppas1 ~]# edb-psql -U enterprisedb edb -c "SELECTpg_stop_backup（）;
12
[root@ppas2 ~]# vim /opt/PostgresPlus/9.0AS/data/recovery.confstandby_mode = "on"
primary_conninfo = "host=192.168.122.11 port=5444
user=enterprisedb"trigger_file = "/opt/PostgresPlus/9.0AS/data/recovery_trigger"restore_command = "scp -Cp root@192.168.122.11:/opt/ppas_arch/%f"%p""
[root@ppas2 ~]# chown enterprisedb.enterprisedb /opt/PostgresPlus/
9.0AS/data/recovery.conf[root@ppas2 ~]# /etc/init.d/ppas-9.0 start
13两台服务器的Stream Replication 已经完成＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃下面的留着当备注
14
[root@ppas1 ~]# mkdir /var/run/pgpool
15
[root@ppas1 ~]# cp /opt/PostgresPlus/9.0AS/etc/pgpool.conf.samplestream /opt/PostgresPlus/9.0AS/etc/pgpool.conf
[root@ppas1 ~]# vim /opt/PostgresPlus/9.0AS/etc/pgpool.conf
修改：
backend_hostname0 = "192.168.122.11"
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = "/opt/PostgresPlus/9.0AS/data"
backend_hostname1 = "192.168.122.12"
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = "/opt/PostgresPlus/9.0AS/data"
16
[root@ppas1 ~]# edb-psql -U enterprisedb edb -c "select md5（"1q2w3e4r"）;"
md5
---------------------------------
5416d7cd6ef195a0f7622a9c56b55e84
（1 row）
[root@ppas1 ~]# echo "enterprisedb:5416d7cd6ef195a0f7622a9c56b55e84" >> /opt/PostgresPlus/9.0AS/etc/pcp.conf
17
启动PGPool
[root@ppas1 ~]# /opt/PostgresPlus/9.0AS/bin/pgpool -f /opt/PostgresPlus/9.0AS/etc/pgpool.conf -F /opt/PostgresPlus/9.0AS/etc/
pcp.conf
[root@ppas1 ~]# netstat -natulp | grep 9999
tcp 0 0 127.0.0.1:9999 0.0.0.0:* LISTEN 2434/pgpool
[root@ppas1 ~]#18
[root@ppas1 ~]# edb-psql -p 9999 -U enterprisedb edbedb-psql （9.0.4.10）
Type "help" for help.
edb=# create table a （id int）;
CREATE TABLE
edb=# insert into a values （5）,（6）;
INSERT 0 2
edb=# select * from a;
id
---
5
6
（2 rows）19
[root@ppas1 ~]# /etc/init.d/ppas-9.0 stopStopping Postgres Plus Advanced Server 9.0:
waiting for server to shut down....... doneserver stopped20
[root@ppas2 ~]# edb-psql -h 192.168.122.11 -p 9999 -U enterprisedbedb
edb-psql: server closed the connection unexpectedlyThis probably means the server terminated abnormallybefore or while processing the request.
以上连接出错是由于pgpool 正好此时的路由指向到PPAS1，但此时PPAS1的数据库已经STOP 。
[root@ppas2 ~]# edb-psql -h 192.168.122.11 -p 9999 -U enterprisedbedb
edb-psql （9.0.4.10）
Type "help" for help.
edb=#
重新再连接一次，此时pgpool 会将连接转向PPAS2，同时在pgpool 内部会将PPAS1定为故障状态，直到PPAS1的数据库重新可用。21
edb=# insert into a values （7）;
ERROR: cannot execute INSERT in a read-only transaction
由于PPAS2还在Stream Replication 的Slave 状态，因此不可做写数据操作
edb=# q
22
[root@ppas2 ~]# touch /opt/PostgresPlus/9.0AS/data/recovery_trigger
[root@ppas2 ~]# edb-psql -h 192.168.122.11 -p 9999 -U enterprisedbedb
edb-psql （9.0.4.10）
Type "help" for help.
edb=# insert into a values （7）;
ERROR: cannot execute INSERT in a read-only transaction
此处操作太快了，因此系统还没有改为Master 状态
edb=# insert into a values （7）;
INSERT 0 1
再过1-2 秒进行操作，写入成功
edb=#------------------------------------华丽丽的分割线------------------------------------CentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址