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