search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on Patroni). Automating with Ansible.
https://postgresql-cluster.org
MIT License
1.69k stars 411 forks source link

Two pgbouncer : One for Master node and one for Secondary node #504

Closed kumarashish071092 closed 11 months ago

kumarashish071092 commented 11 months ago

Can we modify the existing playbook to have two pgbouncer ports each for the master and secondary node?

vitabaks commented 11 months ago

@kumarashish071092 why do you need two different ports?

It is important to note that in the current database cluster schemes, pgbouncer is not a load balancer, it only acts as a connection puller and is installed directly in front of the database, routing connections over a local Unix socket.

If you need load balancing (different ports for different database roles), then the "Tour A" scheme using the HAProxy load balancer is suitable for you.

kumarashish071092 commented 11 months ago

Thanks Vitaly for the revert.

I have already installed the HAProxy load balancer . So can we have different connection pool for read and write nodes.

Port 6432 for pgbouncer write/read and port 6533 for pgbouncer read only.

Get Outlook for iOShttps://aka.ms/o0ukef

From: Vitaliy Kukharik @.> Sent: Thursday, November 16, 2023 4:00:41 PM To: vitabaks/postgresql_cluster @.> Cc: Kumar Ashish @.>; Mention @.> Subject: Re: [vitabaks/postgresql_cluster] Two pgbouncer : One for Master node and one for Secondary node (Issue #504)

[Email from a non-Nagarro source: please exercise caution with links and attachments]

@kumarashish071092https://github.com/kumarashish071092 why do you need two different ports?

It is important to note that in the current database cluster schemes, pgbouncer is not a load balancer, it only acts as a connection puller and is installed directly in front of the database, routing connections over a local Unix socket.

If you need load balancing (different ports for different database roles), then the "Tour A" scheme using the HAProxy load balancer is suitable for you.

— Reply to this email directly, view it on GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/504#issuecomment-1814175529, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3RAW64UP7G5AIUOXRLYEXTNDAVCNFSM6AAAAAA7N2WHL2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJUGE3TKNJSHE. You are receiving this because you were mentioned.Message ID: @.***>

vitabaks commented 11 months ago

Yes, "Tour A" scheme provides the ability to distribute the load on reading.

port 5000 (read / write) master port 5001 (read only) all replicas

So actually the connections will be routed to different servers (different connection pools).

kumarashish071092 commented 11 months ago

So 5000 and 5001 will act as different connection pooling or it will only act as load balancer??

Get Outlook for iOShttps://aka.ms/o0ukef

From: Vitaliy Kukharik @.> Sent: Thursday, November 16, 2023 4:08:32 PM To: vitabaks/postgresql_cluster @.> Cc: Kumar Ashish @.>; Mention @.> Subject: Re: [vitabaks/postgresql_cluster] Two pgbouncer : One for Master node and one for Secondary node (Issue #504)

[Email from a non-Nagarro source: please exercise caution with links and attachments]

Yes, "Tour A" scheme provides the ability to distribute the load on reading.

port 5000 (read / write) master port 5001 (read only) all replicas

So actually the connections will be routed to different servers (different connection pools).

— Reply to this email directly, view it on GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/504#issuecomment-1814187581, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3RO7BSIXC4PCP4GEJLYEXUKRAVCNFSM6AAAAAA7N2WHL2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJUGE4DONJYGE. You are receiving this because you were mentioned.Message ID: @.***>

vitabaks commented 11 months ago

Haproxy will direct the load on port 5000 to the pgbouncer on the server with the role of "leader" (Primaty) and connections on port 5001 to the pgbouncer server with the role of "Replica".

image

kumarashish071092 commented 11 months ago

Thanks for the detailed explanation. Please close this issue now.

Get Outlook for iOShttps://aka.ms/o0ukef

From: Vitaliy Kukharik @.> Sent: Thursday, November 16, 2023 4:25:12 PM To: vitabaks/postgresql_cluster @.> Cc: Kumar Ashish @.>; Mention @.> Subject: Re: [vitabaks/postgresql_cluster] Two pgbouncer : One for Master node and one for Secondary node (Issue #504)

[Email from a non-Nagarro source: please exercise caution with links and attachments]

Haproxy will direct the load on port 5000 to the pgbouncer on the server with the role of "leader" (Primaty) and connections on port 5001 to the pgbouncer server with the role of "Replica".

[image]https://user-images.githubusercontent.com/37010174/283425011-532d3a6a-d245-4cfd-8ac4-4821201e30b6.png

— Reply to this email directly, view it on GitHubhttps://github.com/vitabaks/postgresql_cluster/issues/504#issuecomment-1814213213, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVXUU3QM5FA5WFZ2SSYMAJTYEXWJBAVCNFSM6AAAAAA7N2WHL2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJUGIYTGMRRGM. You are receiving this because you were mentioned.Message ID: @.***>