posts/postgresql-high-availability/

[giscus[bot]]

posts/postgresql-high-availability/

Today is the day that you start running PostgresSQL in a cluster! In this tutorial we’ll be setting up a production ready Postgres cluster that’s highly available and fault tolerant using PostgreSQL, etcd, Patroni, HA Proxy, and keepalived. This resilient combination will ensure that you can always reach your database even when a node in the cluster goes down!

https://technotim.live/posts/postgresql-high-availability/

[Po3Tato]

Great walk through. Typo on the VIP "ping 192.168.60.100" instead of "192.168.60.110". Also i am working to figure out how to have the network being handled by Tailscale. Stuck at the VIP part but gaining some progress.

[mayrink85]

Also noticed this @Po3Tato and have submitted a PR to fix this typo.

[m1ckyb]

In the Verifying Our Postgres Cluster section, the ports are wrong, you have :8080 but should be 8008 :)

But thanks for this guide..

[Po3Tato]

Got a question. Getting this error
'''
mn-postgresql-srv02 etcd[1358]: {"level":"warn","ts":"2024-12-28T23:42:08.875944Z","caller":"embed/config_logging.go:170","msg":"rejected connection on client endpoint","remote-addr":"127.0.0.1:58096","server-name":"","error":"tls: first record does not look like a TLS handshake"}
'''
My certs look fine. Got any tips?

[mayrink85]

Hi @m1ckyb I saw the same thing and have submitted a PR to fix this.

[niwiunig]

Hey, may i ask what is the problem, i am stuck at this :(

[gforke97]

I got stuck on that too, it works if you pass the cert locations to the etcdctl command like this:
etcdctl endpoint health --cacert="/etc/etcd/ssl/ca.crt" --cert=$(ls /etc/etcd/ssl/etcd-.crt) --key=$(ls /etc/etcd/ssl/etcd-.key)
etcdctl member list --cacert="/etc/etcd/ssl/ca.crt" --cert=$(ls /etc/etcd/ssl/etcd-.crt) --key=$(ls /etc/etcd/ssl/etcd-.key)

[gforke97]

etcdctl endpoint health --cacert="/etc/etcd/ssl/ca.crt" --cert=$(ls /etc/etcd/ssl/etcd-*.crt) --key=$(ls /etc/etcd/ssl/etcd-*.key)
etcdctl member list --cacert="/etc/etcd/ssl/ca.crt" --cert=$(ls /etc/etcd/ssl/etcd-*.crt) --key=$(ls /etc/etcd/ssl/etcd-*.key)
Markdown formmating removed the wildcards above...

[MrSliff]

Hey, thanks for the Guide.

I am stuck at the Patroni Part, seems Patroni is not able to connect to etcd. I get a 404 not found Error on Patroni Logs. Etcd itself is working fine.

Patroni Logs:

Feb 24 00:00:05 pg3node1.example.com patroni[2328]: 2025-02-24 00:00:05,446 ERROR: Failed to get list of machines from https://192.168.25.102:2380/v3: <Unknown error: '404 page not found', code: 2>
Feb 24 00:00:05 pg3node1..example.com patroni[2328]: 2025-02-24 00:00:05,496 ERROR: Failed to get list of machines from https://192.168.25.101:2380/v3: <Unknown error: '404 page not found', code: 2>
Feb 24 00:00:05 pg3node1..example.com patroni[2328]: 2025-02-24 00:00:05,504 ERROR: Failed to get list of machines from https://192.168.25.103:2380/v3: <Unknown error: '404 page not found', code: 2>
Feb 24 00:00:05 pg3node1.example.com patroni[2328]: 2025-02-24 00:00:05,513 ERROR: KVCache.run <Unknown error: '404 page not found', code: 2>

Etcd:

sudo etcdctl \

--endpoints=https://192.168.25.101:2379,https://192.168.25.102:2379,https://192.168.25.103:2379
--cacert=/etc/etcd/ssl/ca.crt
--cert=/etc/etcd/ssl/etcd-node1.crt
--key=/etc/etcd/ssl/etcd-node1.key
endpoint status --write-out=table
+-----------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| ENDPOINT | ID | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+-----------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| https://192.168.25.101:2379 | 1a331161db35df36 | 3.5.18 | 20 kB | false | false | 16 | 53 | 53 | |
| https://192.168.25.102:2379 | 8d19e5479f15a22d | 3.5.18 | 20 kB | false | false | 16 | 53 | 53 | |
| https://192.168.25.103:2379 | b8905488b0c0e243 | 3.5.18 | 20 kB | true | false | 16 | 53 | 53 | |
+-----------------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

[KevinKao809]

May I know what is HAPROXY version you use? I am using 2.0.33 but got some exception.

[RampedIndent]

This tutorial is great, but it is missing one critical thing to be production ready....

It doesn't have backups configured

[Mailstorm-ctrl]

Can't seem to get this working. I either get an error such as "error":"tls: first record does not look like a TLS handshake" when checking etcd status or I'll get "tls: private key does not match public key" which is crazy because they do or I'll get the listening messages but when checking endpoint health I will get an unhealthy cluster for some reason.

[gforke97]

you need to pass it the SSL Infos (in the tutorial he just does that without explanation if you scroll down...)
etcdctl endpoint health --cacert="/etc/etcd/ssl/ca.crt" --cert=$(ls /etc/etcd/ssl/etcd-*.crt) --key=$(ls /etc/etcd/ssl/etcd-*.key)
etcdctl member list --cacert="/etc/etcd/ssl/ca.crt" --cert=$(ls /etc/etcd/ssl/etcd-*.crt) --key=$(ls /etc/etcd/ssl/etcd-*.key)

[gforke97]

and later for the patroni commands you need to pass it the config file and for commands that send stuff back like switching nodes -k for insecure...
patronictl -c /etc/patroni/patroni.yml list

[Mailstorm-ctrl]

I see that now. But I was getting errors before because I was looking at etcd documentation to test the endpoint. I ended up remaking the ca and it's working now for some reason

[Kamoliddinxoji]

There may be an issue with the HAProxy timeouts in the postgres_backend section. The following values:

timeout connect 5s
timeout client 30s
timeout server 30s

are too short for many PostgreSQL clients like pgAdmin or application pools that keep idle connections open. This can cause unexpected disconnections after 30 seconds of inactivity.

[Iraithium]

I was thinking the same thing. What did you end up changing your values to?

[Judg3d]

Its great except in my experience (very limited btw) etcd cant handle having nodes down. When i bring any node down for whatever reason (i have a 5 node cluster) the whole thing just freezes

[dulhaver]

a comment on your pg_hba settings ...

• md5 - is water under the bridge since quite some postgres versions
• scram-sha-256 - should be used as algorithm for password encryption