Postgresql高可用之Patroni方案

Hi, Everyone!Welcome to DQF's Blog,For technical exchange, Please add QQ: 905030209,Thanks!

基础环境配置

Host planning

hostname ip software
test-1 10.0.0.11 Etcd+Postgresql+Patroni
test-2 10.0.0.12 Etcd+Postgresql+Patroni
test-3 10.0.0.13 Etcd+Postgresql+Patroni

sync time to aliyun.com

yum install -y ntpdate
echo '#sync time to aliyun.com' >>/var/spool/cron/root
echo '*/5 * * * * /usr/sbin/ntpdate ntp.aliyun.com &>/dev/null' >>/var/spool/cron/root

Etcd安装部署

Get etcd package

yum install -y etcd

Configure etcd.conf

主机test-1配置

cat /etc/etcd/etcd.conf

ETCD_NAME=etcd0
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.11:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.0.0.11:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.11:2380"
ETCD_INITIAL_CLUSTER="etcd0=http://10.0.0.11:2380,etcd1=http://10.0.0.12:2380,etcd2=http://10.0.0.13:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.11:2379"
ETCD_INITIAL_CLUSTER_TOKEN="etcd_cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

主机test-2配置

cat /etc/etcd/etcd.conf

ETCD_NAME=etcd1
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.12:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.0.0.12:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.12:2380"
ETCD_INITIAL_CLUSTER="etcd0=http://10.0.0.11:2380,etcd1=http://10.0.0.12:2380,etcd2=http://10.0.0.13:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.12:2379"
ETCD_INITIAL_CLUSTER_TOKEN="etcd_cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

主机test-3配置

cat /etc/etcd/etcd.conf
ETCD_NAME=etcd2
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.13:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.0.0.13:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.13:2380"
ETCD_INITIAL_CLUSTER="etcd0=http://10.0.0.11:2380,etcd1=http://10.0.0.12:2380,etcd2=http://10.0.0.13:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.13:2379"
ETCD_INITIAL_CLUSTER_TOKEN="etcd_cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

Start etcd service

systemctl start etcd.service
systemctl status etcd.service

Pg安装部署

install pip and postgresql dependent packages

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql10-server postgresql-devel python-pip python-devel gcc gcc-c++

patroni安装

pip install patroni

pip install pip --upgrade
pip install setuptools --upgrade
pip install python-etcd --upgrade
pip install patroni --upgrade

add env variables to postgres

<details><summary>cat /var/lib/pgsql/.bash_profile</summary>
    <pre><code>
    [ -f /etc/profile ] && source /etc/profile
    PGDATA=/var/lib/pgsql/10/data
    export PGDATA
    # If you want to customize your settings,
    # Use the file below. This is not overridden
    # by the RPMS.
    [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
    # BEGIN ANSIBLE MANAGED BLOCK
    # PostgreSQL
    export PGDATA=/data/pg_data
    export PGHOME=/usr/pgsql-10
    export PATH=$PGHOME/bin:$PATH
    # Patroni
    export PATRONI_NAMESPACE=/db/
    # END ANSIBLE MANAGED BLOCK
    </code></pre>
</details>

create directory /etc/patroni

mkdir -p /etc/patroni

create pg_data directory

mkdir -p /data/pg_data

chmod pg_data directory

chown -R postgres.postgres /data/pg_data
chmod -R 700 /data/pg_data

enable watch dog

modprobe softdog

chown /dev/watchdog

chown -R postgres.postgres /dev/watchdog

config for systemd

cat /etc/systemd/system/patroni.service
    [Unit]
    Description=Runners to orchestrate a high-availability PostgreSQL
    After=syslog.target network.target

    [Service]
    Type=simple

    User=postgres
    Group=postgres
    ExecStart=/usr/bin/patroni /etc/patroni/patroni.yml
    KillMode=process
    TimeoutSec=30
    Restart=no

    [Install]
    WantedBy=multi-user.targ

configure patroni

主机test-1 patroni.yml

cat /etc/patroni/patroni.yml
    <pre><code>
    scope: postgres
    namespace: /db/
    name: pg_ha0

    restapi:
        listen: 10.0.0.11:8008
        connect_address: 10.0.0.11:8008

    etcd:
        hosts: 10.0.0.11:2379,10.0.0.12:2379,10.0.0.13:2379

    bootstrap:
        dcs:
            ttl: 30
            loop_wait: 10
            retry_timeout: 10
            maximum_lag_on_failover: 1048576
            postgresql:
                use_pg_rewind: true

        initdb:
        - encoding: UTF8
        - data-checksums

        pg_hba:
        - host replication replicator 127.0.0.1/32 md5
        - host replication replicator 10.0.0.11/0 md5
        - host replication replicator 10.0.0.12/0 md5
        - host replication replicator 10.0.0.13/0 md5
        - host all all 0.0.0.0/0 md5

        users:
            admin:
                password: admin
                options:
                    - createrole
                    - createdb

    postgresql:
        listen: 0.0.0.0:5432
        connect_address: 10.0.0.11:5432
        data_dir: /data/pg_data
        bin_dir: /usr/pgsql-10/bin
        pgpass: /tmp/pgpass
        authentication:
            replication:
                username: replicator
                password: replicator
            superuser:
                username: postgres
                password: postgres
        parameters:
            unix_socket_directories: '.'

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

主机test-2 patroni.yml

cat /etc/patroni/patroni.yml
    <pre><code>
    scope: postgres
    namespace: /db/
    name: pg_ha1

    restapi:
        listen: 10.0.0.12:8008
        connect_address: 10.0.0.12:8008

    etcd:
        hosts: 10.0.0.11:2379,10.0.0.12:2379,10.0.0.13:2379

    bootstrap:
        dcs:
            ttl: 30
            loop_wait: 10
            retry_timeout: 10
            maximum_lag_on_failover: 1048576
            postgresql:
                use_pg_rewind: true

        initdb:
        - encoding: UTF8
        - data-checksums

        pg_hba:
        - host replication replicator 127.0.0.1/32 md5
        - host replication replicator 10.0.0.11/0 md5
        - host replication replicator 10.0.0.12/0 md5
        - host replication replicator 10.0.0.13/0 md5
        - host all all 0.0.0.0/0 md5

        users:
            admin:
                password: admin
                options:
                    - createrole
                    - createdb

    postgresql:
        listen: 0.0.0.0:5432
        connect_address: 10.0.0.12:5432
        data_dir: /data/pg_data
        bin_dir: /usr/pgsql-10/bin
        pgpass: /tmp/pgpass
        authentication:
            replication:
                username: replicator
                password: replicator
            superuser:
                username: postgres
                password: postgres
        parameters:
            unix_socket_directories: '.'

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

主机test-3 patroni.yml

cat /etc/patroni/patroni.yml
    scope: postgres
    namespace: /db/
    name: pg_ha2

    restapi:
        listen: 10.0.0.13:8008
        connect_address: 10.0.0.13:8008

    etcd:
        hosts: 10.0.0.11:2379,10.0.0.12:2379,10.0.0.13:2379

    bootstrap:
        dcs:
            ttl: 30
            loop_wait: 10
            retry_timeout: 10
            maximum_lag_on_failover: 1048576
            postgresql:
                use_pg_rewind: true

        initdb:
        - encoding: UTF8
        - data-checksums

        pg_hba:
        - host replication replicator 127.0.0.1/32 md5
        - host replication replicator 10.0.0.11/0 md5
        - host replication replicator 10.0.0.12/0 md5
        - host replication replicator 10.0.0.13/0 md5
        - host all all 0.0.0.0/0 md5

        users:
            admin:
                password: admin
                options:
                    - createrole
                    - createdb

    postgresql:
        listen: 0.0.0.0:5432
        connect_address: 10.0.0.13:5432
        data_dir: /data/pg_data
        bin_dir: /usr/pgsql-10/bin
        pgpass: /tmp/pgpass
        authentication:
            replication:
                username: replicator
                password: replicator
            superuser:
                username: postgres
                password: postgres
        parameters:
            unix_socket_directories: '.'

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

start patroni

systemctl start patroni.service
patronictl -c /etc/patroni/patroni.yml list
patronictl -c /etc/patroni/patroni.yml --help

generate haproxy.cfg

在haproxy主机上配置

cat /tmp/haproxy.cfg

listen pg_master
    bind *:5000
    mode tcp
    option httplog
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_10.0.0.11_5432 10.0.0.11:5432  maxconn 100 check port 8008
    server postgresql_10.0.0.12_5432 10.0.0.12:5432  maxconn 100 check port 8008
    server postgresql_10.0.0.13_5432 10.0.0.13:5432  maxconn 100 check port 8008

listen pg_replicas
    bind *:5001
    mode tcp
    option httplog
    option httpchk OPTIONS /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_10.0.0.11_5432 10.0.0.11:5432  maxconn 100 check port 8008
    server postgresql_10.0.0.12_5432 10.0.0.12:5432  maxconn 100 check port 8008
    server postgresql_10.0.0.13_5432 10.0.0.13:5432  maxconn 100 check port 8008

问题总结

  • zookeeper 问题

Error: Could not find or load main class org.apache.zookeeper.server.quorum.QuorumPeerMain

解决方法

  • pip install 问题

fatal error: libpq-fe.h: No such file or directory

[解决方法](https://unix.stackexchange.com/questions/345814/gcc-error-installing-psycopg2-package-for-python3-on-centos-7-3)

参考文献

Postgresql 下载链接
Postgresql 中文手册
Patroni 文档链接
Patroni Github
Patroni 安装文档-1
Patroni 安装文档-2
Patroni 安装文档-3
Patroni 安装文档-4
Zookeeper 下载链接
Etcd 安装文档

One thought on “Postgresql高可用之Patroni方案

Leave a Reply

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Blog Theme: FreeDom by DQF.