pgpool+postgresql异步流复制+数据库集群及负载的配置方法

pgpool+postgres 集群、负载、主备的配置文档

系统环境：虚拟机（centos 6.5）

db1:postgres（master）+pgpool(master) node1:ip:172.16.144.145

db2:postgres（salve）+pgpool(salve) node2:ip:172.16.144.146 db3:postgres(salve)

node3:ip:172.16.144.147

第一章 初始化配置

首先配置3台主机

3台主机新加用户postgres；

在3台主机上新建文件 vi /home/postgre/postgresql.log 数据库的启动和pgpool的启动全部都是使用postgres用户； 以postgres用户互相SSH信任；

数据库的安装目录为：/usr/local/pgsql/

pgpool的安装目录为：/usr/local/pgpool/ (pgpool安装的时候，./configure –

prefix=/usr/local/pgpool要用命令指定安装目录，在以后的配置中比较方便，个人建议)。 数据库安装之后，要给postgresql和pgpool添加环境变量 具体的添加如下：

用root账户编辑/etc/profile文件，在文件的最后面添加以下代码 export PATH=/usr/local/pgsql/bin:$PATH:/usr/local/pgpool/bin export PGDATA=/usr/local/pgsql/data export PGHOME=/usr/local/pgsql export LANG=zh_CN.UTF-8 export PGPORT=5432

保存文件后，需要使用postgres用户 使用source /etc/profile命令使环境变量生效。

使用root账户，对/usr/local/pgsql的那个目录使用chown -R postgres:postgres pgsql

以node1，配置ntpd服务，确保node1,node2,node3的时间保持一致， 在node2和node3中要加上定时任务去同步node1的ntp服务。

主数据库的数据库需要初始化，备数据库不用数据库初始化。 主数据库的初始化的方法：

在node1的/usr/local/pgsql/目录下面新建一个文件夹叫data, 使用数据库的初始化的命令：

initdb -D /usr/local/pgsql/data --locale=zh_CN.UTF8

数据库的启动命令：pg_ctl -D /usr/local/pgsql -l /home/postgres/postgresql.log start

给数据库的postgres用户添加密码：

在终端中输入psql命令后，进入数据库，然后使用以下命令改密码

alter user postgres with password '123456';

备主机的数据库的目录下面也需要建data文件夹

第二章 数据库的流复制配置

2.1在主库中创建流复制用户

CREATE USER repuser replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';

2.2修改主库pg_hba.conf文件（目录在/usr/local/pgsql/data）

在最后添加如下行。

host replication repuser 172.16.144.0/24 trust host all all 172.16.144.0/24 trust host all postgres 172.16.144.0/24 trust

2.3修改主库postgresql.conf文件

修改如下几个参数 listen_addresses = '*' wal_level = hot_standby max_wal_senders = 2 hot_standby = on

max_wal_senders是Slave库的节点数〃有多少个slave库就设多少。

wal_level是write ahead log参数值,设置流复制务必将此值更新成hot_standby。

使用postgres用户启动主数据库，命令见上一章。

2.4在salve 主机上使用命令： 在备机上使用命令来跟主库进行同步：

pg_basebackup -h 172.16.144.145 -U repuser -F p -P -x -R -D /usr/local/pgsql/data/ -l node1dbbackup160619

在两台备主机上的数据库安装目录下面的data文件夹中都有了数据。 在两台备机的/usr/local/pgsql/data/下面同时有了recovery.conf文件

$ vi recovery.conf --新增以下三行 standby_mode = 'on'

trigger_file = '/usr/local/pgsql/data/pg.trigger'

primary_conninfo = 'host=172.16.144.145 port=5432 user=repuser password=123456 keepalives_idle=60'

recovery_target_timeline = 'latest'

2.5启动两台备机的postgres数据库

测试：

在主数据库上通过：

psql进入数据库命令

使用默认的数据库CREATE TABLE rep_test (test varchar(40)); 插入数据：

INSERT INTO rep_test VALUES ('data one');

INSERT INTO rep_test VALUES ('some more words'); INSERT INTO rep_test VALUES ('lalala'); INSERT INTO rep_test VALUES ('hello there'); INSERT INTO rep_test VALUES ('blahblah'); 在备机上通过psql命令进入数据库： 查询rep_test表，看数据是否插入成功；

也可以在主机的数据库中使用命令查看流复制的连接备机情况了：

select pid,state client_addr,sync_priority,sync_state from pg_stat_replication;

查看备库落后主库多少字节的wal日志命令：select

pg_xlog_location_diff(pg_current_xlog_location(),replay_location)

from pg_stat_replication;

pgpool的配置 3.1pgpool的安装

tar zxvf pgpool-II-3.4.6.tar.gz cd pgpool-II-3.4.6 mkdir -p /opt/pgpool

./configure --prefix=/us/local/pgpool -with-pgsql=path -with-pgsql=/usr/local/pgsql/ make make install

3.2pgpool相关函数的安装

pgpool 函数不是必需安装，但建议安装 pgpool_regclass, pgpool_recovery 函数。

cd /pgpool-II-3.4.6/src/sql make make install

安装完成后可以在/opt/PostgreSQL/93/share/postgresql/extension/看到pgpool相关文件。

ls -l /usl/local/pgsqlshare/postgresql/extension/ total 36

pgpool_recovery--1.0.sql pgpool_recovery.control pgpool-recovery.sql

pgpool_regclass--1.0.sql pgpool_regclass.control pgpool-regclass.sql plpgsql--1.0.sql plpgsql.control

plpgsql--unpackaged--1.0.sql

登陆需要安装的库中，和安装插件一样执行以下两条命令(一定要执行)。 create extension pgpool_regclass; create extension pgpool_recovery;

psql -U postgres -h 172.16.144.145 -p 5432 -f /usr/local/pgsql/share/extension/pgpool-recovery.sql template1

执行以上命令时，会在主数据库上的template1表空间下面创建几个recovery需要的函数。 3.3配置pcp.conf cd /usr/local/pgpool/etc/

cp pcp.conf.sample pcp.conf

pgpool 提供 pcp 接口〃可以查看、管理 pgpool 的状态〃并且可以远程操作 pgpool 。pcp.conf是用来对 pcp 相关命令认证的文件〃格式为 USERID:MD5PASSWD。

执行pg_md5 -u postgres -p 命令后，让输入密码，然后会出现md5加密后

的字符串

password:123456

XXXXXXXXXXXXXXXXXXXXXXX

然后在pcp.conf中添加postgres:XXXXXXXXXXXXXX 配置pgpool.conf

# ---------------------------- # pgPool-II configuration file # ---------------------------- #

# This file consists of lines of the form: #

# name = value #

# Whitespace may be used. Comments are introduced with \# The complete list of parameter names and allowed values can be found in the # pgPool-II documentation. #

# This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use \ Some # parameters, which are marked below, require a server shutdown and restart to # take effect. #

#------------------------------------------------------------------------------ # CONNECTIONS

#------------------------------------------------------------------------------

# - pgpool Connection Settings -

listen_addresses = '*'

# Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) port = 9999

# Port number

# (change requires restart) socket_dir = '/tmp'

# Unix domain socket path # The Debian package defaults to # /var/run/postgresql # (change requires restart)

# - pgpool Communication Manager Connection Settings -

pcp_listen_addresses = '*'

# Host name or IP address for pcp process to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) pcp_port = 9898

# Port number for pcp # (change requires restart) pcp_socket_dir = '/tmp'

# Unix domain socket path for pcp # The Debian package defaults to # /var/run/postgresql # (change requires restart)

listen_backlog_multiplier = 2

# Set the backlog parameter of listen(2) to

# num_init_children * listen_backlog_multiplier.

# (change requires restart)

# - Backend Connection Settings -

# Host name or IP address to connect to for backend 0 # Port number for backend 0

# Weight for backend 0 (only in load balancing mode) # Data directory for backend 0 # Controls various backend behavior

# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER

# - Authentication -

enable_pool_hba = on

# Use pool_hba.conf for client authentication pool_passwd = 'pool_passwd'

# File name of pool_passwd for md5 authentication. # \ # (change requires restart) authentication_timeout = 60

# Delay in seconds to complete client authentication # 0 means no timeout.

# - SSL Connections - ssl = off

# Enable SSL support # (change requires restart) #ssl_key = './server.key'

# Path to the SSL private key file # (change requires restart) #ssl_cert = './server.cert'

# Path to the SSL public certificate file # (change requires restart) #ssl_ca_cert = ''

# Path to a single PEM format file # containing CA root certificate(s) # (change requires restart) #ssl_ca_cert_dir = ''

# Directory containing CA root certificate(s) # (change requires restart)

#------------------------------------------------------------------------------ # POOLS

#------------------------------------------------------------------------------

# - Pool size -

num_init_children = 32

# Number of pools # (change requires restart) max_pool = 4

# Number of connections per pool # (change requires restart)

# - Life time -

child_life_time = 300

# Pool exits after being idle for this many seconds child_max_connections = 0

# Pool exits after receiving that many connections # 0 means no exit connection_life_time = 0

# Connection to backend closes after being idle for this many seconds # 0 means no close client_idle_limit = 0

# Client is disconnected after being idle for that many seconds # (even inside an explicit transactions!) # 0 means no disconnection

#------------------------------------------------------------------------------ # LOGS

#------------------------------------------------------------------------------

# - Where to log -

log_destination = 'stderr'

# Where to log

# Valid values are combinations of stderr, # and syslog. Default to stderr.

# - What to log -

log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.

log_connections = off

# Log connections log_hostname = off

# Hostname will be shown in ps status # and in logs if connections are logged log_statement = off

# Log all statements log_per_node_statement = off

# Log all statements

# with node and backend informations

log_standby_delay = 'if_over_threshold'

# Log standby delay

# Valid values are combinations of always, # if_over_threshold, none

# - Syslog specific -

syslog_facility = 'LOCAL0'

# Syslog local facility. Default to LOCAL0 syslog_ident = 'pgpool'

# Syslog program identification string # Default to 'pgpool'

# - Debug -

debug_level = 0

# Debug message verbosity level

# 0 means no message, 1 or more mean verbose

#log_error_verbosity = default # terse, default, or verbose messages

#client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error

#log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic

#------------------------------------------------------------------------------ # FILE LOCATIONS

#------------------------------------------------------------------------------

pid_file_name = '/usr/local/pgpool/pgpool.pid'

# PID file name

# (change requires restart) logdir = '/tmp'

# Directory of pgPool status file # (change requires restart)

#------------------------------------------------------------------------------ # CONNECTION POOLING

#------------------------------------------------------------------------------

connection_cache = on

# Activate connection pools # (change requires restart)

# Semicolon separated list of queries # to be issued at the end of a session # The default is for 8.3 and later reset_query_list = 'ABORT; DISCARD ALL'

# The following one is for 8.2 and before #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'

#------------------------------------------------------------------------------ # REPLICATION MODE

#------------------------------------------------------------------------------

replication_mode = off

# Activate replication mode # (change requires restart) replicate_select = off

# Replicate SELECT statements # when in replication mode

# replicate_select is higher priority than # load_balance_mode.

insert_lock = off

# Automatically locks a dummy row or a table # with INSERT statements to keep SERIAL data # consistency

# Without SERIAL, no lock will be issued lobj_lock_table = ''

# When rewriting lo_creat command in # replication mode, specify table name to # lock

# - Degenerate handling -

replication_stop_on_mismatch = off

# On disagreement with the packet kind # sent from backend, degenerate the node # which is most likely \ # If off, just force to exit this session

failover_if_affected_tuples_mismatch = off

# On disagreement with the number of affected # tuples in UPDATE/DELETE queries, then # degenerate the node which is most likely # \

# If off, just abort the transaction to # keep the consistency

#------------------------------------------------------------------------------ # LOAD BALANCING MODE

#------------------------------------------------------------------------------

load_balance_mode = on

# Activate load balancing mode # (change requires restart) ignore_leading_white_space = on

# Ignore leading white spaces of each query white_function_list = ''

# Comma separated list of function names # that don't write to database # Regexp are accepted black_function_list = 'currval,lastval,nextval,setval'

# Comma separated list of function names # that write to database # Regexp are accepted

database_redirect_preference_list = ''

app_name_redirect_preference_list = ''

# comma separated list of pairs of app name and node id. # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby' # valid for streaming replicaton mode only.

# comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' # valid for streaming replicaton mode only.

allow_sql_comments = off

#------------------------------------------------------------------------------ # MASTER/SLAVE MODE

#------------------------------------------------------------------------------

# if on, ignore SQL comments when judging if load balance or # query cache is possible.

# If off, SQL comments effectively prevent the judgment # (pre 3.4 behavior).

master_slave_mode = on

# Activate master/slave mode # (change requires restart) master_slave_sub_mode = 'stream'

# Master/slave sub mode

# Valid values are combinations slony or # stream. Default is slony. # (change requires restart)

# - Streaming -

sr_check_period = 5

# Streaming replication check period # Disabled (0) by default sr_check_user = 'repuser'

# Streaming replication check user

# This is neccessary even if you disable streaming # replication delay check by sr_check_period = 0 sr_check_password = '123456'

# Password for streaming replication check user delay_threshold = 10000000

# Threshold before not dispatching query to standby node # Unit is in bytes # Disabled (0) by default

# - Special commands -

follow_master_command = ''

# Executes this command after master failover # Special values: # %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new master node id

# %H = hostname of the new master node # %M = old master node id # %P = old primary node id

# %r = new master port number # %R = new master database cluster path

# %% = '%' character

#------------------------------------------------------------------------------ # HEALTH CHECK

#------------------------------------------------------------------------------

health_check_period = 0

# Health check period # Disabled (0) by default

health_check_timeout = 20

# Health check timeout # 0 means no timeout health_check_user = 'nobody'

# Health check user health_check_password = ''

# Password for health check user health_check_max_retries = 0

# Maximum number of times to retry a failed health check before giving up. health_check_retry_delay = 1

# Amount of time to wait (in seconds) between retries. connect_timeout = 10000

# Timeout value in milliseconds before giving up to connect to backend.

#------------------------------------------------------------------------------ # FAILOVER AND FAILBACK

#------------------------------------------------------------------------------ #failover_command = ''

failover_command = '/home/postgres/scripts/failover_stream.sh %d %H /usr/local/pgsql/data/pg.trigger' # Executes this command at failover # Special values: # %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new master node id

# %H = hostname of the new master node # %M = old master node id # %P = old primary node id

# %r = new master port number # %R = new master database cluster path

# Default is 10000 ms (10 second). Flaky network user may want to increase # the value. 0 means no timeout.

# Note that this value is not only used for health check, # but also for ordinary conection to backend.

# %% = '%' character failback_command = ''

# Executes this command at failback. # Special values: # %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new master node id

# %H = hostname of the new master node # %M = old master node id # %P = old primary node id

# %r = new master port number # %R = new master database cluster path

# %% = '%' character

fail_over_on_backend_error = on

# Initiates failover when reading/writing to the # backend communication socket fails # If set to off, pgpool will report an # error and disconnect the session.

search_primary_node_timeout = 10

# Timeout in seconds to search for the # primary node when a failover occurs. # 0 means no timeout, keep searching # for a primary node forever.

#------------------------------------------------------------------------------ # ONLINE RECOVERY

#------------------------------------------------------------------------------

recovery_user = 'postgres'

# Online recovery user recovery_password = '123456'

# Online recovery password recovery_1st_stage_command = 'basebackup.sh'

# Executes a command in first stage recovery_2nd_stage_command = ''

# Executes a command in second stage recovery_timeout = 90

# Timeout in seconds to wait for the # recovering node's postmaster to start up # 0 means no wait client_idle_limit_in_recovery = 0

# Client is disconnected after being idle # for that many seconds in the second stage # of online recovery # 0 means no disconnection # -1 means immediate disconnection

#------------------------------------------------------------------------------ # WATCHDOG

#------------------------------------------------------------------------------

# - Enabling -

use_watchdog = off

# Activates watchdog # (change requires restart)

# -Connection to up stream servers -

trusted_servers = ''

# trusted server list which are used # to confirm network connection # (hostA,hostB,hostC,...) # (change requires restart) ping_path = '/bin'

# ping command path # (change requires restart)

# - Watchdog communication Settings -

wd_hostname = ''

# Host name or IP address of this watchdog # (change requires restart) wd_port = 9000

# port number for watchdog service # (change requires restart) wd_authkey = ''

# Authentication key for watchdog communication # (change requires restart)

# - Virtual IP control Setting -

delegate_IP = ''

# delegate IP address

# If this is empty, virtual IP never bring up. # (change requires restart) ifconfig_path = '/sbin'

# ifconfig command path # (change requires restart) if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'

# startup delegate IP command # (change requires restart) if_down_cmd = 'ifconfig eth0:0 down'

# shutdown delegate IP command # (change requires restart)

arping_path = '/usr/sbin' # arping command path # (change requires restart)

arping_cmd = 'arping -U $_IP_$ -w 1'

# arping command # (change requires restart)

# - Behaivor on escalation Setting -

clear_memqcache_on_escalation = on

# Clear all the query cache on shared memory # when standby pgpool escalate to active pgpool # (= virtual IP holder).

# This should be off if client connects to pgpool

# not using virtual IP. # (change requires restart) wd_escalation_command = ''

# Executes this command at escalation on new active pgpool. # (change requires restart)

# - Lifecheck Setting -

# -- common --

wd_lifecheck_method = 'heartbeat'

wd_interval = 10

# -- heartbeat mode --

wd_heartbeat_port = 9694

wd_heartbeat_keepalive = 2

wd_heartbeat_deadtime = 30

heartbeat_destination0 = 'host0_ip1'

heartbeat_destination_port0 = 9694

heartbeat_device0 = ''

#heartbeat_destination1 = 'host0_ip2' #heartbeat_destination_port1 = 9694 #heartbeat_device1 = ''

# -- query mode --

# Method of watchdog lifecheck ('heartbeat' or 'query') # (change requires restart) # lifecheck interval (sec) > 0 # (change requires restart) # Port number for receiving heartbeat signal # (change requires restart) # Interval time of sending heartbeat signal (sec) # (change requires restart) # Deadtime interval for heartbeat signal (sec) # (change requires restart) # Host name or IP address of destination 0 # for sending heartbeat signal. # (change requires restart) # Port number of destination 0 for sending # heartbeat signal. Usually this is the # same as wd_heartbeat_port. # (change requires restart) # Name of NIC device (such like 'eth0') # used for sending/receiving heartbeat # signal to/from destination 0.

# This works only when this is not empty # and pgpool has root privilege. # (change requires restart)

wd_life_point = 3

# lifecheck retry times # (change requires restart) wd_lifecheck_query = 'SELECT 1'

# lifecheck query to pgpool from watchdog # (change requires restart) wd_lifecheck_dbname = 'template1'

# Database name connected for lifecheck # (change requires restart) wd_lifecheck_user = 'nobody'

# watchdog user monitoring pgpools in lifecheck # (change requires restart) wd_lifecheck_password = ''

# Password for watchdog user in lifecheck # (change requires restart)

# - Other pgpool Connection Settings -

#other_pgpool_hostname0 = 'host0'

# Host name or IP address to connect to for other pgpool 0 # (change requires restart) #other_pgpool_port0 = 5432

# Port number for othet pgpool 0 # (change requires restart) #other_wd_port0 = 9000

# Port number for othet watchdog 0 # (change requires restart) #other_pgpool_hostname1 = 'host1' #other_pgpool_port1 = 5432 #other_wd_port1 = 9000

#------------------------------------------------------------------------------ # OTHERS

#------------------------------------------------------------------------------ relcache_expire = 0

# Life time of relation cache in seconds. # 0 means no cache expiration(the default). # The relation cache is used for cache the # query result against PostgreSQL system # catalog to obtain various information # including table structures or if it's a # temporary table or not. The cache is # maintained in a pgpool child local memory # and being kept as long as it survives. # If someone modify the table by using # ALTER TABLE or some such, the relcache is # not consistent anymore.

# For this purpose, cache_expiration

# controls the life time of the cache. relcache_size = 256

# Number of relation cache # entry. If you see frequently:

check_temp_table = on

# If on, enable temporary table check in SELECT statements. # This initiates queries against system catalog of primary/master

check_unlogged_table = on

# If on, enable unlogged table check in SELECT statements. # This initiates queries against system catalog of primary/master # thus increases load of master.

# If you are absolutely sure that your system never uses unlogged tables # and you want to save access to primary/master, you could turn this off. # Default is on.

#------------------------------------------------------------------------------ # IN MEMORY QUERY MEMORY CACHE

#------------------------------------------------------------------------------ memory_cache_enabled = off

# If on, use the memory cache functionality, off by default

# thus increases load of master.

# If you are absolutely sure that your system never uses temporary tables # and you want to save access to primary/master, you could turn this off. # Default is on.

# \ # in the pgpool log, you might want to increate this number.

memqcache_method = 'shmem'

# Cache storage method. either 'shmem'(shared memory) or # 'memcached'. 'shmem' by default

# (change requires restart) memqcache_memcached_host = 'localhost'

# Memcached host name or IP address. Mandatory if # memqcache_method = 'memcached'. # Defaults to localhost.

# (change requires restart) memqcache_memcached_port = 11211

# Memcached port number. Mondatory if memqcache_method = 'memcached'. # Defaults to 11211.

# (change requires restart) memqcache_total_size = 67108864

# Total memory size in bytes for storing memory cache. # Mandatory if memqcache_method = 'shmem'. # Defaults to 64MB.

# (change requires restart) memqcache_max_num_cache = 1000000

# Total number of cache entries. Mandatory # if memqcache_method = 'shmem'.

# Each cache entry consumes 48 bytes on shared memory.

# Defaults to 1,000,000(45.8MB).

# (change requires restart) memqcache_expire = 0

# Memory cache entry life time specified in seconds. # 0 means infinite life time. 0 by default.

# (change requires restart) memqcache_auto_cache_invalidation = on

# If on, invalidation of query cache is triggered by corresponding # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered # by memqcache_expire. on by default.

# (change requires restart) memqcache_maxcache = 409600

# Maximum SELECT result size in bytes.

# Must be smaller than memqcache_cache_block_size. Defaults to 400KB.

# (change requires restart) memqcache_cache_block_size = 1048576

# Cache block size in bytes. Mandatory if memqcache_method = 'shmem'. # Defaults to 1MB.

# (change requires restart) memqcache_oiddir = '/usr/local/pgpool/oiddir'

# Temporary work directory to record table oids

# (change requires restart) white_memqcache_table_list = ''

# Comma separated list of table names to memcache # that don't write to database # Regexp are accepted black_memqcache_table_list = ''

# Comma separated list of table names not to memcache # that don't write to database # Regexp are accepted backend_hostname0 = '172.16.144.145' backend_port0 = 5432 backend_weight0 = 1

backend_data_directory0 = '/usr/local/pgsql/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '172.16.144.146' backend_port1 = 5432 backend_weight1 = 2

backend_data_directory1 = '/usr/local/pgsql/data' backend_flag1= 'ALLOW_TO_FAILOVER' backend_hostname2 = '172.16.144.147' backend_port2 = 5432 backend_weight2 = 2

backend_data_directory2 = '/usr/local/pgsql/data' backend_flag2= 'ALLOW_TO_FAILOVER'

在主库的主机上/home/postgres/scripts下新建文件failover_stream.sh

#! /bin/sh

# Failover command for streaming replication.

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

# If standby goes down, do nothing. If primary goes down, create a # trigger file so that standby takes over primary node. #

# Arguments: $1: failed node id. $2: new master hostname. $3: path to # trigger file.

failed_node=$1 new_master=$2 trigger_file=$3

# Do nothing if standby goes down. if [ $failed_node = 1 ]; then fi

# Create the trigger file.

/usr/bin/ssh -T $new_master /bin/touch $trigger_file exit 0;

exit 0;

在主库的$PGDATA目录下面新建basebackup.sh

#! /bin/sh

# Recovery script for streaming replication. # This script assumes followings: #

# 1) Executed on the primary node by pgpool_recovery function. # 2) Executed as \# 3) \

# 4) can connect to \# password.

# 5) Password less access using ssh from the primary node to the # target node is possible. # 6) Arguments for the scripts are: # # # #

datadir=$1 desthost=$2 destdir=$3 port=$4

/usr/local/pgsql/bin/psql -p $port -c \postgres

rsync -C -a -c --delete -e ssh --exclude postgresql.conf --exclude postmaster.pid \\ --exclude postmaster.opts --exclude pg_log --exclude pg_xlog \\ --exclude recovery.conf $datadir/ $desthost:$destdir/

ssh -T $desthost mv $destdir/recovery.done $destdir/recovery.conf

/usr/local/pgsql/bin/psql -c \

$1: database cluster path on the primary node $2: hostname or IP address to be recovered $3: database cluster path on target node

$4: port number of of the primary database cluster

# 7) psql, rsync, ssh are available in the command search path.

在$PGDATA目录下面新建pgpool_remote_start文件（赋予可执行权限）

#! /bin/sh

if [ $# -ne 2 ] then

echo \ exit 1 fi DEST=$1 DESTDIR=$2

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

ssh -T $DEST $PGCTL -w -D $DESTDIR -l /home/postgres/postgresql.log start

配置pool_hba.conf cd /usr/local/pgpool/etc/

cat /usr/local/pgsql/data/pg_hba.conf > pool_hba.conf

启动pgpool命令：

pgpool -n >/var/log/pgpool/pgpool.log 2>&1 & 停止pgpool命令： pgpool -m fast stop

登录pgpool命令：

psql -p 9999 -U postgres -d postgres

查看pgpool所有的节点信息： show pool_nodes;

status:

1:节点启动，还没有被连接 2：节点启动，已经被连接 3：节点down掉了

模拟节点down掉的情况：主库和备库一正常运行，备库二直接停止服务，当再重新启动备库二时，在主库上查看pool_nodes节点发现：

当用show pool_nodes;命令去查看的时候，发现有个节点的status为3的时候，说明这个节点已经down了，重新恢复的命令如下：（需要恢复的节点，数据库服务必须是停止状态） 节点恢复的命令：pcp_recovery_node -d 3600 172.16.144.145 9898 postgres 123456 2 172.16.144.145 pgpool(安装的主机IP) 9898：pgpool的端口号 postgres:用户名 123456：密码 2：节点ID

命令执行后成功发现：

此时status状态值由3变为2，代表这个节点正常了。

把一个新节点加入到pgpool中的命令：

pcp_attach_node -d 5 172.16.144.200 5432 postgres 123456 1 172.16.144.200要加入节点的数据库IP地址 5432：要加入节点的数据库端口号 postgres:要加入节点的数据库的用户名 123456：要加入节点的数据库密码

1：要加入的节点ID

第四章pgpoolAdmin的安装方法 linux下PHP环境的配置

使用yum命令安装php，命令如下：

yum -y install php

yum -y install php-gd php-xml php-mbstring php-ldap php-pear php-xmlrpc yum -y install php-pdo php-domxml-php4-php5 php-pecl-apc php-gd php-mbstring php-pgsql

1.使用linux自带的apache+php

2.使用tomcat运行PHP（建议使用这种，这种比较好，使用第一种方法会出现文件的写权限问题）

由于其需要安装在应用服务器中，我们下载并安装apache-tomcat-7.0.16.zip，解压缩到目录/home/postgres/website/。看看是否可以启动Tomcat（当然这需要jdk，此处不再说明如何安装）:

chmod -R 777 /home/postgres/apache-tomcat-7.0.16/bin

sh /home/postgres/apache-tomcat-7.0.16/bin/startup.sh sh /home/postgres/apache-tomcat-7.0.16/bin/shutdown.sh #关闭tomcat由于pgpoolAdmin需要使用php的库，你从lib_for_php.tar.gz下载，解压缩到/home/postgres/apache-tomcat-7.0.16/lib 目录，并配置/home/postgres/apache-tomcat-7.0.16/conf/web.xml（参考Running PHP applications in Tomcat 6）,你可以在最后的标记之前增加：

.... php.java.servlet.ContextLoaderListener PhpJavaServlet php.java.servlet.PhpJavaServlet PhpCGIServlet php.java.servlet.fastcgi.FastCGIServlet prefer_system_php_exec On php_include_java Off PhpJavaServlet *.phpjavabridge PhpCGIServlet *.php

pgpoolAdmin-3.1.1.tar.gz，并解压缩到/home/postgres/website/apache-tomcat-7.0.16/webapps中，然后重新启动Tomcat，通过浏览器打开如下地址：http://localhost:8080/pgpooladmin/install/index.php，是不是如下图正常：

若是上图的模样则表明成功，然后一路点next选择相应的配置，若对某些文件有权限问题，请做相应修改，例如（具体可参考Installing pgpool Administration Tool）：

cd /usr/local/etc chown apache pgpool.conf chmod 644 pgpool.conf chown apache pcp.conf chmod 644 pcp.conf su chmod 755 /usr/local/bin/pgpool chmod 755 /usr/local/bin/pcp_*

好，如果你见到下面这个界面，则表示配置成功：

接 下来进入pgpoolAdmin的管理页面http://localhost:8080/pgpooladmin/status.php：默认用户名 postgres密码pgpoolAdmin（后面我们将演示如何修改pgpool以允许

pgpoolAdmin以不同的用户名密码登录）：