Pgpool Concise use tutorial
I. system environment
172.16.3.226 (master)
172.16.3.228 (slave)
172.16.3.229 (pgpool)
# yum install-y gcc-c++ zlib-devel readline-devel
# useradd postgres
# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
Passwd: all authentication tokens updated successfully.
#
1. Install postgresql
# vi / etc/profile
Export PATH=/opt/pgsql/10.0/bin:$PATH
Export LD_LIBRARY_PATH=/opt/pgsql/10.0/lib
Export MANPATH=/opt/pgsql/10.0/share/man:$MANPATH
[root@pgpool ~] #
# tar zxvf pgpool-II-3.6.6.tar.gz
# cd pgpool-II-3.6.6
#. / configure-prefix=/opt/pgpool/3.6.6-sysconfdir=/etc/pgpool/
# make
# make install
# cp src/redhat/pgpool.service / lib/systemd/system/
# chmod + x / lib/systemd/system/pgpool.service
# systemctl enable pgpool.service
#
# vi / lib/systemd/system/pgpool.service
[Unit]
Description=Pgpool-II
After=syslog.target network.target
Wants=network.target
[Service]
Type=forking
EnvironmentFile=-/etc/sysconfig/pgpool
ExecStart=/opt/pgpool/3.6.6/bin/pgpool-f / etc/pgpool/pgpool.conf $OPTS
ExecStop=/opt/pgpool/3.6.6/bin/pgpool-f / etc/pgpool/pgpool.conf $STOP_OPTS stop
ExecReload=/opt/pgpool/3.6.6/bin/pgpool-f / etc/pgpool/pgpool.conf reload
[Install]
WantedBy=multi-user.target
#
# chown postgres-R / opt/pgpool/3.6.6
# chown postgres-R / etc/pgpool/
# su-postgres
[postgres@pgpool ~] $cd / opt/pgpool/3.6.6/
[postgres@pgpool 3.6.6] $mkdir log
# su-postgres
Last login: Mon Sep 11 15:08:36 CST 2017 on pts/1
[postgres@pgpool ~] $
[postgres@pgpool ~] $
[postgres@pgpool ~] $cd / etc/pgpool/
[postgres@pgpool pgpool] $mv pgpool.conf.sample-stream pgpool.conf
[postgres@pgpool pgpool] $vi pgpool.conf
Listen_addresses ='*'
Port = 5432
#-Backend Connection Settings-
Backend_hostname0 = '172.16.3.226'
# Host name or IP address to connect to for backend 0
Backend_port0 = 5432
# Port number for backend 0
Backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
Backend_data_directory0 ='/ pgdata10'
# Data directory for backend 0
Backend_flag0 = 'DISALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
Backend_hostname1 = '172.16.3.228'
Backend_port1 = 5432
Backend_weight1 = 1
Backend_data_directory1 ='/ pgdata10'
Backend_flag1 = 'DISALLOW_TO_FAILOVER'
#-
# POOLS
#-
#-Concurrent session and pool size-
Num_init_children = 25
# Number of concurrent sessions allowed
# (change requires restart)
Max_pool = 10
# Number of connection pool caches per connection
# (change requires restart)
#-Life time-
#-
# REPLICATION MODE
#-
Replication_mode = off
#-
# LOAD BALANCING MODE
#-
Load_balance_mode = on
#-
# MASTER/SLAVE MODE
#-
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 = 10
# Streaming replication check period
# Disabled (0) by default
Sr_check_user = 'postgres'
#-
# 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 = 'postgres'
#-query mode--
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 = 'postgres'
#-
# FILE LOCATIONS
#-
Pid_file_name ='/ opt/pgpool/3.6.6/pgpool.pid'
#-What to log-
Log_line_prefix ='% t [% p]: [% lmer1] db=%d,user=%u,app=%a,client=%h'# printf-style string to output at beginning of each log line.
Log_connections = on
# Log connections
Log_hostname = on
# Hostname will be shown in ps status
# and in logs if connections are logged
Log_statement = on
# Log all statements
Log_per_node_statement = on
# systemctl start pgpool.service
# systemctl status pgpool.service
