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 已经完成 ############################ 下面的留着当备注 [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 stopped 20 [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=# ----------------- blog.csdn.net/beiigang |