PostgreSQL error ( log Docker)

[matteopetri2002]

I'm running two replicated databases with Docker and tried to add a load balancer (HAProxy), but every time I do docker compose up, I get the following log:

haproxy-1     | Connect from 192.168.65.1:27560 to 172.21.0.4:5432 (pgsql/TCP)
haproxy-1     | Connect from 192.168.65.1:41348 to 172.21.0.4:5432 (pgsql/TCP)
pg_primary-1  | 2024-06-18 14:50:12.989 UTC [357] LOG:  invalid length of startup packet
pg_replica-1  | 2024-06-18 14:50:14.094 UTC [304] LOG:  invalid length of startup packet
haproxy-1     | Connect from 192.168.65.1:34021 to 172.21.0.4:5432 (pgsql/TCP)
pg_primary-1  | 2024-06-18 14:50:14.103 UTC [366] LOG:  invalid length of startup packet
haproxy-1     | Connect from 192.168.65.1:38342 to 172.21.0.4:5432 (pgsql/TCP)
pg_replica-1  | 2024-06-18 14:50:14.114 UTC [313] LOG:  invalid length of startup packet
haproxy-1     | Connect from 192.168.65.1:24032 to 172.21.0.4:5432 (pgsql/TCP)
pg_primary-1  | 2024-06-18 14:50:15.705 UTC [368] LOG:  invalid length of startup packet
haproxy-1     | Connect from 192.168.65.1:61890 to 172.21.0.4:5432 (pgsql/TCP)
haproxy-1     | Connect from 192.168.65.1:37349 to 172.21.0.4:5432 (pgsql/TCP)
pg_replica-1  | 2024-06-18 14:50:16.293 UTC [315] LOG:  invalid length of startup packet
pg_primary-1  | 2024-06-18 14:50:16.299 UTC [369] LOG:  invalid length of startup packet
haproxy-1     | Connect from 192.168.65.1:18951 to 172.21.0.4:5432 (pgsql/TCP)
pg_replica-1  | 2024-06-18 14:50:16.309 UTC [316] LOG:  invalid length of startup packet
haproxy-1     | Connect from 192.168.65.1:22642 to 172.21.0.4:5432 (pgsql/TCP)
haproxy-1     | Connect from 192.168.65.1:31553 to 172.21.0.4:5432 (pgsql/TCP)
pg_primary-1  | 2024-06-18 14:50:16.483 UTC [371] LOG:  invalid length of startup packet
pg_primary-1  | 2024-06-18 14:51:21.182 UTC [51] LOG:  checkpoint starting: time
pg_primary-1  | 2024-06-18 14:51:25.360 UTC [51] LOG:  checkpoint complete: wrote 42 buffers (0.3%); 0 WAL file(s) added, 0 removed, 1 recycled; write=4.148 s, sync=0.011 s, total=4.178 s; sync files=10, longest=0.007 s, average=0.002 s; distance=16644 kB, estimate=16644 kB; lsn=0/30413D0, redo lsn=0/3041398

My Docker Compose file is structured like this:

services:
  pg_primary:
    image: postgres:16
    user: postgres
    restart: always
    healthcheck:
      test: 'pg_isready -U user --dbname=postgres'
      interval: 10s
      timeout: 5s
      retries: 5
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_HOST_AUTH_METHOD: "scram-sha-256\nhost replication all 0.0.0.0/0 md5"
      POSTGRES_INITDB_ARGS: "--auth-host=scram-sha-256"
    command: |
      postgres
      -c wal_level=replica
      -c hot_standby=on
      -c max_wal_senders=10
      -c max_replication_slots=10
      -c hot_standby_feedback=on
    volumes:
      - ${PWD}/00_init.sql:/docker-entrypoint-initdb.d/00_init.sql:ro

  pg_replica:
    image: postgres:16
    user: postgres
    restart: always
    healthcheck:
      test: 'pg_isready -U user --dbname=postgres'
      interval: 10s
      timeout: 5s
      retries: 5
    ports:
      - 5433:5432
    environment:
      PGUSER: replicator
      PGPASSWORD: replicator_password
    command: |
      bash -c "
      until pg_basebackup --pgdata=/var/lib/postgresql/data -R --slot=replication_slot --host=pg_primary --port=5432
      do
      echo 'Waiting for primary to connect..'
      sleep 1s
      done
      echo 'Backup done, starting replica...'
      chmod 0700 /var/lib/postgresql/data
      postgres
      "
    depends_on:
      - pg_primary

  pgadmin:
    image: dpage/pgadmin4
    restart: always
    ports:
      - 8080:80
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: admin
    depends_on:
      - pg_primary
      - pg_replica

  haproxy:
    image: haproxy:2.5
    restart: always
    ports:
      - 5434:5432
    volumes:
      - ./haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    depends_on:
      - pg_primary
      - pg_replica
    entrypoint: ["sh", "-c", "sleep 10 && haproxy -f /usr/local/etc/haproxy/haproxy.cfg"]

[LaurentGoderre]

It looks like haproxy is failing to connect to the 2 postgres node. What does your haproxy config look like?

[matteopetri2002]

my haproxy configuration look like this:

global
    log stdout format raw local0
    maxconn 4096

defaults
    log     global
    mode    tcp
    option  tcplog
    timeout connect 10s
    timeout client  1m
    timeout server  1m

frontend pgsql_frontend
    bind *:5432
    default_backend pgsql_backend

backend pgsql_backend
    balance roundrobin
    option httpchk
    server pg_primary pg_primary:5432 check
    server pg_replica pg_replica:5432 check

[matteopetri2002]

I saw another error in the logs; when HAProxy starts, this happens:

haproxy-1 | Server pgsql_backend/pg_primary is DOWN, reason: Layer7 invalid response, check duration: 3ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
haproxy-1 | [WARNING] (8) : Server pgsql_backend/pg_primary is DOWN, reason: Layer7 invalid response, check duration: 3ms. 1 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
pgadmin-1 | NOTE: Configuring authentication for SERVER mode.
pgadmin-1 |
pgadmin-1 | pgAdmin 4 - Application Initialisation
pgadmin-1 | ======================================
pgadmin-1 |
pg_replica-1 | 2024-06-19 10:26:52.804 UTC [26] LOG: invalid length of startup packet
haproxy-1 | [WARNING] (8) : Server pgsql_backend/pg_replica is DOWN, reason: Layer7 invalid response, check duration: 2ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.
haproxy-1 | [ALERT] (8) : backend 'pgsql_backend' has no server available!
haproxy-1 | Server pgsql_backend/pg_replica is DOWN, reason: Layer7 invalid response, check duration: 2ms. 0 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.

[LaurentGoderre]

Ok, the issue is that postgres doesn't recognize the "check" data that haproxy sends during its check. Can you try the following

In your db init

CREATE USER standbyuser WITH PASSWORD 'standbyuser';

Haproxy config:

global
    log stdout format raw local0
    maxconn 4096

defaults
    log     global
    mode    tcp
    option  tcplog
    timeout connect 10s
    timeout client  1m
    timeout server  1m

frontend pgsql_frontend
    bind *:5432
    default_backend pgsql_backend

backend pgsql_backend
    balance roundrobin
    option pgsql-check user standbyuser
    default-server inter 3s fall 3
    server pg_primary pg_primary:5432 check
    server pg_replica pg_replica:5432 check