Postgres Pro
Downloads
Home   >   mailing lists

Thread: Load balancing in postgres master slave HA cluster

Load balancing in postgres master slave HA cluster

From
soumitra bhandary
Date:
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 

Re: Load balancing in postgres master slave HA cluster

From
Prince Pathria
Date:
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)

Happy to help :)
Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com


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 . 

Thanks 
Soumitra 

Re: Load balancing in postgres master slave HA cluster

From
Prince Pathria
Date:
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)

Happy to help :)
Prince Pathria Systems Architect Intern Evive +91 9478670472 goevive.com


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 . 

Thanks 
Soumitra 

Re: Load balancing in postgres master slave HA cluster

From
Shreeyansh Dba
Date:
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

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


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 . 

Thanks 
Soumitra 

Re: Load balancing in postgres master slave HA cluster

From
Shreeyansh Dba
Date:
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

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


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 . 

Thanks 
Soumitra 

Re: Load balancing in postgres master slave HA cluster

From
Achilleas Mantzios
Date:
On 19/2/19 11:13 π.μ., soumitra bhandary wrote:
P {margin-top:0;margin-bottom:0;}
Hi , 

Any one has experience on working with HA proxy set up with Postgres Master Slave replication  .
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 .

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 . 

Thanks 
Soumitra 


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt