Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gw1uj-0003sM-UF for pgsql-admin@arkaria.postgresql.org; Tue, 19 Feb 2019 09:42:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gw1ui-0005KP-O8 for pgsql-admin@arkaria.postgresql.org; Tue, 19 Feb 2019 09:42:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gw1ui-0005GM-Eg for pgsql-admin@lists.postgresql.org; Tue, 19 Feb 2019 09:42:08 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gw1uf-0004BG-GZ for pgsql-admin@lists.postgresql.org; Tue, 19 Feb 2019 09:42:08 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.14.3/8.14.3/Debian-9.4) with ESMTP id x1J9fvYU025716 for ; Tue, 19 Feb 2019 11:42:02 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id x1J9fvun044365 for ; Tue, 19 Feb 2019 11:41:57 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: Load balancing in postgres master slave HA cluster To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios Message-ID: Date: Tue, 19 Feb 2019 11:41:57 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.3.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------F183FCABD7763CD9E72CFF74" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.98.7 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-27.9 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_NAUTICAL,HTML_MESSAGE,TW_PG,URIBL_BLOCKED autolearn=unavailable version=3.3.1 X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------F183FCABD7763CD9E72CFF74 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 19/2/19 11:13 π.μ., soumitra bhandary wrote: > 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 --------------F183FCABD7763CD9E72CFF74 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 19/2/19 11:13 π.μ., soumitra bhandary wrote:
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
--------------F183FCABD7763CD9E72CFF74--