postgresql流复制集群搭建，这个是翻的陈年旧档 主机： ppas1 ppas2 1 安装ppas 9.0 [root@ppas1 ~]# setenforce 0[root@ppas1 ~
postgresql流复制集群搭建，这个是翻的陈年旧档
主机：
ppas1 ppas2
1
安装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 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 ~]#