I'd like to setup the following infrastructure:

PSQL 9.6 in USA (master)

PSQL 9.6 in Europe (slave)

PgPool-II in Europe

App Server in Europe (it reads the DB in 90% of time).

My question is: Can I set write-only and read-only databases in pgpool? I'd like to send all my reads to slave and every write (but ONLY write) to master. Is this possible? Or in master/slave mode the read queries are sended to both master and slave?

1 Answer 1


The right way to handle this is to set up two different database users user_ro and user_rw for instance and then you grant usage on the schemas you need to access and grant only SELECT to the read-only user and all privileges to the read-write user. You then create two database connections in your application and use the appropriate one.

You cannot use pgpool-II to automatically distribute read and write queries because there is no way it can programatically inspect a query and say that it will not produce a write in the database. Even a simple select * from sometable; can trigger a write if there is a trigger on the table to record accesses ( a common scenario for sensitive data).

pgpool-II does a great job of keeping a connection pool open and ready and managing failover from master to replica transparently to the application but it won't solve the problem you have without using two separate connections and dealing with those connections in your application code. There are some ways to finesse this; like attempting all queries on all of the read replicas and retrying ones that failed on the master. But from a conceptual hygiene standpoint it's better to decide when you are are writing to the database and when you are reading from it.

  • 1
    is this answer still valid? According to Pgpool 2 documentation pgpool.net/docs/latest/en/html/…, it can do Load balancing of read/rewrite queries that are based on specific conditions.
    – Hutsul
    Sep 7, 2023 at 19:45
  • It looks like pgpool-II has been improved since 2017.
    – Larry
    Sep 29, 2023 at 0:59

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.