Chinaunix首页 | 论坛 | 博客
  • 博客访问: 775098
  • 博文数量: 56
  • 博客积分: 451
  • 博客等级: 下士
  • 技术积分: 1431
  • 用 户 组: 普通用户
  • 注册时间: 2012-01-20 11:14
文章分类

全部博文(56)

文章存档

2013年(35)

2012年(21)

分类: Mysql/postgreSQL

2013-06-20 15:24:21

简单架构图

软件准备:
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()
很正常!





阅读(9101) | 评论(3) | 转发(0) |
给主人留下些什么吧!~~

chenxliang2016-11-04 11:54:46

2016年10月26、27日,上海浦东,Postgres中国用户大会2016(PG大象会)已经圆满落幕了,大会现场大咖分享视频已经正式上线(含嘉宾演讲PPT),你可直接微信搜索公众号“IT大咖说”(公众号ID:itdakashuo)观看视频。
PG中国社区的发展,需要你的成长与实践,好好努力吧,也可转发朋友圈进行分享,让更多的朋友来了解和学习postgres,为postgreSQL在中国的成长贡献一份力量!谢谢!