2

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?

1 Answer 1

4

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.

1
  • I totally agree that the application should make two connections but both connections can go to the same PgBouncer process. You can then use e.g. dbname to select the correct config (where the connection string defines the actual host and database name to use for that logical client connected dbname). Jun 20, 2023 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.