$ 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

# Mida actual de la base de dades i de les seves taules mes grans
$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
         n_live_tup AS files
  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  | files
-------------+---------+--------
 reserves    | 412 MB  | 186420
 disponibles | 168 MB  | 891200
 hostes      |  54 MB  |  92310
 cases       |  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            # les dades: un subdirectori per base de dades
global          # cataleg compartit entre bases
pg_wal          # el registre d escriptura anticipada
pg_stat         # estadistiques del planificador
pg_tblspc       # enllacos a tablespaces externs
postgresql.auto.conf   # el que escriu 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

Pitjor cas teoric = work_mem x connexions x operacions per consulta
                  = 8 MB x 100 x 3 = 2400 MB

-- A la sessio de l informe mensual, no a tot el servidor
BEGIN;
SET LOCAL work_mem = '128MB';
SELECT casa, sum(import) FROM reserves WHERE data >= '2026-01-01' GROUP BY casa;
COMMIT;

# /etc/postgresql/16/main/conf.d/10-tramontana.conf
# Ajust per a srv-tramontana: 3,8 GB RAM, 2 vCPU, SSD, PostgreSQL 16
# Base de referencia presa el 2026-08-18. Mesurar abans i despres.

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

# ---------- Connexions ----------
# Es BAIXA de 100 a 60. Amb PgBouncer al davant, 60 sobren, i cada
# connexio reservada costa memoria encara que estigui ociosa.
max_connections = 60
superuser_reserved_connections = 3

# ---------- Registre d escriptura anticipada ----------
wal_level = replica              # necessari per a replica i PITR
max_wal_size = 2GB               # menys checkpoints, pics mes suaus
min_wal_size = 256MB
checkpoint_completion_target = 0.9   # reparteix l escriptura en el temps
archive_mode = on
archive_command = '/usr/local/bin/arxivar_wal.sh %p %f'
archive_timeout = 900s           # forca un segment cada 15 min

# ---------- Planificador (SSD) ----------
# random_page_cost per defecte es 4.0, calibrat per a discos giratoris
# on un salt aleatori costava molt mes que una lectura sequencial.
# En SSD la diferencia es minima: 1.1 reflecteix la realitat i fa que el
# planificador faci servir indexs on toca.
random_page_cost = 1.1
effective_io_concurrency = 200   # el SSD aten moltes peticions alhora

# ---------- Paral·lelisme (2 vCPU: contencio) ----------
max_worker_processes = 2
max_parallel_workers = 2
max_parallel_workers_per_gather = 1

# ---------- Registre ----------
log_destination = 'stderr'
logging_collector = off          # deixa que systemd/journald ho reculli
log_line_prefix = '%m [%p] %q%u@%d '
log_min_duration_statement = 250ms   # registra les consultes lentes
log_checkpoints = on
log_connections = off            # sorollos amb una agrupacio al davant
log_lock_waits = on              # esperes de blocatge: simptoma important
log_temp_files = 0               # qualsevol fitxer temporal = work_mem curt
log_autovacuum_min_duration = 1s

# ---------- Estadistiques ----------
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 i shared_preload_libraries requereixen REINICI
$ 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

# L index d encerts de la memoria cau compartida. Es mesura ABANS i
# DESPRES, deixant passar unes hores de transit real.
$ sudo -u postgres psql -d tramontana -c "
  SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit + blks_read), 0), 2)
         AS encert_pct
  FROM pg_stat_database WHERE datname = 'tramontana';"
 encert_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. Quantes en necessita PostgreSQL (arrenca, pregunta i surt)
$ sudo -u postgres /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main \
      -C shared_memory_size_in_huge_pages
495

# 2. Reservar-les amb marge, de forma persistent
$ echo 'vm.nr_hugepages = 520' | \
      sudo tee /etc/sysctl.d/71-postgresql-hugepages.conf
$ sudo sysctl --system

# 3. Demanar a PostgreSQL que les faci servir
$ 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]
; L aplicacio es connecta a PgBouncer creient-se 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 d autenticacio delegada: evita duplicar contrasenyes
auth_user = pgbouncer_auth

pool_mode = transaction

; --- El dimensionament, que es la decisio central ---
; max_client_conn: quants clients d aplicacio acceptem (barats)
max_client_conn = 200
; default_pool_size: connexions REALS a PostgreSQL per parell usuari/bd.
; Amb 2 vCPU, 25 es folgat. Aquest es el numero que protegeix el servidor.
default_pool_size = 25
; Marge temporal per a pics, amb avis al registre
reserve_pool_size = 5
reserve_pool_timeout = 3

; Tancar connexions de servidor ocioses molt de temps
server_idle_timeout = 600
; Reciclar connexions cada hora: evita fuites de memoria acumulades
server_lifetime = 3600
; Si un client espera mes d aixo per una connexio, error clar
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

TIPUS     BASE_DE_DADES   USUARI           ADRECA             METODE

# /etc/postgresql/16/main/pg_hba.conf
# L ordre IMPORTA: guanya la primera coincidencia.

# --- 1. Manteniment local per socol Unix ---
# 'peer' compara l usuari del sistema amb el rol demanat: 'sudo -u postgres
# psql' funciona sense contrasenya, i ningu mes no pot fer-se passar per ell.
local   all             postgres                                peer
local   all             all                                     peer

# --- 2. PgBouncer i l aplicacio, des del bucle local ---
# Nomes el rol d aplicacio, nomes la seva base de dades, amb 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. Monitoracio (08-06), nomes lectura d estadistiques ---
host    postgres        monitor         127.0.0.1/32            scram-sha-256

# --- 4. Replicacio (apartat 12), amb TLS OBLIGATORI ---
# 'hostssl' rebutja la connexio si no va xifrada; el WAL porta
# les dades completes i no pot viatjar en clar.
hostssl replication     replicador      10.0.2.16/32            scram-sha-256

# --- 5. Acces administratiu des de la xarxa interna, xifrat ---
hostssl tramontana      operador        10.0.2.0/24             scram-sha-256

# --- 6. Denegacio explicita de tota la resta ---
# Redundant (el comportament per defecte ja es denegar) pero deixa
# constancia de la intencio i produeix un missatge clar al registre.
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 rows)

# Ha de 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 ha de funcionar: rol d aplicacio contra una altra base de dades
$ 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 certificat autosignat i l enllaca en instal·lar
$ sudo ls -l /var/lib/postgresql/16/main/server.crt
lrwxrwxrwx 1 postgres postgres 36 -> /etc/ssl/certs/ssl-cert-snakeoil.pem

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

# Certificat de servidor per a la base de dades
$ openssl req -new -nodes -out bd.csr -keyout bd.key \
      -subj "/CN=srv-tramontana.intern"
$ 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

-- Executat com a postgres: sudo -u postgres psql -d tramontana

-- 1. Rol d aplicacio: nomes es pot connectar i treballar amb dades
CREATE ROLE svc_tramontana WITH LOGIN
    PASSWORD 's-injecta-des-de-pass'
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT
    CONNECTION LIMIT 30;

-- 2. Tancar l esquema public, que a PostgreSQL 15+ ja ve restringit,
--    pero conve ser explicit. Abans de la 15, QUALSEVOL usuari podia
--    crear objectes a 'public'.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE tramontana FROM PUBLIC;

-- 3. Esquema propi de l aplicacio
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION postgres;

-- 4. Permisos acotats: connectar-se, fer servir l esquema, i DML sobre
--    les taules existents. NO CREATE: no pot alterar l 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. I per a les taules FUTURES, que es el que gairebe tothom oblida:
--    sense aixo, la taula que crei la proxima migracio sera inaccessible
--    per a l aplicacio i el desplegament fallara en produccio.
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 nomes lectura per a informes (informe_reserves.sh) i per a
--    la monitoracio de 08-06
CREATE ROLE lector_informes WITH LOGIN PASSWORD 'una-altra-de-diferent'
    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 monitoracio: rol predefinit, sense ser superusuari
CREATE ROLE monitor WITH LOGIN PASSWORD 'una-altra-mes';
GRANT pg_monitor TO monitor;

-- 8. Rol de replicacio (apartat 12)
CREATE ROLE replicador WITH LOGIN REPLICATION PASSWORD 'i-una-altra';

$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d tramontana -c "CREATE TABLE prova(id int);"
ERROR:  permission denied for schema app

$ PGPASSWORD=$(pass tramontana/db) psql -h 127.0.0.1 -U svc_tramontana \
      -d tramontana -c "SELECT * FROM pg_shadow;"
ERROR:  permission denied for table pg_shadow

$ sudo -u postgres psql -c "\du" | grep -E 'svc_tramontana|lector'
 lector_informes | 5 connections                      | {}
 svc_tramontana  | No inheritance, 30 connections     | {}

$ sudo -u postgres psql -d tramontana <<SQL
ALTER ROLE svc_tramontana PASSWORD '$(pass tramontana/db)';
SQL
# I s esborra de l historial de psql, que guarda TOT el que s ha teclejat
$ shred -u ~/.psql_history 2>/dev/null || true

#!/usr/bin/env bash
#
# /usr/local/bin/arxivar_wal.sh - Arxiva un segment de WAL
#
# PostgreSQL l invoca com: arxivar_wal.sh %p %f
#   %p = ruta relativa al segment; %f = nomes el nom
#
# CONTRACTE CRITIC:
#   - Ha de retornar 0 NOMES si el segment esta segur i verificat.
#   - Si retorna != 0, PostgreSQL HO REINTENTA indefinidament i NO esborra
#     el segment. Es el correcte: millor omplir pg_wal que perdre dades.
#   - MAI no ha de sobreescriure un fitxer existent amb contingut diferent.
#
set -euo pipefail

readonly ORIGEN="$1"
readonly NOM="$2"
readonly DESTI="/srv/tramontana/backups/wal"

umask 077

# Ja arxivat i identic: exit idempotent (07-06)
if [[ -f "${DESTI}/${NOM}" ]]; then
    if cmp -s "$ORIGEN" "${DESTI}/${NOM}"; then
        exit 0
    fi
    logger -t arxivar_wal "ERROR: ${NOM} ja existeix amb contingut DIFERENT"
    exit 1
fi

# Copia atomica: escriure a temporal i reanomenar. Si el proces mor a
# mitges, no queda un segment truncat que sembli valid.
tmp="${DESTI}/.${NOM}.$$"
trap 'rm -f "$tmp"' EXIT

cp "$ORIGEN" "$tmp"
sync -f "$tmp"                  # forcar a disc ABANS de reanomenar
mv "$tmp" "${DESTI}/${NOM}"
sync "$DESTI"

exit 0

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

$ sudo -u postgres psql -c "SELECT pg_switch_wal();"   # forcar un segment
$ 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

# Fragment afegit a copia_tramontana.sh
copiar_postgresql() {
    local desti="${TRAMONTANA_BACKUP_DIR}/base/$(date +%F)"
    log "iniciant copia base de PostgreSQL"
    sudo -u postgres pg_basebackup -h /var/run/postgresql -U postgres \
        -D "$desti" -Ft -z -Xs -c fast --manifest-checksums=SHA256 \
        || morir 74 "pg_basebackup ha fallat"
    sudo -u postgres pg_verifybackup "$desti" \
        || morir 65 "la copia base NO verifica: no es puja"
    log "copia base verificada: $(formatar_bytes "$(du -sb "$desti" | cut -f1)")"

    restic backup "$desti" "${TRAMONTANA_BACKUP_DIR}/wal" \
        --tag postgresql --tag base \
        || morir 74 "restic ha fallat en pujar la copia"
}

# ===== ES FA A srv-tramontana-proves, MAI sobre produccio =====
# Restaurar sobre el servidor viu destrueix l unica copia de les dades
# posteriors a l incident. Es restaura a part i s extreu el que s ha perdut.

# 1. Aturar PostgreSQL a la maquina de recuperacio i buidar PGDATA
$ sudo systemctl stop postgresql@16-main
$ sudo -u postgres mv /var/lib/postgresql/16/main \
      /var/lib/postgresql/16/main.trencat-$(date +%F)
$ sudo -u postgres mkdir -m 0700 /var/lib/postgresql/16/main

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

# 3. Dir a PostgreSQL d on treu el WAL i fins on reproduir
$ sudo -u postgres tee /var/lib/postgresql/16/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /srv/tramontana/backups/wal/%f %p'
# Reproduir fins JUST ABANS del DELETE. Un segon de marge.
recovery_target_time = '2026-08-18 14:31:55+02'
recovery_target_action = 'pause'
EOF

# 'pause' i no 'promote': el servidor s atura a l instant objectiu i
# espera. Aixi pots MIRAR les dades abans de confirmar. Si t has passat,
# reinicies amb un altre objectiu sense haver destruit res.

# 4. El senyal que activa el mode recuperacio (PostgreSQL >= 12)
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 5. Arrencar i 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 abans de confirmar res
$ sudo -u postgres psql -d tramontana -c \
    "SELECT count(*) FROM app.reserves WHERE data < '2026-08-01';"
 count
-------
 40218

# 7. Confirmar: promoure el servidor recuperat
$ 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. Extreure NOMES el que s ha perdut i portar-ho a produccio
$ sudo -u postgres pg_dump -d tramontana -t app.reserves \
      --data-only --where="data < '2026-08-01'" > /tmp/recuperades.sql
$ scp /tmp/recuperades.sql [email protected]:/tmp/
$ ssh [email protected] \
    "sudo -u postgres psql -d tramontana -1 -f /tmp/recuperades.sql"

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

-- Ajust PER TAULA, que es com es fa: no es canvia el global per una
-- taula problematica.
ALTER TABLE app.disponibles SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- netejar al 2 %, no al 20 %
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 2         -- mes agressiu
);

-- La taula d auditoria nomes creix: no necessita el mateix tractament,
-- necessita una politica de retencio.
ALTER TABLE app.auditoria SET (autovacuum_vacuum_scale_factor = 0.1);

# conf.d/30-manteniment.conf — ajustos globals
autovacuum_max_workers = 2           # amb 2 vCPU, no mes
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 1000  # per defecte 200: massa lent

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 edat_xid,
         round(100.0*age(datfrozenxid)/2000000000, 1) AS pct_cap_al_limit
  FROM pg_database ORDER BY age(datfrozenxid) DESC LIMIT 3;"
  datname   | edat_xid | pct_cap_al_limit
------------+----------+------------------
 tramontana | 48212104 |              2.4
 postgres   | 12088311 |              0.6

$ sudo -u postgres psql -c "
  SELECT pid, state, age(backend_xid) AS edat,
         now()-xact_start AS duracio, 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        |  edat   |     duracio     |         consulta
------+---------------------+---------+-----------------+---------------------------
 4471 | idle in transaction | 8812044 | 3 days 04:12:09 | BEGIN; SELECT * FROM app...

# conf.d/30-manteniment.conf
idle_in_transaction_session_timeout = 300s   # matar despres de 5 min ociosa
statement_timeout = 60s                      # cap consulta mes d 1 min
lock_timeout = 10s                           # no esperar blocatges eternament

$ 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_mitjana,
         round(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0),1) AS encert,
         left(query, 60) AS consulta
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 ms_total  | calls | ms_mitjana | encert |                 consulta
-----------+-------+------------+--------+--------------------------------------------
   4128200 |  1088 |     3794.3 |   41.2 | SELECT casa, sum(import) FROM app.reserves
    881400 | 92104 |        9.6 |   99.8 | SELECT * FROM app.disponibles WHERE casa =
    412900 |  1204 |      342.9 |   98.1 | SELECT * FROM app.reserves WHERE hoste_id

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT casa, sum(import) FROM app.reserves
WHERE data >= '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 reserves  (cost=0.00..47188.20 rows=186782 width=18)
                             (actual time=0.031..3402.118 rows=186420 loops=1)
         Filter: (data >= '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

-- Index de cobertura: conte tot el que la consulta necessita, aixi
-- que es respon SENSE tocar la taula (Index Only Scan).
CREATE INDEX CONCURRENTLY idx_reserves_data_casa_import
    ON app.reserves (data) INCLUDE (casa, import);
ANALYZE app.reserves;

 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_reserves_data_casa_import on reserves
         (actual time=0.048..96.221 rows=186420 loops=1)
         Index Cond: (data >= '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 data DESC

# Taules amb molts recorreguts sequencials sobre volum gran
$ sudo -u postgres psql -d tramontana -c "
  SELECT relname, seq_scan, seq_tup_read, idx_scan,
         seq_tup_read/nullif(seq_scan,0) AS files_per_recorregut
  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 | files_per_recorregut
----------+----------+--------------+----------+----------------------
 reserves |     1088 |    202744960 |    91204 |               186346

# Indexs que ningu no fa servir: ocupen espai i alenteixen cada INSERT
$ sudo -u postgres psql -d tramontana -c "
  SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS mida, 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     | mida  | idx_scan
----------------------+-------+----------
 idx_reserves_telefon | 22 MB |        0

# ===== Al PRIMARI (10.0.2.15) =====
# wal_level = replica i archive_mode = on ja hi son.
$ sudo -u postgres psql -c "
  SELECT slot_name FROM pg_create_physical_replication_slot('replica_16');"

# conf.d/40-replicacio.conf al PRIMARI
max_wal_senders = 3
max_replication_slots = 3
wal_keep_size = 1GB
# Limit de seguretat: si la ranura acumula mes de 8 GB, s invalida.
# Millor perdre la replica que aturar el primari.
max_slot_wal_keep_size = 8GB

# ===== A 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 retard_bytes,
         write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
-[ RECORD 1 ]+----------------
client_addr  | 10.0.2.16
state        | streaming
sync_state   | async
retard_bytes | 0 bytes
write_lag    | 00:00:00.002
replay_lag   | 00:00:00.004

# A la replica, perque les consultes llargues no es tallin per conflicte
# amb la reproduccio del WAL
$ echo "max_standby_streaming_delay = 300s" | \
      sudo tee -a /etc/postgresql/16/main/conf.d/40-replicacio.conf

# ~/tramontana-infra/roles/bd/defaults/main.yml
---
bd_version: 16
bd_ram_mb: 3800
# Les formules viuen a les variables: canvia la RAM i tot es 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_xarxes_permeses:
  - { xarxa: '127.0.0.1/32', bd: tramontana, rol: svc_tramontana, tipus: host }
  - { xarxa: '10.0.2.0/24',  bd: tramontana, rol: operador,       tipus: hostssl }
  - { xarxa: '10.0.2.16/32', bd: replication, rol: replicador,    tipus: hostssl }

# ~/tramontana-infra/roles/bd/tasks/main.yml
---
- name: Comprovar que la RAM declarada coincideix amb la real
  ansible.builtin.assert:
    that: (ansible_memtotal_mb - bd_ram_mb) | abs < 400
    fail_msg: >-
      bd_ram_mb ({{ bd_ram_mb }}) no coincideix amb la RAM real
      ({{ ansible_memtotal_mb }} MB). Els calculs de memoria serien
      incorrectes i podrien deixar el servidor sense memoria.

- name: Instal·lar PostgreSQL i PgBouncer
  ansible.builtin.apt:
    name:
      - "postgresql-{{ bd_version }}"
      - "postgresql-contrib-{{ bd_version }}"
      - pgbouncer
      - python3-psycopg2        # necessari per als moduls postgresql_*
    state: present
  tags: [paquets]

- name: Bloquejar la versio major (politica de 05-03)
  ansible.builtin.dpkg_selections:
    name: "postgresql-{{ bd_version }}"
    selection: hold

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

- name: Instal·lar l script d arxivat de WAL
  ansible.builtin.copy:
    src: arxivar_wal.sh
    dest: /usr/local/bin/arxivar_wal.sh
    owner: root
    group: root
    mode: '0755'

- name: Activar include_dir a 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 l ajust calculat
  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: Recarregar postgresql

- name: Forcar els handlers abans de verificar
  ansible.builtin.meta: flush_handlers

# --- Verificacio: un pg_hba invalid deixa el servidor inaccessible ---
- name: Verificar que pg_hba.conf no te errors
  community.postgresql.postgresql_query:
    db: postgres
    login_unix_socket: /var/run/postgresql
    query: "SELECT count(*) AS errors FROM pg_hba_file_rules WHERE error IS NOT NULL"
  become: true
  become_user: postgres
  register: hba
  failed_when: hba.query_result[0].errors | int > 0

- name: Crear els rols amb contrasenya des de Vault
  community.postgresql.postgresql_user:
    name: "{{ item.nom }}"
    password: "{{ item.password }}"
    role_attr_flags: "{{ item.flags }}"
    conn_limit: "{{ item.limit | default(omit) }}"
    state: present
  become: true
  become_user: postgres
  loop:
    - { nom: svc_tramontana, password: "{{ vault_bd_app }}",
        flags: 'LOGIN,NOSUPERUSER,NOCREATEDB,NOCREATEROLE', limit: 30 }
    - { nom: replicador, password: "{{ vault_bd_replicador }}",
        flags: 'LOGIN,REPLICATION' }
    - { nom: monitor, password: "{{ vault_bd_monitor }}", flags: 'LOGIN' }
  no_log: true                     # les contrasenyes no surten per pantalla
  tags: [rols]

- name: Concedir pg_monitor al rol de monitoracio
  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 l arxivat 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: arxivador
  failed_when: arxivador.query_result[0].failed_count | int > 0
  tags: [verificar]

# roles/bd/handlers/main.yml
---
- name: Recarregar 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 reinici talla les connexions. Nomes es dispara quan canvia un
  # parametre que ho requereix, i en produccio s executa en finestra.
  ansible.builtin.systemd:
    name: "postgresql@{{ bd_version }}-main"
    state: restarted

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

#!/usr/bin/env bash
# Fragment per integrar a revisio_salut.sh: bloc de base de dades
comprovar_base_dades() {
    local estat=0

    # 1. Connexions esperant a l agrupacio
    local esperant
    esperant="$(psql -h 127.0.0.1 -p 6432 -U operador -d pgbouncer -tAc \
        "SHOW POOLS" | awk -F'|' '$1=="tramontana"{print $4}')"
    if (( esperant > 0 )); then
        error "hi ha $esperant clients esperant connexio"; estat=1
    fi

    # 2. Retard de la replica (bytes que es perdrien ara mateix)
    local retard
    retard="$(sudo -u postgres psql -tAc \
        "SELECT coalesce(max(pg_wal_lsn_diff(sent_lsn,replay_lsn)),0)::bigint
         FROM pg_stat_replication")"
    if (( retard > 104857600 )); then          # 100 MB
        error "la replica va $(formatar_bytes "$retard") per darrere"; estat=1
    fi

    # 3. Fallades d arxivat de WAL: si falla, pg_wal creix sense limit
    local fallades
    fallades="$(sudo -u postgres psql -tAc "SELECT failed_count FROM pg_stat_archiver")"
    if (( fallades > 0 )); then
        error "l arxivat de WAL ha fallat $fallades vegades"; estat=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"; estat=2
    elif (( pct > 50 )); then
        error "wraparound al ${pct}%"; estat=1
    fi

    # 5. Transaccions obertes eternament
    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 transaccions ocioses de mes de 10 min"; estat=1
    fi

    (( estat == 0 )) && log "base de dades correcta"
    return "$estat"
}

$ 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 arxivar_wal --since "6 hours ago" | tail -2
arxivar_wal[8812]: cp: cannot create regular file
  '/srv/tramontana/backups/wal/.000000010000000000000A31.8812': No space left on device

lv-backups ple -> arxivar_wal.sh falla -> els segments no s esborren
  -> pg_wal creix -> s omple /var/lib -> PostgreSQL S ATURA

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

# --- PAS 1: espai immediat al desti d arxivat ---
# Pujar a restic el que ja esta arxivat i alliberar el mes antic
$ sudo restic backup /srv/tramontana/backups/wal --tag wal-urgent
$ sudo restic check --read-data-subset=5%      # verificar ABANS d esborrar

# Nomes aleshores, esborrar els WAL anteriors a la copia base mes antiga
# que volem conservar. pg_archivecleanup calcula quins sobren: MAI no
# s esborren a ma per data.
$ 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--dades-lv--backups  15G  4.2G  9.9G  30% /srv/tramontana/backups

# --- PAS 2: drenar la cua 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. Retencio automatica de WAL, setmanal, despres de verificar restic
$ cat /home/operador/scripts/purgar_wal.sh
#!/usr/bin/env bash
set -euo pipefail
source "$(dirname "${BASH_SOURCE[0]}")/lib/comuns.sh"

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

main() {
    requereix_comanda pg_archivecleanup
    requereix_comanda restic

    # No purgar mai per sota de la copia base mes antiga que es conserva
    local base_mes_antiga
    base_mes_antiga="$(find "$BASE_DIR" -maxdepth 1 -type d -name '20*' | sort | head -1)" \
        || morir 75 "no hi ha cap copia base: NO es purga res"
    [[ -n "$base_mes_antiga" ]] || morir 75 "no hi ha copia base; avortant"

    # I nomes si restic te el contingut segur
    restic check --read-data-subset=2% >/dev/null \
        || morir 65 "restic no verifica: no es purga res"

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

# 2. Alerta ABANS del problema, no quan ja no hi ha sortida
# /etc/systemd/system/vigilar-wal.service (executat cada 15 min)
[Service]
Type=oneshot
ExecStart=/home/operador/scripts/vigilar_wal.sh

# vigilar_wal.sh: dos llindars, dos nivells
#   - .ready > 20         -> avis: l arxivat s esta endarrerint
#   - us de lv-backups > 75 % -> avis; > 90 % -> critic
#   - failed_count > 0    -> critic immediat

# 3. Xarxa de seguretat al propi PostgreSQL
# Limita quant WAL pot acumular una ranura abans d invalidar-la.
max_slot_wal_keep_size = 8GB

# 4. Ampliar lv-backups amb marge (05-04), ara amb dades
$ sudo lvextend -L +10G /dev/vg-dades/lv-backups
$ sudo cryptsetup resize backups-xifrat
$ sudo resize2fs /dev/mapper/backups-xifrat