Step 1 \u2014 Install PostgreSQL on all 3 PostgreSQL nodes<\/h3>\n\n\n\n# On postgres-01, postgres-02, postgres-03\nsudo apt update\nsudo apt install -y postgresql-common\n# postgresql-common: provides the PGDG repository setup script\n\nsudo \/usr\/share\/postgresql-common\/pgdg\/apt.postgresql.org.sh\n# This script adds the official PostgreSQL apt repository (postgresql.org)\n# and imports its GPG key \u2014 ensures you get the latest PostgreSQL version,\n# not the older version bundled with Ubuntu\n\nsudo apt update\nsudo apt install -y postgresql-18 postgresql-contrib-18\n# Install version 18 explicitly \u2014 the generic \"postgresql\" meta-package installs Ubuntu's\n# default bundled version (16) in addition to the PGDG version, leaving two versions installed\n# Always specify the version number to avoid this\n# postgresql-contrib-18: additional modules including pg_rewind, which Patroni uses\n# to resync the old primary after a failover without a full base backup<\/code><\/pre>\n\n\n\nStep 2 \u2014 Stop and disable the PostgreSQL service<\/h3>\n\n\n\n# On postgres-01, postgres-02, postgres-03\nsudo systemctl stop postgresql\n# Patroni manages PostgreSQL startup entirely\n# If PostgreSQL is already running when Patroni starts, Patroni will fail with:\n# \"postmaster is already running\"\n\nsudo systemctl disable postgresql\n# Prevents PostgreSQL from starting automatically on boot\n# Patroni's own systemd service starts PostgreSQL when the node joins the cluster<\/code><\/pre>\n\n\n\n
\n\n\n\n4. etcd Installation<\/h2>\n\n\n\nStep 1 \u2014 Install etcd on all 3 PostgreSQL nodes<\/h3>\n\n\n\n# On postgres-01, postgres-02, postgres-03\nsudo apt-get install -y wget curl\n\nwget https:\/\/github.com\/etcd-io\/etcd\/releases\/download\/v3.6.10\/etcd-v3.6.10-linux-amd64.tar.gz\n# Download the etcd binary directly from GitHub releases\n# The apt package is often outdated \u2014 always install from the official releases\n# Check https:\/\/github.com\/etcd-io\/etcd\/releases for the latest stable version\n\ntar xvf etcd-v3.6.10-linux-amd64.tar.gz\n# xvf: extract (x), verbose (v), from file (f)\n\nsudo mv etcd-v3.6.10-linux-amd64\/etcd \/usr\/local\/bin\/\nsudo mv etcd-v3.6.10-linux-amd64\/etcdctl \/usr\/local\/bin\/\n# etcd: the etcd server binary\n# etcdctl: the etcd client CLI \u2014 used for health checks and inspecting cluster state\n\n# Verify the installation\netcd --version\n# Expected: etcd Version: 3.6.10\n# If \"etcd: command not found\": \/usr\/local\/bin is not in PATH \u2014 run: export PATH=$PATH:\/usr\/local\/bin\n\netcdctl version\n# Expected: etcdctl version: 3.6.10<\/code><\/pre>\n\n\n\nStep 2 \u2014 Create the etcd system user<\/h3>\n\n\n\n# On postgres-01, postgres-02, postgres-03\nsudo useradd --system --home \/var\/lib\/etcd --shell \/bin\/false etcd\n\n# --system: creates a system account with no login shell by default\n# --home \/var\/lib\/etcd: etcd stores its data here\n# --shell \/bin\/false: prevents interactive login \u2014 etcd runs as a daemon only<\/code><\/pre>\n\n\n\n
\n\n\n\n5. TLS Certificate Generation<\/h2>\n\n\n\n
All certificates are generated once on postgres-01 and then distributed to the other nodes.<\/p>\n\n\n\n
The CA private key (ca.key<\/code>) stays on postgres-01 after distribution is complete \u2014 do not copy it to other nodes.<\/p>\n\n\n\nStep 1 \u2014 Create the working directory<\/h3>\n\n\n\n# On postgres-01\nmkdir ~\/certs && cd ~\/certs\n# All certificate files are created here before being copied to each node<\/code><\/pre>\n\n\n\nStep 2 \u2014 Generate the Certificate Authority<\/h3>\n\n\n\n# On postgres-01\nopenssl genrsa -out ca.key 2048\n# genrsa: generate an RSA private key; 2048: key length in bits\n\nopenssl req -x509 -new -nodes -key ca.key -subj \"\/CN=etcd-ca\" -days 7300 -out ca.crt\n# req -x509: create a self-signed certificate (not a signing request)\n# -new -nodes: new certificate, no passphrase on the private key\n# -subj \"\/CN=etcd-ca\": the certificate's Common Name \u2014 identifies this as the cluster CA\n# -days 7300: valid for 20 years\n# ca.crt: distributed to every node as the root of trust<\/code><\/pre>\n\n\n\nStep 3 \u2014 Generate per-node etcd certificates<\/h3>\n\n\n\n
Each node gets its own certificate with its IP as a Subject Alternative Name (SAN). TLS hostname verification requires the server's IP to appear in the SAN \u2014 without it, connections will fail.<\/p>\n\n\n\n
# On postgres-01 \u2014 generate all three node certificates here, then distribute\n\n# Certificate for postgres-01 (192.168.0.203)\nopenssl genrsa -out etcd-node1.key 2048\n\ncat > temp.cnf <<EOF\n[ req ]\ndistinguished_name = req_distinguished_name\nreq_extensions = v3_req\n[ req_distinguished_name ]\n[ v3_req ]\nsubjectAltName = @alt_names\n[ alt_names ]\nIP.1 = 192.168.0.203\nIP.2 = 127.0.0.1\nEOF\n\n# temp.cnf: OpenSSL config that adds the node IP as a SAN\n# IP.2 = 127.0.0.1: allows etcdctl to connect locally without specifying a remote address\n\nopenssl req -new -key etcd-node1.key -out etcd-node1.csr \\\n -subj \"\/CN=etcd-node1\" \\\n -config temp.cnf\n# req -new: generate a certificate signing request (CSR)\n# -subj: the certificate's identity \u2014 CN identifies the node in logs\n\nopenssl x509 -req -in etcd-node1.csr -CA ca.crt -CAkey ca.key \\\n -CAcreateserial -out etcd-node1.crt -days 7300 \\\n -sha256 -extensions v3_req -extfile temp.cnf\n# x509 -req: sign the CSR with the CA to produce a certificate\n# -CAcreateserial: creates ca.srl to track serial numbers across certificates\n# -extensions v3_req -extfile temp.cnf: embed the SANs into the signed certificate\n\nopenssl x509 -in etcd-node1.crt -text -noout | grep -A1 \"Subject Alternative Name\"\n# Verify the SAN was embedded \u2014 Expected: IP Address:192.168.0.203, IP Address:127.0.0.1\n# If the SAN is missing: the -extensions and -extfile flags were not applied correctly\n\nrm temp.cnf\n\n# Certificate for postgres-02 (192.168.0.204)\nopenssl genrsa -out etcd-node2.key 2048\n\ncat > temp.cnf <<EOF\n[ req ]\ndistinguished_name = req_distinguished_name\nreq_extensions = v3_req\n[ req_distinguished_name ]\n[ v3_req ]\nsubjectAltName = @alt_names\n[ alt_names ]\nIP.1 = 192.168.0.204\nIP.2 = 127.0.0.1\nEOF\n\nopenssl req -new -key etcd-node2.key -out etcd-node2.csr \\\n -subj \"\/CN=etcd-node2\" -config temp.cnf\n\nopenssl x509 -req -in etcd-node2.csr -CA ca.crt -CAkey ca.key \\\n -CAcreateserial -out etcd-node2.crt -days 7300 \\\n -sha256 -extensions v3_req -extfile temp.cnf\n\nopenssl x509 -in etcd-node2.crt -text -noout | grep -A1 \"Subject Alternative Name\"\n# Expected: IP Address:192.168.0.204, IP Address:127.0.0.1\nrm temp.cnf\n\n\n# Certificate for postgres-03 (192.168.0.205)\nopenssl genrsa -out etcd-node3.key 2048\n\ncat > temp.cnf <<EOF\n[ req ]\ndistinguished_name = req_distinguished_name\nreq_extensions = v3_req\n[ req_distinguished_name ]\n[ v3_req ]\nsubjectAltName = @alt_names\n[ alt_names ]\nIP.1 = 192.168.0.205\nIP.2 = 127.0.0.1\nEOF\n\nopenssl req -new -key etcd-node3.key -out etcd-node3.csr \\\n -subj \"\/CN=etcd-node3\" -config temp.cnf\n\nopenssl x509 -req -in etcd-node3.csr -CA ca.crt -CAkey ca.key \\\n -CAcreateserial -out etcd-node3.crt -days 7300 \\\n -sha256 -extensions v3_req -extfile temp.cnf\n\nopenssl x509 -in etcd-node3.crt -text -noout | grep -A1 \"Subject Alternative Name\"\n# Expected: IP Address:192.168.0.205, IP Address:127.0.0.1\nrm temp.cnf<\/code><\/pre>\n\n\n\nStep 4 \u2014 Generate the PostgreSQL server certificate<\/h3>\n\n\n\n
One shared certificate covers all PostgreSQL nodes. <\/p>\n\n\n\n
It is used for both PostgreSQL connections and the Patroni REST API.<\/p>\n\n\n\n
# On postgres-01\nopenssl genrsa -out server.key 2048\n\nopenssl req -new -key server.key -out server.req\n# You will be prompted for certificate details \u2014 the Common Name is not critical\n# since connections are verified by IP SAN, not CN\n# Warning \"No -copy_extensions given\" is harmless \u2014 the server cert does not need SANs\n\nopenssl req -x509 -key server.key -in server.req -out server.crt -days 7300\n# Self-signed server certificate \u2014 signed directly with server.key, not the CA\n# Patroni and PostgreSQL use this certificate to identify themselves to clients<\/code><\/pre>\n\n\n\nStep 5 \u2014 Distribute certificates to postgres-02 and postgres-03<\/h3>\n\n\n\n
postgres-01 keeps its own certs in ~\/certs \u2014 no scp needed for it.<\/p>\n\n\n\n
# On postgres-01\nscp ~\/certs\/ca.crt ~\/certs\/etcd-node2.crt ~\/certs\/etcd-node2.key \\\n ~\/certs\/server.crt ~\/certs\/server.key fernando@192.168.0.204:\/tmp\/\n\nscp ~\/certs\/ca.crt ~\/certs\/etcd-node3.crt ~\/certs\/etcd-node3.key \\\n ~\/certs\/server.crt ~\/certs\/server.key fernando@192.168.0.205:\/tmp\/<\/code><\/pre>\n\n\n\nStep 6 \u2014 Install certificates on each PostgreSQL node<\/h3>\n\n\n\n
All certificates live in \/etc\/etcd\/certs\/<\/code> on every node. <\/p>\n\n\n\nThe directory is owned by etcd:etcd<\/code> so the etcd daemon can read its certs. <\/p>\n\n\n\nEn postgres<\/code> user gets read access via ACL so Patroni can connect to etcd.<\/p>\n\n\n\nImportant:<\/strong> set file permissions before locking down the directory. <\/p>\n\n\n\nAfter chmod 700<\/code> the shell cannot expand globs inside the directory as a non-root user \u2014 use explicit filenames.<\/p>\n\n\n\n# On postgres-01, postgres-02, postgres-03\nsudo mkdir -p \/etc\/etcd\/certs\nsudo apt-get install -y acl\n# acl: provides setfacl \u2014 needed to grant postgres user access without changing ownership<\/code><\/pre>\n\n\n\nOn postgres-01<\/strong> \u2014 copy from ~\/certs (files were never in \/tmp on this node):<\/p>\n\n\n\nsudo cp ~\/certs\/ca.crt \/etc\/etcd\/certs\/\nsudo cp ~\/certs\/etcd-node1.crt \/etc\/etcd\/certs\/\nsudo cp ~\/certs\/etcd-node1.key \/etc\/etcd\/certs\/\nsudo cp ~\/certs\/server.crt \/etc\/etcd\/certs\/\nsudo cp ~\/certs\/server.key \/etc\/etcd\/certs\/<\/code><\/pre>\n\n\n\nOn postgres-02<\/strong> \u2014 move from \/tmp:<\/p>\n\n\n\nsudo mv \/tmp\/ca.crt \/etc\/etcd\/certs\/\nsudo mv \/tmp\/etcd-node2.crt \/etc\/etcd\/certs\/\nsudo mv \/tmp\/etcd-node2.key \/etc\/etcd\/certs\/\nsudo mv \/tmp\/server.crt \/etc\/etcd\/certs\/\nsudo mv \/tmp\/server.key \/etc\/etcd\/certs\/<\/code><\/pre>\n\n\n\nOn postgres-03<\/strong> \u2014 move from \/tmp:<\/p>\n\n\n\nsudo mv \/tmp\/ca.crt \/etc\/etcd\/certs\/\nsudo mv \/tmp\/etcd-node3.crt \/etc\/etcd\/certs\/\nsudo mv \/tmp\/etcd-node3.key \/etc\/etcd\/certs\/\nsudo mv \/tmp\/server.crt \/etc\/etcd\/certs\/\nsudo mv \/tmp\/server.key \/etc\/etcd\/certs\/<\/code><\/pre>\n\n\n\nOn all three nodes<\/strong> \u2014 set permissions then lock the directory:<\/p>\n\n\n\netcd certs must be owned by etcd<\/code> \u2014 the etcd daemon runs as this user. <\/p>\n\n\n\nServer certs must be owned by postgres<\/code> \u2014 PostgreSQL enforces that its SSL private key is owned by the database user or root. <\/p>\n\n\n\nUtilizando etcd<\/code> ownership will cause PostgreSQL to refuse to start with: “private key file must be owned by the database user or root”.<\/p>\n\n\n\n# Set file permissions first \u2014 must happen before chmod 700 on the directory\n# After chmod 700, the shell cannot expand globs as a non-root user\n\n# etcd certs: owned by etcd\n# On postgres-01:\nsudo chown etcd:etcd \/etc\/etcd\/certs\/etcd-node1.crt \/etc\/etcd\/certs\/etcd-node1.key \/etc\/etcd\/certs\/ca.crt\nsudo chmod 600 \/etc\/etcd\/certs\/etcd-node1.key\nsudo chmod 644 \/etc\/etcd\/certs\/etcd-node1.crt \/etc\/etcd\/certs\/ca.crt\n\n# On postgres-02:\nsudo chown etcd:etcd \/etc\/etcd\/certs\/etcd-node2.crt \/etc\/etcd\/certs\/etcd-node2.key \/etc\/etcd\/certs\/ca.crt\nsudo chmod 600 \/etc\/etcd\/certs\/etcd-node2.key\nsudo chmod 644 \/etc\/etcd\/certs\/etcd-node2.crt \/etc\/etcd\/certs\/ca.crt\n\n# On postgres-03:\nsudo chown etcd:etcd \/etc\/etcd\/certs\/etcd-node3.crt \/etc\/etcd\/certs\/etcd-node3.key \/etc\/etcd\/certs\/ca.crt\nsudo chmod 600 \/etc\/etcd\/certs\/etcd-node3.key\nsudo chmod 644 \/etc\/etcd\/certs\/etcd-node3.crt \/etc\/etcd\/certs\/ca.crt\n\n# Server certs: owned by postgres (all three nodes \u2014 same files on each)\nsudo chown postgres:postgres \/etc\/etcd\/certs\/server.crt \/etc\/etcd\/certs\/server.key\nsudo chmod 600 \/etc\/etcd\/certs\/server.key\nsudo chmod 644 \/etc\/etcd\/certs\/server.crt\n\n# Lock the directory \u2014 run this last, after all file permissions are set\nsudo chown etcd:etcd \/etc\/etcd\/certs\nsudo chmod 700 \/etc\/etcd\/certs\n\n# Grant the postgres user read access to the directory and all files inside it\n# Patroni needs to read the etcd certs to connect with TLS\nsudo setfacl -R -m u:postgres:rX \/etc\/etcd\/certs\n# -R: apply recursively to all files; rX: read + execute on directories (to traverse)<\/code><\/pre>\n\n\n\nStep 7 \u2014 Create the combined PEM file for Patroni<\/h3>\n\n\n\n
Patroni's