PGPool for Postgres load balancing, where to sit it

Question

So I've been doing a stack of reading. I'm still not 100% clear of the best way to go about it.

Let's take a simple setup. 3 nodes to server UWSGI apps (in random), 3 nodes for PGSQL, 1 nominal master and 2 read only slaves - it's not setup yet but that's the goal. 80% of the requests are read only anyway for most apps.

Now what really bugs me is that PGPool is usually illustrated as a single machine. If we are going for high availability as the primary goal with load balancing as a highly desirable side effect, introducing the need for a PGPool in the middle is introducing a singular point of failure as well as perhaps an otherwise unecesaey node.

Trying to ascertain what to do I imagine that really the app servers could be PGpools as they're the first one to notice if a DB goes down, but I've heard multiple PGPools are Complicated.

I mean what I really want is the built in replication ability with the ability to execute a failover and mark the new master etc, I know PGPool does it. I'd like it to be decentralised if possible - can people please point me in the right direction.?

Answer 1

Direction: I would definitly recommand this step by step tutorial that may point you to the right direction. This is the one I used when setting up similar config. You can adapt it to your needs eyes wide open.

Multiple PgPool: The tutorial seems long (and it is: 6 parts!) but the part related to setting up multiple Pgpool is less than half one of 6 parts (see part 5 and 6). Even if I could consider setting up pgpool as a pain, objectively, this part is not the worth (consisting in setting up ip arping and watchdog). If you have already been through the rest for configuring one pgpool and stream or wal replication that will not be a problem.

Decentralized or not: This may be only based opinion, but if I had to setup pgpool redondancy (I did not), I would prefer placing pgpools along postgresql servers in order to simplify my setup and make the whole thing accessible from outside "as if" it was a simple pgsql server. App and database layers stay good at what they are supposed to do and could stay technically "agnostic" to ascendant/descendant layer.