Обсуждение: Load balancing in postgres master slave HA cluster
Hi ,
Any one has experience on working with HA proxy set up with Postgres Master Slave replication .
Query : I would like to divert the Write requests to Master and all read requests to Slave server .
To achieve that can I use HA proxy server ? If any one has already implemented the same . Please share me the steps .
Or ,
Any other method can be used to achieve the same using freeware .
Thanks
Soumitra
Use pgpool to achieve query balancing. It can send inserts/updates etc to master and selects to all nodes.
Flow: app_server(app+pgpool) -> db_servers(pg_master +pg_slave1+pg_slave2)
On Tue, Feb 19, 2019 at 2:44 PM soumitra bhandary <soumitra.bhandary@hotmail.com> wrote:
Hi ,Any one has experience on working with HA proxy set up with Postgres Master Slave replication .Query : I would like to divert the Write requests to Master and all read requests to Slave server .To achieve that can I use HA proxy server ? If any one has already implemented the same . Please share me the steps .Or ,Any other method can be used to achieve the same using freeware .ThanksSoumitra
Use pgpool to achieve query balancing. It can send inserts/updates etc to master and selects to all nodes.
Flow: app_server(app+pgpool) -> db_servers(pg_master +pg_slave1+pg_slave2)
On Tue, Feb 19, 2019 at 2:44 PM soumitra bhandary <soumitra.bhandary@hotmail.com> wrote:
Hi ,Any one has experience on working with HA proxy set up with Postgres Master Slave replication .Query : I would like to divert the Write requests to Master and all read requests to Slave server .To achieve that can I use HA proxy server ? If any one has already implemented the same . Please share me the steps .Or ,Any other method can be used to achieve the same using freeware .ThanksSoumitra
Hi Sumitra,
You can achieve this by using the pgpool which support load balancing/load connections and allow huge insert/updates. Either you can divert your read queries to slave for read and write to master for read/write operations
On Tue, Feb 19, 2019 at 2:44 PM soumitra bhandary <soumitra.bhandary@hotmail.com> wrote:
Hi ,Any one has experience on working with HA proxy set up with Postgres Master Slave replication .Query : I would like to divert the Write requests to Master and all read requests to Slave server .To achieve that can I use HA proxy server ? If any one has already implemented the same . Please share me the steps .Or ,Any other method can be used to achieve the same using freeware .ThanksSoumitra
Hi Sumitra,
You can achieve this by using the pgpool which support load balancing/load connections and allow huge insert/updates. Either you can divert your read queries to slave for read and write to master for read/write operations
On Tue, Feb 19, 2019 at 2:44 PM soumitra bhandary <soumitra.bhandary@hotmail.com> wrote:
Hi ,Any one has experience on working with HA proxy set up with Postgres Master Slave replication .Query : I would like to divert the Write requests to Master and all read requests to Slave server .To achieve that can I use HA proxy server ? If any one has already implemented the same . Please share me the steps .Or ,Any other method can be used to achieve the same using freeware .ThanksSoumitra
On 19/2/19 11:13 π.μ., soumitra bhandary wrote:
HAProxy does not know the specifics of pgsql protocol neither does it parse queries. So your infrastructure has to know in advance which activity should be redirected to the readonly port and which to the readwrite port. So in essence you will have to have at least two datasources defined .P {margin-top:0;margin-bottom:0;} Hi ,Any one has experience on working with HA proxy set up with Postgres Master Slave replication .
You may find this helpful : https://severalnines.com/blog/postgresql-load-balancing-using-haproxy-keepalived
Query : I would like to divert the Write requests to Master and all read requests to Slave server .To achieve that can I use HA proxy server ? If any one has already implemented the same . Please share me the steps .Or ,Any other method can be used to achieve the same using freeware .ThanksSoumitra
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt