-
Notifications
You must be signed in to change notification settings - Fork 16
/
haproxy-example.cfg
162 lines (134 loc) · 6.44 KB
/
haproxy-example.cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
# haproxy postgresql master check
#
# haproxy listen on: 5432
# Setup architecture : haproxy (2) <-> pgbouncer(2) <-> postgresql (1 + 1)
# DB, remote instance #1 listen: 6432 (master node) (
# DB, remote instance #2 listen: 6432 (replica node)
# passwordless auth for check user pgc
# The check is performed directly, without consulting the pgbouncer, hence checking
# happens on port 6432 , bouncers listen on 5432
# external failover, promoting replica to master in case of failure: using repmgr
# template1 database is accessible by user pgc
#
# haproxy will pass connection to postgresql master node:
# $ psql -h -p 5432 -U pgc template1
#
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
spread-checks 5
# turn on stats unix socket
stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode tcp
log global
option dontlognull
option redispatch
retries 3
timeout queue 1m
timeout connect 1s
timeout client 3600s
timeout server 3600s
timeout check 2s
maxconn 500
#---------------------------------------------------------------------
# statistics
#---------------------------------------------------------------------
# Host HA-Proxy's web stats on Port 8182.
listen HAProxy-Statistics *:8182
mode http
option httplog
stats enable
stats uri /haproxy?stats
stats refresh 20s
stats realm PSQL Haproxy\ Statistics # Title text for popup window
stats show-node
stats show-legends
stats show-desc PSQL load balancer stats (master)
stats auth pgadmin:pgsecret
#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend front_pg
mode tcp
bind *:5432
acl pg_single_master nbsrv(backend_pg) eq 1
tcp-request connection reject if !pg_single_master
default_backend backend_pg
#---------------------------------------------------------------------
# the postgresql cluster backend (master + standby)
#---------------------------------------------------------------------
backend backend_pg
option tcp-check
tcp-check connect
# user: pgc
# database: template1
#
tcp-check send-binary 00000025 # packet length
tcp-check send-binary 00030000 # protocol version
tcp-check send-binary 7573657200 # "user" ( 5 bytes )
tcp-check send-binary 70676300 # "pgc" ( 4 bytes )
tcp-check send-binary 646174616261736500 # "database" ( 9 bytes )
tcp-check send-binary 74656d706c6174653100 # "template1" ( 10 bytes )
tcp-check send-binary 00 # terminator
# expect: Auth
#
tcp-check expect binary 52 # Auth request
tcp-check expect binary 00000008 # packet length ( 8 bytes )
tcp-check expect binary 00000000 # auth response ok
# write: run simple query
# "select pg_is_in_recovery();"
#
tcp-check send-binary 51 # simple query
tcp-check send-binary 00000020 # packet length ( 4 bytes)
tcp-check send-binary 73656c65637420 # "select " ( 7 bytes )
# "pg_is_in_recovery();"
tcp-check send-binary 70675f69735f696e5f7265636f7665727928293b # ( 20 bytes )
tcp-check send-binary 00 # terminator ( 1 byte )
# write: terminate session
tcp-check send-binary 58 # Termination packet
tcp-check send-binary 00000004 # packet length: 4 (no body)
# avoids : <template1-pgc-2019-01-18 11:23:06 CET>LOG: could not receive data from client: Connection reset by peer
# expect: Row description packet
#
tcp-check expect binary 54 # row description packet (1 byte)
tcp-check expect binary 0000002a # packet length: 42 (0x2a)
tcp-check expect binary 0001 # field count: 1
tcp-check expect binary 70675f69735f696e5f7265636f7665727900 # field name: pg_is_in_recovery
tcp-check expect binary 00000000 # table oid: 0
tcp-check expect binary 0000 # column index: 0
tcp-check expect binary 00000010 # type oid: 16
tcp-check expect binary 0001 # column length: 1
tcp-check expect binary ffffffff # type modifier: -1
tcp-check expect binary 0000 # format: text
# expect: query result data
#
# "f" means node in master mode
# "t" means node in standby mode (read-only)
#
tcp-check expect binary 44 # data row packet
tcp-check expect binary 0000000b # packet lenght: 11 (0x0b)
tcp-check expect binary 0001 # field count: 1
tcp-check expect binary 00000001 # column length in bytes: 1
tcp-check expect binary 66 # column data, "f"
# write: terminate session
tcp-check send-binary 58 # Termination packet
tcp-check send-binary 00000004 # packet length: 4 (no body)
# close open sessions in case the downed server is still running but is out of sync with the master
default-server on-marked-down shutdown-sessions
# server list to check
server pgnode1 192.168.1.100:5432 check inter 5000 fastinter 2000 downinter 5000 rise 2 fall 3 port 6432 # this defaults to the initial master
server pgnode2 192.168.1.101:5432 check inter 5000 fastinter 2000 downinter 5000 rise 2 fall 3 port 6432 # this defaults to the initial standby
# These 2 above can change role depending on their role