PostgresSQL Clustering the hard way
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!
Nodes
# ha proxy 192.168.60.100 # haproxy-01 192.168.60.101 # haproxy-02 192.168.60.102 # haproxy-03 # postgres 192.168.60.103 # postgres-01 192.168.60.104 # postgres-02 192.168.60.105 # postgres-03
PostgreSQL
On postgres nodes install latest postgres.
Install updated postgres repositories.
sudo apt update sudo apt install -y postgresql-common sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
We’ll configure postgres later. Stop and disable the service because Patroni will handle the lifecycle of postgres
sudo systemctl stop postgresql sudo systemctl disable postgresql
etcd
Install
This should be installed on the postgres servers.
Make sure you have wget and curl
sudo apt update sudo apt-get install -y wget curl
Find latest release. https://github.com/etcd-io/etcd/releases
wget https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz
Uncompress and rename.
tar xvf etcd-v3.5.17-linux-amd64.tar.gz mv etcd-v3.5.17-linux-amd64 etcd
Move all binaries into /usr/local/bin/ for later use.
sudo mv etcd/etcd* /usr/local/bin/
Check etcd version
etcd --version etcdctl version
Let’s create a user for etcd service.
sudo useradd --system --home /var/lib/etcd --shell /bin/false etcd
Before configuring etcd, we need to repeat all of these steps for the other 2 nodes.
Let’s configure etcd.
Make dir and edit file.
sudo mkdir -p /etc/etcd sudo mkdir -p /etc/etcd/ssl
Linux
sudo apt install openssl
Verify it’s installed and working
openssl version
Should see something like:
OpenSSL 3.4.0 22 Oct 2024 (Library: OpenSSL 3.4.0 22 Oct 2024)
Now let’s generate and configure certs
mkdir certs cd certs
Generate cert authority
openssl genrsa -out ca.key 2048 openssl req -x509 -new -nodes -key ca.key -subj "/CN=etcd-ca" -days 7300 -out ca.crt
Generate certificate each node. Note, pay attention to SANS, I am using IP, update with your IP and oh DNS/hostname. node1
# Generate a private key openssl genrsa -out etcd-node1.key 2048 # Create temp file for config cat > temp.cnf <<EOF [ req ] distinguished_name = req_distinguished_name req_extensions = v3_req [ req_distinguished_name ] [ v3_req ] subjectAltName = @alt_names [ alt_names ] IP.1 = 192.168.60.103 IP.2 = 127.0.0.1 EOF # Create a csr openssl req -new -key etcd-node1.key -out etcd-node1.csr \ -subj "/C=US/ST=YourState/L=YourCity/O=YourOrganization/OU=YourUnit/CN=etcd-node1" \ -config temp.cnf # Sign the cert openssl x509 -req -in etcd-node1.csr -CA ca.crt -CAkey ca.key -CAcreateserial \ -out etcd-node1.crt -days 7300 -sha256 -extensions v3_req -extfile temp.cnf # Verify the cert and be sure you see Subject Name Alternative openssl x509 -in etcd-node1.crt -text -noout | grep -A1 "Subject Alternative Name" # Remove temp file rm temp.cnf
node 2
- Generate a private key
openssl genrsa -out etcd-node2.key 2048
- Create temp file for config
cat > temp.cnf <<EOF [ req ] distinguished_name = req_distinguished_name req_extensions = v3_req [ req_distinguished_name ] [ v3_req ] subjectAltName = @alt_names [ alt_names ] IP.1 = 192.168.60.104 IP.2 = 127.0.0.1 EOF
- Create a csr
openssl req -new -key etcd-node2.key -out etcd-node2.csr \
-subj "/C=US/ST=YourState/L=YourCity/O=YourOrganization/OU=YourUnit/CN=etcd-node2" \ -config temp.cnf
- Sign the cert
openssl x509 -req -in etcd-node2.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
-out etcd-node2.crt -days 7300 -sha256 -extensions v3_req -extfile temp.cnf
- Verify the cert and be sure you see Subject Name Alternative
openssl x509 -in etcd-node2.crt -text -noout | grep -A1 "Subject Alternative Name"
- Remove temp file
rm temp.cnf
node3
- Generate a private key
openssl genrsa -out etcd-node3.key 2048
- Create temp file for config
cat > temp.cnf <<EOF [ req ] distinguished_name = req_distinguished_name req_extensions = v3_req [ req_distinguished_name ] [ v3_req ] subjectAltName = @alt_names [ alt_names ] IP.1 = 192.168.60.105 IP.2 = 127.0.0.1 EOF
- Create a csr
openssl req -new -key etcd-node3.key -out etcd-node3.csr \
-subj "/C=US/ST=YourState/L=YourCity/O=YourOrganization/OU=YourUnit/CN=etcd-node3" \ -config temp.cnf
- Sign the cert
openssl x509 -req -in etcd-node3.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
-out etcd-node3.crt -days 7300 -sha256 -extensions v3_req -extfile temp.cnf
- Verify the cert and be sure you see Subject Name Alternative
openssl x509 -in etcd-node3.crt -text -noout | grep -A1 "Subject Alternative Name"
- Remove temp file
rm temp.cnf
List all files
ls
Should see:
ca.crt ca.key ca.srl etcd-node1.crt etcd-node1.csr etcd-node2.crt etcd-node2.csr etcd-node2.key etcd-node3.crt etcd-node3.csr etcd-node3.key
Secure copy (scp) the certs to each node:
scp ca.crt etcd-node1.crt etcd-node1.key [email protected]:/tmp/ scp ca.crt etcd-node2.crt etcd-node2.key [email protected]:/tmp/ scp ca.crt etcd-node3.crt etcd-node3.key [email protected]:/tmp/
Should see:
ssh into each node
We need to move certs from /tmp to ssl location (/etc/etcd/ssl)
sudo mkdir -p /etc/etcd/ssl sudo mv /tmp/etcd-node*.crt /etc/etcd/ssl/ sudo mv /tmp/etcd-node*.key /etc/etcd/ssl/ sudo mv /tmp/ca.crt /etc/etcd/ssl/ sudo chown -R etcd:etcd /etc/etcd/ sudo chmod 600 /etc/etcd/ssl/etcd-node*.key sudo chmod 644 /etc/etcd/ssl/etcd-node*.crt /etc/etcd/ssl/ca.crt
Configure Create our config
sudo nano /etc/etcd/etcd.env