$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-0ubuntu0.24.04.1)

$ sudo -u postgres psql -c "SELECT name, setting, unit, source FROM pg_settings
   WHERE name IN ('shared_buffers','effective_cache_size','work_mem',
                  'maintenance_work_mem','max_connections','wal_buffers');"
         name         | setting | unit |  source
----------------------+---------+------+----------
 effective_cache_size | 524288  | 8kB  | default
 maintenance_work_mem | 65536   | kB   | default
 max_connections      | 100     |      | default
 shared_buffers       | 16384   | 8kB  | default
 wal_buffers          | 512     | 8kB  | default
 work_mem             | 4096    | kB   | default

# Tamano actual de la base de datos y de sus tablas mayores
$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
         n_live_tup AS filas
  FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
  ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 5;"
   relname   |  total  | filas
-------------+---------+--------
 reservas    | 412 MB  | 186420
 disponibles | 168 MB  | 891200
 huespedes   |  54 MB  |  92310
 casas       |  96 kB  |      5
 auditoria   | 1204 MB | 512880

$ ps -eo pid,ppid,user,comm --forest | grep -A9 'postgres$' | head -12
   1121       1 postgres postgres
   1210    1121 postgres  \_ postgres: checkpointer
   1211    1121 postgres  \_ postgres: background writer
   1213    1121 postgres  \_ postgres: walwriter
   1214    1121 postgres  \_ postgres: autovacuum launcher
   1215    1121 postgres  \_ postgres: logical replication launcher
   3402    1121 postgres  \_ postgres: svc_tramontana tramontana 127.0.0.1(51422) idle
   3403    1121 postgres  \_ postgres: svc_tramontana tramontana 127.0.0.1(51424) SELECT

$ sudo -u postgres psql -c "SHOW data_directory; SHOW config_file; SHOW hba_file;"
      data_directory
---------------------------
 /var/lib/postgresql/16/main
        config_file
-------------------------------------------
 /etc/postgresql/16/main/postgresql.conf
         hba_file
-------------------------------------------
 /etc/postgresql/16/main/pg_hba.conf

$ sudo ls -1 /var/lib/postgresql/16/main/ | head -8
base            # los datos: un subdirectorio por base de datos
global          # catalogos compartidos entre bases
pg_wal          # el registro de escritura anticipada
pg_stat         # estadisticas del planificador
pg_tblspc       # enlaces a tablespaces externos
postgresql.auto.conf   # lo que escribe ALTER SYSTEM
PG_VERSION
postmaster.pid

$ sudo cp /etc/postgresql/16/main/postgresql.conf \
          /etc/postgresql/16/main/postgresql.conf.bak-$(date +%F)
$ echo "include_dir = 'conf.d'" | sudo tee -a /etc/postgresql/16/main/postgresql.conf
$ sudo -u postgres mkdir -p /etc/postgresql/16/main/conf.d

Peor caso teorico = work_mem x conexiones x operaciones por consulta
                  = 8 MB x 100 x 3 = 2400 MB

-- En la sesion del informe mensual, no en todo el servidor
BEGIN;
SET LOCAL work_mem = '128MB';
SELECT casa, sum(importe) FROM reservas WHERE fecha >= '2026-01-01' GROUP BY casa;
COMMIT;

# /etc/postgresql/16/main/conf.d/10-tramontana.conf
# Ajuste para srv-tramontana: 3,8 GB RAM, 2 vCPU, SSD, PostgreSQL 16
# Base de referencia tomada el 2026-08-18. Medir antes y despues.

# ---------- Memoria ----------
shared_buffers = 960MB
effective_cache_size = 2560MB
work_mem = 8MB
maintenance_work_mem = 256MB
wal_buffers = 16MB

# ---------- Conexiones ----------
# Se BAJA de 100 a 60. Con PgBouncer delante, 60 sobran, y cada
# conexion reservada cuesta memoria aunque este ociosa.
max_connections = 60
superuser_reserved_connections = 3

# ---------- Registro de escritura anticipada ----------
wal_level = replica              # necesario para replica y PITR
max_wal_size = 2GB               # menos checkpoints, picos mas suaves
min_wal_size = 256MB
checkpoint_completion_target = 0.9   # reparte la escritura en el tiempo
archive_mode = on
archive_command = '/usr/local/bin/archivar_wal.sh %p %f'
archive_timeout = 900s           # fuerza un segmento cada 15 min

# ---------- Planificador (SSD) ----------
# random_page_cost por defecto es 4.0, calibrado para discos giratorios
# donde un salto aleatorio costaba mucho mas que una lectura secuencial.
# En SSD la diferencia es minima: 1.1 refleja la realidad y hace que el
# planificador use indices donde debe.
random_page_cost = 1.1
effective_io_concurrency = 200   # el SSD atiende muchas peticiones a la vez

# ---------- Paralelismo (2 vCPU: contencion) ----------
max_worker_processes = 2
max_parallel_workers = 2
max_parallel_workers_per_gather = 1

# ---------- Registro ----------
log_destination = 'stderr'
logging_collector = off          # deja que systemd/journald lo recoja
log_line_prefix = '%m [%p] %q%u@%d '
log_min_duration_statement = 250ms   # registra las consultas lentas
log_checkpoints = on
log_connections = off            # ruidoso con un pool delante
log_lock_waits = on              # esperas de bloqueo: sintoma importante
log_temp_files = 0               # cualquier fichero temporal = work_mem corto
log_autovacuum_min_duration = 1s

# ---------- Estadisticas ----------
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = top

$ sudo -u postgres psql -c "SELECT pg_reload_conf();"
# shared_buffers y shared_preload_libraries requieren REINICIO
$ sudo systemctl restart postgresql@16-main

$ sudo -u postgres psql -c "SELECT name, setting, unit, source, pending_restart
   FROM pg_settings WHERE name IN ('shared_buffers','work_mem','random_page_cost');"
      name       | setting | unit |               source                | pending_restart
-----------------+---------+------+-------------------------------------+-----------------
 random_page_cost| 1.1     |      | configuration file                  | f
 shared_buffers  | 122880  | 8kB  | configuration file                  | f
 work_mem        | 8192    | kB   | configuration file                  | f

# El indice de aciertos del cache compartido. Se mide ANTES y DESPUES,
# tras dejar pasar unas horas de trafico real.
$ sudo -u postgres psql -d tramontana -c "
  SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 2)
         AS acierto_pct
  FROM pg_stat_database WHERE datname = 'tramontana';"
 acierto_pct
-------------
       99.42

$ cat /sys/kernel/mm/transparent_hugepage/enabled
always [madvise] never

$ cat /sys/kernel/mm/transparent_hugepage/defrag
always defer defer+madvise [madvise] never

# 1. Cuantas necesita PostgreSQL (arranca, pregunta y sale)
$ sudo -u postgres /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main \
      -C shared_memory_size_in_huge_pages
495

# 2. Reservarlas con margen, de forma persistente
$ echo 'vm.nr_hugepages = 520' | \
      sudo tee /etc/sysctl.d/71-postgresql-hugepages.conf
$ sudo sysctl --system

# 3. Pedirle a PostgreSQL que las use
$ echo "huge_pages = try" | \
      sudo tee -a /etc/postgresql/16/main/conf.d/10-tramontana.conf
$ sudo systemctl restart postgresql@16-main

# 4. Verificar
$ grep -E 'HugePages_Total|HugePages_Free' /proc/meminfo
HugePages_Total:     520
HugePages_Free:       25

$ sudo apt install pgbouncer
$ pgbouncer --version
PgBouncer 1.21.0

; /etc/pgbouncer/pgbouncer.ini
[databases]
; La aplicacion se conecta a PgBouncer creyendo que es PostgreSQL
tramontana = host=127.0.0.1 port=5432 dbname=tramontana

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Consulta de autenticacion delegada: evita duplicar contrasenas
auth_user = pgbouncer_auth

pool_mode = transaction

; --- El dimensionado, que es la decision central ---
; max_client_conn: cuantos clientes de aplicacion aceptamos (baratos)
max_client_conn = 200
; default_pool_size: conexiones REALES a PostgreSQL por par usuario/bd.
; Con 2 vCPU, 25 es holgado. Este es el numero que protege al servidor.
default_pool_size = 25
; Margen temporal para picos, con aviso en el registro
reserve_pool_size = 5
reserve_pool_timeout = 3

; Cerrar conexiones de servidor ociosas mucho tiempo
server_idle_timeout = 600
; Reciclar conexiones cada hora: evita fugas de memoria acumuladas
server_lifetime = 3600
; Si un cliente espera mas de esto por una conexion, error claro
query_wait_timeout = 20

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = operador
stats_users = operador, monitor

$ sudo chattr -i /etc/tramontana/app.conf
$ sudo cp /etc/tramontana/app.conf /etc/tramontana/app.conf.bak-$(date +%F)
$ sudo sed -i 's/^db_port=5432/db_port=6432/' /etc/tramontana/app.conf
$ sudo diff -u /etc/tramontana/app.conf.bak-$(date +%F) /etc/tramontana/app.conf
--- /etc/tramontana/app.conf.bak-2026-08-18
+++ /etc/tramontana/app.conf
@@ -2,7 +2,7 @@
 db_host=127.0.0.1
-db_port=5432
+db_port=6432
 db_name=tramontana
$ sudo chattr +i /etc/tramontana/app.conf
$ sudo systemctl restart tramontana

$ psql -h 127.0.0.1 -p 6432 -U operador -d pgbouncer -c "SHOW POOLS;"
  database   |     user      | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-------------+---------------+-----------+------------+-----------+---------+---------
 tramontana  | svc_tramontana|        63 |          0 |         4 |      21 |       0

TIPO      BASE_DE_DATOS   USUARIO          DIRECCION          METODO

# /etc/postgresql/16/main/pg_hba.conf
# El orden IMPORTA: gana la primera coincidencia.

# --- 1. Mantenimiento local por socket Unix ---
# 'peer' compara el usuario del sistema con el rol pedido: 'sudo -u postgres
# psql' funciona sin contrasena, y nadie mas puede hacerse pasar por el.
local   all             postgres                                peer
local   all             all                                     peer

# --- 2. PgBouncer y la aplicacion, desde el bucle local ---
# Solo el rol de aplicacion, solo su base de datos, con SCRAM.
host    tramontana      svc_tramontana  127.0.0.1/32            scram-sha-256
host    tramontana      pgbouncer_auth  127.0.0.1/32            scram-sha-256

# --- 3. Monitorizacion (08-06), solo lectura de estadisticas ---
host    postgres        monitor         127.0.0.1/32            scram-sha-256

# --- 4. Replicacion (apartado 12), con TLS OBLIGATORIO ---
# 'hostssl' rechaza la conexion si no va cifrada; el WAL lleva
# los datos completos y no puede viajar en claro.
hostssl replication     replicador      10.0.2.16/32            scram-sha-256

# --- 5. Acceso administrativo desde la red interna, cifrado ---
hostssl tramontana      operador        10.0.2.0/24             scram-sha-256

# --- 6. Denegacion explicita de todo lo demas ---
# Redundante (el comportamiento por defecto ya es denegar) pero deja
# constancia de la intencion y produce un mensaje claro en el registro.
host    all             all             0.0.0.0/0               reject
host    all             all             ::/0                    reject

$ sudo -u postgres psql -c "SELECT pg_reload_conf();"
$ sudo -u postgres psql -c "SELECT line_number, type, database, user_name,
    address, auth_method, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"
(0 filas)

# Debe funcionar
$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -p 5432 \
      -U svc_tramontana -d tramontana -c 'SELECT 1;' >/dev/null && echo OK
OK

# NO debe funcionar: rol de aplicacion contra otra base de datos
$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d postgres -c 'SELECT 1;'
psql: error: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user
"svc_tramontana", database "postgres", no encryption

# Ubuntu genera un certificado autofirmado y lo enlaza al instalar
$ sudo ls -l /var/lib/postgresql/16/main/server.crt
lrwxrwxrwx 1 postgres postgres 36 -> /etc/ssl/certs/ssl-cert-snakeoil.pem

# CA interna (se genera una vez, en una maquina segura, NO en el servidor)
$ openssl req -new -x509 -days 3650 -nodes -out ca-tramontana.crt \
      -keyout ca-tramontana.key -subj "/CN=CA interna Tramontana"

# Certificado de servidor para la base de datos
$ openssl req -new -nodes -out bd.csr -keyout bd.key \
      -subj "/CN=srv-tramontana.interno"
$ openssl x509 -req -in bd.csr -days 825 -CA ca-tramontana.crt \
      -CAkey ca-tramontana.key -CAcreateserial -out bd.crt

$ sudo install -o postgres -g postgres -m 0600 bd.key /etc/postgresql/16/main/
$ sudo install -o postgres -g postgres -m 0644 bd.crt /etc/postgresql/16/main/

# conf.d/20-tls.conf
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/bd.crt'
ssl_key_file  = '/etc/postgresql/16/main/bd.key'
ssl_ca_file   = '/etc/postgresql/16/main/ca-tramontana.crt'
ssl_min_protocol_version = 'TLSv1.2'
ssl_prefer_server_ciphers = on

$ sudo -u postgres psql -c "SELECT pid, ssl, version, cipher, client_addr
    FROM pg_stat_ssl JOIN pg_stat_activity USING (pid) WHERE ssl;"
 pid  | ssl | version |         cipher         | client_addr
------+-----+---------+------------------------+-------------
 8812 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 10.0.2.16

-- Ejecutado como postgres: sudo -u postgres psql -d tramontana

-- 1. Rol de aplicacion: solo puede conectarse y trabajar con datos
CREATE ROLE svc_tramontana WITH LOGIN
    PASSWORD 'se-inyecta-desde-pass'
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT
    CONNECTION LIMIT 30;

-- 2. Cerrar el esquema publico, que en PostgreSQL 15+ ya viene
--    restringido, pero conviene ser explicito. Antes de la 15,
--    CUALQUIER usuario podia crear objetos en 'public'.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE tramontana FROM PUBLIC;

-- 3. Esquema propio de la aplicacion
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION postgres;

-- 4. Permisos acotados: conectarse, usar el esquema, y DML sobre
--    las tablas existentes. NO CREATE: no puede alterar el esquema.
GRANT CONNECT ON DATABASE tramontana TO svc_tramontana;
GRANT USAGE ON SCHEMA app TO svc_tramontana;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app
    TO svc_tramontana;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO svc_tramontana;

-- 5. Y para las tablas FUTURAS, que es lo que casi todo el mundo olvida:
--    sin esto, la tabla que cree la proxima migracion sera inaccesible
--    para la aplicacion y el despliegue fallara en produccion.
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO svc_tramontana;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
    GRANT USAGE ON SEQUENCES TO svc_tramontana;

-- 6. Rol de solo lectura para informes (informe_reservas.sh) y para
--    la monitorizacion de 08-06
CREATE ROLE lector_informes WITH LOGIN PASSWORD 'otra-distinta'
    NOSUPERUSER NOCREATEDB NOCREATEROLE CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE tramontana TO lector_informes;
GRANT USAGE ON SCHEMA app TO lector_informes;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO lector_informes;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO lector_informes;

-- 7. Rol de monitorizacion: rol predefinido, sin ser superusuario
CREATE ROLE monitor WITH LOGIN PASSWORD 'otra-mas';
GRANT pg_monitor TO monitor;

-- 8. Rol de replicacion (apartado 12)
CREATE ROLE replicador WITH LOGIN REPLICATION PASSWORD 'y-otra';

$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d tramontana -c "CREATE TABLE prueba(id int);"
ERROR:  permiso denegado para el esquema app

$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d tramontana -c "SELECT * FROM pg_shadow;"
ERROR:  permiso denegado para la tabla pg_shadow

$ sudo -u postgres psql -c "\du" | grep -E 'svc_tramontana|lector'
 lector_informes | 5 conexiones                       | {}
 svc_tramontana  | Sin herencia, 30 conexiones        | {}

$ sudo -u postgres psql -d tramontana <<SQL
ALTER ROLE svc_tramontana PASSWORD '$(pass tramontana/db)';
SQL
# Y se borra del historial de psql, que guarda TODO lo tecleado
$ shred -u ~/.psql_history 2>/dev/null || true

#!/usr/bin/env bash
#
# /usr/local/bin/archivar_wal.sh - Archiva un segmento de WAL
#
# PostgreSQL lo invoca como: archivar_wal.sh %p %f
#   %p = ruta relativa al segmento; %f = solo el nombre
#
# CONTRATO CRITICO:
#   - Debe devolver 0 SOLO si el segmento esta a salvo y verificado.
#   - Si devuelve != 0, PostgreSQL REINTENTA indefinidamente y NO borra
#     el segmento. Es lo correcto: mejor llenar pg_wal que perder datos.
#   - NUNCA debe sobrescribir un fichero existente con contenido distinto.
#
set -euo pipefail

readonly ORIGEN="$1"
readonly NOMBRE="$2"
readonly DESTINO="/srv/tramontana/backups/wal"

umask 077

# Ya archivado e identico: exito idempotente (07-06)
if [[ -f "${DESTINO}/${NOMBRE}" ]]; then
    if cmp -s "$ORIGEN" "${DESTINO}/${NOMBRE}"; then
        exit 0
    fi
    logger -t archivar_wal "ERROR: ${NOMBRE} ya existe con contenido DISTINTO"
    exit 1
fi

# Copia atomica: escribir a temporal y renombrar. Si el proceso muere a
# medias, no queda un segmento truncado que parezca valido.
tmp="${DESTINO}/.${NOMBRE}.$$"
trap 'rm -f "$tmp"' EXIT

cp "$ORIGEN" "$tmp"
sync -f "$tmp"                  # forzar a disco ANTES de renombrar
mv "$tmp" "${DESTINO}/${NOMBRE}"
sync "$DESTINO"

exit 0

$ sudo install -o root -g root -m 0755 archivar_wal.sh /usr/local/bin/
$ sudo -u postgres mkdir -p /srv/tramontana/backups/wal

$ sudo -u postgres psql -c "SELECT pg_switch_wal();"   # forzar un segmento
$ sudo -u postgres psql -c "SELECT archived_count, last_archived_wal,
    last_archived_time, failed_count, last_failed_wal FROM pg_stat_archiver;"
 archived_count |    last_archived_wal     |      last_archived_time       | failed_count
----------------+--------------------------+-------------------------------+--------------
             47 | 000000010000000000000031 | 2026-08-18 12:41:08.221+02    |            0

$ sudo -u postgres pg_basebackup \
      -h /var/run/postgresql -U postgres \
      -D /srv/tramontana/backups/base/$(date +%F) \
      -Ft -z -Xs -P -c fast --manifest-checksums=SHA256
 1843712/1843712 kB (100%), 1/1 tablespace

$ sudo -u postgres pg_verifybackup /srv/tramontana/backups/base/2026-08-18
backup successfully verified

# Fragmento anadido a respaldo_tramontana.sh
respaldar_postgresql() {
    local destino="${TRAMONTANA_BACKUP_DIR}/base/$(date +%F)"
    log "iniciando copia base de PostgreSQL"
    sudo -u postgres pg_basebackup -h /var/run/postgresql -U postgres \
        -D "$destino" -Ft -z -Xs -c fast --manifest-checksums=SHA256 \
        || morir 74 "pg_basebackup fallo"
    sudo -u postgres pg_verifybackup "$destino" \
        || morir 65 "la copia base NO verifica: no se sube"
    log "copia base verificada: $(formatear_bytes "$(du -sb "$destino" | cut -f1)")"

    restic backup "$destino" "${TRAMONTANA_BACKUP_DIR}/wal" \
        --tag postgresql --tag base \
        || morir 74 "restic fallo al subir la copia"
}

# ===== SE HACE EN srv-tramontana-pruebas, NUNCA sobre produccion =====
# Restaurar sobre el servidor vivo destruye la unica copia de los datos
# posteriores al incidente. Se restaura aparte y se extrae lo perdido.

# 1. Parar PostgreSQL en la maquina de recuperacion y vaciar PGDATA
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres mv /var/lib/postgresql/16/main \
      /var/lib/postgresql/16/main.roto-$(date +%F)
$ sudo -u postgres mkdir -m 0700 /var/lib/postgresql/16/main

# 2. Descomprimir la ultima copia base ANTERIOR al incidente
$ sudo -u postgres tar -xzf /srv/tramontana/backups/base/2026-08-18/base.tar.gz \
      -C /var/lib/postgresql/16/main

# 3. Decirle a PostgreSQL de donde saca el WAL y hasta donde reproducir
$ sudo -u postgres tee /var/lib/postgresql/16/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /srv/tramontana/backups/wal/%f %p'
# Reproducir hasta JUSTO ANTES del DELETE. Un segundo de margen.
recovery_target_time = '2026-08-18 14:31:55+02'
recovery_target_action = 'pause'
EOF

# 'pause' y no 'promote': el servidor se detiene en el instante objetivo
# y espera. Asi puedes MIRAR los datos antes de confirmar. Si te pasaste,
# reinicias con otro objetivo sin haber destruido nada.

# 4. La senal que activa el modo recuperacion (PostgreSQL >= 12)
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 5. Arrancar y observar
$ sudo systemctl start postgresql@16-main
$ sudo journalctl -u postgresql@16-main -f
LOG:  starting point-in-time recovery to 2026-08-18 14:31:55+02
LOG:  restored log file "000000010000000000000031" from archive
LOG:  restored log file "000000010000000000000032" from archive
LOG:  recovery stopping before commit of transaction 84412, time 2026-08-18 14:32:04+02
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

# 6. VERIFICAR antes de confirmar nada
$ sudo -u postgres psql -d tramontana -c \
    "SELECT count(*) FROM app.reservas WHERE fecha < '2026-08-01';"
 count
-------
 40218

# 7. Confirmar: promover el servidor recuperado
$ sudo -u postgres psql -c "SELECT pg_wal_replay_resume();"
$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f

# 8. Extraer SOLO lo perdido y llevarlo a produccion
$ sudo -u postgres pg_dump -d tramontana -t app.reservas \
      --data-only --where="fecha < '2026-08-01'" > /tmp/recuperadas.sql
$ scp /tmp/recuperadas.sql [email protected]:/tmp/
$ ssh [email protected] \
    "sudo -u postgres psql -d tramontana -1 -f /tmp/recuperadas.sql"

$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, n_live_tup AS vivas, n_dead_tup AS muertas,
         round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) AS pct_muertas,
         last_autovacuum
  FROM pg_stat_user_tables WHERE n_dead_tup > 1000
  ORDER BY n_dead_tup DESC;"
   relname   | vivas  | muertas | pct_muertas |        last_autovacuum
-------------+--------+---------+-------------+-------------------------------
 disponibles | 891200 |  412880 |        31.7 | 2026-08-16 03:12:41+02
 reservas    | 186420 |   18122 |         8.9 | 2026-08-18 04:02:11+02

-- Ajuste POR TABLA, que es como se hace: no se cambia el global por
-- una tabla problematica.
ALTER TABLE app.disponibles SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- limpiar al 2 %, no al 20 %
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 2         -- mas agresivo
);

-- La tabla de auditoria solo crece: no necesita el mismo tratamiento,
-- necesita una politica de retencion.
ALTER TABLE app.auditoria SET (autovacuum_vacuum_scale_factor = 0.1);

# conf.d/30-mantenimiento.conf — ajustes globales
autovacuum_max_workers = 2           # con 2 vCPU, no mas
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 1000  # por defecto 200: demasiado lento

ERROR:  database is not accepting commands to avoid wraparound data loss
in database "tramontana"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

$ sudo -u postgres psql -c "
  SELECT datname, age(datfrozenxid) AS edad_xid,
         round(100.0*age(datfrozenxid)/2000000000, 1) AS pct_hacia_limite
  FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 3;"
  datname   | edad_xid  | pct_hacia_limite
------------+-----------+------------------
 tramontana |  48212104 |              2.4
 postgres   |  12088311 |              0.6

$ sudo -u postgres psql -c "
  SELECT pid, state, age(backend_xid) AS edad,
         now()-xact_start AS duracion, left(query,50) AS consulta
  FROM pg_stat_activity
  WHERE backend_xid IS NOT NULL ORDER BY age(backend_xid) DESC LIMIT 3;"
 pid  |        state        |  edad   |    duracion     |         consulta
------+---------------------+---------+-----------------+---------------------------
 4471 | idle in transaction | 8812044 | 3 days 04:12:09 | BEGIN; SELECT * FROM app...

# conf.d/30-mantenimiento.conf
idle_in_transaction_session_timeout = 300s   # matar tras 5 min ociosa
statement_timeout = 60s                      # ninguna consulta mas de 1 min
lock_timeout = 10s                           # no esperar bloqueos eternamente

$ sudo -u postgres psql -d tramontana -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
$ sudo -u postgres psql -d tramontana -c "
  SELECT round(total_exec_time::numeric,0) AS ms_total, calls,
         round(mean_exec_time::numeric,1) AS ms_media,
         round(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0),1) AS acierto,
         left(query, 60) AS consulta
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 ms_total  | calls | ms_media | acierto |                  consulta
-----------+-------+----------+---------+---------------------------------------------
   4128200 |  1088 |   3794.3 |    41.2 | SELECT casa, sum(importe) FROM app.reservas
    881400 | 92104 |      9.6 |    99.8 | SELECT * FROM app.disponibles WHERE casa =
    412900 |  1204 |    342.9 |    98.1 | SELECT * FROM app.reservas WHERE huesped_id

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT casa, sum(importe) FROM app.reservas
WHERE fecha >= '2026-01-01' GROUP BY casa;

 HashAggregate  (cost=48122.11..48122.16 rows=5 width=40)
                (actual time=3781.442..3781.449 rows=5 loops=1)
   Group Key: casa
   Buffers: shared hit=812 read=41208
   ->  Seq Scan on reservas  (cost=0.00..47188.20 rows=186782 width=18)
                             (actual time=0.031..3402.118 rows=186420 loops=1)
         Filter: (fecha >= '2026-01-01'::date)
         Rows Removed by Filter: 0
         Buffers: shared hit=812 read=41208
 Planning Time: 0.184 ms
 Execution Time: 3781.512 ms

-- Indice de cobertura: contiene todo lo que la consulta necesita, asi
-- que se responde SIN tocar la tabla (Index Only Scan).
CREATE INDEX CONCURRENTLY idx_reservas_fecha_casa_importe
    ON app.reservas (fecha) INCLUDE (casa, importe);
ANALYZE app.reservas;

 HashAggregate  (actual time=182.401..182.409 rows=5 loops=1)
   Group Key: casa
   Buffers: shared hit=1204 read=3811
   ->  Index Only Scan using idx_reservas_fecha_casa_importe on reservas
         (actual time=0.048..96.221 rows=186420 loops=1)
         Index Cond: (fecha >= '2026-01-01'::date)
         Heap Fetches: 0
         Buffers: shared hit=1204 read=3811
 Execution Time: 182.478 ms

$ sudo journalctl -u postgresql@16-main --since today | grep -oP 'duration: \K[0-9.]+' | \
      sort -rn | head -3
3812.402
1204.118
890.331

$ sudo journalctl -u postgresql@16-main --since today | grep 'temporary file'
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp4471.0", size 18874368
STATEMENT:  SELECT ... ORDER BY fecha DESC

# Tablas con muchos recorridos secuenciales sobre volumen grande
$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, seq_scan, seq_tup_read, idx_scan,
         seq_tup_read/nullif(seq_scan,0) AS filas_por_recorrido
  FROM pg_stat_user_tables
  WHERE seq_scan > 100 AND seq_tup_read/nullif(seq_scan,0) > 10000
  ORDER BY seq_tup_read DESC;"
 relname  | seq_scan | seq_tup_read | idx_scan | filas_por_recorrido
----------+----------+--------------+----------+---------------------
 reservas |     1088 |    202744960 |    91204 |              186346

# Indices que nadie usa: ocupan espacio y ralentizan cada INSERT
$ sudo -u postgres psql -d tramontana -c "
  SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS tam, idx_scan
  FROM pg_stat_user_indexes WHERE idx_scan < 50
    AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
  ORDER BY pg_relation_size(indexrelid) DESC;"
    indexrelname       |  tam  | idx_scan
-----------------------+-------+----------
 idx_reservas_telefono | 22 MB |        0

# ===== En el PRIMARIO (10.0.2.15) =====
# wal_level = replica y archive_mode = on ya estan puestos.
$ sudo -u postgres psql -c "
  SELECT slot_name FROM pg_create_physical_replication_slot('replica_16');"

# conf.d/40-replicacion.conf en el PRIMARIO
max_wal_senders = 3
max_replication_slots = 3
wal_keep_size = 1GB
# Limite de seguridad: si la ranura acumula mas de 8 GB, se invalida.
# Mejor perder la replica que detener el primario.
max_slot_wal_keep_size = 8GB

# ===== En la REPLICA (10.0.2.16) =====
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres rm -rf /var/lib/postgresql/16/main/*

$ sudo -u postgres PGPASSWORD=$(pass tramontana/replicador) pg_basebackup \
      -h 10.0.2.15 -U replicador -D /var/lib/postgresql/16/main \
      -R -P -Xs -C -S replica_16 \
      -d "sslmode=verify-full sslrootcert=/etc/postgresql/ca-tramontana.crt"
 1843712/1843712 kB (100%), 1/1 tablespace

$ sudo -u postgres cat /var/lib/postgresql/16/main/postgresql.auto.conf
primary_conninfo = 'user=replicador passfile=''/var/lib/postgresql/.pgpass''
  host=10.0.2.15 port=5432 sslmode=verify-full ...'
primary_slot_name = 'replica_16'

$ sudo systemctl start postgresql@16-main
$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t

$ sudo -u postgres psql -x -c "
  SELECT client_addr, state, sync_state,
         pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS retraso_bytes,
         write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
-[ RECORD 1 ]-+----------------
client_addr   | 10.0.2.16
state         | streaming
sync_state    | async
retraso_bytes | 0 bytes
write_lag     | 00:00:00.002
replay_lag    | 00:00:00.004

# En la replica, para que las consultas largas no se corten por conflicto
# con la reproduccion del WAL
$ echo "max_standby_streaming_delay = 300s" | \
      sudo tee -a /etc/postgresql/16/main/conf.d/40-replicacion.conf

# ~/tramontana-infra/roles/basedatos/defaults/main.yml
---
bd_version: 16
bd_ram_mb: 3800
# Las formulas viven en las variables: cambia la RAM y todo se recalcula
bd_shared_buffers_mb: "{{ (bd_ram_mb * 0.25) | int }}"
bd_effective_cache_mb: "{{ (bd_ram_mb * 0.67) | int }}"
bd_work_mem_mb: 8
bd_maintenance_work_mem_mb: "{{ (bd_ram_mb * 0.07) | int }}"
bd_max_connections: 60
bd_random_page_cost: 1.1          # SSD
bd_log_min_duration_ms: 250
bd_archive_dir: /srv/tramontana/backups/wal
bd_pgbouncer_pool_size: 25
bd_pgbouncer_max_clients: 200
bd_redes_permitidas:
  - { red: '127.0.0.1/32', bd: tramontana, rol: svc_tramontana, tipo: host }
  - { red: '10.0.2.0/24',  bd: tramontana, rol: operador,       tipo: hostssl }
  - { red: '10.0.2.16/32', bd: replication, rol: replicador,    tipo: hostssl }

# ~/tramontana-infra/roles/basedatos/tasks/main.yml
---
- name: Comprobar que la RAM declarada coincide con la real
  ansible.builtin.assert:
    that: (ansible_memtotal_mb - bd_ram_mb) | abs < 400
    fail_msg: >-
      bd_ram_mb ({{ bd_ram_mb }}) no coincide con la RAM real
      ({{ ansible_memtotal_mb }} MB). Los calculos de memoria serian
      incorrectos y podrian dejar el servidor sin memoria.

- name: Instalar PostgreSQL y PgBouncer
  ansible.builtin.apt:
    name:
      - "postgresql-{{ bd_version }}"
      - "postgresql-contrib-{{ bd_version }}"
      - pgbouncer
      - python3-psycopg2        # necesario para los modulos postgresql_*
    state: present
  tags: [paquetes]

- name: Bloquear la version mayor (politica de 05-03)
  ansible.builtin.dpkg_selections:
    name: "postgresql-{{ bd_version }}"
    selection: hold

- name: Directorio de archivado de WAL
  ansible.builtin.file:
    path: "{{ bd_archive_dir }}"
    state: directory
    owner: postgres
    group: postgres
    mode: '0700'

- name: Instalar el script de archivado de WAL
  ansible.builtin.copy:
    src: archivar_wal.sh
    dest: /usr/local/bin/archivar_wal.sh
    owner: root
    group: root
    mode: '0755'

- name: Activar include_dir en postgresql.conf
  ansible.builtin.lineinfile:
    path: "/etc/postgresql/{{ bd_version }}/main/postgresql.conf"
    line: "include_dir = 'conf.d'"
    regexp: '^#?\s*include_dir\s*='
    backup: true
  notify: Reiniciar postgresql

- name: Desplegar el ajuste calculado
  ansible.builtin.template:
    src: 10-tramontana.conf.j2
    dest: "/etc/postgresql/{{ bd_version }}/main/conf.d/10-tramontana.conf"
    owner: postgres
    group: postgres
    mode: '0640'
    backup: true
  notify: Reiniciar postgresql

- name: Desplegar pg_hba.conf
  ansible.builtin.template:
    src: pg_hba.conf.j2
    dest: "/etc/postgresql/{{ bd_version }}/main/pg_hba.conf"
    owner: postgres
    group: postgres
    mode: '0640'
    backup: true
  notify: Recargar postgresql

- name: Forzar los handlers antes de verificar
  ansible.builtin.meta: flush_handlers

# --- Verificacion: pg_hba invalido deja el servidor inaccesible ---
- name: Verificar que pg_hba.conf no tiene errores
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT count(*) AS errores FROM pg_hba_file_rules WHERE error IS NOT NULL"
  become: true
  become_user: postgres
  register: hba
  failed_when: hba.query_result[0].errores | int > 0

- name: Crear los roles con contrasena desde Vault
  community.postgresql.postgresql_user:
    name: "{{ item.nombre }}"
    password: "{{ item.password }}"
    role_attr_flags: "{{ item.flags }}"
    conn_limit: "{{ item.limite | default(omit) }}"
    state: present
  become: true
  become_user: postgres
  loop:
    - { nombre: svc_tramontana, password: "{{ vault_bd_app }}",
        flags: 'LOGIN,NOSUPERUSER,NOCREATEDB,NOCREATEROLE', limite: 30 }
    - { nombre: replicador, password: "{{ vault_bd_replicador }}",
        flags: 'LOGIN,REPLICATION' }
    - { nombre: monitor, password: "{{ vault_bd_monitor }}", flags: 'LOGIN' }
  no_log: true                     # las contrasenas no salen por pantalla
  tags: [roles]

- name: Conceder pg_monitor al rol de monitorizacion
  community.postgresql.postgresql_membership:
    group: pg_monitor
    target_roles: monitor
    state: present
  become: true
  become_user: postgres

- name: Configurar PgBouncer
  ansible.builtin.template:
    src: pgbouncer.ini.j2
    dest: /etc/pgbouncer/pgbouncer.ini
    owner: postgres
    group: postgres
    mode: '0640'
    backup: true
  notify: Reiniciar pgbouncer

- name: Verificar que el archivado de WAL funciona
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT failed_count, last_archived_time FROM pg_stat_archiver"
  become: true
  become_user: postgres
  register: archivador
  failed_when: archivador.query_result[0].failed_count | int > 0
  tags: [verificar]

# roles/basedatos/handlers/main.yml
---
- name: Recargar postgresql
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT pg_reload_conf()"
  become: true
  become_user: postgres

- name: Reiniciar postgresql
  # Un reinicio corta las conexiones. Solo se dispara cuando cambia un
  # parametro que lo requiere, y en produccion se ejecuta en ventana.
  ansible.builtin.systemd:
    name: "postgresql@{{ bd_version }}-main"
    state: restarted

- name: Reiniciar pgbouncer
  ansible.builtin.systemd:
    name: pgbouncer
    state: restarted

#!/usr/bin/env bash
# Fragmento a integrar en revision_salud.sh: bloque de base de datos
comprobar_basedatos() {
    local estado=0

    # 1. Conexiones esperando en el pool
    local esperando
    esperando="$(psql -h 127.0.0.1 -p 6432 -U operador -d pgbouncer -tAc \
        "SHOW POOLS" | awk -F'|' '$1=="tramontana"{print $4}')"
    if (( esperando > 0 )); then
        error "hay $esperando clientes esperando conexion"; estado=1
    fi

    # 2. Retraso de la replica (bytes que se perderian ahora mismo)
    local retraso
    retraso="$(sudo -u postgres psql -tAc \
        "SELECT coalesce(max(pg_wal_lsn_diff(sent_lsn,replay_lsn)),0)::bigint
         FROM pg_stat_replication")"
    if (( retraso > 104857600 )); then          # 100 MB
        error "la replica va $(formatear_bytes "$retraso") por detras"; estado=1
    fi

    # 3. Fallos de archivado de WAL: si falla, pg_wal crece sin limite
    local fallos
    fallos="$(sudo -u postgres psql -tAc "SELECT failed_count FROM pg_stat_archiver")"
    if (( fallos > 0 )); then
        error "el archivado de WAL ha fallado $fallos veces"; estado=2
    fi

    # 4. Wraparound
    local pct
    pct="$(sudo -u postgres psql -tAc \
        "SELECT round(100.0*max(age(datfrozenxid))/2000000000) FROM pg_database")"
    if (( pct > 75 )); then
        error "wraparound al ${pct}%: EMERGENCIA"; estado=2
    elif (( pct > 50 )); then
        error "wraparound al ${pct}%"; estado=1
    fi

    # 5. Transacciones abiertas eternamente
    local zombis
    zombis="$(sudo -u postgres psql -tAc \
        "SELECT count(*) FROM pg_stat_activity
         WHERE state='idle in transaction' AND now()-xact_start > interval '10 min'")"
    if (( zombis > 0 )); then
        error "$zombis transacciones ociosas de mas de 10 min"; estado=1
    fi

    (( estado == 0 )) && log "base de datos correcta"
    return "$estado"
}

$ sudo du -sh /var/lib/postgresql/16/main/* | sort -rh | head -4
9.8G	/var/lib/postgresql/16/main/pg_wal
1.6G	/var/lib/postgresql/16/main/base
2.1M	/var/lib/postgresql/16/main/global

$ ls /var/lib/postgresql/16/main/pg_wal/*.ready 2>/dev/null | wc -l
612

$ sudo -u postgres psql -c "SELECT archived_count, failed_count,
    last_failed_wal, last_failed_time FROM pg_stat_archiver;"
 archived_count | failed_count |     last_failed_wal      |       last_failed_time
----------------+--------------+--------------------------+-------------------------------
           4128 |         2044 | 000000010000000000000A31 | 2026-08-18 03:12:55.118+02

$ sudo journalctl -t archivar_wal --since "6 hours ago" | tail -2
archivar_wal[8812]: cp: no se puede crear el fichero regular
  '/srv/tramontana/backups/wal/.000000010000000000000A31.8812': Dispositivo sin espacio

lv-backups lleno -> archivar_wal.sh falla -> segmentos no se borran
  -> pg_wal crece -> se llena /var/lib -> PostgreSQL SE DETIENE

$ sudo -u postgres psql -c "SELECT slot_name, active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retenido
  FROM pg_replication_slots;"
 slot_name  | active | retenido
------------+--------+----------
 replica_16 | t      | 12 MB

# --- PASO 1: espacio inmediato en el destino de archivado ---
# Subir a restic lo que ya esta archivado y liberar lo mas antiguo
$ sudo restic backup /srv/tramontana/backups/wal --tag wal-urgente
$ sudo restic check --read-data-subset=5%      # verificar ANTES de borrar

# Solo entonces, borrar los WAL anteriores a la copia base mas antigua
# que queremos conservar. pg_archivecleanup calcula cuales sobran: NUNCA
# se borran a mano por fecha.
$ sudo -u postgres pg_archivecleanup -d /srv/tramontana/backups/wal \
      000000010000000000000900
pg_archivecleanup: removing file "000000010000000000000412"
...
pg_archivecleanup: 1288 files removed

$ df -h /srv/tramontana/backups
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg--datos-lv--backups  15G  4.2G  9.9G  30% /srv/tramontana/backups

# --- PASO 2: drenar la cola de .ready ---
$ sudo -u postgres psql -c "SELECT pg_switch_wal();"
$ sleep 60
$ ls /var/lib/postgresql/16/main/archive_status/*.ready | wc -l
0
$ sudo du -sh /var/lib/postgresql/16/main/pg_wal
412M	/var/lib/postgresql/16/main/pg_wal

$ sudo -u postgres psql -c "SELECT pg_stat_reset_shared('archiver');"

# 1. Retencion automatica de WAL, semanal, tras verificar restic
$ cat /home/operador/scripts/purgar_wal.sh
#!/usr/bin/env bash
set -euo pipefail
source "$(dirname "${BASH_SOURCE[0]}")/lib/comunes.sh"

readonly WAL_DIR=/srv/tramontana/backups/wal
readonly BASE_DIR=/srv/tramontana/backups/base

main() {
    requiere_comando pg_archivecleanup
    requiere_comando restic

    # Nunca purgar por debajo de la copia base mas antigua que se conserva
    local base_mas_antigua
    base_mas_antigua="$(find "$BASE_DIR" -maxdepth 1 -type d -name '20*' | sort | head -1)" \
        || morir 75 "no hay ninguna copia base: NO se purga nada"
    [[ -n "$base_mas_antigua" ]] || morir 75 "no hay copia base; abortando"

    # Y solo si restic tiene el contenido a salvo
    restic check --read-data-subset=2% >/dev/null \
        || morir 65 "restic no verifica: no se purga nada"

    local wal_inicial
    wal_inicial="$(awk '/^START WAL LOCATION/{print $6}' \
        "${base_mas_antigua}/backup_label" | tr -d ')')"
    log "purgando WAL anterior a $wal_inicial"
    pg_archivecleanup -d "$WAL_DIR" "$wal_inicial"
}
main "$@"

# 2. Alerta ANTES del problema, no cuando ya no hay salida
# /etc/systemd/system/vigilar-wal.service (ejecutado cada 15 min)
[Service]
Type=oneshot
ExecStart=/home/operador/scripts/vigilar_wal.sh

# vigilar_wal.sh: dos umbrales, dos niveles
#   - .ready > 20         -> aviso: el archivado se esta atrasando
#   - uso de lv-backups > 75 % -> aviso; > 90 % -> critico
#   - failed_count > 0    -> critico inmediato

# 3. Red de seguridad en el propio PostgreSQL
# Limita cuanto WAL puede acumular una ranura antes de invalidarla.
max_slot_wal_keep_size = 8GB

# 4. Ampliar lv-backups con margen (05-04), ahora con datos
$ sudo lvextend -L +10G /dev/vg-datos/lv-backups
$ sudo cryptsetup resize backups-cifrado
$ sudo resize2fs /dev/mapper/backups-cifrado