使用Patroni搭建AntDB单机版本高可用集群

# 创建用户并设置密码
useradd antdb
passwd antdb

# 添加 antdb 到 sudo 中
echo 'antdb  ALL=(ALL)  NOPASSWD: ALL' >> /etc/sudoers

cat >> /etc/security/limits.conf <<EOF

# Add for antdb by hongye
antdb soft nproc 65536
antdb hard nproc 65536
antdb soft nofile 278528
antdb hard nofile 278528
antdb soft stack unlimited
antdb soft core unlimited
antdb hard core unlimited
antdb soft memlock 250000000
antdb hard memlock 250000000
EOF

cat >>  /etc/sysctl.conf << EOF

# add for antdb by hongye
kernel.shmmax=17179869184 # 16GB
kernel.shmall=4194304     # 16GB / 4KB
kernel.shmmni=4096
kernel.msgmnb=4203520
kernel.msgmax=65536
kernel.msgmni=32768
kernel.sem=501000 641280000 501000 12800

fs.aio-max-nr=6553600
fs.file-max=26289810
net.core.rmem_default=8388608
net.core.rmem_max=16777216
net.core.wmem_default=8388608
net.core.wmem_max=16777216
net.core.netdev_max_backlog=262144
net.core.somaxconn= 65535
net.ipv4.tcp_rmem=8192 87380 16777216
net.ipv4.tcp_wmem=8192 65536 16777216
net.ipv4.tcp_max_syn_backlog=262144
net.ipv4.tcp_keepalive_time=180
net.ipv4.tcp_keepalive_intvl=10
net.ipv4.tcp_keepalive_probes=3
net.ipv4.tcp_fin_timeout=1
net.ipv4.tcp_synack_retries=1
net.ipv4.tcp_syn_retries=1
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_max_tw_buckets=256000
net.ipv4.tcp_retries1=2
net.ipv4.tcp_retries2=3
vm.dirty_background_ratio=5
vm.dirty_expire_centisecs=6000
vm.dirty_writeback_centisecs=500
vm.dirty_ratio=20
vm.overcommit_memory=0
vm.overcommit_ratio= 120
vm.vfs_cache_pressure = 100
vm.swappiness=10
vm.drop_caches = 2
vm.min_free_kbytes = 2048000
vm.zone_reclaim_mode=0
kernel.core_uses_pid=1
kernel.core_pattern=/home/antdb/data/coredump/core-%e-%p-%t
kernel.sysrq=0
EOF

yum install -y perl-ExtUtils-Embed \
 flex \
 bison \
 readline-devel \
 zlib-devel \
 openssl-devel \
 pam-devel \
 libxml2-devel \
 libxslt-devel \
 openldap-devel \
 python-devel \
 gcc-c++ \
 libssh2-devel \
 perl-Data-Dumper \
 python-setuptools.noarch \
 gcc \
 unzip \
 dstat \
 libffi-devel   # 解决错误： ModuleNotFoundError: No module named '_ctypes'

# 软件安装
cd /home/antdb
unzip adb_sql.zip
mkdir /home/antdb/adb_sql/build
cd /home/antdb/adb_sql/build
../configure --prefix=/home/antdb/app/antdb_5.0 --enable-grammar-oracle --with-python --disable-cluster
make install-world-contrib-recurse


# 配置用户环境变量
cat >> /home/antdb/.bashrc <<"EOF"

# Add for antdb by hongye
export ADBHOME=/home/antdb/app/antdb_5.0
export PATH=$ADBHOME/bin:$PATH
export LD_LIBRARY_PATH=$ADBHOME/lib:$LD_LIBRARY_PATH
export PGDATABASE=postgres
export PGPORT=5432
export PGHOST=127.0.0.1
export PGDATA=/home/antdb/data/data_5.0
EOF
source /home/antdb/.bashrc


# 初始化数据库，只在第一节点运行（192.168.242.101）
initdb

# 配置基本数据库参数，只在第一节点运行（192.168.242.101）
cat >> $PGDATA/postgresql.conf <<EOF

# add for antdb by hongye
wal_level = replica
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 10
wal_keep_segments = 512
hot_standby = on
shared_buffers = 512MB
max_connections = 300
port = 5432
listen_addresses = '*'
log_directory = 'pg_log'
log_destination ='csvlog'
logging_collector = on
log_min_messages = error
EOF

# 配置访问权限控制，只在第一节点运行（192.168.242.101）
cat >> $PGDATA/pg_hba.conf <<EOF

# add for antdb by hongye
host  replication   all   192.168.242.0/24    trust
host  all           all   192.168.242.0/24    trust
EOF

# 启动数据块，只在第一节点运行（192.168.242.101）
pg_ctl start

# 搭建其他节点的备库，在第其他节点运行（192.168.242.102, 192.168.242.103）
pg_basebackup -D /home/antdb/data/data_5.0 -Fp -R -Xs -v -P -h 192.168.242.101 -p 5432 -U antdb
pg_ctl start

cd /home/antdb/
tar -zxvf Python-3.8.0.tgz
cd Python-3.8.0
./configure --enable-shared    # 不要加 --enable-optimizations，否则会导致 make 编译报错
make
make install



# 防止找不到命令的报错： 
# sudo: pip3: command not found
sudo ln -s /usr/local/bin/pip3 /usr/bin/pip3
sudo ln -s /usr/local/bin/python3 /bin/python3


# 防止找不到库报错：
# python3: error while loading shared libraries: libpython3.8.so.1.0: cannot open shared object file: No such file or directory
echo "/usr/local/lib" >> /etc/ld.so.conf.d/python_3.8.conf
ldconfig -v
python3

cd /home/antdb
tar -zxvf patroni_1.6.3.tar.gz
sudo pip3 install --no-index --find-links=file:/home/antdb/patroni_1.6.3 patroni
sudo pip3 install --no-index --find-links=file:/home/antdb/patroni_1.6.3 python-etcd

# 检查 Patroni
which patroni
patroni -h

sudo mkdir /etc/etcd

# 以下参数不同节点配置有区别（注意修改name和initial-cluster，以及其他配置项中的本节点IP）
sudo cat > /etc/etcd/conf.yaml <<EOF
name: etcd-3
data-dir: /var/lib/etcd/data
listen-client-urls: http://192.168.242.101:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.242.101:2379,http://127.0.0.1:2379
listen-peer-urls: http://192.168.242.101:2380
initial-advertise-peer-urls: http://192.168.242.101:2380
initial-cluster: etcd-1=http://192.168.242.101:2380,etcd-2=http://192.168.242.102:2380,etcd-3=http://192.168.242.103:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
EOF

sudo cat > /usr/lib/systemd/system/etcd.service <<EOF
[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
WorkingDirectory=/var/lib/etcd/
ExecStart=/bin/etcd --config-file=/etc/etcd/conf.yaml
Restart=on-failure
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF

# 注意检查防火墙和selinux，避免以下错误： publish error: etcdserver: request timed out
sudo systemctl daemon-reload
sudo systemctl restart etcd
sudo systemctl status etcd

# 时间不同步会有一些报错，但能正常部署并运行，实际环境中需要配置ntp时间同步
[root@Server1 ~]# sudo systemctl status etcd
● etcd.service - Etcd Server
   Loaded: loaded (/usr/lib/systemd/system/etcd.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2019-12-16 01:03:59 EST; 24h ago
 Main PID: 41320 (etcd)
   CGroup: /system.slice/etcd.service
           └─41320 /bin/etcd --config-file=/etc/etcd/conf.yaml

Dec 16 18:45:41 Server1 etcd[41320]: failed to send out heartbeat on time (exceeded the 100ms timeout for 2.127838662s, to db179a449b6b0de8)
Dec 16 18:45:41 Server1 etcd[41320]: server is likely overloaded
Dec 16 18:45:41 Server1 etcd[41320]: failed to send out heartbeat on time (exceeded the 100ms timeout for 2.127879201s, to beef4df2df8a8cfc)
Dec 16 18:45:41 Server1 etcd[41320]: server is likely overloaded
Dec 16 19:45:21 Server1 etcd[41320]: failed to send out heartbeat on time (exceeded the 100ms timeout for 1.812316393s, to db179a449b6b0de8)
Dec 16 19:45:21 Server1 etcd[41320]: server is likely overloaded
Dec 16 19:45:21 Server1 etcd[41320]: failed to send out heartbeat on time (exceeded the 100ms timeout for 1.813021423s, to beef4df2df8a8cfc)
Dec 16 19:45:21 Server1 etcd[41320]: server is likely overloaded
Dec 16 19:45:28 Server1 etcd[41320]: the clock difference against peer db179a449b6b0de8 is too high [59m7.515068949s > 1s] (prober "ROUND_TRIPPER_RAFT_MESSAGE")
Dec 16 19:45:28 Server1 etcd[41320]: the clock difference against peer db179a449b6b0de8 is too high [59m7.518988151s > 1s] (prober "ROUND_TRIPPER_SNAPSHOT")

[root@Server1 ~]# etcdctl member list
beef4df2df8a8cfc: name=etcd-3 peerURLs=http://192.168.242.103:2380 clientURLs=http://127.0.0.1:2379,http://192.168.242.103:2379 isLeader=false
db179a449b6b0de8: name=etcd-2 peerURLs=http://192.168.242.102:2380 clientURLs=http://127.0.0.1:2379,http://192.168.242.102:2379 isLeader=false
eeaa227dea3a38d4: name=etcd-1 peerURLs=http://192.168.242.101:2380 clientURLs=http://127.0.0.1:2379,http://192.168.242.101:2379 isLeader=true

# 不同节点需要修改对应的IP信息，以及开头的name信息
sudo vi /etc/patroni.yml 

# /namespace/scope/members/name
scope: test # level 2
namespace: /antdb5/  # level 1
name: node_74  # level 3 

restapi:  # https://github.com/zalando/patroni/blob/master/docs/SETTINGS.rst#rest-api
  listen: 127.0.0.1:8008  # 修改为实际地址
  connect_address: 127.0.0.1:8008 # 修改为实际地址

etcd:
  host: 192.168.242.103:2379

bootstrap:
  # this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 15
    loop_wait: 5
    retry_timeout: 5
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
#     use_slots: true
      parameters:
         listen_addresses: "*"
         port: 5432
         wal_level: hot_standby
         log_directory: "pg_log"
         log_destination: "csvlog"
         hot_standby: "on"
         wal_keep_segments: 8
         max_wal_senders: 5


  # some desired options for 'initdb'
  initdb:  # Note: It needs to be a list (some options need values, others are switches)
  - encoding: UTF8
  - data-checksums

  pg_hba:  # Add following lines to pg_hba.conf after running 'initdb'
  - host replication replicator 192.168.242.0/24 trust
  - host all all 192.168.242.0/24 trust

  # Additional script to be launched after initial cluster creation (will be passed the connection URL as parameter)
# post_init: /usr/local/bin/setup_cluster.sh

  # Some additional users users which needs to be created after initializing new cluster
  users:
    admin:
      password: admin
      options:
        - createrole
        - createdb

postgresql:
  listen: 192.168.242.103:5432
  connect_address: 192.168.242.103:5432
  data_dir: /home/antdb/data/data_5.0
  bin_dir: /home/antdb/app/antdb_5.0/bin
  pgpass: /home/antdb/data/.pgpass
  authentication:
    replication:
      username: replicator
      password: antdb
    superuser:
      username: antdb
      password: antdb
  parameters:
    unix_socket_directories: '/tmp'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
EOF

psql -p 5432 

create role replicator with login replication password 'antdb';
alter role antdb password 'antdb';

sudo cat > /usr/lib/systemd/system/patroni.service <<EOF

[Unit]
Description=Runners to orchestrate a high-availability AntDB5.0
After=syslog.target network.target

[Service]
Type=simple
User=antdb
Group=antdb
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl start patroni
# sudo systemctl stop patroni
sudo systemctl status patroni -l

# patroni的日志在系统日志中：
tail -f /var/log/messages

patronictl -c /etc/patroni.yml list 

+---------+----------+-----------------+--------------+---------+----+-----------+-----------------+
| Cluster |  Member  |       Host      |     Role     |  State  | TL | Lag in MB | Pending restart |
+---------+----------+-----------------+--------------+---------+----+-----------+-----------------+
|  pgtest | antdb5_1 | 192.168.242.101 |    Leader    | running |  1 |           |                 |
|  pgtest | antdb5_2 | 192.168.242.102 |              | running |  1 |         0 |        *        |
|  pgtest | antdb5_3 | 192.168.242.103 | Sync Standby | running |  1 |         0 |        *        |
+---------+----------+-----------------+--------------+---------+----+-----------+-----------------+

# 修改配置
export EDITOR=vi
patronictl -c /etc/patroni.yml edit-config pgtest

# 重启集群
patronictl -c /etc/patroni.yml restart pgtest

# 重启节点
patronictl -c /etc/patroni.yml restart pgtest antdb5_1

[antdb@test-node25 ~]$ python -c "import psycopg2; print(psycopg2.__version__)"
2.8.4 (dt dec pq3 ext lo64)
[antdb@test-node25 ~]$ sudo python -c "import psycopg2; print(psycopg2.__version__)"
2.8.4 (dt dec pq3 ext lo64)

[antdb@test-node25 danghb]$ sudo python t.py 
('Patroni requires psycopg2 version {0} available', '2.8.4')
[antdb@test-node25 danghb]$ cat t.py 
if __name__ == "__main__":    
    min_psycopg2 = (2, 5, 4)
    min_psycopg2_str = '.'.join(map(str, min_psycopg2))

    def parse_version(version):
        for e in version.split('.'):
            try:
                yield int(e)
            except ValueError:
                break

    try:
        import psycopg2
        version_str = psycopg2.__version__.split(' ')[0]
        version = tuple(parse_version(version_str))
        if version < min_psycopg2:
            print('Patroni requires psycopg2>={0}, but only {1} is available', min_psycopg2_str, version_str)
        else:
           print('Patroni requires psycopg2 version {0} available', version_str)
    except ImportError:
        print('Patroni requires psycopg2>={0} or psycopg2-binary', min_psycopg2_str)
[antdb@test-node25 danghb]$ 

sudo vi /usr/lib/systemd/system/dangtest.service 
[Unit]
Description=dangtest
After=syslog.target network.target

[Service]
Type=simple
User=antdb
Group=antdb
ExecStart=/usr/bin/python /home/antdb/danghb/t.py
KillMode=process

[Install]
WantedBy=multi-user.target