Ejecutar en los 6 servidores (postgres-01\/02\/03 y haproxy-01\/02\/03). <\/p>\n\n\n\n
Los servidores usan UTC por defecto; config\u00faralo a tu zona horaria local antes que nada. <\/p>\n\n\n\n
Las marcas de tiempo desajustadas o incorrectas causan confusi\u00f3n en los registros y la validaci\u00f3n de certificados.<\/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\nPaso 2: confirme que los puertos requeridos est\u00e9n abiertos<\/h3>\n\n\n\n Antes de empezar, confirme que los siguientes puertos est\u00e1n abiertos:<\/p>\n\n\n\nFuente<\/th> Destino<\/th> Puerto<\/th> Prop\u00f3sito<\/th><\/tr><\/thead> Nodos de PostgreSQL<\/td> Nodos de PostgreSQL<\/td> 2379<\/td> Cliente de etcd (Patroni \u2192 etcd)<\/td><\/tr> Nodos de PostgreSQL<\/td> Nodos de PostgreSQL<\/td> 2380<\/td> comunicaci\u00f3n entre pares de etcd<\/td><\/tr> Nodos de PostgreSQL<\/td> Nodos de PostgreSQL<\/td> 5432<\/td> Replicaci\u00f3n de PostgreSQL<\/td><\/tr> Nodos de PostgreSQL<\/td> Nodos de PostgreSQL<\/td> 8008<\/td> API REST de Patroni<\/td><\/tr> Nodos de HAProxy<\/td> Nodos de PostgreSQL<\/td> 8008<\/td> Comprobaci\u00f3n de estado de HAProxy<\/td><\/tr> Nodos de HAProxy<\/td> Nodos de HAProxy<\/td> 112\/VRRP<\/td> elecci\u00f3n de VIP de keepalived<\/td><\/tr> Solicitudes<\/td> VIP<\/td> 5432<\/td> Conexiones de clientes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n3. Instalaci\u00f3n de PostgreSQL<\/h2>\n\n\n\nPaso 1: Instalar PostgreSQL en los 3 nodos de 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\nPaso 2 \u2014 Detener e inhabilitar el servicio de 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. Instalaci\u00f3n de etcd<\/h2>\n\n\n\nPaso 1: Instala etcd en los 3 nodos de 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\nPaso 2 \u2014 Crear el usuario del sistema 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. Generaci\u00f3n de Certificados TLS<\/h2>\n\n\n\n Todos los certificados se generan una vez en postgres-01 y luego se distribuyen a los otros nodos.<\/p>\n\n\n\n
La clave privada de CA (ca.clave<\/code>) permanece en postgres-01 despu\u00e9s de que se completa la distribuci\u00f3n; no lo copie a otros nodos.<\/p>\n\n\n\nPaso 1 \u2014 Crear el directorio de trabajo<\/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\nPaso 2 \u2014 Generar la Autoridad Certificadora<\/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\nPaso 3: Generar certificados etcd por nodo<\/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\n<\/pre><\/div>\n\n\nPaso 4 \u2014 Generar el certificado del servidor PostgreSQL<\/h3>\n\n\n\n Un certificado compartido cubre todos los nodos de PostgreSQL. <\/p>\n\n\n\n
Se utiliza tanto para conexiones PostgreSQL como para la 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\nPaso 5 \u2014 Distribuir certificados a postgres-02 y postgres-03<\/h3>\n\n\n\n postgres-01 mantiene sus propios certificados en ~\/certs, no se necesita scp para \u00e9l.<\/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\nPaso 6 \u2014 Instalar certificados en cada nodo de PostgreSQL<\/h3>\n\n\n\n Todos los certificados residen en \/etc\/etcd\/certs\/<\/code> en cada nodo. <\/p>\n\n\n\nEl directorio pertenece a etcd:etcd<\/code> as\u00ed que el demonio etcd puede leer sus certificados. <\/p>\n\n\n\nEn postgres<\/code> el usuario obtiene acceso de lectura a trav\u00e9s de ACL para que Patroni pueda conectarse a etcd.<\/p>\n\n\n\nImportante:<\/strong> establecer los permisos del archivo antes de bloquear el directorio. <\/p>\n\n\n\nDespu\u00e9s chmod 700<\/code> el shell no puede expandir globs dentro del directorio como un usuario no root; usa nombres de archivo expl\u00edcitos.<\/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\nEn postgres-01<\/strong> \u2014 copiar desde ~\/certs (los archivos nunca estuvieron en \/tmp en este nodo):<\/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\nEn postgres-02<\/strong> \u2014 mover desde \/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\nEn postgres-03<\/strong> \u2014 mover desde \/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\nEn los tres nodos<\/strong> \u2014 Establece permisos y luego bloquea el directorio:<\/p>\n\n\n\nlos certificados de etcd deben ser propiedad de etcd<\/code> \u2014 el demonio de etcd se ejecuta como este usuario. <\/p>\n\n\n\nLos certificados del servidor deben ser propiedad de postgres<\/code> \u2014 PostgreSQL exige que su clave privada SSL sea propiedad del usuario de la base de datos o de root. <\/p>\n\n\n\nUtilizando etcd<\/code> La propiedad obligar\u00e1 a PostgreSQL a negarse a iniciarse con: \u201cel archivo de clave privada debe ser propiedad del usuario de la base de datos o de 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\nPaso 7: Crear el archivo PEM combinado para Patroni<\/h3>\n\n\n\n Patroni’s restapi.certfile<\/code> espera un \u00fanico archivo que contenga tanto el certificado como la clave privada.<\/p>\n\n\n