0%

PostgreSQL集群及读写分离方案

Posted on Disqus:

为了方便，只搭建一主一从。首先准备两台服务器，信息如下：

IP 角色 端口
192.168.0.31 master 5432
pgpool-II 9999
192.168.0.32 slave 5432

一、基础环境配置

  1. host设置

    1
    2
    3
    # 修改vi /etc/hosts, 增加以下内容
    192.168.0.31 master
    192.168.0.32 slave

  2. 安装

    PostgreSQL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    # 添加源
    rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
    # 下载
    yum install postgresql10-server postgresql10
    # 安装
    /usr/pgsql-10/bin/postgresql-10-setup initdb

    # 启动
    systemctl enable postgresql-10.service
    systemctl start postgresql-10.service

    # 验证
    su - postgres -c "psql"
    # 出现以下信息则成功
    psql (10.0)
    Type "help" for help.
    postgres=#

    # 创建密码
    postgres=# \password postgres

    # 查看路径（/var/lib/pgsql/10/data）
    postgres=# show data_directory;

    # 编辑文件 (vi /var/lib/pgsql/10/data/pg_hba.conf)
    # host all all 127.0.0.1/32 ident 修改为允许所有网络登录，并使用md5方式进行认证：
    # host all all 0.0.0.0/0 md5

    # 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf)
    listen_addresses = '*' # 表示开放外网访问

    # 打开防火墙
    sudo firewall-cmd --add-service=postgresql --permanent
    sudo firewall-cmd --reload
    # 重启
    systemctl restart postgresql-10.service

    pgpool-II

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 安装
    yum install http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm
    yum install pgpool-II-pg10
    # 可选
    yum install pgpool-II-pg10-debuginfo
    yum install pgpool-II-pg10-devel
    yum install pgpool-II-pg10-extensions

    #启动
    systemctl enable pgpool.service
    systemctl start pgpool.service

二、流复制

  1. Master

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 创建用于复制的用户
    su - postgres
    psql
    postgres=# CREATE ROLE pgrepuser REPLICATION LOGIN PASSWORD 'pgreppass';
    # 编辑文件 (vi /var/lib/pgsql/10/data/pg_hba.conf)
    # host replication pgrepuser 0.0.0.0/0 md5

    # 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf),修改配置(根据实际情况填写)
    wal_level = hot_standby
    archive_mode = on
    max_wal_sender = 4
    wal_keep_segments = 10

    # 重启数据库
    systemctl restart postgresql-10.service

  2. Slave

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    # 停止服务
    systemctl stop postgresql-10.service

    su - postgres
    # 使用 pg_basebackup 生成备库
    #1. 清空 $PGDATA 目录
    rm -rf /var/lib/pgsql/10/data

    # pg_basebackup 命令生成备库
    pg_basebackup -D /var/lib/pgsql/10/data -Fp -Xs -v -P -h master -U pgrepuser

    # 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf)
    hot_standby = on

    # 新建文件 (vi /var/lib/pgsql/10/data/recovery.conf)
    standby_mode = 'on'
    primary_conninfo = 'host=master port=5432 user=pgrepuser password=pgreppass'
    trigger_file = 'failover.now'
    recovery_target_timeline = 'latest'

    # 重启数据库
    systemctl restart postgresql-10.service

    #验证：在master新增数据slave节点可以看到数据。

三、读写分离

  1. pgpool配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    cd /etc/pgpool-II
    cp -pv pgpool.conf.sample-stream pgpool.conf

    # 修改 vi pgpool.conf
    listen_addresses = '*'# 外网访问
    # 0为主库
    backend_hostname0 = 'master
    backend_port0 = 5432
    backend_weight0 = 0 # 分配比例
    backend_data_directory0 = '/var/lib/pgsql/10/data'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_hostname1 = 'slave'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/var/lib/pgsql/10/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    #hba认证
    enable_pool_hba = on
    # 执行log
    log_statement = on
    log_per_node_statement = on
    # 流复制
    sr_check_user = 'replicator' # 流复制账号
    sr_check_password = '123456'  # 流复制密码
    # 函数默认分发到从节点，过滤如下
    black_function_list = 'currval,lastval,nextval,setval,funcw_.*'

    # 修改 vi pool_hba.conf
    host    all         all         0.0.0.0/0          md5

    # 修改 vi pcp.conf
    pcp:e10adc3949ba59abbe56e057f20f883e # 密码为123456

    # 生成pool_passwd
    pg_md5  123456

    # 与 postgresql 用户密码一致
    pg_md5 -m -u postges postgres

    # 启动pgpool
    # systemctl restart pgpool.service 
    pgpool -n -d > /etc/pgpool-II/pgpool.log 2>&1 &

    # 连接
    su - postgres
    psql postgres -h master -p 9999 -U postgres

    # 节点信息
    postgres=# show pool_nodes;
    node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
    ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
    0       | master   | 5432 | up     | 0.000000  | primary | 28         | false             | 0
    1       | slave    | 5432 | up     | 1.000000  | standby | 6          | true              | 0

    # 查看日志
    tail -f /etc/pgpool-II/pgpool.log 

    # 下面是测试情况：
    # select 1;
    2017-10-30 06:38:25: pid 3637: LOG:  DB node id: 1 backend pid: 3658 statement: select * from test where id = 1;

    # update test set name = 'test' where id = 2;
    DB node id: 0 backend pid: 8032 statement: update test set name = 'test' where id = 2;
    #/*REPLICATION*/select 1; # 强制master节点执行
    DB node id: 0 backend pid: 8032 statement: 	/*REPLICATION*/select 1;
    # DB node id,0表示主节点执行，1表示从节点

四、错误解决

  1. 端口占用

    1
    2
    3
    4
    5
    6
    2017-10-30 01:50:21: pid 3790: FATAL:  failed to bind a socket: "/tmp/.s.PGSQL.9998"
    2017-10-30 01:50:21: pid 3790: DETAIL:  bind socket failed with error: "Address already in use"

    # 非正常结束导致的，删除以下目录即可
    rm -f /tmp/.s.PGSQL.9999
    rm -f /tmp/.s.PGSQL.9898

    ​

五、后续优化

  1. 宕机主从切换

    1
    2
    # 修改 vi pgpool.conf
    follow_master_command = '/etc/pgpool-II/failover_stream.sh'

    新建切换脚本

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    #! /bin/sh 
    # Failover command for streaming replication. 
    # Arguments: $1: new master hostname. 

    new_master=$1 
    trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA" 

    # Prompte standby database. 
    /usr/bin/ssh -T $new_master $trigger_command 

    exit 0;

  2. pgpool集群

    配置虚拟ip(delegate_IP)，使用WATCHDOG监控，服务A宕机时，服务B自动接管虚拟IP对外提供服务。​