How to implement load balancing on a PostgreSQL cluster so that read requests go only to the slave?

Question

PostgreSQL

Good afternoon!
There is a task to distribute queries to the PostgreSQL cluster according to the principle "for writing - to the master, for reading - only to the slave". Is there a solution that will allow you not to change the code of the application itself that sends requests? In the world of MySQL, as far as I remember, Percona ProxySQL can do this, are there analogues for Postgres?
Thank you.

Answer 1

But as? Well, in plain text: how is it possible in principle and in theory to do this without the participation of the application?

begin isolation level repeatable read ;
select ... from users;
-- а дальше-то что?

Okay, you can really delay begin until the next request arrives, but absolutely no later than the select request, you must decide whether to send this transaction to the replica or to the master. How do you define it? With equal probability, the next command could be a commit, another select, or a write query. You can’t find out in any way, but you already need to send this transaction somewhere and get the result. And in exactly the same way, you cannot send a request first to the replica, and then write to the master, because the transaction snapshot will fall apart. Is it a read or write request? Yes, the database itself does not know this! Horseradish will figure it out what this pl / python store in a language that is incomprehensible to the base will decide to do.
select * from calculate_user_rating(423);
Replicas should be able to use the application itself. In addition to the application itself, no one else knows whether it is permissible to send a request to replicas and, if so, to which ones.

Answer 2

Found this article in my bookmarks, haven't tried it myself - https://www.percona.com/blog/2019/10/31/postgresql...