Re: PostgreSQL: How can use slave for some read operations?

[Muhammed Fahid]

Thank you for the clarification.

On Wed, Mar 15, 2023 at 1:42 PM Илья Шипицин  wrote:

> there are several L7 balancing tool like pgPool.
>
> as for haproxy, currently it does not provide such advanced postgresql
> routing
>
> ср, 15 мар. 2023 г. в 06:09, Muhammed Fahid :
>
>> Hi,
>>
>> I have A master and a slave PostgreSQL databases. I would like to know
>> that major read operations can be processed with slave for reducing load in
>> master ??
>>
>> for example : I have a large number of products.when customers want to
>> list all products. Is it possible to read from a slave database? instead of
>> from the master database ?. If major read operations are done in master its
>> slows down the other operations in master.
>>
>

Re: PostgreSQL: How can use slave for some read operations?

[Brendan Kearney]

what i have done is create frontends and backends for all of the load 
balanced nodes, and separate f/e and b/e for the individual nodes.  for 
instance:


frontend mariadb
    mode tcp
    bind 192.168.120.3:3306
    default_backend mariadb

frontend mariadb1
    mode tcp
    bind 192.168.120.3:3316
    default_backend mariadb1

frontend mariadb2
    mode tcp
    bind 192.168.120.3:3326
    default_backend mariadb2

frontend mariadb3
    mode tcp
    bind 192.168.120.3:3336
    default_backend mariadb3

...

backend mariadb
    source 192.168.120.3
    mode tcp
    option mysql-check user haproxy

    server mariadb1 192.168.88.1:3306 check inter 1 send-proxy-v2
    server mariadb2 192.168.88.2:3306 check inter 1 send-proxy-v2
    server mariadb3 192.168.88.3:3306 check inter 1 send-proxy-v2

backend mariadb1
    source 192.168.120.3
    mode tcp
    option mysql-check user haproxy
    server mariadb1 192.168.88.1:3306 check inter 1 send-proxy-v2

backend mariadb2
    source 192.168.120.3
    mode tcp
    option mysql-check user haproxy
    server mariadb2 192.168.88.2:3306 check inter 1 send-proxy-v2

backend mariadb3
    source 192.168.120.3
    mode tcp
    option mysql-check user haproxy
    server mariadb3 192.168.88.3:3306 check inter 1 send-proxy-v2

by doing this, i can load balance across all mariadb nodes using port 
3306, but also hit each of the nodes individually using the same VIP 
name, but a different port (3316, 3326, 3336).  i chose to keep the same 
frontend IP, so that kerberos authentication still works, as the krb 
principal is tied to the DNS name of the VIP.


essentially you would wind up with different VIPs for the R/W access and 
R/O access.


HTH,

brendan kearney

On 3/15/23 4:12 AM, Илья Шипицин wrote:

there are several L7 balancing tool like pgPool.

as for haproxy, currently it does not provide such advanced postgresql 
routing


ср, 15 мар. 2023 г. в 06:09, Muhammed Fahid :

Hi,

I have A master and a slave PostgreSQL databases. I would like to
know that major read operations can be processed with slave for
reducing load in master ??

for example : I have a large number of products.when customers
want to list all products. Is it possible to read from a slave
database? instead of from the master database ?. If major read
operations are done in master its slows down the other operations
in master.

Re: PostgreSQL: How can use slave for some read operations?

[Илья Шипицин]

there are several L7 balancing tool like pgPool.

as for haproxy, currently it does not provide such advanced postgresql
routing

ср, 15 мар. 2023 г. в 06:09, Muhammed Fahid :

> Hi,
>
> I have A master and a slave PostgreSQL databases. I would like to know
> that major read operations can be processed with slave for reducing load in
> master ??
>
> for example : I have a large number of products.when customers want to
> list all products. Is it possible to read from a slave database? instead of
> from the master database ?. If major read operations are done in master its
> slows down the other operations in master.
>

PostgreSQL: How can use slave for some read operations?

[Muhammed Fahid]

Hi,

I have A master and a slave PostgreSQL databases. I would like to know that
major read operations can be processed with slave for reducing load in
master ??

for example : I have a large number of products.when customers want to list
all products. Is it possible to read from a slave database? instead of from
the master database ?. If major read operations are done in master its
slows down the other operations in master.