postgresql trace app request to postgresql query in logs and pg_statement

gistfile1.txt

how to generate a unique request id mapping to app request to a db query request.

i have a setup of 
1) postgresql
2) flask app running on port 5000
3) haproxy running on port 8000 with backend as flask app. haproxy also generated a uuid header mapping app request to postgresql query.

```
postgres@pg:~$ cat /etc/haproxy/haproxy.cfg
global
        log /dev/log    local0
        log /dev/log    local1 notice
        chroot /var/lib/haproxy
        stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
        stats timeout 30s
        user haproxy
        group haproxy
        daemon

defaults
        log     global
        mode    http
        option  httplog
        option  dontlognull
        timeout connect 5000
        timeout client  50000
        timeout server  50000
        errorfile 400 /etc/haproxy/errors/400.http
        errorfile 403 /etc/haproxy/errors/403.http
        errorfile 408 /etc/haproxy/errors/408.http
        errorfile 500 /etc/haproxy/errors/500.http
        errorfile 502 /etc/haproxy/errors/502.http
        errorfile 503 /etc/haproxy/errors/503.http
        errorfile 504 /etc/haproxy/errors/504.http

frontend httpin
    bind *:80
    mode http
    option forwardfor
    option httplog
    unique-id-format %[uuid()]
    unique-id-header X-Request-ID
    default_backend servers

backend servers
    balance roundrobin
    server server1 127.0.0.1:5000 check
```

 systemctl restart postgresql
 
 then 
 simple flask app
 
 ```
 root@pg:~# cat app.py
import psycopg2
from flask import Flask, request

app = Flask(__name__)

@app.route("/")
def index():
    # Get the request ID from the custom header
    request_id = request.headers.get("X-Request-ID")

    # Make a request to PostgreSQL with the request ID in a comment at the beginning of the query
    query = f"/* Request ID: {request_id} */ SELECT 1;"
    conn = psycopg2.connect(
        host="127.0.0.1",
        database="postgres",
        user="root",
        password="",
    )
    cursor = conn.cursor()
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    conn.close()

    # Handle the results as needed
    return str("Request ID:" + str(request_id)), 200
```


postgresql config to log statements (for now log_statement = all)
```
postgres@pg:~$ cat 12/main/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
log_statement = 'all'
```

now start the flask app

$ flask run 
root@pg:~# flask run
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
127.0.0.1 - - [24/Feb/2023 19:20:03] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [24/Feb/2023 19:20:05] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [24/Feb/2023 19:21:03] "GET / HTTP/1.1" 200 -


make a few http requests to localhost using curl
```
postgres@pg:~$ for i in {1..5}; do curl -i http://localhost/ ; echo ; done
HTTP/1.0 200 OK
content-type: text/html; charset=utf-8
content-length: 47
server: Werkzeug/2.0.2 Python/3.10.6
date: Fri, 24 Feb 2023 13:57:36 GMT
connection: keep-alive

Request ID:a7b0f746-d3ce-4788-884a-99e737911f55
HTTP/1.0 200 OK
content-type: text/html; charset=utf-8
content-length: 47
server: Werkzeug/2.0.2 Python/3.10.6
date: Fri, 24 Feb 2023 13:57:36 GMT
connection: keep-alive

Request ID:fdfc3092-329d-4d17-8f7a-8db6281ad2ae
HTTP/1.0 200 OK
content-type: text/html; charset=utf-8
content-length: 47
server: Werkzeug/2.0.2 Python/3.10.6
date: Fri, 24 Feb 2023 13:57:36 GMT
connection: keep-alive

Request ID:fbd02570-1922-4255-b5d9-e84d7b0fa695
HTTP/1.0 200 OK
content-type: text/html; charset=utf-8
content-length: 47
server: Werkzeug/2.0.2 Python/3.10.6
date: Fri, 24 Feb 2023 13:57:36 GMT
connection: keep-alive

Request ID:5841f422-2f85-4fb7-90a6-267a538a81c3
HTTP/1.0 200 OK
content-type: text/html; charset=utf-8
content-length: 47
server: Werkzeug/2.0.2 Python/3.10.6
date: Fri, 24 Feb 2023 13:57:36 GMT
connection: keep-alive

Request ID:76b2f53a-0853-49b7-81dc-be67cee3532f
```


and mapped queries in postgresl logs

```
postgres@pg:~$ grep Req /var/log/postgresql/postgresql-12-main.log  | tail -5
2023-02-24 19:27:36.055 IST [2338] root@postgres LOG:  statement: /* Request ID: a7b0f746-d3ce-4788-884a-99e737911f55 */ SELECT 1;
2023-02-24 19:27:36.069 IST [2341] root@postgres LOG:  statement: /* Request ID: fdfc3092-329d-4d17-8f7a-8db6281ad2ae */ SELECT 1;
2023-02-24 19:27:36.082 IST [2344] root@postgres LOG:  statement: /* Request ID: fbd02570-1922-4255-b5d9-e84d7b0fa695 */ SELECT 1;
2023-02-24 19:27:36.096 IST [2347] root@postgres LOG:  statement: /* Request ID: 5841f422-2f85-4fb7-90a6-267a538a81c3 */ SELECT 1;
2023-02-24 19:27:36.109 IST [2350] root@postgres LOG:  statement: /* Request ID: 76b2f53a-0853-49b7-81dc-be67cee3532f */ SELECT 1;
```