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 :(

[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