PostgreSQL Pgpool-II单机多实例的Streaming replication

简单架构图

软件准备：
PostgreSQL 9.1
pgpool-II-3.1.1
pgpoolAdmin-3.1.1

一.首先安装PostgreSQL数据库软件（略）
然后切换到Postgres用户初始化两个数据库实例

$ initdb -D /opt/PostgreSQL/9.1/data

$ initdb -D /opt/PostgreSQL/9.1/standby

然后分别在data和standby目录下的postgresql.conf文件追加如下内容hot_standby = on

wal_level = hot_standby

max_wal_senders = 1

logging_collector = on

log_filename = '%A.log'

log_line_prefix = '%p %t '

log_truncate_on_rotation = on

log_statement = 'all'

standby目录下额外多加一条port=5433

 

然后修改pg_hba.conf文件客户端认证记录，达到实现无密码登录的目的

然后启动主库

$ pg_ctl -D /opt/PostgreSQL/9.1/data start

 

二.安装pgpool-II

注意安装pgpool之前，确保libpq已经安装上了，如果提示

$ tar xfz /some/where/pgpool-II-3.1.1.tar.gz

$ cd pgpool-II-3.1.1

$ ./configure

$ make

$ sudo make install

$ vi install-functions.sh 
内容如下：

---

#! /bin/sh
cd sql/pgpool-recovery
make
make install
psql -f pgpool-recovery.sql template1
psql -f pgpool-recovery.sql postgres
cd ../pgpool-regclass
make
make install
psql -f pgpool-regclass.sql template1
psql -f pgpool-regclass.sql postgres
cd ../pgpool-walrecrunning
make
make install
psql -f pgpool-walrecrunning.sql template1
psql -f pgpool-walrecrunning.sql postgres

---

$ sh install-functions.sh

在配置的过程中会出现一些错误

1.no acceptable C compiler found in $PATH错误

解决办法：yum -y install gcc

2.libpq is not installed or libpq is old

解决办法 ./configure --with-pgsql=/opt/PostgreSQL/9.1/ --with-pgsql-libdir=/opt/PostgreSQL/9.1/lib/ --with-pgsql-includedir=/opt/PostgreSQL/9.1/include/

3.sh install-functions.sh执行的时候报错cp: cannot create regular file `/opt/PostgreSQL/9.1/share/postgresql/contrib//_inst.2412_': Permission denied

解决办法：是相应目录权限不够所致，修改相应目录权限即可！

 

修改pgpool配置文件，因为pgpooladmin是PHP编写的，需要放在apache下执行，所以需要apache用户能修改pgpool.conf和pcp.conf文件

# cp /some/where/pgpool.conf /usr/local/etc

# chown apache /usr/local/etc/pgpool.conf

# cp /some/where/pcp.conf /usr/local/etc

# chown apache /usr/local/etc/pcp.conf

 

$ vi /opt/PostgreSQL/9.1/data/basebackup.sh

内容如下：

---

#/bin/sh -x

PRIMARY_PORT=5432

STANDBY_PORT=5433

PRIMARY=/opt/PostgreSQL/9.1/data

STANDBY=/opt/PostgreSQL/9.1/standby

 

master_db_cluster=$1

recovery_node_host_name=$2

recovery_db_cluster=$3

 

if [ $master_db_cluster = $PRIMARY ];then

  PORT=$PRIMARY_PORT

  SOURCE_CLUSTER=$PRIMARY

  DEST_CLUSTER=$STANDBY

else

  PORT=$STANDBY_PORT

  SOURCE_CLUSTER=$STANDBY

  DEST_CLUSTER=$PRIMARY

fi

 

psql -p $PORT -c "SELECT pg_start_backup('Streaming Replication', true)" postgres

 

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

--exclude postmaster.opts --exclude pg_log \

--exclude recovery.conf --exclude recovery.done \

--exclude pg_xlog \

$SOURCE_CLUSTER/ $DEST_CLUSTER/

 

mkdir $DEST_CLUSTER/pg_xlog

chmod 700 $DEST_CLUSTER/pg_xlog

rm $DEST_CLUSTER/recovery.done

cat > $DEST_CLUSTER/recovery.conf <

standby_mode          = 'on'

primary_conninfo      = 'port=$PORT user=postgres'

trigger_file = '/var/log/pgpool/trigger/trigger_file1'

EOF

 

psql -p $PORT -c "SELECT pg_stop_backup()" postgres

---

$ chmod 755 basebackup.sh

$ vi /opt/PostgreSQL/9.1/data/pgpool_remote_start

内容如下：

---

#! /bin/sh

#

# Start PostgreSQL on the recovery target node

#

if [ $# -ne 2 ]

then

    echo "pgpool_remote_start remote_host remote_datadir"

    exit 1

fi

 

DEST=$1

DESTDIR=$2

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

 

$PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &

---

$ chmod 755 pgpool_remote_start

 

vi /usr/local/etc/failover.sh

内容如下：

---

#!/bin/sh

# Execute command by failover.

# special values:  %d = node id

#                  %h = host name

#                  %p = port number

#                  %D = database cluster path

#                  %m = new master node id

#                  %M = old master node id

#                  %H = new master node host name

#                  %P = old primary node id

#                  %% = '%' character

failed_node_id=$1

failed_host_name=$2

failed_port=$3

failed_db_cluster=$4

new_master_id=$5

old_master_id=$6

new_master_host_name=$7

old_primary_node_id=$8

trigger=/var/log/pgpool/trigger/trigger_file1

 

if [ $failed_node_id = $old_primary_node_id ];then      # master failed

    touch $trigger      # let standby take over

---

$ chmod 755 failover.sh

然后创建一些必须的目录

# mkdir /var/run/pgpool

# chown apache /var/run/pgpool

# mkdir /var/log/pgpool

# chown apache /var/log/pgpool

# mkdir /var/log/pgpool/trigger

# chmod 777 /var/log/pgpool/trigger

 

创建apache用户

$ createuser apache

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n) n

Shall the new role be allowed to create more new roles? (y/n) n

 

三.安装pgpoolAdmin

首先需要把LAPP环境搭建好（linux+apache+PostgreSQL+PHP）

因为pgpoolAdmin的运行需要php支持PostgreSQL

给大家一个简单的方法：

Yum install httpd

 Yum install php php-pdo php-domxml-php4-php5 php-pecl-apc php-gd php-mbstring php-pgsql

两条命令搞定

然后把pgpooladmin解压放到/var/www/html目录下

# cd /var/www/html/pgpoolAdmin

# mkdir templates_c

# chmod 777 templates_c

# chown apache conf/pgmgt.conf.php

# chmod 644 conf/pgmgt.conf.php

然后就可以通过

来访问设置语言及设置

然后就可以用postgres/pgpoolAdmin来登录pgpoolAdmin了

 

四.测试

$ createdb -p 9999 test

$ psql -p 9999 test

test=# create table t1(i int);

CREATE TABLE

test=#

然后往t1表里插入数据

psql -p 9999 test

test=# insert into t1 values(1);

这时候切换到standby中可以查看结果如下

test=# \q

psql -p 5433 test

-- now connected to standby server

test=# select * from t1;

i

---

1

(1 row)

可以看到，数据已经过来了，查看数据库日志文件：

---

12778 2013-06-20 14:32:13 CST LOG:  statement: SELECT pg_current_xlog_location()

12787 2013-06-20 14:32:19 CST LOG:  statement: SELECT pg_is_in_recovery()

12789 2013-06-20 14:32:23 CST LOG:  statement: SELECT pg_current_xlog_location()

12796 2013-06-20 14:32:29 CST LOG:  statement: SELECT pg_is_in_recovery()

12799 2013-06-20 14:32:33 CST LOG:  statement: SELECT pg_current_xlog_location()

12807 2013-06-20 14:32:39 CST LOG:  statement: SELECT pg_is_in_recovery()

12809 2013-06-20 14:32:43 CST LOG:  statement: SELECT pg_current_xlog_location()

12816 2013-06-20 14:32:49 CST LOG:  statement: SELECT pg_is_in_recovery()

12818 2013-06-20 14:32:53 CST LOG:  statement: SELECT pg_current_xlog_location()

12825 2013-06-20 14:32:59 CST LOG:  statement: SELECT pg_is_in_recovery()

12828 2013-06-20 14:33:03 CST LOG:  statement: SELECT pg_current_xlog_location()

---

很正常！