SQL SERVER高可用 NAS pgsql 高可用

一主两从+haproxy IP地址、端口、版本号根据自身的服务器和要求来改变
架构图
服务器配置
服务器四台 (192.168.0.78 192.168.0.79 192.168.0.80 192.168.0.81)
操作系统版本 Centos 7.6

CPU 192.168.0.78 192.168.0.79 192.168.0.81 | 2核 192.168.0.80 | 4核
内存 192.168.0.78 192.168.0.79 192.168.0.81 | 4G 192.168.0.80 | 8G
磁盘 四台都是200G

软件 etcd patroni python postgresql

etcd Version: 3.3.11
patroni Version : 2.1.1
python Version : 3.6
postgresql Version : 13.4

端口  ：etcd  2379 2380    |    postgresql  5432    |    haproxy   7000 监听主节点端口5000   监听从节点端口5001   |    patroni   8008
etcd不仅是一个存储还是做一个数据强一致性和服务发现的作用，主要用于分布式集群当中
patroni是一个python代码的开源产品，通过DCS来对postgresql各个节点的状态进行判断

环境

关闭防火墙  ()systemctl stop firewalld)      关闭沙盒  (setenforce 0)  永久关闭vim /etc/selinux/config   ----->   SELINUX=disabled

安装etcd

(三台都做 192.168.0.78,192.168.0.79,192.168.0.80)

yum -y install etcd

vim /etc/etcd/etcd.conf #IP地址和集群名称三台机器都不一样三台都要改

开启etcd服务 并设置开机自启
vim /usr/lib/systemd/system/etcd.service
    [Unit]
    Description=Etcd Server
    After=network.target
    After=network-online.target
    Wants=network-online.target
    [Service]
    Type=notify
    WorkingDirectory=/var/lib/etcd/
    EnvironmentFile=-/etc/etcd/etcd.conf
    User=etcd
    # set GOMAXPROCS to number of processors
    ExecStart=/bin/bash -c "GOMAXPROCS=$(nproc) /usr/bin/etcd --name=\"${ETCD_NAME}\" --data-dir=\"${ETCD_DATA_DIR}\" --listen-client-urls=\"${ETCD_LISTEN_CLIENT_URLS}\" --initial-advertise-peer-urls=\"${ETCD_INITIAL_ADVERTISE_PEER_URLS}\" --advertise-client-urls=\"${ETCD_ADVERTISE_CLIENT_URLS}\" --initial-cluster=\"${ETCD_INITIAL_CLUSTER}\" --initial-cluster-token=\"${ETCD_INITIAL_CLUSTER_TOKEN}\" --initial-cluster-state=\"${ETCD_INITIAL_CLUSTER_STATE}\""
    Restart=on-failure
    LimitNOFILE=65536
    [Install]
    WantedBy=multi-user.target

查看etcd集群状态    etcdctl member list     etcdctl cluster-health

因为是patroni控制postgresql，所以安装完postgresql-13之后不用启动
**

安装postgresql13

**

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum -y install postgresql13-server

**

安装patroni

** (三台都做 192.168.0.78,192.168.0.79,192.168.0.80)
因为postgresql用的是13版本，所以patroni要安装2.0.0以上的版本
python 环境要3以上的版本
安装python3.6版本
yum -y install python36
( 三台都做 192.168.0.78,192.168.0.79,192.168.0.80)

升级pip    wget https://bootstrap.pypa.io/get-pip.py     ----->   python3 get-pip.py
温馨提示  如果升级报错原因可能是下载的py文件版本不对   报错会提示你如何下载对应的版本   会给一个URL
安装patroni
    yum install -y gcc python-devel epel-release
    pip3 install psycopg2-binary
    pip3 install --upgrade setuptools
    pip3 install patroni[etcd]
编辑patroni的配置文件
mkdir  -p /usr/patroni/conf
cd /usr/patroni/conf
vim patroni_postgresql.yml
        scope: pgsql
        namespace: /service/
        name: pgcs-0002
        restapi:
          listen: 0.0.0.0:8008
          connect_address: 192.168.0.79:8008
        etcd:
          host: 192.168.0.79:2379
        bootstrap:
          dcs:
            ttl: 30
            loop_wait: 10
            retry_timeout: 10
            maximum_lag_on_failover: 1048576
            master_start_timeout: 300
            synchronous_mode: false
            postgresql:
              use_pg_rewind: true
              use_slots: true
              parameters:
                listen_addresses: "0.0.0.0"
                port: 5432
                wal_level: logical
                hot_standby: "on"
                wal_keep_segments: 100
                max_wal_senders: 10
                max_replication_slots: 10
                wal_log_hints: "on"
          initdb:
          - encoding: UTF8
          - locale: C
          - lc-ctype: zh_CN.UTF-8
          - data-checksums
          pg_hba:
          - host replication repl 0.0.0.0/0 md5
          - host all all 0.0.0.0/0 md5
        postgresql:
          listen: 0.0.0.0:5432
          connect_address: 192.168.0.79:5432
          data_dir: /var/lib/pgsql/13/data
          bin_dir: /usr/pgsql-13/bin
          authentication:
            replication:
              username: repl
              password: "repl91"
            superuser:
              username: postgres
              password: "postgres91"
          basebackup:
            max-rate: 100M
            checkpoint: fast
        tags:
            nofailover: false
            noloadbalance: false
            clonefrom: false
            nosync: false

因为是三台都安装所以配置文件需要修改 以下是需要修改的地方

配置完之后配置启动  vim /usr/lib/systemd/system/patroni.service   并设置开机自启systemctl enable patroni
    [Unit]
    Description=Runners to orchestrate a high-availability PostgreSQL
    After=syslog.target network.target
    [Service]
    Type=simple
    User=postgres
    Group=postgres
    #StandardOutput=syslog
    ExecStart=/usr/local/bin/patroni /usr/patroni/conf/patroni_postgresql.yml
    ExecReload=/bin/kill -s HUP $MAINPID
    KillMode=process
    TimeoutSec=30
    Restart=no
    [Install]
    WantedBy=multi-user.target

查看patroni集群     显示一下代表 安装成功

安装haproxy 192.168.0.81
yum -y install haproxy
cd /etc/haproxy
cp haproxy.cfg haproxy.cfg.bak
vim haproxy.conf
global
maxconn 100000
log 127.0.0.1 local3 info
defaults
mode tcp
log global
retries 2
timeout queue 5s
timeout connect 5s
timeout client 60m
timeout server 60m
timeout check 15s
listen stats
mode tcp
bind 192.168.0.81:7000
stats enable
stats refresh 30s
stats uri /stats
stats auth admin:123456
stats hide-version
listen master
log global
bind 192.168.0.81:5000
mode tcp
maxconn 2000
option tcplog
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
server db78 192.168.0.78:5432 check port 8008
server db79 192.168.0.79:5432 check port 8008
server db80 192.168.0.80:5432 check port 8008
listen replicas
log global
bind 192.168.0.81:5001
mode tcp
maxconn 6000
option tcplog
option httpchk OPTIONS /replica
balance roundrobin
http-check expect status 200
default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
server db78 192.168.0.78:5432 check port 8008
server db79 192.168.0.79:5432 check port 8008
server db80 192.168.0.80:5432 check port 8008
启动haproxy并设置开机自启
设置haproxy日志
vim /etc/rsyslog.conf
这两行取消注释
15 $ModLoad imudp
16 $UDPServerRun 514
然后在76行按照73行写
73 local7.* /var/log/boot.log
74
75
76 local2.* /var/log/haproxy.log
然后重启rsyslog和haproxy服务
haproxy页面监控

命令

手动切换   patronictl -c /usr/patroni/conf/patroni_postgresql.yml
patronictl -c /usr/patroni/conf/patroni_postgresql.yml restart pgsql  (patroni集群名字)
patronictl -c /usr/patroni/conf/patroni_postgresql.yml edit-config   进入修改配置界面   ----->将要更改的配置修改好
然后 重启集群   例如添加最大连接数  max_connections: 4000;  😊
数据库优化（常更新） 修改shared_buffers参数  使用patronictl -c /usr/patroni/conf/patroni_postgresql.yml edit-config   进入修改配置界面，然后加入shared_buffers: xGB  x为大小  通常设置为内存的25%   设置work_mem大小  添加work_mem: xMB  x为大小

pgsql备份和恢复

pgsql备份
    #####pg_dump -U postgres -h localhost -p 5432 -Fc -f  备份存放目录(/opt/xxxx.bak)   要备份的库   -Fc   压缩备份（例如tar.gz  zip  bak） -f  指定备份存放目录
    #####pg_dumpall -U postgres -h localhost -p 5432 -c -f 备份存放目录(/opt/xxxx.sql) 
    #####备份机器   192.168.0.80  备份存放目录  /opt/pg_back        (xxx.bak为当日压缩备份，xxx.sql为一周全量备份)
    pg_dump -h 127.0.0.1 -U postgres -d kjpt_test | gzip > /opt/kjpt_test.sql.gz  压缩备份
pgsql恢复
    #####pg_restore  -U postgres -h localhost -p 5432 -W  -d 数据库名字  -f  备份时的目录       pg_store 可以恢复自定义压缩的备份文件 
    #####pgsql  -h localhost -U postgres -p 5432  -f  备份存放目录   恢复sql的备份文件
    还原压缩备份     gunzip -c kjpt_test.sql.gz | psql -h 127.0.0.1 -U postgres -d kjpt

pgsql修改密码：alter user 用户名 with password ‘密码’;

如有疑惑，可以留言。