Ex\u00e9cutez sur les 6 serveurs (postgres-01\/02\/03 et haproxy-01\/02\/03). <\/p>\n\n\n\n
Les serveurs sont r\u00e9gl\u00e9s sur UTC par d\u00e9faut \u2014 d\u00e9finissez votre fuseau horaire local avant toute autre chose. <\/p>\n\n\n\n
Des horodatages incorrects ou discordants provoquent de la confusion dans les journaux et la validation des certificats.<\/p>\n\n\n
\nsudo timedatectl set-timezone Europe\/Madrid\ntimedatectl\n# Expected: Time zone: Europe\/Madrid (CET\/CEST, +0100\/+0200)\n# NTP service should show: active\n# System clock synchronized: yes\n<\/pre><\/div>\n\n\n\u00c9tape 2 \u2014 Confirmer que les ports requis sont ouverts<\/h3>\n\n\n\n Avant de commencer, confirmez que les ports suivants sont ouverts :<\/p>\n\n\n\nSource<\/th> Destination<\/th> Port<\/th> Objectif<\/th><\/tr><\/thead> N\u0153uds PostgreSQL<\/td> N\u0153uds PostgreSQL<\/td> 2379<\/td> Client etcd (Patroni \u2192 etcd)<\/td><\/tr> N\u0153uds PostgreSQL<\/td> N\u0153uds PostgreSQL<\/td> 2380<\/td> communication entre pairs etcd<\/td><\/tr> N\u0153uds PostgreSQL<\/td> N\u0153uds PostgreSQL<\/td> 5432<\/td> R\u00e9plication PostgreSQL<\/td><\/tr> N\u0153uds PostgreSQL<\/td> N\u0153uds PostgreSQL<\/td> 8008<\/td> API REST Patroni<\/td><\/tr> N\u0153uds HAProxy<\/td> N\u0153uds PostgreSQL<\/td> 8008<\/td> V\u00e9rification de l'\u00e9tat de sant\u00e9 HAProxy<\/td><\/tr> N\u0153uds HAProxy<\/td> N\u0153uds HAProxy<\/td> 112\/VRRP<\/td> \u00c9lection VIP keepalived<\/td><\/tr> Applications<\/td> VIP<\/td> 5432<\/td> Connexions clients<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n3. Installation de PostgreSQL<\/h2>\n\n\n\n\u00c9tape 1 \u2014 Installer PostgreSQL sur les 3 n\u0153uds PostgreSQL<\/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\n<\/pre><\/div>\n\n\n\u00c9tape 2 \u2014 Arr\u00eatez et d\u00e9sactivez le service PostgreSQL<\/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\n<\/pre><\/div>\n\n\n4. Installation d'etcd<\/h2>\n\n\n\n\u00c9tape 1 \u2014 Installer etcd sur les 3 n\u0153uds PostgreSQL<\/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\n<\/pre><\/div>\n\n\n\u00c9tape 2 \u2014 Cr\u00e9er l'utilisateur syst\u00e8me etcd<\/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\n<\/pre><\/div>\n\n\n5. G\u00e9n\u00e9ration de certificats TLS<\/h2>\n\n\n\n Tous les certificats sont g\u00e9n\u00e9r\u00e9s une seule fois sur postgres-01 puis distribu\u00e9s aux autres n\u0153uds.<\/p>\n\n\n\n
La cl\u00e9 priv\u00e9e du certificat\n\nLa cl\u00e9 priv\u00e9e du CAca.cl\u00e9<\/code>) reste sur postgres-01 apr\u00e8s la fin de la distribution \u2014 ne le copiez pas sur d'autres n\u0153uds.<\/p>\n\n\n\n\u00c9tape 1 \u2014 Cr\u00e9er le r\u00e9pertoire de travail<\/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\n<\/pre><\/div>\n\n\n\u00c9tape 2 \u2014 G\u00e9n\u00e9rer l'autorit\u00e9 de certification<\/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\n<\/pre><\/div>\n\n\n\u00c9tape 3 \u2014 G\u00e9n\u00e9rer les certificats etcd par n\u0153ud<\/h3>\n\n\n\n Chaque n\u0153ud obtient son propre certificat avec son adresse IP comme nom alternatif du sujet (SAN). La v\u00e9rification du nom d'h\u00f4te TLS exige que l'adresse IP du serveur apparaisse dans le SAN \u2013 sans cela, les connexions \u00e9choueront.<\/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\n<\/pre><\/div>\n\n\n\u00c9tape 4 \u2014 G\u00e9n\u00e9rer le certificat du serveur PostgreSQL<\/h3>\n\n\n\n Un certificat partag\u00e9 couvre tous les n\u0153uds PostgreSQL. <\/p>\n\n\n\n
Il est utilis\u00e9 \u00e0 la fois pour les connexions PostgreSQL et pour l'API REST de Patroni.<\/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\n<\/pre><\/div>\n\n\n\u00c9tape 5 \u2014 Distribuer les certificats \u00e0 postgres-02 et postgres-03<\/h3>\n\n\n\n postgres-01 garde ses propres certificats dans ~\/certs \u2014 aucun scp n'est n\u00e9cessaire pour lui.<\/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\/\n<\/pre><\/div>\n\n\n\u00c9tape 6 \u2014 Installer les certificats sur chaque n\u0153ud PostgreSQL<\/h3>\n\n\n\n Tous les certificats r\u00e9sident dans \/etc\/etcd\/certs\/<\/code> sur chaque n\u0153ud. <\/p>\n\n\n\nLe r\u00e9pertoire appartient \u00e0 etcd:etcd<\/code> alors le d\u00e9mon etcd peut lire ses certificats. <\/p>\n\n\n\nLe PostgreSQL<\/code> l'utilisateur obtient un acc\u00e8s en lecture via ACL afin que Patroni puisse se connecter \u00e0 etcd.<\/p>\n\n\n\nImportant :<\/strong> d\u00e9finir les permissions de fichier avant de verrouiller le r\u00e9pertoire. <\/p>\n\n\n\nApr\u00e8s chmod 700<\/code> le shell ne peut pas d\u00e9velopper les jokers \u00e0 l'int\u00e9rieur du r\u00e9pertoire en tant qu'utilisateur non root \u2014 utilisez des noms de fichiers explicites.<\/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\n<\/pre><\/div>\n\n\nSur postgres-01<\/strong> \u2014 copier depuis ~\/certs (les fichiers n'ont jamais \u00e9t\u00e9 dans \/tmp sur ce n\u0153ud) :<\/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\/\n<\/pre><\/div>\n\n\nSur postgres-02<\/strong> \u2014 d\u00e9placer depuis \/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\/\n<\/pre><\/div>\n\n\nSur postgres-03<\/strong> \u2014 d\u00e9placer depuis \/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\/\n<\/pre><\/div>\n\n\nSur les trois n\u0153uds<\/strong> \u2014 d\u00e9finissez les permissions puis verrouillez le r\u00e9pertoire :<\/p>\n\n\n\nles certificats etcd doivent appartenir \u00e0 etcd<\/code> \u2014 le d\u00e9mon etcd s'ex\u00e9cute en tant que cet utilisateur. <\/p>\n\n\n\nLes certificats de serveur doivent appartenir \u00e0 PostgreSQL<\/code> \u2014 PostgreSQL s'assure que sa cl\u00e9 priv\u00e9e SSL appartient \u00e0 l'utilisateur de la base de donn\u00e9es ou \u00e0 root. <\/p>\n\n\n\nUtilisation etcd<\/code> La propri\u00e9t\u00e9 entra\u00eenera le refus de d\u00e9marrage de PostgreSQL avec le message : \u201c le fichier de cl\u00e9 priv\u00e9e doit appartenir \u00e0 l'utilisateur de la base de donn\u00e9es ou \u00e0 root \u201d.<\/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)\n<\/pre><\/div>\n\n\n\u00c9tape\u00a07\u00a0\u2014 Cr\u00e9er le fichier PEM combin\u00e9 pour Patroni<\/h3>\n\n\n\n Patroni's restapi.certfile<\/code> attend un fichier unique contenant \u00e0 la fois le certificat et la cl\u00e9 priv\u00e9e.<\/p>\n\n\n