|
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_arch3[root@ppas1 ~]# mkdir /opt/PostgresPlus/9.0/.ssh[root@ppas1 ~]# chown enterprisedb.enterprisedb /opt/PostgresPlus/9.0/.ssh/[root@ppas1 ~]# ssh [email protected] "mkdir .ssh"[root@ppas1 ~]# su -enterprisedbbash-4.1$ ssh-keygen -t rsabash-4.1$ scp ~/.ssh/id_rsa.pub [email protected]:.ssh/id_rsa_ppas1.pub4[root@ppas2 ~]# touch /root/.ssh/authorized_keys[root@ppas2 ~]# cat /root/.ssh/id_rsa_ppas1.pub >> /root/.ssh/authorized_keys5bash-4.1$ ssh [email protected] 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 [email protected] "mkdir .ssh"[root@ppas2 ~]# su -enterprisedbbash-4.1$ ssh-keygen -t rsabash-4.1$ scp ~/.ssh/id_rsa.pub [email protected]:.ssh/id_rsa_ppas2.pub7[root@ppas1 ~]# touch /root/.ssh/authorized_keys[root@ppas1 ~]# cat /root/.ssh/id_rsa_ppas2.pub >> /root/.ssh/authorized_keys8bash-4.1$ ssh [email protected] 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.confwal_level = hot_standbyarchive_mode = onarchive_command = 'cp -i %p /opt/ppas_arch/%f < /dev/null'max_wal_senders = 1hot_standby = onlog_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.conf10[root@ppas2 ~]# /etc/init.d/ppas-9.0 stop11将PPAS1的数据全备到PPAS2[root@ppas1 ~]# edb-psql -U enterprisedb edb -c "SELECTpg_start_backup('label',true);"[root@ppas1 ~]# rsync -a /opt/PostgresPlus/9.0AS/data/[email protected]:/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=5444user=enterprisedb'trigger_file = '/opt/PostgresPlus/9.0AS/data/recovery_trigger'restore_command = 'scp -Cp [email protected]:/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 start13两台服务器的Stream Replication 已经完成＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃下面的留着当备注14[root@ppas1 ~]# mkdir /var/run/pgpool15[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 = 5444backend_weight0 = 1backend_data_directory0 = '/opt/PostgresPlus/9.0AS/data'backend_hostname1 = '192.168.122.12'backend_port1 = 5444backend_weight1 = 1backend_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.conf17启动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 9999tcp 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 TABLEedb=# insert into a values (5),(6);INSERT 0 2edb=# select * from a;id---56(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 enterprisedbedbedb-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 enterprisedbedbedb-psql (9.0.4.10)Type "help" for help.edb=#重新再连接一次，此时pgpool 会将连接转向PPAS2，同时在pgpool 内部会将PPAS1定为故障状态，直到PPAS1的数据库重新可用。21edb=# insert into a values (7);ERROR: cannot execute INSERT in a read-only transaction由于PPAS2还在Stream Replication 的Slave 状态，因此不可做写数据操作edb=# q22[root@ppas2 ~]# touch /opt/PostgresPlus/9.0AS/data/recovery_trigger[root@ppas2 ~]# edb-psql -h 192.168.122.11 -p 9999 -U enterprisedbedbedb-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
(以上内容不代表本站观点。) --------------------------------- |
本網站是"非商業"(non-commercial)。
