iframe-proxy | Sunbelt Computer Software

Repository navigation

History

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

Sunbelt Computer Software

PL/B Language Development and Support

Navigation Menu

Repository navigation

haproxy-example.cfg

haproxy-example.cfg

Sunbelt Computer Software

PL/B Language Development and Support

FilesExpand file tree

haproxy-example.cfg

Latest commit

History

haproxy-example.cfg

File metadata and controls