Hot-Standby does not support writes

[pires]

haproxy will load-balance any connection to any node, master or slave. Without knowing the type of query (read-only or write) there's no way of actually do proper load-balancing. And AFAIK, haproxy will not be the best choice.

Either I'm missing something here or there is the need for a more thought-out solution.

[Winslett]

@pires "load balance" was not the correct term. I should have used "properly connect to the master". I will update the README. For load balancing reads / writes, you are correct that a different solution is needed.

As for high availability, in my testing, haproxy was the only tool which worked reliably with multiple role changes. PGPool stopped connecting to the leader properly after a few failovers between hosts or disconnects.

Do you have a solution that would make the proxying a better experience?

[pires]

@Winslett well, no. For proper load-balancing to happen we'd need to actually jump into the pipe and understand the query itself for breaking into read and write operations. pgpool does act as the middleman (proxy) and handles that for you, namely with Streaming-Replication.

While using Streaming replication and Hot Standby, it is important to determine which query can be sent to the primary or the standby, and which one should not be sent to the standby. pgpool-II's Streaming Replication mode carefully takes care of this.

But unfortunately, they're scarce on details.

I know a similar solution is kind of far-fetched but definitely needed.

[schmitch]

there could be an easy solution with https://github.com/skynetservices/skydns, where you could put a postgresql service which will hold the ip of the master. This has some major gains, since you could just use older applications with your HA postgres

[pires]

@schmitch we're discussing different issues here. Slaves can't write but should be able to read in order to scale. Right now, that doesn't seem possible with current implementation [this project].

[schmitch]

@pires this is not done easily especially if you make full usage of sql. Currently we have made a router inside our application to do this.

Mostly its really hard to do a custom implementation on a loadbalancer, etc. since you would need to have a fully fledged sql parser that looks at every query and routes them correctly.

I know there are some solutions to do it, especially when using pgpool-II, but they are really error prone (we did that already), after we found out that it is way easier to scale reads, when tuning your application codebase.

[pires]

@schmitch I totally feel your pain. But it would be awesome! Otherwise, we just have failover and no throughput scaling.

[schmitch]

@pires awesome yes, but there are several points against it.

First if you want "true" scale your master should never be used for reads if you have enough read slaves. Second it is really hard due to the fact that there are some SELECT queries which also writes. Third Performance... If you need to parse your SQL queries the whole thing will be WAY slower than just routing them to the correct database. ;)

[pires]

@schmitch correct. So, should I close this issue?

[schmitch]

@pires it would be better to give some examples, these could either be done via skydns, where people put their read slaves and master into skydns with a low ttl or as low as governor provides, then on a failover it could decomission the slave or change the master ip. this would be one example.

the other one would be about application sql routing and failover directly through etcd.

however which way people choose, the application MUST support it. Connection Pooler needs to autoretry connections and or the application should do this also and should not fail hard.

Btw.: I did not use your project, but I've seen this project on HN and seen the draft and it is closely the thing what we do. Before we had the setup with pgpool-II which were really bad, we also had something setup with pgbouncer were we could only read / write from the master and every master change we would reload the pgbouncer but that wasn't as good as we thought since it didn't scaled reads.

However on top of that to get the most benefits people should change their applications to support read only queries and a way to failover via etcd, since that brings the most benefits. design the database access as netflix did it with hystrix.

[pires]

@schmitch don't take me wrong but doing it at the app layer kinda feels wrong. To me, both as a developer and an enabler - in the sense of enabling developers to do all sorts of crazy stuff without actually knowing details of PostgreSQL or whatever -, we (Compose, me, whatever) need the proxy behavior. I know it's hard. I know it's error prone. But it's a huge feature to have. Not a must, but a really-awesome-to-have feature.

Btw.: I did not use your project

Which project do you refer to?

Oh, and I'm eager to contribute to this project. Can we go the go way? :-D

[schmitch]

@pires i know it feels wrong but as said i would not prefer it to somebody only as a exception if you are planning to change that legacy code.

Which project do you refer to?

As said we use our own project which is close to that what you do. The only thing we do not as you do is the initalization.

[pires]

@schmitch to be clear, I'm just part of the community and am not working on this project as a member of Compose.

[tvb]

@Winslett You said "properly connect to the master." but how is this accomplished? haproxy has no knowledge of which pgsql node is master. right?

[tvb]

I altered haproxy_status.sh to let haproxy put down the server that is read_only so no queries will be sent to it:

#!/bin/bash

while true
do {
        response=$(echo "SHOW transaction_read_only; SELECT pg_is_in_recovery();" | psql -t postgres --port $2 --host $1 2> /dev/null | tr -d "\n" | tr -d " ")

        if [ "${response}" == "offf" ]
        then
                echo "HTTP/1.1 200 OK"
                echo "X-XLOG-POSITION: $(echo "SELECT pg_current_xlog_location();" | psql -t postgres --port $2 --host $1 2> /dev/null | tr -d ' ' | head -n 1)"
        else
                echo "HTTP/1.1 503 Service unavailable"
                echo "X-XLOG-POSITION: $(echo "SELECT pg_last_xlog_replay_location();" | psql -t postgres --port $2 --host $1 2> /dev/null | tr -d ' ' | head -n 1)"
        fi

} | nc -l $3; done

[miketonks]

Another solution is to have postgres server listening on different ports for master / slave connections. This way haproxy default behaviour will suffice without any special health-check.

As a nice extra, we get a read-only pool from the slaves that can be used for scaling.