使用HAProxy for Postgresql 9.4进行负载平衡 [英] Load Balancing using HAProxy for Postgresql 9.4

问题描述

我使用4个节点(虚拟机)之间的BDR(双向复制)设置了PostgreSQL的多主复制.

I have made a setup of multi-master replication of PostgreSQL using BDR (Bi-Directional Replication) among 4 nodes (virtual machines).

现在，我想放置一个负载均衡器以实现高可用性.为此，我已在另一台虚拟机上安装并配置了"HAProxy"，该虚拟机正在通过5432/tcp进行侦听以进行连接. haproxy配置如下:

Now i want to put a load-balancer for High Availability. For this i have installed and configured "HAProxy" on a different virtual machine, which is listening over 5432/tcp to connect. The haproxy configuration is as follows:

listen pgsql_bdr *:5432
    mode tcp
    option  httpchk
    balance roundrobin
    server master 192.168.123.1:5432 check backup
    server slave1 192.168.123.2:5432 check
    server slave2 192.168.123.3:5432 check
    server slave3 192.168.123.4:5432 check

HAProxy服务器的IP地址为192.168.123.5

The ip address of HAProxy server is 192.168.123.5

我在应用程序中使用了HAproxy服务器的IP地址来与数据库连接(必须将连接重定向到实际的数据库服务器).但是那时我遇到了以下错误:

I have use the IP address of HAproxy server in my application to connect with the database (that must redirect the connection to actual database servers). But at that time i am getting following error:

连接到服务器时出错:服务器意外关闭了连接.这可能意味着服务器在处理请求之前或期间异常终止.

Error connecting to the server: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

请注意，我已经尝试使用2种方式解决问题.首先，我在所有服务器(HAProxy和所有postgres服务器)上禁用了防火墙，并且尝试将配置替换为以下内容:

And note that i have try to resolve the problem using 2 manner. 1st I disabled the firewall on all servers (HAProxy and all postgres servers) and also i try to replace configuration with the following:

listen pgsql_bdr 0.0.0.0:5432

或

listen pgsql_bdr 127.0.0.1:5432

或

listen pgsql_bdr localhost:5432

但是在我的情况下，所有方法都无法正常工作.

But all was not working in my case.

请帮助我解决问题.在这种情况下我做错了什么?

Please help me to solve the problem. what i am doing wrong in this scenario?

提前谢谢！

推荐答案

检查您的haproxy /stats，我猜由于错误的检查，所有后端都被标记了下来-postgres可能不会响应200-.使用 pgsql-check

Check your haproxy /stats, I am guessing that all backends are marked down, due to wrong check - postgres probably won't respond with 200 - option httpchk. Use pgsql-check

option pgsql-check user healcheckuser

我也不知道BDR的要求/限制，但是在从站设置中通常有一些合并器，例如 pgbouncer -在客户端旁边，或在每个后端的前面，或任何需要的东西.

Also I am not aware of BDR requirements/limitations, but it is common in slaves setups, to have some pooler e.g. pgbouncer - next to client, or in front of each backend, or whatever that is needed.

修改

要查看统计信息，可以使用统计信息套接字:

To check stats you can use stats socket:

# stats about all frontents and backends in a csv format
echo "show stat" | socat unix-connect:/run/haproxy/admin.sock stdio

# version, PID, current connections, session rates, tasks, etc
echo "show info" | socat unix-connect:/run/haproxy/admin.sock stdio

# sessions with the used backend/frontend, the source
echo "show sess" | socat unix-connect:/run/haproxy/admin.sock stdio

# informations about errors if there are any
echo "show errors" | socat unix-connect:/run/haproxy/admin.sock stdio

当我使用Postgres 9.4进行测试时(一些奴隶，没有BDR)，haproxy 1.5.14，没有检查

As I tested with postgres 9.4, (some slaves, no BDR), haproxy 1.5.14, no checks

global
    log /dev/log    local0
    log /dev/log    local1 notice
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

listen pgsql *:15432
    mode tcp
    balance roundrobin
    server master dev1.db.fed:5432
    server slave1 dev2.db.fed:5432

我能够毫无问题地进行连接:

And I was able to connect without any issue:

root@lb:/# echo "show sess" | socat unix-connect:/run/haproxy/admin.sock stdio
0x20d89f0: proto=tcpv4 src=10.163.74.109:46815 fe=pgsql be=pgsql srv=slave1 ts=08 age=3m8s calls=3 rq[f=848202h,i=0,an=00h,rx=,wx=,ax=] rp[f=048202h,i=0,an=00h,rx=,wx=,ax=] s0=[7,8h,fd=1,ex=] s1=[7,8h,fd=2,ex=] exp=
0x20e11a0: proto=unix_stream src=unix:1 fe=GLOBAL be=<NONE> srv=<none> ts=09 age=0s calls=2 rq[f=c08200h,i=0,an=00h,rx=30s,wx=,ax=] rp[f=008002h,i=0,an=00h,rx=,wx=,ax=] s0=[7,8h,fd=3,ex=] s1=[7,0h,fd=-1,ex=] exp=30s

查询正在运行，数据即将来临，似乎还可以.

Queries are running, data is coming, seems OK.

但是，再次，我目前无法测试BDR.

But again, I'm not able to test BDR at the moment.

这篇关于使用HAProxy for Postgresql 9.4进行负载平衡的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！