WIP haproxy pgbouncer postgres

gistfile1.txt

root@ubuntu-linux-20-04-desktop:~# cat /etc/haproxy/haproxy.cfg 
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    description PostgreSQL Database HAProxy Stats page
    log 127.0.0.1 local0 info
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     3000
    user        haproxy
    group       haproxy
    daemon
    # 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
    log global
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

#---------------------------------------------------------------------
# stats page
#---------------------------------------------------------------------
listen statspage
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats refresh 30s
    stats auth  admin:admin
    stats show-desc
    stats show-legends
#---------------------------------------------------------------------
# postgresql databases
#---------------------------------------------------------------------
listen pgRW
        balance roundrobin
        mode tcp
        bind 0.0.0.0:6432
        timeout client 20m
        timeout connect 1s
        timeout server 20m
        option tcplog
        balance leastconn
        option log-health-checks
        option tcpka
        option tcplog
        option httpchk GET /primary
        http-check expect string "primary"
        server db1 127.0.0.1:5432 check addr 127.0.0.1 port 5000 inter 5000 rise 2 fall 3
        server db2 127.0.0.1:5433 check addr 127.0.0.1 port 5001 inter 5001 rise 2 fall 3

listen pgRO
        balance roundrobin
        mode tcp
        bind 0.0.0.0:6433
        timeout client 20m
        timeout connect 1s
        timeout server 20m
        option tcplog
        balance leastconn
        option log-health-checks
        option tcpka
        option tcplog
        option httpchk GET /replica
        http-check expect string "replica"
        server db1 127.0.0.1:5432 check addr 127.0.0.1 port 5000 inter 5000 rise 2 fall 3
        server db2 127.0.0.1:5433 check addr 127.0.0.1 port 5001 inter 5001 rise 2 fall 3


-------------------
postgres@ubuntu-linux-20-04-desktop:~/temp$ cat db1/pgbouncer.ini 
[databases]
* = host=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres 

[pgbouncer]
listen_addr = *  
listen_port = 6432
auth_type =  trust 
auth_file = /var/lib/postgres/temp/db1/userlist.txt  
admin_users = postgres
pool_mode = session  
max_client_conn = 100  
default_pool_size = 20  
logfile = /var/lib/postgres/temp/db1/pgb.log
pidfile = /var/lib/postgres/temp/db1/pgbouncer.pid  


------------------------
health check
root@ubuntu-linux-20-04-desktop:~# find . -name '*.py' | grep flaskap |xargs cat
from flask import Flask
from flask import Response

import os
import psycopg2

def query_pg_directly_for_primary():
    conn = psycopg2.connect(host='localhost',
                            port=5432,
                            database='postgres',
                            user='postgres',
                            password='postgres')
    cur = conn.cursor()
    cur.execute('SELECT pg_is_in_recovery();')
    result = cur.fetchone()
    cur.close()
    conn.close()
    

def get_pgb_connection():
    conn = psycopg2.connect(host='localhost',
                            port=6432,
                            database='postgres',
                            user='postgres',
                            password='postgres')
    return conn

def check_is_primary():
    dbconn = get_db_connection()
    pgconn = get_pgb_connection()
    cur = conn.cursor()
    cur.execute('SELECT pg_is_in_recovery();')
    results = cur.fetchall()
    cur.close()
    conn.close()

@app.route('/')
def index():
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('SELECT * FROM books;')
    books = cur.fetchall()
    cur.close()
    conn.close()
    return render_template('index.html', books=books)

app = Flask(__name__)


@app.route('/primary')
def primary():
    content = 'primary'
    response_code = 200
    return Response(
        response=content,
        status=response_code
    )

@app.route('/replica')
def replica():
    content = 'primary'
    response_code = 503
    return Response(
        response=content,
        status=response_code
    )
from flask import Flask
from flask import Response

app = Flask(__name__)


@app.route('/primary')
def primary():
    content = 'replica'
    response_code = 503
    return Response(
        response=content,
        status=response_code
    )

@app.route('/replica')
def replica():
    content = 'replica'
    response_code = 200
    return Response(
        response=content,
        status=response_code
    )
root@ubuntu-linux-20-04-desktop:~#