搭建pgpool+流复制

1、检查数据量大小和空间剩余量
2、检查防火墙
service iptables status
iptables: Firewall is not running.
cat /etc/selinux/config 
SELINUX=disabled
Setenforce 0
setenforce: SELinux is disabled
3、修改主端IP地址
vi /etc/sysconfig/network-scripts/ifcfg-eth0
serivce network restart

4、安装gcc
yum install gcc  -y
5、修改host文件
vi /etc/hosts

6、创建pgpool安装路径
mkdir -p /home/postgres/pgpool/3.6.4
chown -R postgres:postgres  /home/postgres/pgpool/3.6.4
chmod +s /bin/ping
chmod +s /sbin/ifup
chmod +s /sbin/ip
chmod +s /sbin/ifconfig
chmod +s /sbin/arping
7、修改postgres用户的环境变量
vi /home/postgres/.bash_profile 
export PATH
export HG_HOME=/home/postgres/database/3.0/
export PGUSER=postgres
export PGPORT=5432  
export PGDATA=$HG_HOME/data
export PGPOOL=/home/postgres/pgpool/3.6.4
export PATH=$HG_HOME/bin:$PGPOOL/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$HG_HOME/lib:$PGPOOL/lib:$LD_LIBRARY_PATH

使环境变量生效（需在两节点进行）
source /home/postgres/.bash_profile

8、修改部分系统参数
cp /etc/sysctl.conf /etc/sysctl.conf.bak

vi /etc/sysctl.conf

kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 0
fs.aio-max-nr = 1048576
net.ipv4.tcp_timestamps = 0
使参数生效

sysctl -p
9、root和postgres用户建立SSH互信(主/备 节点) 
两个节点均执行以下命令
root用户互信
ssh-keygen -t rsa -P ''
ssh-copy-id -i ~/.ssh/id_rsa.pub root@postgres1
ssh-copy-id -i ~/.ssh/id_rsa.pub root@postgres2
postgres用户互信
su - postgres
ssh-keygen -t rsa -P ''
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@postgres1
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@postgres2
root用户与postgres用户互信
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@postgres1
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@postgres2
ssh-copy-id -i ~/.ssh/id_rsa.pub root@postgres1
ssh-copy-id -i ~/.ssh/id_rsa.pub root@postgres2

10、主节点配置pg_hba.conf文件
cd $PGDATA
vi pg_hba.conf
host    all             all           192.168.10.0/24         trust
host    replication     all           192.168.10.0/24         trust

11、主节点配置postgresql.conf文件
cp $PGDATA/postgresql.conf $PGDATA/postgresql.conf.bak
由于客户是已经正常使用的业务环境，需要特别注意一条条的修改。
vi $PGDATA/postgresql.conf

shared_buffers = 8192MB		原来大小为128M，而实际内存是32G
listen_addresses = '*'
port = 5432
log_destination = ' csvlog '
logging_collector = on
log_directory = 'hgdb_log'
log_filename = 'hgdb-%Y-%m-%d.log'
log_duration = on
log_error_verbosity = default
log_line_prefix = '%m'
log_statement = 'all'
wal_level = hot_standby
max_wal_senders = 5
hot_standby = on

archive_mode archive_command参数保留原配置，归档路径：/home/postgres/backup/archivedir/
修改成功后，重启数据库
pg_ctl  restart -m f 
12、传送data目录到备端
在postgres2执行如下命令
pg_basebackup -h 192.168.10.111 -p 5432 -U postgres -F p -P -x -R -D /home/postgres/database/3.0/data  -l postgresbak20171012
修改recovery.conf文件
vi $PGDATA/recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres host=192.168.10.111 port=5432 sslmode=prefer sslcompression=1'
recovery_target_timeline = 'latest'
12、安装配置pgpool-II 3.6.4(主备)
1）主备节点安装pgpool-II 3.6.4
安装包位置：/opt/pgpool-II-3.6.4.tar.gz
tar -zxvf pgpool-II-3.6.4.tar.gz
chown -R postgres:postgres pgpool-II-3.6.4
su - postgres
cd pgpool-II-3.6.4
./configure --prefix=/home/postgres/pgpool/3.6.4
make
make install
pgpool-II提供了pgpool_recovery用于在线恢复，需要单独编译安装，并在数据库中创建。主备节点均执行编译安装。
su - postgres
cd /opt/pgpool-II-3.6.4/src/sql		解压路径
[postgres@postgres1 sql]$ ls
insert_lock.sql  Makefile  pgpool_adm  pgpool-recovery  pgpool-regclass
[postgres@postgres1 sql]$ cd pgpool-recovery/
[postgres@postgres1 pgpool-recovery]$ make && make install
在数据库中执行pgpool-recovery.sql，只需在主节点(postgres1)执行即可，通过流复制会在standby节点上同步，通过Pgpool-II访问的每个数据库都执行pgpool-recovery.sql，但是，在执行pgpool-recovery.sql后创建的数据库不需要执行此操作，因为template1数据库将被克隆以创建新的数据库。
[postgres@postgres1 pgpool-recovery]$ psql -f pgpool-recovery.sql template1
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[postgres@postgres1 pgpool-recovery]$ psql -f pgpool-recovery.sql postgres
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[postgres@postgres1 pgpool-recovery]$psql -f pgpool-recovery.sql gov_monitor
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[postgres@postgres1 pgpool-recovery]$psql -f pgpool-recovery.sql gov_monitor_clear
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

在备端查看pgpool-recovery.sql创建的FUNCTION
[postgres@postgres2 ~]$ psql -d template1
psql (9.5.6)
Type "help" for help.

template1=# \df
                                 List of functions
 Schema |        Name         | Result data type |  Argument data types   |  Type  
--------+---------------------+------------------+------------------------+--------
 public | pgpool_pgctl        | boolean          | text, text             | normal
 public | pgpool_recovery     | boolean          | text, text, text, text | normal
 public | pgpool_remote_start | boolean          | text, text             | normal
 public | pgpool_switch_xlog  | text             | text                   | normal
(4 rows)
使用Pgpool-II 3.3或更高版本，主备端调整postgresql.conf文件，添加pgpool.pg_ctl参数。
[postgres@postgres1 data]$ vi $PGDATA/postgresql.conf
pgpool.pg_ctl = '/home/postgres/database/3.0/bin/pg_ctl'
服务器进程发出信号
[postgres@postgres1 data]$ pg_ctl reload
2）主备节点配置pgpool.conf
[postgres@postgres1 ~]$ cd $PGPOOL/etc
[postgres@postgres1 etc]$ cp pgpool.conf.sample pgpool.conf
具体配置信息见文档 pgpool.conf
  
3）主备节点配置pcp.conf
生成postgres用户密码的md5密钥
[postgres@postgres1 ~]$ pgpool /home/postgres/pgpool/3.6.4 /bin/pg_md5 postgres123
fc8db03f0d7adbd8f1cd859d7cbf6094
创建pcp.conf的配置文件，加入以下信息：
[postgres@postgres1 ~]$ cd $PGPOOL/etc
[postgres@postgres1 etc]$ cp pcp.conf.sample pcp.conf
[postgres@postgres1 etc]$ vi pcp.conf
postgres:fc8db03f0d7adbd8f1cd859d7cbf6094
4）主备节点配置pool_hba.conf
创建pool_hba.conf的配置文件，加入以下信息：
[postgres@postgres1 ~]$ cd $PGPOOL/etc
[postgres@postgres1 etc]$ cp pool_hba.conf.sample pool_hba.conf
[postgres@postgres1 etc]$ vi pool_hba.conf
host    all         all         192.168.10.0/24       trust

7）启动和关闭pgpool
1. 启动命令
[postgres@postgres1 ~]$ pgpool             ##直接启动，不输出调试信息
pgpool启动在后台，开启debug模式，debug日志输出到/tmp/pgpool.log。
[postgres@postgres1 ~]$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
2. 关闭命令
pgpool -m fast stop
[postgres@postgres1 ~]$ pgpool stop [-m {s[mart]|f[ast]|i[mmediate]}]
stop有三个模式："smart", "fast", "immediate"
smart       等待客户端断开后关闭
fast        不等待客户端断开，立刻关闭
immediate   等同于fast模式

注意pgpool的启动和关闭顺序，启动时先主后备，关闭时先备后主。

启动pgpool后，会生成一个VIP地址，对外提供访问
[postgres@postgres1 data]$ ifconfig
eth0      Link encap:Ethernet  HWaddr 00:50:56:81:58:F9  
          inet addr:192.168.90.220  Bcast:192.168.90.255  Mask:255.255.255.0
          inet6 addr: fe80::250:56ff:fe81:58f9/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:118658 errors:0 dropped:0 overruns:0 frame:0
          TX packets:34730 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:11838536 (11.2 MiB)  TX bytes:41313228 (39.3 MiB)

eth0:0    Link encap:Ethernet  HWaddr 00:50:56:81:58:F9  
          inet addr:192.168.90.223  Bcast:192.168.90.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

启动pgpool后，使用端口5432和VIP地址连接数据库
[postgres@postgres1 data]$ psql -p 5432
psql (3.0.2)
输入 "help" 来获取帮助信息.

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | postgres1  | 5432 | up     | 0.500000  | primary | 28         | true              | 0
 1       | postgres2  | 5433 | up     | 0.500000  | standby | 43         | false             | 0
(2 rows)
13、完成后状态检查
查看pgpool和数据库进程。
ps -ef |grep postgres
ps -ef |grep pgpool
检查流复制是否正常：
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

检查备库落厚多少字节
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication;

pgpool.conf

listen_addresses = '*'
port = 5866
socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off


pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'


backend_hostname0 = 'postgres1'        									#主机名1
backend_port0 = 5432                									#端口号
backend_weight0 = 1
backend_data_directory0 = '/home/postgres/database/3.0/data'						#数据库data目录
backend_flag0 = 'ALLOW_TO_FAILOVER'


backend_hostname1 = 'postgres2'											#主机名2
backend_port1 = 5433													#端口号
backend_weight1 = 1														
backend_data_directory1 = '/home/postgres/database/3.0/data'						#数据库data目录
backend_flag1 = 'ALLOW_TO_FAILOVER'


enable_pool_hba = on                  
pool_passwd = 'pool_passwd'
authentication_timeout = 60


ssl = off


num_init_children = 200
max_pool = 4


child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0


log_destination = 'stderr'


log_line_prefix = '%t: pid %p: '
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = off
log_standby_delay = 'none'


syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'


pid_file_name = '/home/postgres/pgpool/3.6.4/pgpool.pid'						#pid文件存放路径。建议为pgpool安装路径。
logdir = '/var/log/pgpool'


connection_cache = on               
reset_query_list = 'ABORT; DISCARD ALL'


replication_mode = off             
replicate_select = off
insert_lock = on
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off


load_balance_mode = on              
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'nextval,setval,nextval,setval'
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = off


master_slave_mode = on             
master_slave_sub_mode = 'stream'      


sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'postgres123'
sr_check_database = 'postgres'
delay_threshold = 10000000


follow_master_command = ''


health_check_period = 20
health_check_timeout = 10
health_check_user = 'postgres'
health_check_password = 'postgres123'
health_check_database = 'postgres'
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000


#failover_command = '/home/cluster/pgpool/3.6.4/etc/failover.sh  %H'
#failback_command = ''
#fail_over_on_backend_error = on
#search_primary_node_timeout = 300


#recovery_user = 'postgres'
#recovery_password = 'postgres'
#recovery_1st_stage_command = 'basebackup.sh'      
#recovery_2nd_stage_command = ''
#recovery_timeout = 90
#client_idle_limit_in_recovery = 0


use_watchdog = on          


trusted_servers = 'postgres1,postgres2'                        				#主机名1和2
ping_path = '/bin'


wd_hostname = 'postgres1'            							#本机地址
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'


delegate_IP = '192.168.10.109'                                          #vip
if_cmd_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'
arping_path = '/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'


clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''


wd_monitoring_interfaces_list = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10


wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'postgres2'       								#远程地址
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'eth0'


wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''


other_pgpool_hostname0 = 'postgres2'        								#远程地址
other_pgpool_port0 = 5866                								#远程pgpool端口
other_wd_port0 = 9000


relcache_expire = 0
relcache_size = 256
check_temp_table = on
check_unlogged_table = on


memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''