In this video I am showing you how to setup PostgreSQL HA cluster with Patroni, we are using HA proxy as a load-balancer and etcd is our "witness" Configuration Files: dem-linux.com/...
Added a small change to the ha proxy configuration: frontend patroni-prod mode tcp maxconn 5000 bind *:5432 default_backend patroni_servers backend patroni_servers mode tcp option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server node1 10.10.0.181:5432 maxconn 100 check port 8008 server node2 10.10.0.182:5432 maxconn 100 check port 8008 This will allow you to talk to the databases by HA-PROXY-IP:5432, this means that applications would use the load-balancer IP to reach the databases. If not prefred then just use psql -U postgres -h load-balancer-ip -p 5000
Hi dear, do I have to create these users admin, postgres and replicator manually or should Patron create it automatically? When I start Patroni on Ubuntu 22.04 . Do I also have to start postgresql.service and postgresql@12-main.service ???
Hey, no need to create the users, and and new version of patroni will start postgres as well, so you only need patroni service, you can also check for pid and see if postgres starts with patroni service, if not then start postgres.
patroni cluster has no leader and it show like I'm setting up a Patroni cluster and facing an issue where both nodes remain in the 'stopped' state, and the logs indicate that the system is waiting for a leader to bootstrap. Here is the output when I run patronictl -c /etc/postgres0.yml list: Cluster: postgres (7366xxxxxxxxxxxxxxx) ---------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-------------+----------------+---------+---------+----+-----------+ | postgresql0 | xxx.xxx.xx.57 | Replica | stopped | | unknown | | postgresql1 | xxx.xxx.xx.129 | Replica | stopped | | unknown | +-------------+----------------+---------+---------+----+----------- Kindly help me to solve this issue
I do the same you but aways meet error >> CRITICAL: system ID mismatch, node patrono-node2 belongs to a different cluster: 7396713246569953512 != 7396707261106546582
Check the etcd, seems like you have more then one etcd and they are not in the cluster, remove one etcd and delete the cluster by deleting the datadir of patrini and reinit the cluster
Hi, that guy. According to FHS, the /mnt directory is reserved for temporarily mounted file systems. You shouldn't be configuring databases there. /srv, /var /mypatronidb are all better choices. As for the insecure md5 algorithm, it's been ditched by Postgres and defaults to scram-sha-256, starting from v14 you use in this video. You can just change that in your configs and all will work.
How do we ensure our connections through HAProxy are not failing in case the HAProxy (Node4) goes down? Is there a way we can setup HA for HAProxy loadbalancing ?
@@dem_linux9021 I'm glad you responded almost immediately. Can't thank u enough. Any plans on making a video on it? I'm not a Linux expert but a HADR PostGreSQL is the need for hour in my environment.
@@yashkotari hey, I can try to make a video with keepalived it's not that hard all you need is a conf on both ha proxy with a virtual IP, example conf file on ha proxy 1: vrrp_script chk_haproxy { script "/usr/bin/killall -0 haproxy" interval 2 weight 2 } vrrp_instance VI_1 { interface eth0 virtual_router_id 51 state MASTER priority 101 virtual_ipaddress { 10.10.0.150 } track_script { chk_haproxy } } and on the ha proxy2: vrrp_script chk_haproxy { script "/usr/bin/killall -0 haproxy" interval 2 weight 2 } vrrp_instance VI_1 { interface eth0 virtual_router_id 51 state SLAVE priority 101 virtual_ipaddress { 10.10.0.150 } track_script { chk_haproxy } } now you would use 10.10.0.150 as your entry to the ha proxy, so in this case maybe add a dns record ha-proxy.local > 10.10.0.150. This IP will then switch between ha proxy 1 and ha proxy 2 depending on the state. I will try to make a fast video about it this week :)
@@dem_linux9021 Hi brother, can you tell me if HA proxy is required for failover? Or can I just skip installing the HA Proxy and still if the master dies the replica takes its place?
authentication: replication: username: replicator password: password superuser: username: postgres password: password Here, should I create this user & password manually after install postgres on all node?Is there any other configuration needed on postgres node?
Hi, you could run on 1 server both etcd and proxy but if your server dies you lose the whole point of High availability and etcd at the same time, so nothing that I would recommend.
your tutorial is helpful for me, but can you show me how to access that cluster (node1 & node2) via node-etcd? so my apps just only need to access node-etcd ip to connect that clustered database
Hi, you can just use ip to the load balancer to access the database that’s why I use haproxy it will automatically handle failover and point to the leader/master node.
