0

I am trying to create a connection pooling system with load balancing. From what I unsderstand PGbouncer doesn't have a load balancing option and all I can do is to create a file with all the users+pass and configure the dbs/clusters. but in this option i cannot direct the connections to specific cluster. i'll explain: inserts will go to primary and selects will go to slave. what is possible is to let user "user1" connect to cluster on port 5432 to DB "database123".

How can I redirect queries to standby with other tools? I tried to do this with pgpool but for some reason the standby is always on "waiting" status --> Cannot configure pgpool with master and slave nodes

hello
  • 11
  • 1

1 Answers1

0

It is impossible to tell from an SQL statement if it will modify data or not. What about SELECT delete_my_data();?

So all tools that try to figure that out by looking at the SQL statement are potentially problematic.

The best you can do is to write your application so that it uses two data sources: one for reading and one for writing, and you determine what goes where.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Oh I didn't think about calling function with select statments.... you're correct. But anyway, how can I configure the pgpool to direct all select to standby? It doesn't seem to work. when I run show pool_nodes; I see the count select, and all goes to main.... – hello Dec 23 '20 at 06:48
  • I don't know enough about pgPool to answer that - I have heard that it is difficult to configure right. – Laurenz Albe Dec 23 '20 at 07:05
  • Just made it, all selects go through standby and insert/update/delete to main. But as you mentioned before, it's problematic when selecting functions... – hello Dec 23 '20 at 07:45