pgpool 简明使用教程

一、系统环境

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.安装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]: [%l-1] 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