PostgreSQL – Using PgPool II for Write-Only and Read-Only Databases

pgpoolpostgresql

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?

Best Answer

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.

Related Question