HAPROXY: How to ensure all clients are connected to one server only?

Duy Vu

8/23/24, 2:09 AM

I have 3 servers setup: 1 primary, 2 backups. I used the following configuration:

backend pg_production_backend
    option pgsql-check user pg_user
    server primary pghost.primary:5432 check on-marked-down shutdown-sessions on-marked-up shutdown-backup-sessions
    server secondary pghost.secondary:5432 check backup on-marked-down shutdown-sessions on-marked-up shutdown-backup-sessions
    server tertiary pghost.tertiary:5432 check backup on-marked-down shutdown-sessions on-marked-up shutdown-backup-sessions

It works correctly when primary goes down, and connections will go to the first available backup; and connections return to primary when it comes back on.

The problem occurs when primary is down, and secondary is down. Connections will goto tertiary; when secondary comes back online; the connected clients remained on tertiary, and new connections go to secondary.

How to force all connections only goto tertiary or secondary while primary is still down?

1 + 0

haproxy

Score:0

Server

symcbean

8/23/24, 9:08 AM

This is how haproxy is designed to work. It doesn't know what state the sessions are in. Idle? Waiting for a result? In the middle of a transaction?

You need to recycle the connections at the client side.

Alternatively use proxysql (do be aware that this has a bigger performance overhead than haproxy).

+ 4

Duy Vu

8/24/24, 12:57 AM

It should not care what state the sessions are in; it should just disconnect all clients and force them to reconnect to the higher tier (or weight) servers. I also tried setting the weights but it made no difference

symcbean

8/27/24, 7:45 PM

"It should not care what state the sessions are in" - OMG! YES IT MUST.

Duy Vu

8/29/24, 1:05 AM

Then how does it switch to non-backup servers when it is back online? Does it care about the state of sessions?

symcbean

8/29/24, 12:44 PM

Personally, I use an iptables rule to block new connections on the host I want to isolate. IF you've configured the client end correctly, then the existing connections will be lost through normal recycling over a period. When the connections have fully migrated, I remove the iptables rule.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: HAPROXY: How to ensure all clients are connected to one server only?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.