HAProxy TCP Binary Check for Postgres Replica Instance

haproxy-postgres-binary-checks.cfg

# HAProxy TCP Binary Check for Postgres Replica Instance
# Copyright (C) "2021" Matous Jan Fialka, <https://mjf.cz/>
# Released under the terms of "The MIT License"

# https://www.postgresql.org/docs/current/protocol-message-formats.html

backend postgres

    bind :5432

    mode tcp

    option tcp-check
    
    # ------------------------
    # Escablish TCP Connection
    # ------------------------
    
    tcp-check connect
    
    # ----------------------------
    # Send Session Startup Message
    # ----------------------------
    
    # Length: "65" Octets
    tcp-check send-binary "00000042"
    
    # Type: Protocol Version 3
    tcp-check send-binary "00030000"
    
    # Data: "user\0"
    tcp-check send-binary "7573657200"
    
    # Data: "proxier\0"
    tcp-check send-binary "70726f7869657200"
    
    # Data: "database\0"
    tcp-check send-binary "646174616261736500"
    
    # Data: "template1\0"
    tcp-check send-binary "74656d706c6174653100"
    
    # Data: "application_name\0"
    tcp-check send-binary "6170706c69636174696f6e5f6e616d6500"
    
    # Data: "haproxy\0"
    tcp-check send-binary "686170726f787900"
    
    # End of Message.
    tcp-check send-binary "00"
    
    # ---------------------------------------------------
    # Check for Response with Clear-text Password Request
    # ---------------------------------------------------
    
    # Type: Response ("R")
    tcp-check expect binary "52"
    
    # Length: 8 Octets
    tcp-check expect binary "00000008"
    
    # Data: Clear-text Password Request
    tcp-check expect binary "00000003"
    
    # -----------------
    # Send the Password
    # -----------------
    
    # Type: Password ("p")
    tcp-check send-binary "70"
    
    # Length: "11" Octets
    tcp-check send-binary "0000000b"
    
    # Data: "123456\0" (password)
    tcp-check send-binary "31323334353600"
    
    # ----------------
    # Check a Response
    # ----------------
    
    # Type: Response ("R")
    tcp-check expect binary "52"
    
    # Length: 8 Octets
    tcp-check expect binary "00000008"
    
    # Data: Authentication OK
    tcp-check expect binary "00000000"
    
    # ------------
    # Send a Query
    # ------------
    
    # Type: Query ("Q")
    tcp-check send-binary "51"
    
    # Length: "31" Octets
    tcp-check send-binary "0000001f"
    
    # Data: "SELECT "
    tcp-check send-binary "53454c45435420"

    # Data: "pg_is_in_recovery()\0"
    tcp-check send-binary "70675f69735f696e5f7265636f76657279282900"

    # ------------------------
    # Send Session Termination
    # ------------------------
    
    # NOTE: This effectively avoids the "connection reset by peer" messages in logs.
    
    # Type: Termination ("X")
    tcp-check send-binary "58"
    
    # Length: 4 Octets (no data)
    tcp-check send-binary "00000004"

    # ------------------------
    # Check SQL Query Response
    # ------------------------
    
    # Type: Row Description ("T")
    tcp-check expect binary "54"
    
    # Length: "42" Octets
    tcp-check expect binary "0000002a"
    
    # Field Count: 1
    tcp-check expect binary "0001"
    
    # Column Name: "pg_is_in_recovery\0"
    tcp-check expect binary "70675f69735f696e5f7265636f7665727900"
    
    # Table OID: 0
    tcp-check expect binary "00000000"
    
    # Column Index: 0
    tcp-check expect binary "0000"
    
    # Type OID: "16"
    tcp-check expect binary "00000010"
    
    # Column Length: 1
    tcp-check expect binary "0001"
    
    # Type Modifier: -1
    tcp-check expect binary "ffffffff"
    
    # Format: 0 (Text)
    tcp-check expect binary "0000"
    
    # Type: Data Row ("D")
    tcp-check expect binary "44"
    
    # Length: "11"
    tcp-check expect binary "0000000b"
    
    # Field Count: 1
    tcp-check expect binary "0001"
    
    # Column Length: 1
    tcp-check expect binary "00000001"
    
    # Data: "f" ("66" heximal; use "74" for "t")
    tcp-check expect binary "66"
    
    # ------------------------
    # Send Session Termination
    # ------------------------
    
    # Type: Termination ("X")
    tcp-check send-binary "58"
    
    # Length: 4 Octets (no data)
    tcp-check send-binary "00000004"

    # -------
    # Servers
    # -------

    server postgres1 postgres1.domain.tld:5432
    server postgres2 postgres2.domain.tld:5432
    server postgres3 postgres3.domain.tld:5432

# vi:ft=sh