Postgresql – HAProxy + Patroni – Configure reads from a master when no slaves available

haproxypatronipostgresql

I have a HAProxy + Patroni setup with split reads and writes.

The problem is, when both my replicas go down, only my writes are supported, the reads stop because both replicas are down.

How is this problem generally handled in this setup? Is there a way to have HAProxy direct the read traffic to the master in case no read servers are available?

Best Answer

This pattern of defining a frontend will use the replica as the main target, but if the read service is dead, then it will route traffic to the write servers - users should use 5911 for connecting to the read replica service.

frontend a_listen_fe                                                           
    bind *:5911                                                                
    acl is-read-service-dead nbsrv(cluster-a-slave) lt 1                       
    use_backend cluster-a-master if is-read-service-dead                       
    default_backend cluster-a-slave                                            
                                                                               
                                                                               
listen cluster-a-master                                                        
    bind *:5011                                                                
    option httpchk OPTIONS /master                                             
    http-check expect status 200                                               
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions     
    server postgresql_a1 127.0.0.1:6011 maxconn 100 check port 8011        
    server postgresql_a2 127.0.0.1:6012 maxconn 100 check port 8012        
    server postgresql_a3 127.0.0.1:6013 maxconn 100 check port 8013        
                                                                               
backend cluster-a-slave                                                        
    option httpchk OPTIONS /replica                                            
    http-check expect status 200                                               
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions     
    server postgresql_a1 127.0.0.1:6011 maxconn 100 check port 8011        
    server postgresql_a2 127.0.0.1:6012 maxconn 100 check port 8012        
    server postgresql_a3 127.0.0.1:6013 maxconn 100 check port 8013
Related Question