Replies: 5 comments 24 replies
-
|
Hello @z7x The vip-manager is designed to run the VIP on the server where the leader (Primary) Postgres role is, so it is expected that it will be one subnet. In your case, there may be PostgreSQL High-Availability with HAProxy Load Balancing scheme using HAProxy and keepalived (to provide an VIP address). In our configuration keepalived checks the status of the HAProxy service and in case of a failure delegates the VIP to another balancer server. That is, VIP is no longer tied to the leader role. I've already had a similar discussion on the Slack channel (available to project sponsors), I'll duplicate it for you:
I think this is possible if you set |
Beta Was this translation helpful? Give feedback.
-
|
Hi @vitabaks Thanks for your reply. Can I re-run the "deploy" command with Method 2 to change from pgboucner to HAproxy load balancer and then add additional VIP instances? Are you open to doing some paid work for me, as I am not an expert :) (I didn't know how to message you privately, so please feel free to remove this line from here when you see it) |
Beta Was this translation helpful? Give feedback.
-
Note: Here we have added the vip_manager_disable variable to disable it
Please see the sponsoring program to get personalized support. |
Beta Was this translation helpful? Give feedback.
-
Would you mind elaborating on this? How do I deploy individual inventory to each site separately, ensuring the replication is running between all servers, whilst keeping only one leader server and two HAproxy/load balancers? Many thanks |
Beta Was this translation helpful? Give feedback.
-
|
Use 3 separate inventory files for this task. First, deploy the database cluster, and then the load balancers separately for each site. Database clusterNote: Leave the 'with_haproxy_load_balancing' variable with the value 'false', and comment out the 'cluster_vip' variable in vars/main.yml.
Note: It is assumed that the ETCD cluster is located in 3 sites, see examples here Deploy a database cluster: Load balancers (site A)
Note: Here we define the ETCD nodes of the cluster because the Deploy the load balancers for site A
Load balancers (site B)
Deploy the load balancers for site B
Note: In these examples, the load balancers are located on database servers, but they can also be dedicated servers. A minimum of 2 load balancer servers per site is recommended. therefore, you could think about dedicated VMs for load balancers, or add second VM with a database for the second site and install the load balancer on servers with databases. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @vitabaks, I successfully deployed all three inventories with the following output for the first inventory file (no errors in any of the three). I ended up creating 4 Ubuntu server VMs on Site A and Site B. The IPs are different this time.
The HAProxy log is this: Edit2: It seems I can connect using port 5000 to all 4 servers and make changes on all of them (is this expected?), but the VIPs I set for both sites, 10.2.7.39 and 192.168.16.39, are currently dead. |
Beta Was this translation helpful? Give feedback.
-
|
In this case, given your "non-standard" configuration, do not pay attention to the output of "PostgreSQL Cluster connection info". Use port 5000 and the load balancer will direct traffic to the Primary server. Details here |
Beta Was this translation helpful? Give feedback.
-
Can I get the playbook execution log for the load balancer? check the list of IP addresses on balancer servers ( |
Beta Was this translation helpful? Give feedback.
-
|
I think I understood what was going on, the (empty) |
Beta Was this translation helpful? Give feedback.
-
Thank you so much. This worked, and all seems to be working as expected. 😎 (I think)Can I check with you to see if I enabled SSL correctly? I went to pgbouncer.ini on each server to manually specify the parameters as below. I obtained this wildcard SSL from my IONOS account and I placed it in pgbouncer directory. I wonder if there are any parameters I need to specify, either in certbot or pgbouncer. I'm looking into this. |
Beta Was this translation helpful? Give feedback.
-
|
At the moment, TLS is not automatically configured, but it is in the plans - #361 |
Beta Was this translation helpful? Give feedback.
-
|
See pgbouncer variables in pgbouncer_client_tls_sslmode: "disable"
pgbouncer_client_tls_key_file: ""
pgbouncer_client_tls_cert_file: ""This is preferable to manual actions. |
Beta Was this translation helpful? Give feedback.
-
|
Manual changes are not desirable. For example, you specified |
Beta Was this translation helpful? Give feedback.
-
|
This was just a typo when I was replying to your message. 👍🏼 If I run Otherwise, so far so good. I imported a table with 530k rows through a DNS hostname pointing to both VIPs. All worked really well! |
Beta Was this translation helpful? Give feedback.
-
|
Hi @vitabaks I noticed a problem with my setup. I took Site B down (both VMs) to test, and I could no longer connect via Site A VIP until at least one of the VMs at Site B was running and vice versa. I can't connect at all if one of the sites is completely down. Any ideas what could have gone wrong? Could Apache2 interfere with any of these components? My colleague installed it a few days ago on all 4 servers to build custom PHP API access. Although, now thinking about it, I don't think I actually tested taking one site down when I thought it was all working. These are the recent logs ETCD service status: Keepalived service status: |
Beta Was this translation helpful? Give feedback.
-
Standby Cluster may be suitable for you. This is when two sites have two separate clusters etcd and patroni, where the second (standby) cluster is replicated from the main cluster. In this configuration, there is no automatic failover to the second site, it serves for disaster recovery (DR). Details https://patroni.readthedocs.io/en/latest/replica_bootstrap.html#standby-cluster |
Beta Was this translation helpful? Give feedback.
-
|
Can I check the following in order to set up a standby cluster? Site A inventory Site B inventory In terms of Site A main.yml Site B main.yml Did I miss anything or did I get it wrong? I can use REST API to promote the Standby Cluster to become the new Master Cluster when the original master goes down and then back to the original master when it's back online. (according to their documentation, it should work with our cloud workflows) |
Beta Was this translation helpful? Give feedback.
-
Yes, you can specify any cluster name at your discretion.
Yes it should work.
After the backup cluster is promoted (if the cluster is lost on site A) You need to create a new cluster on site A and make it a Standby cluster. Before promote:
After promote:
|
Beta Was this translation helpful? Give feedback.
-
|
In your inventory, I see that only two clusters are listed in the "etcd_cluster" group, at least 3 servers are required for the DCS cluster. You can deploy an etcd cluster on separate 3 small servers and use it for multiple Postgres clusters where there may already be 2 servers in a database cluster. It is important to understand that the DCS cluster and the Postgres cluster are different clusters. The requirement for a minimum of 3 servers applies to the DCS cluster. Please see: |
Beta Was this translation helpful? Give feedback.
-
|
you need “manually promote” the healthy cluster by removing standby_cluster section from the dynamic configuration. Promote standby cluster via patronictl |
Beta Was this translation helpful? Give feedback.
-
This is my setup atm This was my idea of my configuration. The subdomain can be controlled/updated by our custom API cloud workflow and A records updated/added/removed etc. |
Beta Was this translation helpful? Give feedback.
-
|
|
Beta Was this translation helpful? Give feedback.
-
|
Beta Was this translation helpful? Give feedback.
-
So, I managed to set up three Ubuntu VMs thanks to @vitabaks (thank you) with the following IPs:
Site 1
10.2.7.44
10.2.7.45
Site 2
192.168.16.46
The VIP is 10.2.7.48, but if Site 1 goes down (i.e. due to a power outage), I'd like to have a second VIP, say 192.168.16.48, to be waiting for connections.
Is this possible?
Can I simply edit vip-manager.yml and change the IP to 192.168.16.48 on Site 2? It seems to be switching over correctly, but do I use DNS round-robin to work with this?
Beta Was this translation helpful? Give feedback.
All reactions