我们如何使用HAproxy实现到Postgres Master的路由请求 [英] How can we achieve route requests to postgres master using HAproxy

问题描述

我正在尝试为postgres数据库设置负载平衡。我们有一个postgres的主人和奴隶。我配置了HA代理以实现负载平衡。简单的负载平衡可以正常工作。

I am trying to setup a load balancing for postgres database. We have a postgres master and slave. I configured HA proxy to achieve load balancing. Simple load balancing is working fine.

该数据库用于位桶。

当位桶向其中写入数据时通过负载均衡器访问数据库很好，如果它命中了主服务器，但是当它命中了从服务器（处于读取模式）时，由于无法更新数据库而遇到了问题。

When bitbucket is writing data to database over the loadbalancer it is fine if it is hitting master, but problem encounters when it is hitting slave(which is in read mode) as it cant update the database.

我怎么说 HAproxy将请求路由到postgres主服务器。

How can i say HAproxy to route requests to postgres master.

从pg_stat_replication中选择*以在主服务器和从服务器上执行，并且必须根据输出将请求路由至流媒体大师，有什么方法可以使用HAproxy来实现？

Select * from pg_stat_replication to be executed on master and slave and based on the output it has to route the request to the streaming master, is there any way that we achieve it using HAproxy ?

谢谢，
Karthik

Thanks, Karthik

推荐答案

这可以使用自定义运行状况检查来完成，只有当服务器是主服务器时，此检查才能成功。

This can be done using a custom health check, which only succeeds if the server is the master. All the other slave servers will be marked down.

 backend pgsql
   mode tcp
   option tcp-check
   tcp-check send-binary 0000002c # length
   tcp-check send-binary 00030000 # version
   tcp-check send-binary 75736572 # 'user'.bytes.map { |c| c.to_s(16) }
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 6170706c69636174696f6e # 'application'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 6461746162617365 # 'database'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 706f737467726573 # 'postgres'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 00 # name/value terminator
   tcp-check expect string R
   tcp-check send-binary 51 # 'Q'
   tcp-check send-binary 00000020 # length
   tcp-check send-binary 73656c6563742070675f69735f696e5f7265636f7665727928293b # 'select pg_is_in_recovery();'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 58 # 'X'
   tcp-check send-binary 00000004 # length
   tcp-check expect string f
   tcp-check expect string Z
   server pg1 1.2.3.4:5432 check
   server pg2 5.6.7.8:5432 check

tcp-check行使用Postgres客户端协议发送和接收二进制数据包，该协议在其< a href = https://www.postgresql.org/docs/9.6/static/protocol.html rel = nofollow noreferrer>文档。

The ''tcp-check'' lines are sending and receiving binary packets using the Postgres client protocol, which is defined in their documentation.

此外，您还可以具有仅在主数据库发生故障时使用的辅助只读后端。这是额外的前端/后端配置。

In addition, you can also have a secondary 'readonly' backend that is only used if the primary fails. Here's the extra frontend/backend config for that.

 frontend pgsql
   mode tcp
   bind *:5432
   acl readonly.pgsql nbsrv(pgsql) eq 0
   use_backend readonly.pgsql if readonly.pgsql
   acl pgsql nbsrv(pgsql) gt 0
   use_backend pgsql if pgsql

 <...primary backend...>

 backend readonly.pgsql
   mode tcp
   option tcp-check
   tcp-check send-binary 0000002c # length
   tcp-check send-binary 00030000 # version
   tcp-check send-binary 75736572 # 'user'.bytes.map { |c| c.to_s(16) }
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 6170706c69636174696f6e # 'application'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 6461746162617365 # 'database'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 706f737467726573 # 'postgres'
   tcp-check send-binary 00 # string terminator
   tcp-check send-binary 00 # name/value terminator
   tcp-check expect string R
   tcp-check send-binary 58 # 'X'
   tcp-check send-binary 00000004 # length
   server pg1 1.2.3.4:5432 check
   server pg2 5.6.7.8:5432 check

如果将奴隶标记为 down是对HAProxy健康检查的一种很好的使用，这是有争议的。上面的方法确实有效，但是还有其他方式在没有HAProxy的情况下执行此操作。

It's debatable if marking slaves as 'down' is a good use of HAProxy health checks. The above does work, but there other ways of doing this without HAProxy.

这篇关于我们如何使用HAproxy实现到Postgres Master的路由请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！