database

PostgreSQL Replication High Availability

Mahyuddin

14 July 2018 • 6 min read

Kali ini penulis mencoba untuk menulis lebih tepatnya sebagai catatan dari hasil oprek PostgreSQL High Availability. Untuk osnya penulis menggunakan CentosOS 7.

Kebutuhan 4 VM:

VM1: Node DB1
Vm2: Node DB2
VM3: Node ETCD
VM4: Node HAProxy

Di setiap node tambahkan ip dan hostname

$ sudo tee -a  /etc/hosts << EOF
192.168.1.101   pg1-preko
192.168.1.102   pg2-preko
192.168.1.103   etcd0-preko
192.168.1.104   haproxy0-preko
EOF

Selanjutnya konfigurasi Etcd terlebih dahulu untuk menangani kluster

ETCD

Etcd sendiri merupakan basisdata konfigurasi, untuk lebih lanjut bisa dibaca di web resminya https://coreos.com/etcd/

$ wget https://github.com/coreos/etcd/releases/download/v3.3.8/etcd-v3.3.8-linux-amd64.tar.gz
$ tar xvfz etcd-v3.3.8-linux-amd64.tar.gz
$ sudo cp etcd-v3.3.8-linux-amd64/etcd* /usr/bin

Konfigurasi ETCD

Lanjut dengan menyediakan kebutuhun Etcd:

$ sudo mkdir -p /var/lib/etcd/data
$ sudo mkdir -p /var/lib/etcd/wal
$ sudo groupadd -r etcd
$ sudo useradd -r -g etcd -d /var/lib/etcd -s /sbin/nologin -c "etcd user" etcd
$ sudo chown -R etcd:etcd /var/lib/etcd

Buat berkas konfigurasi:

$ sudo vim /etc/etcd.conf

Isi seperti berikut:

# This is the configuration file for the etcd server.
# Human-readable name for this member.
name: 'etcd0-preko'

# Path to the data directory.
data-dir: /var/lib/etcd/data

# Path to the dedicated wal directory.
wal-dir: /var/lib/etcd/wal

# Number of committed transactions to trigger a snapshot to disk.
snapshot-count: 10000

# Time (in milliseconds) of a heartbeat interval.
heartbeat-interval: 100

# Time (in milliseconds) for an election to timeout.
election-timeout: 1000

# Raise alarms when backend size exceeds the given quota. 0 means use the
# default quota.
quota-backend-bytes: 0

# List of comma separated URLs to listen on for peer traffic.
listen-peer-urls: http://192.168.1.103:2380

# List of comma separated URLs to listen on for client traffic.
listen-client-urls: http://localhost:2379,http://192.168.1.103:2379

# Maximum number of snapshot files to retain (0 is unlimited).
max-snapshots: 5

# Maximum number of wal files to retain (0 is unlimited).
max-wals: 5

# Comma-separated white list of origins for CORS (cross-origin resource sharing).
cors:

# List of this member's peer URLs to advertise to the rest of the cluster.
# The URLs needed to be a comma-separated list.
initial-advertise-peer-urls: http://192.168.1.103:2380

# List of this member's client URLs to advertise to the public.
# The URLs needed to be a comma-separated list.
advertise-client-urls: http://192.168.1.103:2379

# Discovery URL used to bootstrap the cluster.
discovery:

# Valid values include 'exit', 'proxy'
discovery-fallback: 'proxy'

# HTTP proxy to use for traffic to discovery service.
discovery-proxy:

# DNS domain used to bootstrap initial cluster.
discovery-srv:

# Initial cluster configuration for bootstrapping.
initial-cluster: etcd0-preko=http://192.168.1.103:2380,

# Initial cluster token for the etcd cluster during bootstrap.
initial-cluster-token: 'etcd-preko-cluster'

# Initial cluster state ('new' or 'existing').
initial-cluster-state: 'new'

# Reject reconfiguration requests that would cause quorum loss.
strict-reconfig-check: false

# Valid values include 'on', 'readonly', 'off'
proxy: 'off'

# Time (in milliseconds) an endpoint will be held in a failed state.
proxy-failure-wait: 5000

# Time (in milliseconds) of the endpoints refresh interval.
proxy-refresh-interval: 30000

# Time (in milliseconds) for a dial to timeout.
proxy-dial-timeout: 1000

# Time (in milliseconds) for a write to timeout.
proxy-write-timeout: 5000

# Time (in milliseconds) for a read to timeout.
proxy-read-timeout: 0

# Enable debug-level logging for etcd.
debug: false

# Specify a particular log level for each etcd package (eg: 'etcdmain=CRITICAL,etcdserver=DEBUG'.
log-package-levels:

# Force to create a new one member cluster.
force-new-cluster: true

Buat service systemd:

$ sudo /etc/systemd/system/etcd.service

Isi seperti berikut:

[Unit]
Description=etcd key-value store
Documentation=https://github.com/coreos/etcd
After=network.target

[Service]
User=etcd
Group=etcd
Type=notify
ExecStart=/bin/bash --login -c "/usr/bin/etcd --config-file /etc/etcd.conf.yml"
Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target

Jalankan service Etcd melalui systemctl:

$ sudo systemctl daemon-reload
$ sudo systemctl start etcd
$ sudo systemctl enable etcd

PostgreSQL dan Patroni

Di VM1 dan VM2 pasang PostgreSQL dan Patroni

PostgreSQL

Penulis menggunakan PostgreSQL versi 9.6.

Tambahkan repo PostgreSQl:

$ sudo rpm -ivh https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Lanjut dengan memasang PostgreSQL 9.6:

$ sudo yum install postgresql96 postgresql96-server postgresql96-libs

Buat ssh key di node VM1 dan VM2 dengan user root

$ sudo su
# ssh-keygen -t rsa

Tambahkan publickey ssh antar ke-2 node

Patroni

Patroni merupakan script Python untuk menangani PostgreSQLHigh Availability. Sebelum memasang Patroni pastikan memasang semua dependensi yang dibutuhkan

$ sudo yum install gcc python-pip python-devel
$ sudo pip install --upgrade setuptools

Pasang Patroni

$ sudo pip install psycopg2-binary
$ sudo pip install patroni[etcd]
$ sudo pip install patroni[consul]

Konfigurasi

Buat direktori untuk Patroni

$ sudo mkdir -p /var/lib/pgsql/9.6-patroni
$ chown -R postgres:postgres /var/lib/pgsql/9.6-patroni/

Buat berkas konfigurasi

$ sudo vim /etc/patroni.yml

Isi seperti berikut

scope: postgres
namespace: /pg-preko/
name: pg1-preko

restapi:
    listen: 1192.168.1.101:8008
    connect_address: 192.168.1.101:8008

etcd:
    host: 192.168.1.103: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 192.168.1.101/0 md5
    - host replication replicator 192.168.1.102/0 md5
    - host all all 0.0.0.0/0 md5

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

postgresql:
    listen: 192.168.1.101:5432
    connect_address: 192.168.1.101:5432
    bin_dir: /usr/pgsql-9.6/bin
    data_dir: /var/lib/pgsql/9.6-patroni
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: passwd-replication
        superuser:
            username: postgres
            password: postgres
    parameters:
        unix_socket_directories: '.'

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

Buat service systemd

$ sudo vim /etc/systemd/system/patroni.service

Isi seperti berikut:

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

[Service]
Type=simple

User=postgres
Group=postgres

Environment="PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games"
ExecStart=/bin/patroni /etc/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no

[Install]
WantedBy=multi-user.target

Jalankan Patroni

$ sudo systemctl daemon-reload
$ sudo systemctl start patroni
$ sudo systemctl enable patroni

Catatan: Konfigurasi untuk patroni terkait nama dan ip sesuaikan per node

HAProxy

Pasang HAProxy versi terbaru. Sebelum memasang HAProxy pasang terlebih dahulu dependensi yang dibutuhkan

$ sudo yum install -y make gcc perl pcre-devel zlib-devel openssl-devel

Lanjut dengan compile HAProxy

$ wget http://www.haproxy.org/download/1.8/src/haproxy-1.8.12.tar.gz
$ tar zxvf haproxy-1.8.12.tar.gz
$ cd haproxy-1.8.12/
$ make TARGET=generic ARCH=native CPU=x86_64 -j8 USE_LINUX_TPROXY=1 USE_ZLIB=1 USE_REGPARM=1 USE_PCRE=1 USE_PCRE_JIT=1     USE_OPENSSL=1 SSL_INC=/usr/include SSL_LIB=/usr/lib ADDLIB=-ldl     CFLAGS="-O2 -g -fno-strict-aliasing -DTCP_USER_TIMEOUT=18"
$ sudo make install

Buat service systemd

$ sudo vim /etc/systemd/system/haproxy.service

Isi seperti berikut:

# File located at /etc/systemd/system/haproxy.service for CentOS 7
# Reference https://github.com/horms/haproxy/blob/master/contrib/systemd/haproxy.service.in
[Unit]
Description=HAProxy Load Balancer
After=network.target

[Service]
ExecStartPre=/usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg -c -q
ExecStart=/usr/local/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
ExecReload=/bin/kill -USR2 $MAINPID
KillMode=mixed
Restart=always

[Install]
WantedBy=multi-user.target

Jangan lupa untuk membuat berkas konfigurasi

$ sudo vim /etc/haproxy/haproxy.conf

Isi seperti berikut:

global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen postgres
    bind *:5432
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
    server postgresql_192.168.1.101_5432 192.168.1.101:5432 maxconn 100 check port 8008
    server postgresql_192.168.1.102_5432 192.168.1.102:5432 maxconn 100 check port 8008

Terkhir, tambahkan user untuk HAProxy

$ sudo useradd -r haproxy

Jalankan HAProxy

$ sudo systemctl daemon-reload
$ sudo systemctl start haproxy
$ sudo systemctl enable haproxy