pgpool-II+Hot_standby+Streaming replication环境搭建

1、实验环境说明

1.1 操作系统

1. # cat /etc/issue

1) CentOS release 5.5 (Final)

2) Kernel \r on an \m

2. # uname -a

1) Linux pgpool 2.6.18-194.el5 #1 SMP Fri Apr 2 14:58:14 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

1.2 hostname、IP、hosts设置

pgpool eth0:192.168.100.120 --pgpool-II

db1 eth0:192.168.100.121 --The first Postgresql database cluster

db2 eth0:192.168.100.122 --The second Postgresql database cluster

db3 eth0:192.168.100.124 --The third Postgresql database cluster

3. # cat /etc/hosts

1) # Do not remove the following line,or varIoUs programs

2) # that require network functionality will fail.

3) 127.0.0.1 localhost

4) #::1 localhost6.localdomain6 localhost6

5) 192.168.100.120 pgpool

6) 192.168.100.121 db1

7) 192.168.100.122 db2

8) 192.168.100.124 db3

2、安装

2.1 安装pgpool-II

下载地址：http://pgfoundry.org/projects/pgpool/

该实验使用版本：pgpool-II-3.1

安装步骤如下：

1. [root@pgpool ~]# tar –zxvf pgpool-II-3.1.tar.gz

2. [root@pgpool ~]# cd pgpool-II-3.1

3. [root@pgpool pgpool-II-3.1]# make

4. [root@pgpool pgpool-II-3.1]# make install

此时将会以默认路径安装，配置文件在/usr/local/etc/下生成。

2.2 安装Postgresql

该实验使用版本：postgresql-9.1.2

安装路径为默认路径：/usr/local/pgsql

在四台主机上都进行编译安装，方式如下：

1. [root@db1 ~]# useradd postgres

2. [root@db1 ~]# passwd postgres

3. [root@db1 ~]# mkdir /usr/local

4. [root@db1 ~]# su – postgres

5. [postgres@db1 ~]$ cd postgresql-9.1.2

6. [postgres@db1 postgresql-9.1.2]$ ./configure

7. [postgres@db1 postgresql-9.1.2]$ make

8. [postgres@db1 postgresql-9.1.2]$ make install

然后首先只在db1上初始化一个库/usr/local/pgsql/data：

1. [postgres@db1 ~]$ cd /usr/local/pgsql

2. [postgres@db1 ~]$ mkdir data

3. [postgres@db1 ~]$ initdb –D ./data （本文中环境变量设置过程略）

安装pgpool_regclass：

强烈推荐在需要访问的Postgresql中安装pgpool_regclass函数，该函数被pgpool-II内部使用。如果不安装的话，在不同的schema中处理相同的表名会出现问题（临时表不会出现问题）

1. [postgres@db1 ~]$ cd /home/postgres/pgpool-II-3.1/sql/pgpool-regclass

2. [postgres@db1 ~]$ make

3. [postgres@db1 ~]$ make install

4. [postgres@db1 ~]$ psql –f pgpool-regclass.sql template1

安装C和sql函数（用于执行在线恢复）：

1. [postgres@db1 ~]$ cd /home/postgres/pgpool-II-3.1/sql/pgpool-recovery

2. [postgres@db1 ~]$ make

3. [postgres@db1 ~]$ make install

4. [postgres@db1 ~]$ psql –f pgpool-recovery.sql template1

3、ssh免密码认证

为了使管理节点能够通过ssh服务免密码登陆到受控节点进行必要的操作以及各节点在在线恢复过程中的类似需要，我们必须使必要的节点间能够无障碍连接

host: pgpool

1. [root@pgpool ~]# cd .ssh/

2. [root@pgpool .ssh]# ssh-keygen –t rsa

3. [root@pgpool .ssh]# ssh-copy-id –i id_rsa.pub db1

4. [root@pgpool .ssh]# ssh-copy-id –i id_rsa.pub db2

5. [root@pgpool .ssh]# ssh-copy-id –i id_rsa.pub db3

host: db1

1. [postgres@db1 .ssh]$ cd .ssh/

2. [postgres@db1 .ssh]$ ssh-keygen –t rsa

3. [postgres@db1 .ssh]$ ssh-copy-id –i id_rsa.pub db2

4. [postgres@db1 .ssh]$ ssh-copy-id –i id_rsa.pub db3

host: db2

1. [postgres@db2 .ssh]$ cd .ssh/

2. [postgres@db2 .ssh]$ ssh-keygen –t rsa

3. [postgres@db2 .ssh]$ ssh-copy-id –i id_rsa.pub db1

4. [postgres@db2 .ssh]$ ssh-copy-id –i id_rsa.pub db3

host: db3

1. [postgres@db3 .ssh]$ cd .ssh/

2. [postgres@db3 .ssh]$ ssh-keygen –t rsa

3. [postgres@db3 .ssh]$ ssh-copy-id –i id_rsa.pub db1

4. [postgres@db3 .ssh]$ ssh-copy-id –i id_rsa.pub db2

4、配置pgpool-II

4.1 配置pcp.conf

1. [root@pgpool ~]# pg_md5 highgo --生成密码highgo的加密码

1) cf9920dd1f8a7be7e56a85f8a3e018f6

以pcp.conf.sample为模板，复制一个pcp.conf，配置pcp.conf：

1. [root@pgpool ~]# cd /usr/local/etc/

2. [root@pgpool etc]# cp pcp.conf.sample pcp.conf

3. [root@pgpool etc]# vi pcp.conf

1) ……

2) # USERID:MD5PASSWD

3) postgres:cf9920dd1f8a7be7e56a85f8a3e018f6

4.2 配置pgpool.conf

以pgpool.conf.sample为模板，复制一个pgpool.conf，配置pgpool.conf：

1. [root@pgpool etc]# vi pgpool.conf

1) listen_addresses = ‘*’

2) port = 9999

3) pcp_port = 9898

4) backend_hostname0 = ‘db1’

5) backend_port0 = 5432

6) backend_weight0 = 1

7) backend_data_directory0 = ‘/usr/local/pgsql/data’

8) backend_hostname1 = ‘db2’

9) backend_port1 = 5432

10) backend_weight1 = 1

11) backend_data_directory1 = ‘/usr/local/pgsql/data’

12) backend_hostname2 = ‘db3’

13) backend_port2 = 5432

14) backend_weight2 = 1

15) backend_data_directory2 = ‘/usr/local/pgsql/data’

16) enable_pool_hba = on

17) num_init_children = 100

18) max_pool = 4

19) pid_file_name = ‘/var/run/pgpool/pgpool.pid’ --首先创建目录/var/run/pgpool

20) connection_cache = on

21) replication_mode = off

22) load_balance_mode = on

23) master_slave_mode = on

24) master_slave_sub_mode = ‘stream’

25) sr_check_period = 10

26) sr_check_user = ‘postgres’

27) sr_check_password = ‘highgo’

28) delay_threshold = 10000000

29) health_check_period = 0

30) health_check_timeout = 10

31) health_check_user = ‘postgres’

32) health_check_password = ‘highgo’

33) failover_command = ‘/usr/local/pgsql/bin/failover_stream.sh %d %H %P’

34) failback_command = ‘/bin/rm –f /tmp/trigger_file0’

35) recovery_user = ‘postgres’

36) recovery_password = ‘highgo’

37) recovery_1st_stage_command = ‘basebackup.sh’

failover_stream.sh脚本将在控制节点检查到有节点断开的时候执行，如果断开的是主节点那么将会通过ssh连接到第一个子节点上创建一个触发文件，处于流复制模式的该子节点一旦发现该触发文件那么将立即提升为一个新的主节点，以只读模式提升为读写模式；

basebackup.sh脚本是在执行在线恢复中调用，该脚本必须放置在库目录data中。

4.3 配置pool_hba.conf

以pool_hba.conf.sample为模板，复制一个pool_hba.conf，配置pool_hba.conf：

1. [root@pgpool etc]# vi pool_hba.conf

1) ……

2) # IPv4 local connections:

3) host all all 127.0.0.1/32 trust

4) host all all 192.168.100.0/24 trust

4.4 在/usr/local/pgsql/bin/下创建failover_stream.sh脚本，并赋权755，内容如下：

1) # This script assumes that DB node 0 is primary,and 1 is standby.

2) #

3) # If standby goes down,do nothing. If primary goes down,create a

4) # trigger file so that standby takes over primary node.

5) #

6) # Arguments: $1: Failed node id. $2: new master hostname. $3: path to

7) # trigger file.

8) Failed_node=$1

9) new_master=$2

10) old_primary_node_id=$3

11) trigger_file=/tmp/trigger_file0

12) if [ $Failed_node = $old_primary_node_id ]; then

/usr/bin/ssh -T postgres@$new_master touch $trigger_file

13) fi

5、配置Postgresql

5.1 在db1的库目录/usr/local/pgsql/data下创建脚本bashbackup.sh，并赋权755，内容如下：

1) #!/bin/sh

2) #Recovery script for streaming replication.

3) # This script assumes that DB node 0 is primary,and 1 is standby.

4) #

5) master_node_host_name=`hostname`

6) datadir=$1

7) desthost=$2

8) destdir=$3

9) psql -c "SELECT pg_start_backup('Streaming Replication',true)" postgres

10) rsync -C -a -c --delete --exclude postgresql.conf --exclude postmaster.pid \

11) --exclude postmaster.opts --exclude pg_log --exclude pg_xlog \

12) --exclude recovery.done $datadir/ $desthost:$destdir/

13) ssh -T $desthost rm -f $destdir/pg_xlog

14) ssh -T $desthost mkdir $destdir/pg_xlog

15) ssh -T $desthost chmod 700 $destdir/pg_xlog

16) ssh -T $desthost rm -f $destdir/recovery.done

17) cat > /tmp/recovery <<EOF

18) standby_mode = 'on'

19) primary_conninfo = 'host=$master_node_host_name port=5432 user=postgres'

20) trigger_file = '/tmp/trigger_file0'

21) EOF

22) scp /tmp/recovery $desthost:$destdir/recovery.conf

23) psql -c "SELECT pg_stop_backup()" postgres

5.2 在db1的库目录/usr/local/pgsql/data下创建脚本pgpool_remote_start，并赋权755，内容如下：

1) #! /bin/sh

2) if [ $# -ne 2 ]

3) then

echo "pgpool_remote_start remote_host remote_datadir"

exit 1

4) fi

5) DEST=$1

6) DESTDIR=$2

7) PGCTL=/usr/local/pgsql/bin/pg_ctl

8) ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &

5.3 配置postgresql.conf：

1) listen_addresses = ‘*’

2) port = 5432

3) wal_level = hot_standby

4) max_wal_senders = 2

5) hot_standby = on

5.4 配置pg_hba.conf：

1) # IPv4 local connections:

2) host all all 127.0.0.1/32 trust

3) host all all 192.168.100.0/24 trust

4) # IPv6 local connections:

5) host all all ::1/128 trust

6) # Allow replication connections from localhost,by a user with the

7) # replication privilege.

8) #local replication postgres trust

9) #host replication postgres 127.0.0.1/32 trust

10) host replication postgres 192.168.100.0/24 trust

5.5 将db1中的数据库停掉，然后将库目录复制到其它两个子节点db2、db3：

1. [postgres@db1 ~]$ pg_ctl stop

2. [postgres@db1 ~]$ cd /usr/local/pgslq/

3. [postgres@db1 ~]$ scp –r data db2:/usr/local/pgsql/

4. [postgres@db1 ~]$ scp –r data db3:/usr/local/pgsql/

6、创建pgpool-II管理脚本

创建一个用于方便管理pgpool-II的脚本hgsctl，赋予执行权限，放入/usr/local/bin目录中。脚本内容如下：

#!/bin/sh

##########################################

#Copy right by highgo.com

##########################################

cd /usr/local/bin/

MANAGENODE=pgpool

MANAGEPORT=9898#

PcpuSER=postgres

PCPPASWD=highgo

LOGFILE='/tmp/pgpool.log'

NODECOUNT=`./pcp_node_count 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD`

POOLCOUNT=`./pcp_proc_count 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD|wc -w`

POOLNAME=`./pcp_proc_count 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD`

NODENAME=$2

NODEID=''

function map_idname {

if [ "$NODENAME" = '' ];then

echo Please enter NODENAME !

echo "Usage: hgsctl recovery <NODENAME>"

exit 1

i=0

while [ $i -lt $NODECOUNT ];do

FNODENAME=`pcp_node_info 5 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $i |awk '{print $1}'`

FNODESTATUS=`pcp_node_info 5 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $i |awk '{print $3}'`

if [ "$NODENAME" = "$FNODENAME" ];then

NODEID=$i

break

i=$[$i+1]

done

}

function start_mnode {

pgpool -n -d -D > $LOGFILE 2>&1 &

}

function stop_mnode {

pcp_stop_pgpool 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD f

}

function pool_info {

p=1

echo "---------------------------------------------------------------------------------------------------------------"

while [ $p -le $POOLCOUNT ];do

PID=`echo $POOLNAME |awk '{print $'$p'}'`

./pcp_proc_info 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $PID

p=$[$p+1]

done

}

function status_fun {

echo NODE_NAME NODE_ID PORT STATUS

echo -------------------------

i=0

while [ $i -lt $NODECOUNT ];do

pcp_node_info 5 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $i |awk '{print " "$1" '$i' "$2" "$3}'

i=$[$i+1]

done

}

function attach_node {

./pcp_attach_node 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $NODEID

}

function detach_node {

./pcp_detach_node 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $NODEID

}

function online_recovery {

if [ "$FNODESTATUS" != 3 ];then

echo "Node $NODENAME is RUNNING,Please shutdown the node DB first!"

exit 1

if [ "$NODEID" != '' ];then

./pcp_recovery_node 10 $MANAGENODE $MANAGEPORT $PcpuSER $PCPPASWD $NODEID

else

echo NO THE $NODENAME NODE!

}

case "$1" in

start_m)

start_mnode

;;

stop_m)

stop_mnode

;;

status)

status_fun

;;

pools)

pool_info

;;

attach)

map_idname

attach_node

;;

detach)

map_idname

detach_node

;;

recovery)

map_idname

online_recovery

;;

echo $"Usage: $0 {start_m|stop_m|recovery <NODENAME>|attach <NODENAME>|detach <NODENAME>|status|pools}"

exit 1

;;

esac

原文链接：https://www.f2er.com/postgresql/195606.html

pgpool-II+Hot_standby+Streaming replication环境搭建

1、实验环境说明

1.1 操作系统

1.2 hostname、IP、hosts设置

2、安装

2.1 安装pgpool-II

2.2 安装Postgresql

3、ssh免密码认证

4、配置pgpool-II

4.1 配置pcp.conf

4.2 配置pgpool.conf

4.3 配置pool_hba.conf

5、配置Postgresql

5.1 在db1的库目录/usr/local/pgsql/data下创建脚本bashbackup.sh，并赋权755，内容如下：

5.2 在db1的库目录/usr/local/pgsql/data下创建脚本pgpool_remote_start，并赋权755，内容如下：

5.3 配置postgresql.conf：

5.4 配置pg_hba.conf：

5.5 将db1中的数据库停掉，然后将库目录复制到其它两个子节点db2、db3：

6、创建pgpool-II管理脚本

猜你在找的Postgre SQL相关文章