콘텐츠로 이동

Patroni

https://patroni.readthedocs.io/en/latest/

주의점

  • pip install 할 때

    after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno 101] Network is unreachable')'

이라는 에러 메시지가 표시될 경우 네트워크 설정을 확인할 것. (VPN이나 프록시 설정이 따로 있는지 등) 

# 프록시인 경우 설정 방법
/root/.config/pip/pip.conf
#[global]
#proxy = xxx.xxx.xxx.xxx:xxxx

# 설정 명령어
pip config set global.proxy xxx.xxx.xxx.xxx:xxxx

  • pip install 할 때

    WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv

이라는 메시지가 표시될 경우 유저별 환경이 다른지 확인. root로 실행할 경우 Package Manager에서 Permission 문제가 발생할 가능성이 있다는 의미.

https://github.com/pypa/pip/issues/1668


전제조건

  • PostgreSQL과 ETCD가 설치되어 있어야 함.
  • ETCD가 정상적으로 작동되어야 함.
  • PostgreSQL이 초기화되어 있지 않아야 함.


설치

  1. 필요한 환경변수 설정 

    NAMESPACE=postgres
SCOPE=pg-cluster
IP_ADDRESS1=xxx.xxx.xxx.xxx     # HOSTNAME으로 해도 문제 없음
IP_ADDRESS2=xxx.xxx.xxx.xxx     # HOSTNAME으로 해도 문제 없음
IP_ADDRESS3=xxx.xxx.xxx.xxx     # HOSTNAME으로 해도 문제 없음
IP_ADDRESS_CIDR=xxx.xxx.xxx.xxx/xx
PW_POSTGRES=postgres
PW_REPLICATOR=replicator
PW_PGREWIND=pgrewind

  2. pip 와 관련 Package 설치 

    dnf install python3-pip
dnf install python3-psycopg2
dnf install python-devel

# dnf 설치 후
pip install wheel
pip install psycopg2-binary
pip install python-etcd
pip install patroni[etcd,consul]

  3. patroni 의 설치확인 

    patroni --version
#patroni 4.0.4


설정

  1. 설정파일 작성 (노드 1번일 경우의 예시) 

    mkdir /etc/patroni
vi /etc/patroni/patroni.yml

namespace: ${NAMESPACE}
scope: ${SCOPE}
name: `hostname`

restapi:
    listen: ${IP_ADDRESS1}:8008
    connect_address: ${IP_ADDRESS1}:8008

etcd3:
    host: ${IP_ADDRESS1}:2379

bootstrap:
# this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576

    postgresql:
        use_pg_rewind: true
        use_slots: true
        parameters:
            #archive_command: pgbackrest --stanza=${SCOPE} archive-push /var/pgsql/data/%p
            archive_mode: true
            archive_timeout: 60s
            checkpoint_completion_target: 0.9
            effective_cache_size: 12GB
            effective_io_concurrency: 200
            fsync: true
            hot_standby: true
            log_autovacuum_min_duration: 0
            log_checkpoints: 'on'
            log_connections: 'on'
            log_disconnections: 'on'
            log_filename: 'postgresql-%Y-%m-%d_%H.log'
            log_line_prefix: '%t [%p - %l]: error=%e, %qdb=%d, user=%u, remote=%r, app=%a, client=%h, '
            log_lock_waits: 'on'
            log_min_duration_statement: 0
            log_rotation_age: 60
            log_rotation_size: 0
            log_temp_files: 0
            logging_collector: 'on'
            maintenance_work_mem: 1GB
            max_connections: 150
            max_replication_slots: 10
            max_wal_senders: 5
            max_wal_size: 8GB
            max_worker_processes: 8
            min_wal_size: 1GB
            random_page_cost: 1.1
            shared_buffers: 4GB
            shared_preload_libraries: 'pg_stat_statements'
            temp_buffers: 4MB
            timezone: 'Asia/Tokyo'
            wal_buffers: 16MB
            wal_keep_size: 2048MB
            wal_level: 'logical'
            wal_log_hints: true
            work_mem: 16MB
        #recovery_conf:
            #recovery_target_timeline: latest
            #restore_command: 'pgbackrest --stanza=${SCOPE} archive-get %f /var/pgsql/data/%p'

# some desired options for 'initdb'
initdb: # Note: It needs to be a list (some options need values, others are switches)
    - encoding: UTF8
    - data-checksums
    - lc-locale: C.utf8
    - lc-ctype: C.utf8
    - lc-messages: C.utf8
    - lc-monetary: C.utf8
    - lc-numeric: C.utf8
    - lc-time: C.utf8

pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator ::1/128 md5
    - host replication replicator ${IP_ADDRESS1}/32 md5
    - host replication replicator ${IP_ADDRESS2}/32 md5
    - host replication replicator ${IP_ADDRESS3}/32 md5
    - host all all ${IP_ADDRESS_CIDR} md5

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

postgresql:
    #listen: ${IP_ADDRESS1}:5432
    listen: '*'
    connect_address: ${IP_ADDRESS1}:5432
    data_dir: /var/pgsql/data
    bin_dir: /usr/local/pgsql/bin
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: ${PW_REPLICATOR}
        superuser:
            username: postgres
            password: ${PW_POSTGRES}
        rewind:
            username: pgrewind
            password: ${PW_PGREWIND}

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: true
    >경로나 파라미터 등 상세 부분은 변경해서 사용할 것

  2. 파일 OWNER 변경 

    chown -R postgres:postgres /etc/patroni


서비스 설정

  1. SERVICE 파일 작성 

    vi /usr/lib/systemd/system/patroni.service

[Unit]
Description=patroni
Documentation=https://patroni.readthedocs.io/en/latest/index.html
After=syslog.target network.target etcd.target
Wants=network-online.target

[Service]
Type=simple
User=postgres
Group=postgres
PermissionsStartOnly=true
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -HUP $MAINPID
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=no
TimeoutSec=30s

[Install]
WantedBy=multi-user.target

  2. SERVICE 설정 및 시작 

    systemctl daemon-reload
systemctl start patroni
systemctl enable patroni
systemctl status patroni

  3. SERVICE 로그 확인 

    journalctl -u patroni


상태 확인 및 사용법

  1. 상태 확인 

    patronictl -c /etc/patroni/patroni.yml list
#+ Cluster: cluster-name (*******************) ----+----+-----------+
#| Member  | Host            | Role    | State     | TL | Lag in MB |
#+---------+-----------------+---------+-----------+----+-----------+
#| member1 | xxx.xxx.xxx.xxx | Replica | streaming | 11 |         0 |
#| member2 | xxx.xxx.xxx.xxx | Leader  | running   | 11 |           |
#| member3 | xxx.xxx.xxx.xxx | Replica | streaming | 11 |         0 |
#+---------+-----------------+---------+-----------+----+-----------+

  2. 강제 FAILOVER 

    patronictl -c /etc/patroni/patroni.yml failover
#Candidate ['member1', 'member3'] []: member1
#Are you sure you want to failover cluster cluster-name, demoting current member2? [y/N]: y


MEMBER 추가

  1. 「추가할 서버」 먼저 ETCD의 멤버를 추가 할 필요가 있음.

  2. 「추가할 서버」 PATRONI 설정 파일 작성 

    namespace: ${NAMESPACE}
scope: ${SCOPE}
name: `hostname`

restapi:
    listen: ${IP_ADDRESS-추가}:8008
    connect_address: ${IP_ADDRESS-추가}:8008

etcd3:
    host: ${IP_ADDRESS-추가}:2379

bootstrap:
# this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster
dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576

    postgresql:
        use_pg_rewind: true
        use_slots: true
        parameters:
            archive_command: pgbackrest --stanza=${SCOPE} archive-push /var/pgsql/data/%p
            archive_mode: true
            archive_timeout: 60s
            checkpoint_completion_target: 0.9
            effective_cache_size: 12GB
            effective_io_concurrency: 200
            fsync: true
            hot_standby: true
            log_autovacuum_min_duration: 0
            log_checkpoints: 'on'
            log_connections: 'on'
            log_disconnections: 'on'
            log_filename: 'postgresql-%Y-%m-%d_%H.log'
            log_line_prefix: '%t [%p - %l]: error=%e, %qdb=%d, user=%u, remote=%r, app=%a, client=%h, '
            log_lock_waits: 'on'
            log_min_duration_statement: 0
            log_rotation_age: 60
            log_rotation_size: 0
            log_temp_files: 0
            logging_collector: 'on'
            maintenance_work_mem: 1GB
            max_connections: 150
            max_replication_slots: 10
            max_wal_senders: 5
            max_wal_size: 8GB
            max_worker_processes: 8
            min_wal_size: 1GB
            random_page_cost: 1.1
            shared_buffers: 4GB
            shared_preload_libraries: 'pg_stat_statements'
            temp_buffers: 4MB
            timezone: 'Asia/Tokyo'
            wal_buffers: 16MB
            wal_keep_size: 2048MB
            wal_level: 'logical'
            wal_log_hints: true
            work_mem: 16MB
        recovery_conf:
            recovery_target_timeline: latest
            restore_command: 'pgbackrest --stanza=${SCOPE} archive-get %f /var/pgsql/data/%p'

# some desired options for 'initdb'
initdb: # Note: It needs to be a list (some options need values, others are switches)
    - encoding: UTF8
    - data-checksums
    - lc-locale: C.utf8
    - lc-ctype: C.utf8
    - lc-messages: C.utf8
    - lc-monetary: C.utf8
    - lc-numeric: C.utf8
    - lc-time: C.utf8

pg_hba: # Add following lines to pg_hba.conf after running 'initdb'
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator ::1/128 md5
    - host replication replicator ${IP_ADDRESS1}/32 md5
    - host replication replicator ${IP_ADDRESS2}/32 md5
    - host replication replicator ${IP_ADDRESS3}/32 md5
    - host replication replicator ${IP_ADDRESS-추가}/32 md5
    - host all all ${IP_ADDRESS_CIDR} md5

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

postgresql:
    #listen: ${IP_ADDRESS-추가}:5432
    listen: '*'
    connect_address: ${IP_ADDRESS-추가}:5432
    data_dir: /var/pgsql/data
    bin_dir: /usr/local/pgsql/bin
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: ${PW_REPLICATOR}
        superuser:
            username: postgres
            password: ${PW_POSTGRES}
        rewind:
            username: pgrewind
            password: ${PW_PGREWIND}

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

  3. 「추가할 서버」 pg_hba.conf에 추가 

    host replication replicator <host-patroni-추가>/32 md5

  4. 「다른 서버」 pg_hba.conf에 추가 

    host replication replicator <host-patroni-추가>/32 md5

  5. 「다른 서버」 patroni reload 

    patronictl -c /etc/patroni/patroni.yml reload <cluster-name>

  6. 「추가할 서버」 PATRONI 시작

  7. 「HAPROXY 서버」 HAPROXY 설정 파일에 추가 

    # PRIMARY
server node-추가 <host-patroni-추가>:5432 maxconn 150 check port 8008

# STANDBY
server node-추가 <host-patroni-추가>:5432 maxconn 150 check port 8008


MEMBER 삭제

  1. 「삭제할 서버」 PATRONI 중지

  2. 「다른 서버」 PATRONI RELOAD 

    patronictl -c /etc/patroni/patroni.yml reload <cluster-name>

  3. 「HAPROXY 서버」 HAPROXY 설정 파일로부터 해당 노드의 내용을 삭제


POSTGRESQL의 설정 변경

  1. 아래의 명령어를 실행해 변경 

    patronictl -c /etc/patroni/patroni.yml edit-config

  2. RELOAD가 필요한 파라미터는 RELOAD를 실행 

    patronictl -c /etc/patroni/patroni.yml reload <cluster-name>


CLUSTER 삭제

patronictl -c /etc/patroni/patroni.yml remove <cluster-name>


참고 링크

  • https://docs.microfocus.com/doc/SMAX/24.4/HASQLPatroni
  • https://docs.percona.com/postgresql/13/solutions/high-availability.html