CODEWITHSUNDEEP
postgresqlruby-on-rails-3.2
Jerome
Jerome

Reputation: 6189

PostgreSQL master-slave replication redirecting writes to master

Master replication being set-up and certifiably functioning, the need is to be able to re-direct users write actions to the master from the slave, otherwise :

PG::ReadOnlySqlTransaction: ERROR:  cannot execute UPDATE in a read-only transaction

My understanding of M-S replication may be wildly off base... But having not found parameters that deal with directing writes to master server, I assumed it was part of the underlying functions defined in each postgresql.conf file.

How can update/create be passed by the slave to the master?

Upvotes: 0

Views: 1741

Answers (1)

MatheusOl
MatheusOl

Reputation: 11825

You cannot! PostgreSQL does not have the ability to redirect writes to master node, hence the slave will only accept read-only transactions.

One tool that acts as a middleware and can do load balancing of read-only statements between master and the slaves, but also redirecting write commands/transactions to master is PgPool-II. If you can make your application to differentiate (through different data sources, for instance) read-only and read-write transactions, the performance will be better though and you can redirect read-only to a TCP load balancer.

Upvotes: 1

Related Questions