Is it possible to configure pgbouncer to distribute queries among master and slave?

Question

We have master (write-read) and slave (readonly) Postgres 10 databases. Slave is online replication from master.

Now our application (c# webapi) connects to master only directly. We want to execute all SELECT queries on slave to distribute load among our hardware.

1. Idea is add some connection pooler that will pass all SELECTS on slave, other queries pass to master
2. If replication falls down, it will detect that automatically ASAP and pass all SELECTS to master till replication online again.

Is pgbouncer ok for that? Some another tool?

Answer 1

pgBouncer cannot do that, and indeed it is a questionable feature. How can you reliably tell that a statement like

SELECT pg_create_physical_replication_slot('aname');

won't modify your database?

There is pgPool which boasts such a feature, you might investigate it.

The better technique is usually for your application to use two database connections: one for reading and one for data modifications. That doesn't put you at the mercy of a tool that tries to guess.