본 문서에서는 OpenProxy의 Connection Pooling, 로드밸런싱, 가상 IP 및 이중화 구성에 대한 설명과 Configuration 파라미터를 통해 해당 기능을 관리하는 방법에 대하여 기술합니다.
Starting OpenProxy with config: /opt/opensql/etc/openproxy.toml
OpenProxy started with PID 18472bash $OPENSQL_HOME/scripts/stop_openproxy.shStopping OpenProxy (PID: 18472)
OpenProxy stoppedbash $OPENSQL_HOME/scripts/restart_openproxy.shRestarting OpenProxy...
Stopping OpenProxy (PID: 18472)
OpenProxy stopped.
Starting OpenProxy with config: /opt/opensql/etc/openproxy.toml
OpenProxy started with PID 18531bash $OPENSQL_HOME/scripts/reload_openproxy.shReloading OpenProxy configuration (PID: 18531)...
OpenProxy configuration reloaded successfully.## Patroni 설정 예시
postgresql:
pg_hba:
## OpenProxy 대역에 대해서 인증 없이 접속을 허용하는 trust 옵션을 적용합니다.
- host all all 172.18.65.0/24 trust[pools.simple_db.users.0]
username = "simple_user"
password = "simple_user"
pool_size = 5
statement_timeout = 30000[pools.simple_db.users.1]
username = "*"
pool_size = 3
statement_timeout = 5000[pools.simple_db.shards.0]
servers = [
[ "opensql1", 5432, "Auto", ],
[ "opensql2", 5432, "Auto", ],
[ "opensql3", 5432, "Auto", ],
]
database = "some_db"
use_patroni = true[pools.simple_db]
pool_mode = "session"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true
sharding_function = "pg_bigint_hash"[general.default_pool]
pool_mode = "transaction"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true
auth_query = "SELECT usename, passwd FROM pg_shadow WHERE usename = '$1'"
auth_query_user = "myuser"
auth_query_password = "mypassword"[pools.simple_db]
pool_mode = "session"
default_role = "primary"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true
sharding_function = "pg_bigint_hash"
prepared_statements_cache_size = 500
[pools.simple_db.users.0]
username = "simple_user"
password = "simple_user"
pool_size = 5
statement_timeout = 30000
[pools.simple_db.shards.0]
servers = [
[ "opensql1", 5432, "Auto", ],
[ "opensql2", 5432, "Auto", ],
[ "opensql3", 5432, "Auto", ],
]
database = "some_db"
use_patroni = trueopenproxy=> show pools;
database | user | pool_mode | cl_idle | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us
------------+---------------+-------------+---------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------
simple_db | simple_user | session | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 rows)
openproxy=> show databases;
name | host | port | database | force_user | pool_size | min_pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled
------------------------------+-------------+------+----------+---------------+-----------+---------------+--------------+-------------+-----------------+---------------------+--------+----------
simple_db_shard_0_replica_0 | 178.176.0.4 | 5432 | some_db | simple_user | 5 | 0 | 0 | session | 5 | 0 | 0 | 0
simple_db_shard_0_replica_1 | 178.176.0.2 | 5432 | some_db | simple_user | 5 | 0 | 0 | session | 5 | 0 | 0 | 0
simple_db_shard_0_primary | 178.176.0.3 | 5432 | some_db | simple_user | 5 | 0 | 0 | session | 5 | 0 | 0 | 0
(3 rows)[general.virtual_router]
interface = "eno1"
router_id = 50
priority = 150
advert_int = 3
vip_addresses = [ "192.168.0.200/24" ]
pre_promote_script = "/home/opensql/startup.sh"
pre_demote_script = "/home/opensql/cleanup.sh"
unicast_peers = [ "192.168.0.7", "192.168.0.8" ][general]
renew_interval = 5000 ## 설정파일을 읽어 Config을 업데이트하거나, Patroni 서버에 질의하여
## Postgres 서버 Role을 업데이트 할 주기를 설정할 수 있습니다.
## 단위는 밀리초 (milliseconds) 이며 기본값은 5000 입니다.
[pools.my_pool]
[pools.my_pool.shards.0]
servers = [
[
"192.168.0.8", ## Patroni REST API 서버가 구동중인 호스트들의 IPv4 주소를 입력합니다.
5432, ## PostgreSQL 서버의 Port 번호로 Patroni 연동 시에는 무시됩니다.
"Auto", ## PostgreSQL 노드의 Role 값으으로 Patroni 연동 시에는 무시됩니다.
],
[
"192.168.0.9",
5432,
"Auto",
],
[
"192.168.0.10",
5432,
"Auto",
]
]
database = "postgres"
use_patroni = true
patroni_port = "8008" ## Patroni의 HTTP REST API 서버의 Port 번호를 지정합니다.
## 비어있는 경우 기본값 8008 이 사용됩니다.$ sudo setcap 'cap_net_raw=eip cap_net_admin=eip' openproxy[Service]
AmbientCapabilities=CAP_NET_RAW CAP_NET_ADMIN[pools.my_pool]
pool_mode = "transaction"
query_parser_enabled = true
query_parser_read_write_splitting = true[pools.my_pool]
pool_mode = "transaction"
query_parser_enabled = true
query_parser_read_write_splitting = true
primary_reads_enabled = true[pools.my_pool]
load_balancing_mode = "random" ## "random", "loc"[pools.my_pool]
pool_mode = "transaction"
prepared_statements_cache_size = 1000#!/bin/bash
CFSSL_VERSION=1.6.5
CFSSL_PATH=/usr/local/bin
ARCH=amd64
curl -L "https://github.com/cloudflare/cfssl/releases/download/v${CFSSL_VERSION}/cfssl_${CFSSL_VERSION}_linux_${ARCH}" -o cfssl
curl -L "https://github.com/cloudflare/cfssl/releases/download/v${CFSSL_VERSION}/cfssljson_${CFSSL_VERSION}_linux_${ARCH}" -o cfssljson
curl -L "https://github.com/cloudflare/cfssl/releases/download/v${CFSSL_VERSION}/cfssl-certinfo_${CFSSL_VERSION}_linux_${ARCH}" -o cfssl-certinfo
chmod +x cfssl cfssljson cfssl-certinfo
sudo cp cfssl cfssljson cfssl-certinfo ${CFSSL_PATH}/ETCD_CLIENT_CERT_AUTH=true$ vim Makefile
.PHONY: cfssl ca req clean
CFSSL = @env PATH=$(GOPATH)/bin:$(PATH) cfssl
JSON = env PATH=$(GOPATH)/bin:$(PATH) cfssljson
all: ca req
cfssl:
HTTPS_PROXY=127.0.0.1:12639 go get -u -tags nopkcs11 github.com/cloudflare/cfssl/cmd/cfssl
HTTPS_PROXY=127.0.0.1:12639 go get -u github.com/cloudflare/cfssl/cmd/cfssljson
HTTPS_PROXY=127.0.0.1:12639 go get -u github.com/mattn/goreman
ca:
mkdir -p certs
$(CFSSL) gencert -initca config/ca-csr.json | $(JSON) -bare certs/etcd-ca
req:
$(CFSSL) gencert \
-ca certs/etcd-ca.pem \
-ca-key certs/etcd-ca-key.pem \
-config config/ca-config.json \
config/req-csr.json | $(JSON) -bare certs/${infra0}
$(CFSSL) gencert \
-ca certs/etcd-ca.pem \
-ca-key certs/etcd-ca-key.pem \
-config config/ca-config.json \
config/req-csr.json | $(JSON) -bare certs/${infra1}
$(CFSSL) gencert \
-ca certs/etcd-ca.pem \
-ca-key certs/etcd-ca-key.pem \
-config config/ca-config.json \
config/req-csr.json | $(JSON) -bare certs/${infra2}
$(CFSSL) gencert \
-ca certs/etcd-ca.pem \
-ca-key certs/etcd-ca-key.pem \
-config config/ca-config.json \
config/req-csr.json | $(JSON) -bare certs/${infra0}-peer
$(CFSSL) gencert \
-ca certs/etcd-ca.pem \
-ca-key certs/etcd-ca-key.pem \
-config config/ca-config.json \
config/req-csr.json | $(JSON) -bare certs/${infra1}-peer
$(CFSSL) gencert \
-ca certs/etcd-ca.pem \
-ca-key certs/etcd-ca-key.pem \
-config config/ca-config.json \
config/req-csr.json | $(JSON) -bare certs/${infra2}-peer
clean:
rm -rf certs$ vim config/req-csr.json
{
"CN": "etcd", # 삭제
"hosts": [
"localhost",
"127.0.0.1",
"node1",
"node2",
"node3",
"172.18.0.2",
"172.18.0.3",
"172.18.0.4"
],
"key": {
"algo": "ecdsa",
"size": 384
},
"names": [
{
"O": "autogenerated",
"OU": "etcd cluster",
"L": "the internet"
}
]
}$ vim config/ca-csr.json
{
"CN": "Autogenerated CA", # 삭제
"key": {
"algo": "rsa",
"size": 2048
},
"names": [
{
"O": "TmaxTibero",
"OU": "OpenSQL",
"L": "Seongnam-si",
"ST": "Gyeonggi-do",
"C": "KR"
}
]
}$ infra0=node1 infra1=node2 infra2=node3 make$ ls -l
total 84
-rw-r--r-- 1 opensql opensql 985 1월 6 18:10 etcd-ca.csr
-rw------- 1 opensql opensql 1679 1월 6 18:10 etcd-ca-key.pem
-rw-rw-r-- 1 opensql opensql 1281 1월 6 18:10 etcd-ca.pem
-rw-r--r-- 1 opensql opensql 623 1월 6 18:10 node1.csr
-rw------- 1 opensql opensql 288 1월 6 18:10 node1-key.pem
-rw-rw-r-- 1 opensql opensql 1196 1월 6 18:10 node1.pem
-rw-r--r-- 1 opensql opensql 623 1월 6 18:10 node2.csr
-rw------- 1 opensql opensql 288 1월 6 18:10 node2-key.pem
-rw-rw-r-- 1 opensql opensql 1196 1월 6 18:10 node2.pem
-rw-r--r-- 1 opensql opensql 623 1월 6 18:10 node3.csr
-rw------- 1 opensql opensql 288 1월 6 18:10 node3-key.pem
-rw-rw-r-- 1 opensql opensql 1196 1월 6 18:10 node3.pem
-rw-r--r-- 1 opensql opensql 623 1월 6 18:10 node1-peer.csr
-rw------- 1 opensql opensql 288 1월 6 18:10 node1-peer-key.pem
-rw-rw-r-- 1 opensql opensql 1196 1월 6 18:10 node1-peer.pem
-rw-r--r-- 1 opensql opensql 623 1월 6 18:10 node2-peer.csr
-rw------- 1 opensql opensql 288 1월 6 18:10 node2-peer-key.pem
-rw-rw-r-- 1 opensql opensql 1196 1월 6 18:10 node2-peer.pem
-rw-r--r-- 1 opensql opensql 623 1월 6 18:10 node3-peer.csr
-rw------- 1 opensql opensql 288 1월 6 18:10 node3-peer-key.pem
-rw-rw-r-- 1 opensql opensql 1196 1월 6 18:10 node3-peer.pem$ vim $OPENSQL_HOME/etc/etcd.env
#$OPENSQL_HOME/etc/etcd.env
## 아래 내용을 추가하여 허용할 인증서의 인증기관과 공개키 - 사설키를 각각 등록합니다.
## 위치한 .pem 인증서는 etcd 프로세스를 시작할 사용자가 읽기 권한을 가진 파일이어야 합니다.
#Cert
ETCD_TRUSTED_CA_FILE=$OPENSQL_HOME/etc/pki/etcd-ca.pem
ETCD_CERT_FILE=$OPENSQL_HOME/etc/pki/node3.pem
ETCD_KEY_FILE=$OPENSQL_HOME/etc/pki/node3-key.pem
ETCD_PEER_TRUSTED_CA_FILE=$OPENSQL_HOME/etc/pki/etcd-ca.pem
ETCD_PEER_CERT_FILE=$OPENSQL_HOME/etc/pki/node3-peer.pem
ETCD_PEER_KEY_FILE=$OPENSQL_HOME/etc/pki/node3-peer-key.pem#$OPENSQL_HOME/etc/etcd.env
# ...
ETCD_ADVERTISE_CLIENT_URLS=https://172.18.0.2:2379
ETCD_LISTEN_CLIENT_URLS=https://172.18.0.2:2379,http://127.0.0.1:2379
# ...
#Certs
ETCD_TRUSTED_CA_FILE=$OPENSQL_HOME/etc/pki/etcd-ca.pem
ETCD_CERT_FILE=$OPENSQL_HOME/etc/pki/node1.pem
ETCD_KEY_FILE=$OPENSQL_HOME/etc/pki/node1-key.pem
ETCD_PEER_TRUSTED_CA_FILE=$OPENSQL_HOME/etc/pki/etcd-ca.pem
ETCD_PEER_CERT_FILE=$OPENSQL_HOME/etc/pki/node1-peer.pem
ETCD_PEER_KEY_FILE=$OPENSQL_HOME/etc/pki/node1-peer-key.pem-w, --write-out : 출력값의 Format을 설정하는 옵션으로 fields, json, protobuf, simple, table 을 허용합니다.$ etcdctl member list
Error: dial tcp 127.0.0.1:2379: connect: connection refused$ ETCDCTL_API=3 etcdctl --endpoints="https://192.168.0.10:2379,https://192.168.0.11:2379" \
--key="./etcd-client-key.pem" \
--cert="./etcd-client-crt.pem" \
--cacert="./etcd-ca-crt.pem" \
member list$ etcdctl member list
670b863301943618, started, node1, http://192.168.0.10:2380, http://192.168.0.10:2379, false
7825d7b04510b842, started, node3, http://192.168.0.11:2380, http://192.168.0.11:2379, false
c8245114d55ec576, started, node2, http://192.168.0.12:2380, http://192.168.0.12:2379, false
$ etcdctl member list -w table
+------------------+---------+-------+--------------------------+--------------------------+------------+
| ID | STATUS | NAME | PEER ADDRS | CLIENT ADDRS | IS LEARNER |
+------------------+---------+-------+----------------------------+------------------------+------------+
| 670b863301943618 | started | node1 | http://192.168.0.10:2380 | http://192.168.0.10:2379 | false |
| 7825d7b04510b842 | started | node3 | http://192.168.0.11:2380 | http://192.168.0.11:2379 | false |
| c8245114d55ec576 | started | node2 | http://192.168.0.12:2380 | http://192.168.0.12:2379 | false |
+------------------+---------+-------+----------------------------+------------------------+------------+$ etcdctl endpoint status -w table
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| http://192.168.0.10:2379 | 670b863301943618 | 3.5.21 | 168 kB | true | false | 6 | 520 | 520 | |
| http://192.168.0.11:2379 | c8245114d55ec576 | 3.5.21 | 168 kB | false | false | 6 | 520 | 520 | |
| http://192.168.0.12:2379 | 7825d7b04510b842 | 3.5.21 | 168 kB | false | false | 6 | 520 | 520 | |
+--------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+$ etcdctl get --prefix "/opensql/opensql/members" -w simple
/opensql/opensql/members/pg-1
{"conn_url":"postgres://192.168.131.12:5432/postgres","api_url":"http://192.168.131.12:8008/patroni","state":"running","role":"replica","version":"4.0.5","proxy_url":"postgres://192.168.131.15:6432/postgres","xlog_location":223510016,"replication_state":"streaming","timeline":3}
/opensql/opensql/members/pg-2
{"conn_url":"postgres://192.168.131.13:5432/postgres","api_url":"http://192.168.131.13:8008/patroni","state":"running","role":"primary","version":"4.0.5","proxy_url":"postgres://192.168.131.15:6432/postgres","xlog_location":223510016,"timeline":3}
/opensql/opensql/members/pg-3
{"conn_url":"postgres://192.168.131.14:5432/postgres","api_url":"http://192.168.131.14:8008/patroni","state":"running","role":"replica","version":"4.0.5","proxy_url":"postgres://192.168.131.15:6432/postgres","xlog_location":223510016,"replication_state":"streaming","timeline":3}$ etcdctl get "/opensql/opensql/members/pg-1" "/opensql/opensql/members/pg-3"
/opensql/opensql/members/pg-1
{"conn_url":"postgres://192.168.131.12:5432/postgres","api_url":"http://192.168.131.12:8008/patroni","state":"running","role":"replica","version":"4.0.5","proxy_url":"postgres://192.168.131.15:6432/postgres","xlog_location":223510016,"replication_state":"streaming","timeline":3}
/opensql/opensql/members/pg-2
{"conn_url":"postgres://192.168.131.13:5432/postgres","api_url":"http://192.168.131.13:8008/patroni","state":"running","role":"primary","version":"4.0.5","proxy_url":"postgres://192.168.131.15:6432/postgres","xlog_location":223510016,"timeline":3}
$ etcdctl get "/opensql/opensql/failover" "/opensql/opensql/historz"
/opensql/opensql/failover
{}
/opensql/opensql/history
[[1,223438192,"no recovery target specified","2025-04-18T14:27:03.269756+09:00","pg-1"],[2,223438824,"no recovery target specified","2025-04-21T15:13:37.632010+09:00","pg-2"]]## Endpoint Status를 확인해 각 노드에서 유지하고 있는 가장 최신 Revision 정보를 가져온다.
$ etcdctl endpoint status -w json | jq | grep 'revision'
"revision": 447871,
"revision": 447871,
"revision": 447871,
## 해당 Revision 이전 시점의 Revision들을 더 이상 참조되지 않는 상태로 지정한다.
$ rev=447871
$ etcdctl compact $rev
compacted revision 447871$ etcdctl defrag --endpoints "http://192.168.0.10:2379,http://192.168.0.11:2379,http://192.168.0.12:2379"
Finished defragmenting etcd member[http://192.168.0.10:2379]
Finished defragmenting etcd member[http://192.168.0.11:2379]
Finished defragmenting etcd member[http://192.168.0.12:2379]
$ etcdctl endpoint status --endpoints "..."
http://192.168.0.10:2379, 670b863301943618, 3.5.21, 25 kB, true, false, 8, 448000, 448000,
http://192.168.0.11:2379, c8245114d55ec576, 3.5.21, 25 kB, false, false, 8, 448000, 448000,
http://192.168.0.12:2379, 7825d7b04510b842, 3.5.21, 25 kB, false, false, 8, 448000, 448000,$ etcdctl check perf
60 / 60 Booooooooooooooooooooooooooooooooooooooooooooooooooooooom! 100.00% 1m0s
PASS: Throughput is 150 writes/s
PASS: Slowest request took 0.291186s
PASS: Stddev is 0.029467s
PASS$ etcdctl check perf
60 / 60 Booooooooooooooooooooooooooooooooooooooooooooooooooooooom! 100.00% 1m0s
PASS: Throughput is 150 writes/s
Slowest request took too long: 0.535645s
PASS: Stddev is 0.079037s
FAIL
$ etcdctl check perf --load="xl"
60 / 60 Booooooooooooooooooooooooooooooooooooooooooooooooooooooom! 100.00% 1m0s
FAIL: Throughput too low: 3668 writes/s
Slowest request took too long: 0.645609s
Stddev too high: 0.105516s
FAIL$ ls -l $ETCD_DATA_DIR/member/snap/
total 168
-rw-------. 1 opensql opensql 16805888 Apr 18 14:29 db
$ cp $ETCD_DATA_DIR/member/snap/db ./mysnapshot.db$ ETCDCTL_API=3 etcdctl --endpoints=${ENDPOINT} snapshot save mysnapshot.db
{"level":"info","ts":"2025-04-21T12:18:30.406283+0900","caller":"snapshot/v3_snapshot.go:65","msg":"created temporary db file","path":"mysnapshot.db.part"}
{"level":"info","ts":"2025-04-21T12:18:30.407142+0900","logger":"client","caller":"v3@v3.5.21/maintenance.go:212","msg":"opened snapshot stream; downloading"}
{"level":"info","ts":"2025-04-21T12:18:30.407162+0900","caller":"snapshot/v3_snapshot.go:73","msg":"fetching snapshot","endpoint":"192.168.131.12:2379"}
{"level":"info","ts":"2025-04-21T12:18:30.432538+0900","logger":"client","caller":"v3@v3.5.21/maintenance.go:220","msg":"completed snapshot read; closing"}
{"level":"info","ts":"2025-04-21T12:18:30.480983+0900","caller":"snapshot/v3_snapshot.go:88","msg":"fetched snapshot","endpoint":"192.168.131.12:2379","size":"168 kB","took":"now"}
{"level":"info","ts":"2025-04-21T12:18:30.481039+0900","caller":"snapshot/v3_snapshot.go:97","msg":"saved","path":"mysnapshot.db"}
Snapshot saved at mysnapshot.db$ ETCDCTL_API=3 etcdctl snapshot restore ./mysnapshot.db \
--name node3 \
--initial-cluster node1=http://192.168.0.8:2380,node2=http://192.168.0.9:2380,node3=http://192.168.0.10:2380 \
--initial-cluster-token new-etcd-cluster \
--initial-advertise-peer-urls http://192.168.0.10:2380 \
--skip-hash-check \
# ...
Deprecated: Use `etcdutl snapshot restore` instead.
snapshot/v3_snapshot.go:248 restoring snapshot {"path": "member/snap/db", "wal-dir": "node3.etcd/member/wal", "data-dir": "node3.etcd", "snap-dir": "node3.etcd/member/snap", "stack": "go.etcd.io/..." }
membership/store.go:141 Trimming membership information from the backend...
membership/cluster.go:421 added member {"cluster-id": "154dfe96307df6f0", "local-member-id": "0", "added-peer-id": "3dfe6fc7fff49d22", "added-peer-peer-urls": ["http://192.168.0.8:2380"]}
membership/cluster.go:421 added member {"cluster-id": "154dfe96307df6f0", "local-member-id": "0", "added-peer-id": "7f846315e3b9872d", "added-peer-peer-urls": ["http://192.168.0.9:2380"]}
membership/cluster.go:421 added member {"cluster-id": "154dfe96307df6f0", "local-member-id": "0", "added-peer-id": "c0ea9022befd3eaa", "added-peer-peer-urls": ["http://192.168.0.10:2380"]}
snapshot/v3_snapshot.go:269 restored snapshot {"path": "member/snap/db", "wal-dir": "node3.etcd/member/wal", "data-dir": "node3.etcd", "snap-dir": "node3.etcd/member/snap"}$ ls -l
total 0
drwx------. 3 root root 20 Mar 17 14:48 node1.etcd
$ ls -l node1.etcd/
drwx------. 2 root root 246 Mar 17 14:41 snap
drwx------. 2 root root 257 Mar 17 14:41 wal$ rm -rf $ETCD_DATA_DIR/member
$ cp -r node1.etcd $ETCD_DATA_DIR/member
## Service 정의에 참조된 etcd.env 파일 내용 확인
$ vi $OPENSQL_HOME/etc/etcd/etcd.env
ETCD_NAME=node1
ETCD_INITIAL_CLUSTER=node1=http://192.168.0.8:2380,node2=http://192.168.0.9:2380,node3=http://192.168.0.10:2380
ETCD_INITIAL_CLUSTER_TOKEN=new-etcd-cluster
ETCD_INITIAL_CLUSTER_STATE=new
--initial-cluster-token new-etcd-cluster \
--initial-advertise-peer-urls http://192.168.0.8:2380 \
$ systemctl restart etcd.servicescope: batman
#namespace: /service/
name: postgresql0log:
type: plain
format: "[%(asctime)s] [%(module)s] [%(levelname)s]: %(message)s"
dir: /etc/patroni/logsrestapi:
listen: 0.0.0.0:8008
connect_address: 192.168.0.100:8008etcd3:
protocol: http
# host: 192.168.0.100:2379
hosts:
- 192.168.0.1:2379
- 192.168.0.2:2379
- 192.168.0.3:2379bootstrap:
# 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`.
# WARNING! If you want to change any of the parameters that were set up
# via `bootstrap.dcs` section, please use `patronictl edit-config`!
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
# primary_start_timeout: 300
# synchronous_mode: false
#standby_cluster:
#host: 127.0.0.1
#port: 1111
#primary_slot_name: patroni
slots:
barman:
type: physical
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
# wal_level: hot_standby
# hot_standby: "on"
max_connections: 100
max_worker_processes: 8
# wal_keep_segments: 8
# max_wal_senders: 10
# max_replication_slots: 10
# max_prepared_transactions: 0
# max_locks_per_transaction: 64
# wal_log_hints: "on"
# track_commit_timestamp: "off"
# archive_mode: "on"
# archive_timeout: 1800s
# archive_command: mkdir -p ../wal_archive && test ! -f ../wal_archive/%f && cp %p ../wal_archive/%f
# recovery_conf:
# restore_command: cp ../wal_archive/%f %p
# some desired options for 'initdb'
initdb: # Note: It needs to be a list (some options need values, others are switches)
- encoding: UTF8
- data-checksumspostgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.0.100:5432
proxy_address: 127.0.0.1:6432 # The address of connection pool (e.g., pgbouncer) running next to Patroni/Postgres. Only for service discovery.
#data_dir: data/postgresql0
data_dir: /var/lib/pgsql/16/data
bin_dir: /usr/pgsql-16/bin
# config_dir:
pgpass: /tmp/pgpass0
authentication:
replication:
username: patroni_repl
password: patroni_repl
superuser:
username: postgres
password: zalando
rewind: # Has no effect on postgres 10 and lower
username: patroni_rewind
password: patroni_rewind
pg_hba:
# For kerberos gss based connectivity (discard @.*$)
- local all all trust
- host replication patroni_repl 192.168.0.0/24 trust
- host replication patroni_repl 127.0.0.1/32 trust
- host all all 0.0.0.0/0 md5
- host all barman 192.168.0.0/24 trust
- host replication streaming_barman 192.168.0.0/24 trust
parameters:
log_line_prefix: '%m [%r] [%u] [%a]'
archive_command: 'barman-wal-archive node4 pg %p'
archive_mode: 'true'
wal_level: 'replica'$ patronictl list
2024-10-29 17:20:56,603 - WARNING - Listing members: No cluster names were provided
$ patronictl list opensql
Error: Can not find suitable configuration of distributed configuration store
Available implementations: etcd, etcd3, kubernetes$ patronictl -c $OPENSQL_HOME/etc/patroni.yml list
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 16 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 16 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 16 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+$ alias pctl='patronictl -c $OPENSQL_HOME/etc/patroni.yml'
$ echo 'alias pctl="patronictl -c $OPENSQL_HOME/etc/patroni.yml"' >> ~/.bashrc
$ pctl list
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 16 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 16 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 16 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+$ patronictl list
2024-10-29 15:36:15,282 - WARNING - Listing members: No cluster names were provided
## 테이블 형태로 조회 (기본 옵션)
$ patronictl -c $OPENSQL_HOME/etc/patroni.yml list
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 16 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 16 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 16 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
## JSON 형태로 조회
$ patronictl -c $OPENSQL_HOME/etc/patroni.yml list -f json
[{"Cluster": "opensql", "Member": "postgresql0", "Host": "192.1.1.218", "Role": "Leader", "State": "running", "TL": 17}, {"Cluster": "opensql", "Member": "postgresql1", "Host": "192.1.1.236", "Role": "Replica", "State": "streaming", "TL": 17, "Lag in MB": 0, "Tags": {"nofailover": false, "failover_priority": 150}}, {"Cluster": "opensql", "Member": "postgresql2", "Host": "192.1.1.238", "Role": "Replica", "State": "streaming", "TL": 17, "Lag in MB": 0}]$ patronictl -c $OPENSQL_HOME/etc/patroni.yml topology
+ Cluster: opensql (7364637789542980847) +-----------+----+-----------+---------------------------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+---------------+-------------+---------+-----------+----+-----------+---------------------------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 21 | | |
| + postgresql0 | 192.1.1.218 | Replica | streaming | 21 | 0 | |
| + postgresql1 | 192.1.1.236 | Replica | streaming | 21 | 0 | {failover_priority: 150, nofailover: false} |
+---------------+-------------+---------+-----------+----+-----------+---------------------------------------------+$ patronictl -c $OPENSQL_HOME/etc/patroni.yml dsn
host=192.1.1.238 port=5432$ patronictl -c $OPENSQL_HOME/etc/patroni.yml dsn -r replica
host=192.1.1.218 port=5432
$ patronictl -c $OPENSQL_HOME/etc/patroni.yml dsn -m postgresql1
host=192.1.1.236 port=5432$ pctl restart <cluster_name>$ pctl restart opensql postgresql0
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 16 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 16 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 16 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
When should the restart take place (e.g. 2024-10-31T12:16) [now]:
## now를 입력하면 바로 재시작
Are you sure you want to restart members postgresql0? [y/N]:
Restart if the PostgreSQL version is less than provided (e.g. 9.5.2) []:
Success: restart on member postgresql0$ pctl reload <cluster_name>$ pctl reload opensql
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 16 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 16 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 16 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
Are you sure you want to reload members postgresql0, postgresql1, postgresql2? [y/N]:
Reload request received for member postgresql0 and will be processed within 10 seconds
Reload request received for member postgresql1 and will be processed within 10 seconds
Reload request received for member postgresql2 and will be processed within 10 seconds$ pctl history
+----+------------+------------------------------+----------------------------------+-------------+
| TL | LSN | Reason | Timestamp | New Leader |
+----+------------+------------------------------+----------------------------------+-------------+
| 1 | 26875256 | no recovery target specified | 2024-05-03T14:20:28.841738+09:00 | postgresql2 |
| 2 | 213072680 | no recovery target specified | 2024-05-03T14:45:37.945208+09:00 | postgresql1 |
| 3 | 213101064 | no recovery target specified | 2024-05-03T14:46:14.686504+09:00 | postgresql2 |
| 4 | 805306528 | no recovery target specified | 2024-05-28T17:44:02.808722+09:00 | postgresql1 |
| 5 | 855638176 | no recovery target specified | 2024-05-28T17:54:12.358175+09:00 | postgresql2 |
| 6 | 1879048352 | no recovery target specified | 2024-07-30T15:58:10.527327+09:00 | postgresql0 |
| 7 | 1895825568 | no recovery target specified | 2024-07-30T15:58:52.408275+09:00 | postgresql2 |
| 8 | 2013266080 | no recovery target specified | 2024-08-12T10:00:38.641449+09:00 | postgresql2 |
| 9 | 2030043296 | no recovery target specified | 2024-08-12T10:04:16.370771+09:00 | postgresql2 |
| 10 | 2046820512 | no recovery target specified | 2024-08-12T10:05:07.178679+09:00 | postgresql2 |
| 11 | 2063597728 | no recovery target specified | 2024-08-12T10:47:06.368795+09:00 | postgresql2 |
| 12 | 2080374944 | no recovery target specified | 2024-08-12T10:50:59.596850+09:00 | postgresql2 |
| 13 | 2332033184 | no recovery target specified | 2024-10-28T13:41:28.936064+09:00 | postgresql1 |
| 14 | 2348810400 | no recovery target specified | 2024-10-28T16:04:27.587725+09:00 | postgresql0 |
| 15 | 2365587616 | no recovery target specified | 2024-10-28T16:16:32.842442+09:00 | postgresql2 |
+----+------------+------------------------------+----------------------------------+-------------+$ patronictl -c $OPENSQL_HOME/etc/patroni.yml query -U postgres --password -c "SELECT VERSION();"
Password:
version
PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit$ patronictl -c $OPENSQL_HOME/etc/patroni.yml failover
Current cluster topology
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 20 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Leader | running | 20 | | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Replica | streaming | 20 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
Candidate ['postgresql0', 'postgresql2'] []: postgresql2
Are you sure you want to failover cluster opensql, demoting current leader postgresql1? [y/N]: y
2024-11-04 16:36:12.20700 Successfully failed over to "postgresql2"
+ Cluster: opensql (7364637789542980847) --------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+---------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | running | 20 | 0 | |
+-------------+-------------+---------+---------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | stopped | | unknown | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+---------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 20 | | |
+-------------+-------------+---------+---------+----+-----------+------------------------+$ patronictl -c $OPENSQL_HOME/etc/patroni.yml switchover
Current cluster topology
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Leader | running | 19 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 19 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Replica | streaming | 19 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
Primary [postgresql0]: postgresql0
Candidate ['postgresql1', 'postgresql2'] []: postgresql1
When should the switchover take place (e.g. 2024-11-04T17:34 ) [now]: now
Are you sure you want to switchover cluster opensql, demoting current leader postgresql0? [y/N]: y
2024-11-04 16:35:07.34291 Successfully switched over to "postgresql1"
+ Cluster: opensql (7364637789542980847) --------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+---------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | stopped | | unknown | |
+-------------+-------------+---------+---------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Leader | running | 19 | | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+---------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Replica | running | 19 | 0 | |
+-------------+-------------+---------+---------+----+-----------+------------------------+$ patronictl -c $OPENSQL_HOME/etc/patroni.yml pause
Success: cluster management is paused
$ patronictl -c $OPENSQL_HOME/etc/patroni.yml list
+ Cluster: opensql (7364637789542980847) ----------+----+-----------+------------------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql0 | 192.1.1.218 | Replica | streaming | 25 | 0 | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql1 | 192.1.1.236 | Replica | streaming | 25 | 0 | failover_priority: 150 |
| | | | | | | nofailover: false |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
| postgresql2 | 192.1.1.238 | Leader | running | 25 | | |
+-------------+-------------+---------+-----------+----+-----------+------------------------+
Maintenance mode: on$ patronictl -c $OPENSQL_HOME/etc/patroni.yml resume
Success: cluster management is resumed$ patronictl -c $OPENSQL_HOME/etc/patroni.yml show-config
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
archive_command: barman-wal-archive node4 pg %p
archive_mode: 'true'
authentication_timeout: '200'
log_line_prefix: '%m [%r] [%u] [%a]'
max_connections: '250'
wal_level: replica
wal_receiver_timeout: '30000'
pg_hba:
- local all all trust
- host replication patroni_repl 192.1.1.218/26 trust
- host replication patroni_repl 127.0.0.1/32 trust
- host all all 0.0.0.0/0 md5
- host all barman 192.1.1.218/26 trust
- host replication streaming_barman 192.1.1.218/26 trust
use_pg_rewind: true
use_slots: true
retry_timeout: 10
slots:
barman:
type: physical
ttl: 30$ pctl edit-configloop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
parameters:
archive_command: barman-wal-archive node4 pg %p
archive_mode: 'false'
authentication_timeout: '500'
log_line_prefix: '%m [%r] [%u] [%a]'
max_connections: 500
wal_level: replica
wal_receiver_timeout: '30000'
pg_hba:
- local all all trust
- host replication patroni_repl 192.1.1.218/26 trust
- host replication patroni_repl 127.0.0.1/32 trust
- host all all 0.0.0.0/0 md5
- host all barman 192.1.1.218/26 trust
- host replication streaming_barman 192.1.1.218/26 trust
use_pg_rewind: true
use_slots: true
retry_timeout: 10
slots:
barman:
type: physical
ttl: 45
~
~
~
~
"/tmp/opensql-config-m332oce3.yaml" 25L, 683C