本文介绍ECS实例上的PostgreSQL如何结合pgpool实现读写分离,您也可以通过RDS PostgreSQL实例及只读实例简化操作步骤。

背景信息

不使用pgpool实现数据库的高可用时,pgpool自身是无状态的,性能损耗很小,同时还支持横向扩展,因此搭配自身具有高可用架构的RDS PostgreSQL实例,可以方便快捷地实现读写分离。

部署环境

如果您已经购买PostgreSQL 10本地SSD盘及只读实例(详情请参见创建RDS PostgreSQL实例创建PostgreSQL只读实例),仅需安装pgpool,然后请跳转到配置pgpool查看后续步骤。

说明 PostgreSQL云盘版的只读实例敬请期待。

测试环境:

  • ECS实例规格为16核CPU、64GB内存、1.8TB SSD云盘。
  • ECS实例系统为CentOS 7.7 x64。

操作步骤如下。

  1. 修改配置文件sysctl.conf,命令如下:
    sudo vi /etc/sysctl.conf  
    
    # add by digoal.zhou                
    fs.aio-max-nr = 1048576                
    fs.file-max = 76724600                
    
    # 可选:kernel.core_pattern = /data01/corefiles/core_%e_%u_%t_%s.%p                         
    # /data01/corefiles 提前建好,权限777,如果是软链接,对应的目录修改为777。       
    
    kernel.sem = 4096 2147483647 2147483646 512000                    
    # 信号量, ipcs -l 或 -u 查看,每16个进程一组,每组信号量需要17个信号量。                
    
    kernel.shmall = 107374182                      
    # 所有共享内存段相加大小限制(建议内存的80%),单位为页。                
    kernel.shmmax = 274877906944                   
    # 最大单个共享内存段大小(建议为内存一半), 大于9.2的版本已大幅降低共享内存的使用,单位为字节。                
    kernel.shmmni = 819200                         
    # 一共能生成多少共享内存段,每个PG数据库集群至少2个共享内存段。
    
    net.core.netdev_max_backlog = 10000                
    net.core.rmem_default = 262144                       
    # The default setting of the socket receive buffer in bytes.                
    net.core.rmem_max = 4194304                          
    # The maximum receive socket buffer size in bytes                
    net.core.wmem_default = 262144                       
    # The default setting (in bytes) of the socket send buffer.                
    net.core.wmem_max = 4194304                          
    # The maximum send socket buffer size in bytes.                
    net.core.somaxconn = 4096                
    net.ipv4.tcp_max_syn_backlog = 4096                
    net.ipv4.tcp_keepalive_intvl = 20                
    net.ipv4.tcp_keepalive_probes = 3                
    net.ipv4.tcp_keepalive_time = 60                
    net.ipv4.tcp_mem = 8388608 12582912 16777216                
    net.ipv4.tcp_fin_timeout = 5                
    net.ipv4.tcp_synack_retries = 2                
    net.ipv4.tcp_syncookies = 1                    
    # 开启SYN Cookies。当出现SYN等待队列溢出时,启用cookie来处理,可防范少量的SYN攻击。                
    net.ipv4.tcp_timestamps = 1                    
    # 减少time_wait。
    net.ipv4.tcp_tw_recycle = 0                    
    # 如果=1则开启TCP连接中TIME-WAIT套接字的快速回收,但是NAT环境可能导致连接失败,建议服务端关闭它。
    net.ipv4.tcp_tw_reuse = 1                      
    # 开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接。
    net.ipv4.tcp_max_tw_buckets = 262144                
    net.ipv4.tcp_rmem = 8192 87380 16777216                
    net.ipv4.tcp_wmem = 8192 65536 16777216                
    
    net.nf_conntrack_max = 1200000                
    net.netfilter.nf_conntrack_max = 1200000                
    
    vm.dirty_background_bytes = 409600000                       
    #  系统脏页到达这个值,系统后台刷脏页调度进程 pdflush(或其他) 自动将(dirty_expire_centisecs/100)秒前的脏页刷到磁盘。                
    #  默认为10%,大内存机器建议调整为直接指定多少字节。                
    
    vm.dirty_expire_centisecs = 3000                             
    #  大于这个值的脏页,将被刷到磁盘。3000表示30秒。                
    vm.dirty_ratio = 95                                          
    #  如果系统进程刷脏页太慢,使得系统脏页超过内存 95 % 时,则用户进程如果有写磁盘的操作(如fsync、fdatasync等调用),则需要主动把系统脏页刷出。                
    #  有效防止用户进程刷脏页,在单机多实例,并且使用CGROUP限制单实例IOPS的情况下非常有效。                  
    
    vm.dirty_writeback_centisecs = 100                            
    #  pdflush(或其他)后台刷脏页进程的唤醒间隔, 100表示1秒。                
    
    vm.swappiness = 0                
    #  不使用交换分区。                
    
    vm.mmap_min_addr = 65536                
    vm.overcommit_memory = 0                     
    #  在分配内存时,允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存,内存较少的测试环境可以使用 1。
    
    vm.overcommit_ratio = 90                     
    #  当overcommit_memory = 2 时,用于参与计算允许指派的内存大小。                
    vm.swappiness = 0                            
    #  关闭交换分区。                
    vm.zone_reclaim_mode = 0                     
    # 禁用 numa, 或者在vmlinux中禁止。            
    net.ipv4.ip_local_port_range = 40000 65535                    
    # 本地自动分配的TCP, UDP端口号范围。                
    fs.nr_open=20480000                
    # 单个进程允许打开的文件句柄上限。                
    
    # 以下参数请注意。            
    #vm.extra_free_kbytes = 4096000   # 小内存机器不要设置这样大, 会无法开机。
    #vm.min_free_kbytes = 6291456    # vm.min_free_kbytes 建议每32G内存分配1G vm.min_free_kbytes。     
    # 如果是小内存机器,以上两个值不建议设置。                
    # vm.nr_hugepages = 66536                    
    #  建议shared buffer设置超过64GB时使用大页,页大小 /proc/meminfo Hugepagesize。                
    #vm.lowmem_reserve_ratio = 1 1 1                
    # 对于内存大于64G时,建议设置,否则建议默认值 256 256 32。    
  2. 修改配置文件limits.conf,命令如下:
    sudo vi /etc/security/limits.conf  
    
    * soft    nofile  1024000                
    * hard    nofile  1024000                
    * soft    nproc   unlimited                
    * hard    nproc   unlimited                
    * soft    core    unlimited                
    * hard    core    unlimited                
    * soft    memlock unlimited                
    * hard    memlock unlimited    
    
    # 注释其他  
    # 同时注释/etc/security/limits.d/20-nproc.conf。  
  3. 关闭透明大页、配置大页、并自启动PostgreSQL。命令如下:
    sudo chmod +x /etc/rc.d/rc.local  
    
    sudo vi /etc/rc.local  
    
    # 关闭透明大页。  
    if test -f /sys/kernel/mm/transparent_hugepage/enabled; then                
       echo never > /sys/kernel/mm/transparent_hugepage/enabled                
    fi    
    
    # 两个实例, 每个实例16G shared buffer。  
    #sysctl -w vm.nr_hugepages=17000  
    
    # 自启动两个实例。  
    su - postgres -c "pg_ctl start -D /data01/pg12_3389/pg_data"  
    su - postgres -c "pg_ctl start -D /data01/pg12_8002/pg_data"  
  4. 创建文件系统。命令如下:
    警告 本步骤操作仅针对新磁盘,请确认已挂载新磁盘(例如新挂载磁盘为vdb而不是vda)后再进行操作,否则可能会因为挂载错误磁盘导致磁盘中数据清空。
    parted -a optimal -s /dev/vdb mklabel gpt mkpart primary 1MiB 100%FREE     
    mkfs.ext4 /dev/vdb1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -b 4096 -T largefile -L vdb1  
    vi /etc/fstab   
    LABEL=vdb1 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0  
    
    mkdir /data01  
    mount -a  
  5. 启动irq balance。命令如下:
    sudo systemctl status irqbalance     
    sudo systemctl enable irqbalance        
    sudo systemctl start irqbalance       
    sudo systemctl status irqbalance    
  6. 安装PostgreSQL 10、pgpool工具软件。命令如下:
    sudo yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm        
    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm       
    sudo yum search all postgresql  
    sudo yum search all pgpool  
    sudo yum install -y postgresql12*    
    sudo yum install -y pgpool-II-12-extensions      
  7. 初始化数据库数据目录。命令如下:
    mkdir /data01/pg12_3389  
    sudo chown postgres:postgres /data01/pg12_3389  
  8. 配置postgres用户环境变量。命令如下:
    su - postgres  
    vi .bash_profile  
    
    # 追加    
    export PS1="$USER@`/bin/hostname -s`-> "      
    export PGPORT=3389  
    export PGDATA=/data01/pg12_$PGPORT/pg_data     
    
    export LANG=en_US.utf8      
    export PGHOME=/usr/pgsql-12      
    export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH      
    export DATE=`date +"%Y%m%d%H%M"`      
    export PATH=$PGHOME/bin:$PATH:.      
    export MANPATH=$PGHOME/share/man:$MANPATH      
    export PGHOST=$PGDATA      
    export PGUSER=postgres      
    export PGDATABASE=db1  
    alias rm='rm -i'      
    alias ll='ls -lh'      
    unalias vi      
  9. 初始化主库。命令如下:
    initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8  
  10. 修改配置文件postgresql.conf。命令如下:
    listen_addresses = '0.0.0.0'  
    port = 3389  
    max_connections = 1500  
    superuser_reserved_connections = 13  
    unix_socket_directories = '., /var/run/postgresql, /tmp'  
    tcp_keepalives_idle = 60  
    tcp_keepalives_interval = 10  
    tcp_keepalives_count = 10  
    shared_buffers = 16GB  
    huge_pages = on  
    work_mem = 8MB  
    maintenance_work_mem = 1GB  
    dynamic_shared_memory_type = posix  
    vacuum_cost_delay = 0  
    bgwriter_delay = 10ms  
    bgwriter_lru_maxpages = 1000  
    bgwriter_lru_multiplier = 10.0  
    bgwriter_flush_after = 512kB  
    effective_io_concurrency = 0  
    max_worker_processes = 128  
    max_parallel_maintenance_workers = 3  
    max_parallel_workers_per_gather = 4  
    parallel_leader_participation = off  
    max_parallel_workers = 8  
    backend_flush_after = 256  
    wal_level = replica  
    synchronous_commit = off  
    full_page_writes = on  
    wal_compression = on  
    wal_buffers = 16MB  
    wal_writer_delay = 10ms  
    wal_writer_flush_after = 1MB  
    checkpoint_timeout = 15min  
    max_wal_size = 64GB  
    min_wal_size = 8GB  
    checkpoint_completion_target = 0.2  
    checkpoint_flush_after = 256kB  
    random_page_cost = 1.1  
    effective_cache_size = 48GB  
    log_destination = 'csvlog'  
    logging_collector = on  
    log_directory = 'log'  
    log_filename = 'postgresql-%a.log'  
    log_truncate_on_rotation = on  
    log_rotation_age = 1d  
    log_rotation_size = 0  
    log_min_duration_statement = 1s  
    log_checkpoints = on  
    log_connections = on  
    log_disconnections = on  
    log_line_prefix = '%m [%p] '  
    log_statement = 'ddl'  
    log_timezone = 'Asia/Shanghai'  
    autovacuum = on  
    log_autovacuum_min_duration = 0  
    autovacuum_vacuum_scale_factor = 0.1  
    autovacuum_analyze_scale_factor = 0.05  
    autovacuum_freeze_max_age = 800000000  
    autovacuum_multixact_freeze_max_age = 900000000  
    autovacuum_vacuum_cost_delay = 0  
    vacuum_freeze_table_age = 750000000  
    vacuum_multixact_freeze_table_age = 750000000  
    datestyle = 'iso, mdy'  
    timezone = 'Asia/Shanghai'  
    lc_messages = 'en_US.utf8'  
    lc_monetary = 'en_US.utf8'  
    lc_numeric = 'en_US.utf8'  
    lc_time = 'en_US.utf8'  
    default_text_search_config = 'pg_catalog.english'  
  11. 修改配置文件pg_hba.conf。命令如下:
    说明 因为pgpool-II和数据库服务器处于同一ECS实例,所以设置为127.0.0.1时需要密码才能登录。
    # "local" is for Unix domain socket connections only  
    local   all             all                                     trust  
    # IPv4 local connections:  
    host    all             all             127.0.0.1/32            md5  
    # IPv6 local connections:  
    host    all             all             ::1/128                 trust  
    # Allow replication connections from localhost, by a user with the  
    # replication privilege.  
    local   replication     all                                     trust  
    host    replication     all             127.0.0.1/32            trust  
    host    replication     all             ::1/128                 trust  
    host db123 digoal 0.0.0.0/0 md5  
  12. 创建流复制用户。示例如下:
    create role rep123 login replication encrypted password 'xxxxxxx';  
    CREATE ROLE  
  13. 创建业务用户。示例如下:
    create role digoal login encrypted password 'xxxxxxx';  
    CREATE ROLE  
    
    create database db123 owner digoal;  
    CREATE DATABASE  
  14. 创建pgpool数据库健康心跳用户,检查只读节点回放延迟(wal replay),只要能登录postgres数据库或指定的库即可,配合pgpool参数使用。示例如下:
    create role nobody login encrypted password 'xxxxxxx';  

创建从库

为简化测试步骤,在同一ECS实例创建备库。

  1. 使用pg_basebackup在线创建从库。命令如下:
    pg_basebackup -D /data01/pg12_8002/pg_data -F p --checkpoint=fast -P -h 127.0.0.1 -p 3389 -U rep123  
  2. 修改从库配置文件postgresql.conf。命令如下:
    cd /data01/pg12_8002/pg_data   
    
    vi postgresql.conf  
    
    # 相比主配置,修改如下:  
    port = 8002  
    primary_conninfo = 'hostaddr=127.0.0.1 port=3389 user=rep123' # 不用设置密码, 因为主设置了trust访问。  
    hot_standby = on  
    wal_receiver_status_interval = 1s  
    wal_receiver_timeout = 10s  
    recovery_target_timeline = 'latest'  
  3. 配置从库standby.signal标记。命令如下:
    cd /data01/pg12_8002/pg_data   
    
    touch standby.signal  
  4. 查看主从同步是否正常。命令如下:
    db1=# select * from pg_stat_replication ;  
    -[ RECORD 1 ]----+------------------------------  
    pid              | 21065  
    usesysid         | 10  
    usename          | postgres  
    application_name | walreceiver  
    client_addr      | 127.0.0.1  
    client_hostname  |   
    client_port      | 47064  
    backend_start    | 2020-02-29 00:26:28.485427+08  
    backend_xmin     |   
    state            | streaming  
    sent_lsn         | 0/52000060  
    write_lsn        | 0/52000060  
    flush_lsn        | 0/52000060  
    replay_lsn       | 0/52000060  
    write_lag        |   
    flush_lag        |   
    replay_lag       |   
    sync_priority    | 0  
    sync_state       | async  
    reply_time       | 2020-02-29 01:32:40.635183+08  

配置pgpool

  1. 查询pgpool安装位置。命令如下:
    rpm -qa|grep pgpool  
    pgpool-II-12-extensions-4.1.1-1.rhel7.x86_64  
    pgpool-II-12-4.1.1-1.rhel7.x86_64  
    
    rpm -ql pgpool-II-12-4.1.1  
  2. 修改配置文件pgpool.conf。命令如下:
    cd /etc/pgpool-II-12/  
    
    cp pgpool.conf.sample-stream pgpool.conf  
    
    vi pgpool.conf  
    
    # ----------------------------  
    # pgPool-II configuration file  
    # ----------------------------  
    #  
    # This file consists of lines of the form:  
    #  
    #   name = value  
    #  
    # Whitespace may be used.  Comments are introduced with "#" anywhere on a line.  
    # 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 "pgpool reload".  Some  
    # parameters, which are marked below, require a server shutdown and restart to  
    # take effect.  
    #  
    
    
    #------------------------------------------------------------------------------  
    # CONNECTIONS  
    #------------------------------------------------------------------------------  
    
    # - pgpool Connection Settings -  
    
    listen_addresses = '0.0.0.0'  
                                       # Host name or IP address to listen on:  
                                       # '*' for all, '' for no TCP/IP connections  
                                       # (change requires restart)  
    port = 8001   
                                       # Port number  
                                       # (change requires restart)  
    socket_dir = '/tmp'  
                                       # Unix domain socket path  
                                       # The Debian package defaults to  
                                       # /var/run/postgresql  
                                       # (change requires restart)  
    reserved_connections = 0  
                                       # Number of reserved connections.  
                                       # Pgpool-II does not accept connections if over  
                                       # num_init_chidlren - reserved_connections.  
    
    
    # - 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)  
    serialize_accept = off  
                                       # whether to serialize accept() call to avoid thundering herd problem  
                                       # (change requires restart)  
    
    # - Backend Connection Settings -  
    
    backend_hostname0 = '127.0.0.1'  
                                       # Host name or IP address to connect to for backend 0  
    backend_port0 = 3389   
                                       # Port number for backend 0  
    backend_weight0 = 1  
                                       # Weight for backend 0 (only in load balancing mode)  
    backend_data_directory0 = '/data01/pg12_3389/pg_data'  
                                       # Data directory for backend 0  
    backend_flag0 = 'ALWAYS_MASTER'  
                                       # Controls various backend behavior  
                                       # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER  
                                       # or ALWAYS_MASTER  
    backend_application_name0 = 'server0'  
                                       # walsender's application_name, used for "show pool_nodes" command  
    backend_hostname1 = '127.0.0.1'  
    backend_port1 = 8002  
    backend_weight1 = 1  
    backend_data_directory1 = '/data01/pg12_8002/pg_data'  
    backend_flag1 = 'DISALLOW_TO_FAILOVER'  
    backend_application_name1 = 'server1'  
    
    # - Authentication -  
    
    enable_pool_hba = on   
                                       # Use pool_hba.conf for client authentication  
    pool_passwd = 'pool_passwd'  
                                       # File name of pool_passwd for md5 authentication.  
                                       # "" disables pool_passwd.  
                                       # (change requires restart)  
    authentication_timeout = 60  
                                       # Delay in seconds to complete client authentication  
                                       # 0 means no timeout.  
    
    allow_clear_text_frontend_auth = off  
                                       # Allow Pgpool-II to use clear text password authentication  
                                       # with clients, when pool_passwd does not  
                                       # contain the user password  
    
    # - 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)  
    
    ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'  
                                       # Allowed SSL ciphers  
                                       # (change requires restart)  
    ssl_prefer_server_ciphers = off  
                                       # Use server's SSL cipher preferences,  
                                       # rather than the client's  
                                       # (change requires restart)  
    ssl_ecdh_curve = 'prime256v1'  
                                       # Name of the curve to use in ECDH key exchange  
    ssl_dh_params_file = ''  
                                       # Name of the file containing Diffie-Hellman parameters used  
                                       # for so-called ephemeral DH family of SSL cipher.  
    
    #------------------------------------------------------------------------------  
    # POOLS  
    #------------------------------------------------------------------------------  
    
    # - Concurrent session and pool size -  
    
    num_init_children = 128   
                                       # Number of concurrent sessions allowed  
                                       # (change requires restart)  
    max_pool = 4  
                                       # Number of connection pool caches per connection  
                                       # (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 = 'syslog'  
                                       # 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 = on  
                                       # 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_client_messages = off  
                                       # Log any client messages  
    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 -  
    
    #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 = '/var/run/pgpool-II-12/pgpool.pid'  
                                       # PID file name  
                                       # Can be specified as relative to the"  
                                       # location of pgpool.conf file or  
                                       # as an absolute path  
                                       # (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_create 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 "minority"  
                                       # 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  
                                       # "minority".  
                                       # 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  
    
    black_query_pattern_list = ''  
                                       # Semicolon separated list of query patterns  
                                       # that should be sent to primary node  
                                       # Regexp are accepted  
                                       # valid for streaming replication mode only.  
    
    database_redirect_preference_list = ''  
                                       # comma separated list of pairs of database and node id.  
                                       # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'  
                                       # valid for streaming replication mode only.  
    
    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 replication mode only.  
    allow_sql_comments = off  
                                       # 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).  
    
    disable_load_balance_on_write = 'transaction'  
                                       # Load balance behavior when write query is issued  
                                       # in an explicit transaction.  
                                       # Note that any query not in an explicit transaction  
                                       # is not affected by the parameter.  
                                       # 'transaction' (the default): if a write query is issued,  
                                       # subsequent read queries will not be load balanced  
                                       # until the transaction ends.  
                                       # 'trans_transaction': if a write query is issued,  
                                       # subsequent read queries in an explicit transaction  
                                       # will not be load balanced until the session ends.  
                                       # 'always': if a write query is issued, read queries will  
                                       # not be load balanced until the session ends.  
    
    statement_level_load_balance = off  
                                       # Enables statement level load balancing  
    
    #------------------------------------------------------------------------------  
    # 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 stream, slony  
                                       # or logical. Default is stream.  
                                       # (change requires restart)  
    
    # - Streaming -  
    
    sr_check_period = 3   
                                       # Streaming replication check period  
                                       # Disabled (0) by default  
    sr_check_user = 'nobody'  
                                       # Streaming replication check user  
                                       # This is necessary even if you disable streaming  
                                       # replication delay check by sr_check_period = 0  
    sr_check_password = ''  
                                       # Password for streaming replication check user  
                                       # Leaving it empty will make Pgpool-II to first look for the  
                                       # Password in pool_passwd file before using the empty password  
    
    sr_check_database = 'postgres'  
                                       # Database name for streaming replication check  
    delay_threshold = 512000  
                                       # 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 = failed node id  
                                       #   %h = failed node host name  
                                       #   %p = failed node port number  
                                       #   %D = failed node database cluster path  
                                       #   %m = new master node id  
                                       #   %H = new master node hostname  
                                       #   %M = old master node id  
                                       #   %P = old primary node id  
                                       #   %r = new master port number  
                                       #   %R = new master database cluster path  
                                       #   %N = old primary node hostname  
                                       #   %S = old primary node port number  
                                       #   %% = '%' character  
    
    #------------------------------------------------------------------------------  
    # HEALTH CHECK GLOBAL PARAMETERS  
    #------------------------------------------------------------------------------  
    
    health_check_period = 5  
                                       # Health check period  
                                       # Disabled (0) by default  
    health_check_timeout = 10  
                                       # Health check timeout  
                                       # 0 means no timeout  
    health_check_user = 'nobody'  
                                       # Health check user  
    health_check_password = ''  
                                       # Password for health check user  
                                       # Leaving it empty will make Pgpool-II to first look for the  
                                       # Password in pool_passwd file before using the empty password  
    
    health_check_database = ''  
                                       # Database name for health check. If '', tries 'postgres' first,   
    health_check_max_retries = 60   
                                       # 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.  
                                       # 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 connection to backend.  
    
    #------------------------------------------------------------------------------  
    # HEALTH CHECK PER NODE PARAMETERS (OPTIONAL)  
    #------------------------------------------------------------------------------  
    #health_check_period0 = 0  
    #health_check_timeout0 = 20  
    #health_check_user0 = 'nobody'  
    #health_check_password0 = ''  
    #health_check_database0 = ''  
    #health_check_max_retries0 = 0  
    #health_check_retry_delay0 = 1  
    #connect_timeout0 = 10000  
    
    #------------------------------------------------------------------------------  
    # FAILOVER AND FAILBACK  
    #------------------------------------------------------------------------------  
    
    failover_command = ''  
                                       # Executes this command at failover  
                                       # Special values:  
                                       #   %d = failed node id  
                                       #   %h = failed node host name  
                                       #   %p = failed node port number  
                                       #   %D = failed node database cluster path  
                                       #   %m = new master node id  
                                       #   %H = new master node hostname  
                                       #   %M = old master node id  
                                       #   %P = old primary node id  
                                       #   %r = new master port number  
                                       #   %R = new master database cluster path  
                                       #   %N = old primary node hostname  
                                       #   %S = old primary node port number  
                                       #   %% = '%' character  
    failback_command = ''  
                                       # Executes this command at failback.  
                                       # Special values:  
                                       #   %d = failed node id  
                                       #   %h = failed node host name  
                                       #   %p = failed node port number  
                                       #   %D = failed node database cluster path  
                                       #   %m = new master node id  
                                       #   %H = new master node hostname  
                                       #   %M = old master node id  
                                       #   %P = old primary node id  
                                       #   %r = new master port number  
                                       #   %R = new master database cluster path  
                                       #   %N = old primary node hostname  
                                       #   %S = old primary node port number  
                                       #   %% = '%' character  
    
    failover_on_backend_error = off  
                                       # Initiates failover when reading/writing to the  
                                       # backend communication socket fails  
                                       # If set to off, pgpool will report an  
                                       # error and disconnect the session.  
    
    detach_false_primary = off  
                                       # Detach false primary if on. Only  
                                       # valid in streaming replication  
                                       # mode and with PostgreSQL 9.6 or  
                                       # after.  
    
    search_primary_node_timeout = 300  
                                       # 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 = 'nobody'  
                                       # Online recovery user  
    recovery_password = ''  
                                       # Online recovery password  
                                       # Leaving it empty will make Pgpool-II to first look for the  
                                       # Password in pool_passwd file before using the empty password  
    
    recovery_1st_stage_command = ''  
                                       # 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  
    
    auto_failback = off  
                                       # Detached backend node reattach automatically  
                                       # if replication_state is 'streaming'.  
    auto_failback_interval = 60  
                                       # Min interval of executing auto_failback in  
                                       # seconds.  
    
    #------------------------------------------------------------------------------  
    # 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_priority = 1  
                                        # priority of this watchdog in leader election  
                                        # (change requires restart)  
    
    wd_authkey = ''  
                                        # Authentication key for watchdog communication  
                                        # (change requires restart)  
    
    wd_ipc_socket_dir = '/tmp'  
                                        # Unix domain socket path for watchdog IPC socket  
                                        # The Debian package defaults to  
                                        # /var/run/postgresql  
                                        # (change requires restart)  
    
    
    # - Virtual IP control Setting -  
    
    delegate_IP = ''  
                                        # delegate IP address  
                                        # If this is empty, virtual IP never bring up.  
                                        # (change requires restart)  
    if_cmd_path = '/sbin'  
                                        # path to the directory where if_up/down_cmd exists  
                                        # If if_up/down_cmd starts with "/", if_cmd_path will be ignored.  
                                        # (change requires restart)  
    if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'  
                                        # startup delegate IP command  
                                        # (change requires restart)  
    if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'  
                                        # shutdown delegate IP command  
                                        # (change requires restart)  
    arping_path = '/usr/sbin'  
                                        # arping command path  
                                        # If arping_cmd starts with "/", if_cmd_path will be ignored.  
                                        # (change requires restart)  
    arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'  
                                        # arping command  
                                        # (change requires restart)  
    
    # - Behaviour 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)  
    wd_de_escalation_command = ''  
                                        # Executes this command when master pgpool resigns from being master.  
                                        # (change requires restart)  
    
    # - Watchdog consensus settings for failover -  
    
    failover_when_quorum_exists = on  
                                        # Only perform backend node failover  
                                        # when the watchdog cluster holds the quorum  
                                        # (change requires restart)  
    
    failover_require_consensus = on  
                                        # Perform failover when majority of Pgpool-II nodes  
                                        # agrees on the backend node status change  
                                        # (change requires restart)  
    
    allow_multiple_failover_requests_from_node = off  
                                        # A Pgpool-II node can cast multiple votes  
                                        # for building the consensus on failover  
                                        # (change requires restart)  
    
    
    enable_consensus_with_half_votes = off  
                                        # apply majority rule for consensus and quorum computation  
                                        # at 50% of votes in a cluster with even number of nodes.  
                                        # when enabled the existence of quorum and consensus  
                                        # on failover is resolved after receiving half of the  
                                        # total votes in the cluster, otherwise both these  
                                        # decisions require at least one more vote than  
                                        # half of the total votes.  
                                        # (change requires restart)  
    
    # - Lifecheck Setting -  
    
    # -- common --  
    
    wd_monitoring_interfaces_list = ''  # Comma separated list of interfaces names to monitor.  
                                        # if any interface from the list is active the watchdog will  
                                        # consider the network is fine  
                                        # 'any' to enable monitoring on all interfaces except loopback  
                                        # '' to disable monitoring  
                                        # (change requires restart)  
    
    wd_lifecheck_method = 'heartbeat'  
                                        # Method of watchdog lifecheck ('heartbeat' or 'query' or 'external')  
                                        # (change requires restart)  
    wd_interval = 10  
                                        # lifecheck interval (sec) > 0  
                                        # (change requires restart)  
    
    # -- heartbeat mode --  
    
    wd_heartbeat_port = 9694  
                                        # Port number for receiving heartbeat signal  
                                        # (change requires restart)  
    wd_heartbeat_keepalive = 2  
                                        # Interval time of sending heartbeat signal (sec)  
                                        # (change requires restart)  
    wd_heartbeat_deadtime = 30  
                                        # Deadtime interval for heartbeat signal (sec)  
                                        # (change requires restart)  
    heartbeat_destination0 = 'host0_ip1'  
                                        # Host name or IP address of destination 0  
                                        # for sending heartbeat signal.  
                                        # (change requires restart)  
    heartbeat_destination_port0 = 9694   
                                        # Port number of destination 0 for sending  
                                        # heartbeat signal. Usually this is the  
                                        # same as wd_heartbeat_port.  
                                        # (change requires restart)  
    heartbeat_device0 = ''  
                                        # 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)  
    
    #heartbeat_destination1 = 'host0_ip2'  
    #heartbeat_destination_port1 = 9694  
    #heartbeat_device1 = ''  
    
    # -- 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 = 'nobody'  
                                        # watchdog user monitoring pgpools in lifecheck  
                                        # (change requires restart)  
    wd_lifecheck_password = ''  
                                        # Password for watchdog user in lifecheck  
                                        # Leaving it empty will make Pgpool-II to first look for the  
                                        # Password in pool_passwd file before using the empty password  
                                        # (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 other pgpool 0  
                                        # (change requires restart)  
    #other_wd_port0 = 9000  
                                        # Port number for other 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 = 8192  
                                       # Number of relation cache  
                                       # entry. If you see frequently:  
                                       # "pool_search_relcache: cache replacement happend"  
                                       # in the pgpool log, you might want to increate this number.  
    
    check_temp_table = catalog  
                                       # Temporary table check method. catalog, trace or none.  
                                       # Default is catalog.  
    
    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.  
    enable_shared_relcache = on  
                                       # If on, relation cache stored in memory cache,  
                                       # the cache is shared among child process.  
                                       # Default is on.  
                                       # (change requires restart)  
    
    relcache_query_target = master     # Target node to send relcache queries. Default is master (primary) node.  
                                       # If load_balance_node is specified, queries will be sent to load balance node.  
    #------------------------------------------------------------------------------  
    # IN MEMORY QUERY MEMORY CACHE  
    #------------------------------------------------------------------------------  
    memory_cache_enabled = off  
                                       # If on, use the memory cache functionality, off by default  
                                       # (change requires restart)  
    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. Mandatory 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 = '/var/log/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  

    涉及修改的重要配置如下:

    listen_addresses = '0.0.0.0'  
    port = 8001  
    socket_dir = '/tmp'  
    reserved_connections = 0  
    
    pcp_listen_addresses = ''  
    pcp_port = 9898  
    pcp_socket_dir = '/tmp'  
    
    # - Backend Connection Settings -  
    
    backend_hostname0 = '127.0.0.1'  
                                       # Host name or IP address to connect to for backend 0  
    backend_port0 = 3389   
                                       # Port number for backend 0  
    backend_weight0 = 1  
                                       # Weight for backend 0 (only in load balancing mode)  
    backend_data_directory0 = '/data01/pg12_3389/pg_data'  
                                       # Data directory for backend 0  
    backend_flag0 = 'ALWAYS_MASTER'  
                                       # Controls various backend behavior  
                                       # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER  
                                       # or ALWAYS_MASTER  
    backend_application_name0 = 'server0'  
                                       # walsender's application_name, used for "show pool_nodes" command  
    backend_hostname1 = '127.0.0.1'  
    backend_port1 = 8002  
    backend_weight1 = 1  
    backend_data_directory1 = '/data01/pg12_8002/pg_data'  
    backend_flag1 = 'DISALLOW_TO_FAILOVER'  
    backend_application_name1 = 'server1'  
    
    # - Authentication -  
    
    enable_pool_hba = on   
    
                                       # Use pool_hba.conf for client authentication  
    pool_passwd = 'pool_passwd'  
                                       # File name of pool_passwd for md5 authentication.  
                                       # "" disables pool_passwd.  
                                       # (change requires restart)  
    allow_clear_text_frontend_auth = off  
                                       # Allow Pgpool-II to use clear text password authentication  
                                       # with clients, when pool_passwd does not  
                                       # contain the user password  
    
    # - Concurrent session and pool size -  
    
    num_init_children = 128   
                                       # Number of concurrent sessions allowed  
                                       # (change requires restart)  
    max_pool = 4  
                                       # Number of connection pool caches per connection  
                                       # (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 = 'syslog'  
                                       # Where to log  
                                       # Valid values are combinations of stderr,  
                                       # and syslog. Default to stderr.  
    log_connections = on  
                                       # Log connections  
    
    log_standby_delay = 'if_over_threshold'  
                                       # Log standby delay  
                                       # Valid values are combinations of always,  
                                       # if_over_threshold, none  
    
    #------------------------------------------------------------------------------  
    # FILE LOCATIONS  
    #------------------------------------------------------------------------------  
    
    pid_file_name = '/var/run/pgpool-II-12/pgpool.pid'  
                                       # PID file name  
                                       # Can be specified as relative to the"  
                                       # location of pgpool.conf file or  
                                       # as an absolute path  
                                       # (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'  
    
    #------------------------------------------------------------------------------  
    # 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  
    
    black_query_pattern_list = ''  
                                       # Semicolon separated list of query patterns  
                                       # that should be sent to primary node  
                                       # Regexp are accepted  
                                       # valid for streaming replication mode only.  
    
    database_redirect_preference_list = ''  
                                       # comma separated list of pairs of database and node id.  
                                       # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'  
                                       # valid for streaming replication mode only.  
    
    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 replication mode only.  
    allow_sql_comments = off  
                                       # 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).  
    
    disable_load_balance_on_write = 'transaction'  
                                       # Load balance behavior when write query is issued  
                                       # in an explicit transaction.  
                                       # Note that any query not in an explicit transaction  
                                       # is not affected by the parameter.  
                                       # 'transaction' (the default): if a write query is issued,  
                                       # subsequent read queries will not be load balanced  
                                       # until the transaction ends.  
                                       # 'trans_transaction': if a write query is issued,  
                                       # subsequent read queries in an explicit transaction  
                                       # will not be load balanced until the session ends.  
                                       # 'always': if a write query is issued, read queries will  
                                       # not be load balanced until the session ends.  
    
    statement_level_load_balance = off  
                                       # Enables statement level load balancing  
    
    #------------------------------------------------------------------------------  
    # 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 stream, slony  
                                       # or logical. Default is stream.  
                                       # (change requires restart)  
    
    # - Streaming -  
    
    sr_check_period = 3   
                                       # Streaming replication check period  
                                       # Disabled (0) by default  
    sr_check_user = 'nobody'  
                                       # Streaming replication check user  
                                       # This is necessary even if you disable streaming  
                                       # replication delay check by sr_check_period = 0  
    sr_check_password = ''  
                                       # Password for streaming replication check user  
                                       # Leaving it empty will make Pgpool-II to first look for the  
                                       # Password in pool_passwd file before using the empty password  
    
    sr_check_database = 'postgres'  
                                       # Database name for streaming replication check  
    delay_threshold = 512000  
                                       # Threshold before not dispatching query to standby node  
                                       # Unit is in bytes  
                                       # Disabled (0) by default  
    
    
    #------------------------------------------------------------------------------  
    # HEALTH CHECK GLOBAL PARAMETERS  
    #------------------------------------------------------------------------------  
    
    health_check_period = 5  
                                       # Health check period  
                                       # Disabled (0) by default  
    health_check_timeout = 10  
                                       # Health check timeout  
                                       # 0 means no timeout  
    health_check_user = 'nobody'  
                                       # Health check user  
    health_check_password = ''  
                                       # Password for health check user  
                                       # Leaving it empty will make Pgpool-II to first look for the  
                                       # Password in pool_passwd file before using the empty password  
    
    health_check_database = ''  
                                       # Database name for health check. If '', tries 'postgres' first,   
    health_check_max_retries = 60   
                                       # 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.  
                                       # 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 connection to backend.  
    
    #------------------------------------------------------------------------------  
    # FAILOVER AND FAILBACK  
    #------------------------------------------------------------------------------  
    
    failover_on_backend_error = off  
                                       # Initiates failover when reading/writing to the  
                                       # backend communication socket fails  
                                       # If set to off, pgpool will report an  
                                       # error and disconnect the session.  
    
    
    relcache_expire = 0  # 建议结构变更后, 设置为1,然后reload然后再改回来. 当然也可以直接设置为一个时间      
                                       # 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 = 8192  
                                       # Number of relation cache  
                                       # entry. If you see frequently:  
                                       # "pool_search_relcache: cache replacement happend"  
                                       # in the pgpool log, you might want to increate this number.  
  3. 配置pool_passwd密码文件。命令如下:
    说明 通过pgpool连接数据库时需要使用密码文件,可以理解为pgpool支持了PostgreSQL的认证协议。
    cd /etc/pgpool-II-12  
    
    #用法  
    #pg_md5 --md5auth --username=username password  
    #生成digoal, nobody密码, 自动写入pool_passwd。
    pg_md5 --md5auth --username=digoal "xxxxxxx"  
    pg_md5 --md5auth --username=nobody "xxxxxxx"  
  4. 自动生成pool_passwd文件。命令如下:
    cd /etc/pgpool-II-12  
    cat pool_passwd   
    digoal:md54dd55116da69d3d03bf2e3a1470564f9  
    nobody:md54240e76623e2511d607f431043a5d1c1 
  5. 配置pgpool_hba文件。命令如下:
    cd /etc/pgpool-II-12  
    cp pool_hba.conf.sample pool_hba.conf  
    vi pool_hba.conf  
    
    host all all 0.0.0.0/0 md5  
  6. 配置pcp管理密码文件。命令如下:
    说明 这里是用来管理pgpool的密码和用户,不是数据库的用户和密码。
    cd /etc/pgpool-II-12  
    
    pg_md5 abc  # 例如密码是abc。  
    900150983cd24fb0d6963f7d28e17f72  
    
    cp pcp.conf.sample pcp.conf  
    
    vi pcp.conf  
    
    USERID:MD5PASSWD  
    manage:900150983cd24fb0d6963f7d28e17f72  #表示使用manage用户来管理pcp。
  7. 启动pgpool。命令如下:
    cd /etc/pgpool-II-12  
    pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf  
    说明 查看pgpool日志的命令如下:
    less /var/log/messages   
  8. 通过pgpool连接数据库。命令如下:
    psql -h 127.0.0.1 -p 8001 -U digoal postgres  
    连接成功

常见问题

  • 如何测试读写分离是否成功?

    连接并查询pg_is_in_recovery(),然后断开重连再查询pg_is_in_recovery(),如果交替返回false和true,说明是交替将请求发送给了主库和从库,即读写分离成功。

  • 使用pgpool会增加延迟吗?

    会小幅增加延迟,本文测试环境下约增加0.12毫秒延迟。

  • pgpool的延迟检测和健康检测机制是什么?
    • pgpool不会将SQL请求发送给回放延迟(wal replay)大于设置值的只读节点,当只读节点延迟小于设置值后,才会再次发送。
      说明 您可以连接主库查询当前数据库wal写入位置的Lsn 1,然后连接只读节点查询当前wal replay位置的Lsn 2,对比Lsn 1和Lsn 2相差的字节。
    • pgpool可以检测后端的健康状态,如果发现不健康,SQL请求不会路由到这个节点。
  • 如何停止、重新加载pgpool配置?
    您可以使用pgpool --help查看帮助命令,例如:
    cd /etc/pgpool-II-12  
    pgpool -f ./pgpool.conf -m fast stop   
  • 如果有多个只读实例, 应该如何配置?

    修改pgpool.conf文件,补充多个只读实例的配置,示例如下:

    backend_hostname1 = 'xx.xx.xxx.xx'  
    backend_port1 = 8002  
    backend_weight1 = 1  
    backend_data_directory1 = '/data01/pg12_8002/pg_data'  
    backend_flag1 = 'DISALLOW_TO_FAILOVER'  
    backend_application_name1 = 'server1'  
    
    backend_hostname2 = 'xx.xx.xx.xx'  
    backend_port1 = 8002  
    backend_weight1 = 1  
    backend_data_directory1 = '/data01/pg12_8002/pg_data'  
    backend_flag1 = 'DISALLOW_TO_FAILOVER'  
    backend_application_name1 = 'server1'  
  • 如何通过pcp查询后端状态?

    示例命令如下:

    # pcp_node_info -U manage -h /tmp -p 9898 -n 1 -v  
    Password: 输入密码  
    
    
    Hostname               : 127.0.0.1  
    Port                   : 8002  
    Status                 : 2  
    Weight                 : 0.500000  
    Status Name            : up  
    Role                   : standby  
    Replication Delay      : 0  
    Replication State      :   
    Replication Sync State :   
    Last Status Change     : 2020-02-29 00:20:29  
  • 监听的端口有哪些?

    监听的端口如下:

    • 主库:3389
    • 备库:8002
    • pgpool:8001
    • pcp:9898