• QQ群1:5276420
  • QQ群2:3336901
  • QQ群3:254622631
  • 文档群:150657323
  • 文档翻译平台:按此
  • 邮件列表:按此订阅
微信圈:PostgresChina
http://weibo.com/postgresqlchina

搜索:
用户:

密码:

    注册  晋级


PostgreSQL携手阿里云

【转】:利用pgpool实现PostgreSQL的高可用

原作者:Sure  创作时间:2015-12-29 13:40:48+08  
doudou586 发布于2015-12-30 09:58:48           评论: 0   浏览: 14987   顶: 3611  踩: 3768 
来自sure网友的博文,原文地址:http://my.oschina.net/Suregogo/blog/552765

基于流复制的方式,两节点自动切换:


1、单pgpool:
  1. 环境:
    pgpool:192.168.238.129
    data1:192.168.238.130
    data2:192.168.238.131
    
  2. 图例:
  3. 配置互信:
    ssh-copy-id ha@node1
    ssh-copy-id ha@node2
    
  4. 数据库节点配置,请参照《使用pg_basebackup搭建PostgreSQL流复制环境》。
  5. pgpool配置:
    listen_addresses = '*'
    backend_hostname0 = 'node1'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/home/ha/pgdb/data'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
     
    backend_hostname1 = 'node2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/home/ha/pgdb/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
     
    enable_pool_hba = on
    pool_passwd = 'pool_passwd'
     
    pid_file_name = '/home/ha/pgpool/pgpool.pid'
    logdir = '/home/ha/pgpool/log'
     
    health_check_period = 1
    health_check_user = 'ha'
    health_check_password = 'ha'
     
    failover_command = '/home/ha/pgdb/fail.sh %H'
     
    recovery_user = 'ha'
    recovery_password = 'ha'
    
  6. fail.sh
    # Failover command for streaming replication.
    # This script assumes that DB node 0 is primary, and 1 is standby.
    #
    # If standby goes down, do nothing. If primary goes down, create a
    # trigger file so that standby takes over primary node.
    #
    # Arguments: $1: failed node id. $2: new master hostname. $3: path to
    # trigger file.
     
    new_master=$1
    trigger_command="/home/ha/pgdb/bin/pg_ctl -D /home/ha/pgdb/data promote -m fast"
     
    # Do nothing if standby goes down.
    if [ $failed_node = 1 ]; then
            exit 0;
    fi
     
    # Create the trigger file.
    /usr/bin/ssh -T $new_master $trigger_command
     
    exit 0;
    
  7. 建立pool_passwd
    pg_md5 -m -p -u postgres pool_passwd
    

    PS:在9.1之前一直用的是trigger_file,这里建议用promote -m fast的方式,因为 pg_ctl promote -m fast will skip the checkpoint at end of recovery so that we can achieve very fast failover when the apply delay is low. Write new WAL record XLOG_END_OF_RECOVERY to allow us to switch timeline correctly for downstream log readers. If we skip synchronous end of recovery checkpoint we request a normal spread checkpoint so that the window of re-recovery is low. Simon Riggs and Kyotaro Horiguchi, with input from Fujii Masao. Review by Heikki Linnakangas

  8. 测试
    pgpool节点
    [ha@node0 pgdb]$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
    [1] 22928
    [ha@node0 pgdb]$ psql -h 192.168.238.129 -p 9999 -d postgres -U ha
    Password for user ha: 
    psql (9.4.5)
    Type "help" for help.
     
    postgres=# insert into test values (8);
    INSERT 0 1
    postgres=# select * from test ;
     id 
    ----
      1
      2
      3
      4
      6
      8
    (6 rows)
    

    node1节点:

    [ha@localhost pgdb]$ ps -ef | grep post
    root       2124      1  0 Dec26 ?        00:00:00 /usr/libexec/postfix/master
    postfix    2147   2124  0 Dec26 ?        00:00:00 qmgr -l -t fifo -u
    postfix   13295   2124  0 06:01 ?        00:00:00 pickup -l -t fifo -u
    ha        13395      1  0 06:06 pts/3    00:00:00 /home/ha/pgdb/bin/postgres
    ha        13397  13395  0 06:06 ?        00:00:00 postgres: checkpointer process   
    ha        13398  13395  0 06:06 ?        00:00:00 postgres: writer process   
    ha        13399  13395  0 06:06 ?        00:00:00 postgres: wal writer process   
    ha        13400  13395  0 06:06 ?        00:00:00 postgres: autovacuum launcher process   
    ha        13401  13395  0 06:06 ?        00:00:00 postgres: stats collector process   
    ha        13404  13395  0 06:07 ?        00:00:00 postgres: wal sender process rep 192.168.238.131(59415) streaming 0/21000060
    ha        13418   4087  0 06:07 pts/3    00:00:00 grep post
    [ha@localhost pgdb]$ kill -9 13395
    

    pgpool节点:

    postgres=# insert into test values (8);
    server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
    The connection to the server was lost. Attempting reset: Succeeded.
    postgres=# insert into test values (8);
    INSERT 0 1
    postgres=# insert into test values (8);
    INSERT 0 1
    postgres=# select * from test ;
     id 
    ----
      1
      2
      3
      4
      6
      8
      8
      8
    (8 rows)
    

2.两个pgpool节点

  1. 环境
    pgpool:192.168.238.129 
    pgpool:192.168.238.131
    node1:192.168.238.130
    node2:192.168.238.131
    
  2. 图例
  3. 配置互信,同上。
  4. 数据库节点配置,同上。
  5. pgpool配置: node1
  6. 配置pgpool(主)
    listen_addresses = '*'
    backend_hostname0 = 'node1'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/home/ha/pgdb/data/'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_hostname1 = 'node2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/home/ha/pgdb/data/'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    enable_pool_hba = on
    authentication
    pool_passwd = 'pool_passwd'
    pid_file_name = '/home/ha/pgpool/pgpool.pid'
    logdir = '/tmp/log'
    master_slave_mode = on
    master_slave_sub_mode = 'stream'
    sr_check_period =2
    sr_check_user = 'ha'
    sr_check_password = 'ha'
    health_check_period = 1
    health_check_timeout = 20
    health_check_user = 'ha'
    health_check_password = 'ha'
    failover_command = '/home/ha/pgpool/fail.sh %H'
    recovery_user = 'ha'
    recovery_password = 'ha'
    use_watchdog = on
    wd_hostname = 'node1'    #本端
    delegate_IP = '192.168.238.151'
    #利用ifconfig,查看网卡
    if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0'
    if_down_cmd = 'ifconfig eth1:0 down'
    heartbeat_destination0 = 'node2' #对端
    heartbeat_device0 = 'eth0'
    other_pgpool_hostname0 = 'node2' #对端
    other_pgpool_port0 =9999
    other_wd_port0 = 9000
    
  7. 配置pgpool(从)
    listen_addresses = '*'
    backend_hostname0 = 'node1'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/home/ha/pgdb/data/'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_hostname1 = 'node2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/home/ha/pgdb/data/'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    enable_pool_hba = on
    authentication
    pool_passwd = 'pool_passwd'
    pid_file_name = '/home/ha/pgpool/pgpool.pid'
    logdir = '/tmp/log'
    master_slave_mode = on
    master_slave_sub_mode = 'stream'
    sr_check_period =2
    sr_check_user = 'ha'
    sr_check_password = 'ha'
    health_check_period = 1
    health_check_timeout = 20
    health_check_user = 'ha'
    health_check_password = 'ha'
    failover_command = '/home/ha/pgpool/fail.sh %H'
    recovery_user = 'ha'
    recovery_password = 'ha'
    use_watchdog = on
    wd_hostname = 'node2'    #本端
    delegate_IP = '192.168.238.151'
    #利用ifconfig,查看网卡
    if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0'
    if_down_cmd = 'ifconfig eth1:0 down'
    heartbeat_destination0 = 'node1' #对端
    heartbeat_device0 = 'eth1'
    other_pgpool_hostname0 = 'node1' #对端
    other_pgpool_port0 =9999
    other_wd_port0 = 9000
    
  8. fail.sh
    # Failover command for streaming replication.
    # This script assumes that DB node 0 is primary, and 1 is standby.
    #
    # If standby goes down, do nothing. If primary goes down, create a
    # trigger file so that standby takes over primary node.
    #
    # Arguments: $1: failed node id. $2: new master hostname. $3: path to
    # trigger file.
     
    new_master=$1
    trigger_command="/home/ha/pgdb/bin/pg_ctl -D /home/ha/data start"
     
    # Do nothing if standby goes down.
    if [ $failed_node = 1 ]; then
            exit 0;
    fi
     
    # Create the trigger file.
    /usr/bin/ssh -T $new_master $trigger_command
     
    exit 0;
    
  9. 建立pool_passwd
    pg_md5 -m -p -u postgres pool_passwd
    
  10. 测试
    #数据库、pgpool启动
    [ha@node0 pgdb]$ psql -h 192.168.238.151 -p 9999 -d postgres -U ha
    Password for user ha: 
    psql (9.4.5)
    Type "help" for help.
     
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# 
    --杀掉node1的数据库进程
    postgres=# insert into test values (9);
    server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
    The connection to the server was lost. Attempting reset: Succeeded.
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    --杀掉node1的pgpool进程
    postgres=# insert into test values (9);
    server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
    The connection to the server was lost. Attempting reset: Succeeded.
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=# insert into test values (9);
    INSERT 0 1
    postgres=#
    
    


评论:0   浏览: 14987                   顶: 3611  踩: 3768 

请在登录后发表评论,否则无法保存。


发表评论:

© 2010 PostgreSQL中文社区

Debug: Elapsed Time: 0.0184s / Memory Used: 2.02MB