Skip to content

Instantly share code, notes, and snippets.

@gplv2

gplv2/haproxy.cfg

Forked from arkady-emelyanov/haproxy.cfg
Last active July 11, 2018 21:44
Show Gist options
  • Save gplv2/e124a26295b17316d89e9bb3e6249dd2 to your computer and use it in GitHub Desktop.
Save gplv2/e124a26295b17316d89e9bb3e6249dd2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. gplv2 revised this gist May 11, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion haproxy.cfg
    View file
    Original file line number Diff line number Diff line change
    @@ -8,7 +8,7 @@
    # template1 database is accessible by user web
    #
    # haproxy will pass connection to postgresql master node:
    # $ psql -h 127.0.0.1 -p 5432 -U web template1
    # $ psql -h 127.0.0.1 -p 5432 -U pgc template1
    # psql (9.4.13)
    # Type "help" for help.
    #
  2. gplv2 revised this gist May 11, 2018. 1 changed file with 67 additions and 12 deletions.
    79 changes: 67 additions & 12 deletions haproxy.cfg
    View file
    Original file line number Diff line number Diff line change
    @@ -1,39 +1,94 @@
    # Sample haproxy postgresql master check
    # haproxy postgresql master check
    #
    # haproxy listen: 5431
    # haproxy listen: 5432
    # pg, instance #1 listen: 5432 (master node)
    # pg, instance #2 listen: 5433 (replica node)
    # pg, instance #2 listen: 5432 (replica node)
    # external failover, promoting replica to master in case of failure
    # passwordless auth for user web
    # template1 database is accessible by user web
    #
    # haproxy will pass connection to postgresql master node:
    # $ psql -h 127.0.0.1 -p 5431 -U web template1
    # $ psql -h 127.0.0.1 -p 5432 -U web template1
    # psql (9.4.13)
    # Type "help" for help.
    #
    # template1=#
    #
    frontend fr_pg

    #---------------------------------------------------------------------
    # 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:snowball1

    #---------------------------------------------------------------------
    # main frontend which proxys to the backends
    #---------------------------------------------------------------------

    frontend front_pg
    mode tcp
    bind *:5431
    default_backend bk_pg
    bind *:5432
    default_backend backend_pg

    backend bk_pg
    backend backend_pg
    option tcp-check
    tcp-check connect

    #
    # write: startup message
    #
    # startup message params:
    # user: web
    # user: pgcheck
    # database: template1
    #
    tcp-check send-binary 00000025 # packet length
    tcp-check send-binary 00030000 # protocol version
    tcp-check send-binary 7573657200 # "user"
    tcp-check send-binary 77656200 # "web"
    tcp-check send-binary 70676300 # "pgc"
    tcp-check send-binary 646174616261736500 # "database"
    tcp-check send-binary 74656d706c6174653100 # "template1"
    tcp-check send-binary 00 # terminator
    @@ -81,5 +136,5 @@ backend bk_pg
    tcp-check send-binary 00000004 # packet length: 4 (no body)

    # server list to check
    server pg_01 127.0.0.1:5432 check inter 5s
    server pg_01 127.0.0.1:5433 check inter 5s
    server pgmaster 192.168.60.144:5432 check inter 5000 fastinter 2000 downinter 5000 rise 2 fall 3 port 6432
    server pgstandby 192.168.60.145:5432 check inter 5000 fastinter 2000 downinter 5000 rise 2 fall 3 port 6432 backup
  3. @arkady-emelyanov arkady-emelyanov revised this gist Sep 8, 2017. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions haproxy.cfg
    View file
    Original file line number Diff line number Diff line change
    @@ -4,6 +4,8 @@
    # pg, instance #1 listen: 5432 (master node)
    # pg, instance #2 listen: 5433 (replica node)
    # external failover, promoting replica to master in case of failure
    # passwordless auth for user web
    # template1 database is accessible by user web
    #
    # haproxy will pass connection to postgresql master node:
    # $ psql -h 127.0.0.1 -p 5431 -U web template1
  4. @arkady-emelyanov arkady-emelyanov revised this gist Sep 8, 2017. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions haproxy.cfg
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,7 @@ frontend fr_pg

    backend bk_pg
    option tcp-check
    tcp-check connect

    #
    # write: startup message
  5. @arkady-emelyanov arkady-emelyanov revised this gist Sep 8, 2017. 1 changed file with 14 additions and 13 deletions.
    27 changes: 14 additions & 13 deletions haproxy.cfg
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,7 @@
    # haproxy listen: 5431
    # pg, instance #1 listen: 5432 (master node)
    # pg, instance #2 listen: 5433 (replica node)
    # external failover, promoting replica to master in case of failure
    #
    # haproxy will pass connection to postgresql master node:
    # $ psql -h 127.0.0.1 -p 5431 -U web template1
    @@ -27,30 +28,30 @@ backend bk_pg
    # database: template1
    #
    tcp-check send-binary 00000025 # packet length
    tcp-check send-binary 00030000 # protocol info (?)
    tcp-check send-binary 7573657200 # user (5 bytes)
    tcp-check send-binary 77656200 # web (4 bytes)
    tcp-check send-binary 646174616261736500 # database (9 bytes)
    tcp-check send-binary 74656d706c6174653100 # template1 (10 bytes)
    tcp-check send-binary 00 # terminator (1 byte)
    tcp-check send-binary 00030000 # protocol version
    tcp-check send-binary 7573657200 # "user"
    tcp-check send-binary 77656200 # "web"
    tcp-check send-binary 646174616261736500 # "database"
    tcp-check send-binary 74656d706c6174653100 # "template1"
    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
    tcp-check expect binary 00000008 # packet length
    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)
    tcp-check send-binary 70675f69735f696e5f7265636f7665727928293b # pg_is_in_recovery(); (20 bytes)
    tcp-check send-binary 00 # terminator (1 byte)
    tcp-check send-binary 00000020 # packet length
    tcp-check send-binary 73656c65637420 # "select "
    tcp-check send-binary 70675f69735f696e5f7265636f7665727928293b # "pg_is_in_recovery();"
    tcp-check send-binary 00 # terminator

    # expect: Row description packet
    #
    tcp-check expect binary 54 # 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
  6. @arkady-emelyanov arkady-emelyanov renamed this gist Sep 8, 2017. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions gistfile1.txt → haproxy.cfg
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,10 @@
    # Sample haproxy postgresql master check
    #
    # haproxy listen: 5431
    # pg listen: 5432
    # pg, instance #1 listen: 5432 (master node)
    # pg, instance #2 listen: 5433 (replica node)
    #
    # Use haproxy to connect to postgresql instance:
    # haproxy will pass connection to postgresql master node:
    # $ psql -h 127.0.0.1 -p 5431 -U web template1
    # psql (9.4.13)
    # Type "help" for help.
    @@ -77,3 +78,4 @@ backend bk_pg

    # server list to check
    server pg_01 127.0.0.1:5432 check inter 5s
    server pg_01 127.0.0.1:5433 check inter 5s
  7. @arkady-emelyanov arkady-emelyanov revised this gist Sep 7, 2017. 1 changed file with 27 additions and 27 deletions.
    54 changes: 27 additions & 27 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -18,15 +18,15 @@ frontend fr_pg
    backend bk_pg
    option tcp-check

    #
    #
    # write: startup message
    #
    # startup message params:
    # user: web
    # database: template1
    #
    #
    # startup message params:
    # user: web
    # database: template1
    #
    tcp-check send-binary 00000025 # packet length
    tcp-check send-binary 00030000 # protocol info (?)
    tcp-check send-binary 00030000 # protocol info (?)
    tcp-check send-binary 7573657200 # user (5 bytes)
    tcp-check send-binary 77656200 # web (4 bytes)
    tcp-check send-binary 646174616261736500 # database (9 bytes)
    @@ -35,45 +35,45 @@ backend bk_pg

    # 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
    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();"
    #
    # "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 00000020 # packet length (4 bytes)
    tcp-check send-binary 73656c65637420 # select_ (7 bytes)
    tcp-check send-binary 70675f69735f696e5f7265636f7665727928293b # pg_is_in_recovery(); (20 bytes)
    tcp-check send-binary 00 # terminator (1 byte)

    # expect: Row description packet
    #
    #
    tcp-check expect binary 54 # row description packet
    tcp-check expect binary 0000002a # packet length: 42 (0x2a)
    tcp-check expect binary 0001 # field count: 1
    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 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 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)
    #
    #
    # "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 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)
    tcp-check send-binary 00000004 # packet length: 4 (no body)

    # server list to check
    # server list to check
    server pg_01 127.0.0.1:5432 check inter 5s
  8. @arkady-emelyanov arkady-emelyanov created this gist Sep 7, 2017.
    79 changes: 79 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,79 @@
    # Sample haproxy postgresql master check
    #
    # haproxy listen: 5431
    # pg listen: 5432
    #
    # Use haproxy to connect to postgresql instance:
    # $ psql -h 127.0.0.1 -p 5431 -U web template1
    # psql (9.4.13)
    # Type "help" for help.
    #
    # template1=#
    #
    frontend fr_pg
    mode tcp
    bind *:5431
    default_backend bk_pg

    backend bk_pg
    option tcp-check

    #
    # write: startup message
    #
    # startup message params:
    # user: web
    # database: template1
    #
    tcp-check send-binary 00000025 # packet length
    tcp-check send-binary 00030000 # protocol info (?)
    tcp-check send-binary 7573657200 # user (5 bytes)
    tcp-check send-binary 77656200 # web (4 bytes)
    tcp-check send-binary 646174616261736500 # database (9 bytes)
    tcp-check send-binary 74656d706c6174653100 # template1 (10 bytes)
    tcp-check send-binary 00 # terminator (1 byte)

    # 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)
    tcp-check send-binary 70675f69735f696e5f7265636f7665727928293b # pg_is_in_recovery(); (20 bytes)
    tcp-check send-binary 00 # terminator (1 byte)

    # expect: Row description packet
    #
    tcp-check expect binary 54 # row description packet
    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)

    # server list to check
    server pg_01 127.0.0.1:5432 check inter 5s