Guía completa de PostgreSQL en Linux: mantenimiento, optimización, alta disponibilidad y respaldo

Idea clave: administrar PostgreSQL en Linux no consiste solo en levantar el servicio. La calidad de la base de datos depende de mantenimiento, parámetros adecuados, consultas optimizadas, backups verificables, monitoreo permanente y un plan real de recuperación.

PostgreSQL en Linux es ideal para

• Aplicaciones web y empresariales.
• Sistemas transaccionales críticos.
• Bases de datos geoespaciales con PostGIS.
• Plataformas de analítica y reporting.
• APIs, microservicios y backend moderno.
• Infraestructura open source y DevOps.
• Entornos donde se requiere control, estabilidad y bajo costo de licenciamiento.

Leer más: Cómo instalar PostgreSQL 18 en Linux paso a paso

Tareas esenciales de mantenimiento

• VACUUM: limpia versiones antiguas de filas.
• ANALYZE: actualiza estadísticas para el optimizador.
• REINDEX: reconstruye índices cuando existe bloat o corrupción.
• VACUUM FULL: recupera espacio físico, pero bloquea la tabla.
• Autovacuum: automatiza mantenimiento preventivo.

-- Ejecutar mantenimiento manual básico
VACUUM ANALYZE;

-- Ejecutar en una tabla específica
VACUUM ANALYZE public.mi_tabla;

-- Revisar actividad de autovacuum
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname;

Recomendación: no desactives autovacuum en producción. Si genera carga, ajústalo; si se desactiva, la base puede crecer, degradarse y sufrir problemas graves de rendimiento.

-- Índices con poco o ningún uso
SELECT schemaname, relname AS tabla, indexrelname AS indice, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

-- Tamaño de tablas e índices
SELECT relname AS objeto,
       pg_size_pretty(pg_total_relation_size(relid)) AS tamaño_total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Advertencia: no elimines índices solo porque tienen pocos usos. Primero valida consultas, llaves foráneas, reportes, ventanas de cierre, procesos batch y comportamiento de la aplicación.

EXPLAIN
SELECT * FROM ventas WHERE cliente_id = 100;

EXPLAIN ANALYZE
SELECT * FROM ventas WHERE cliente_id = 100;

EXPLAIN (ANALYZE, BUFFERS)
SELECT cliente_id, SUM(total)
FROM ventas
WHERE fecha >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY cliente_id;

Qué revisar en un plan de ejecución

• Seq Scan en tablas muy grandes.
• Índices que no se usan.
• Diferencia fuerte entre filas estimadas y reales.
• Sorts costosos en memoria o disco.
• Nested loops sobre grandes volúmenes.
• Consultas que leen muchas filas para devolver pocas.
• Uso excesivo de buffers.
• Falta de filtros selectivos.

Recomendación: no copies configuraciones de Internet sin medir. Cada servidor tiene diferente RAM, CPU, discos, concurrencia, consultas, índices, versión de PostgreSQL y patrón de uso.

-- Conexiones activas
SELECT datname, usename, state, count(*)
FROM pg_stat_activity
GROUP BY datname, usename, state
ORDER BY count(*) DESC;

-- Consultas activas más antiguas
SELECT pid, usename, state, now() - query_start AS duracion, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start ASC
LIMIT 10;

-- Bloqueos
SELECT locktype, database, relation, mode, granted
FROM pg_locks
ORDER BY granted;

Indicadores que debes monitorear

• Uso de CPU, RAM, I/O y disco.
• Cantidad de conexiones activas e inactivas.
• Consultas lentas y consultas bloqueadas.
• Tamaño de base de datos, tablas e índices.
• Crecimiento de WAL.
• Estado de autovacuum.
• Lag de replicación.
• Éxito o falla de backups.
• Errores en logs de PostgreSQL.

Leer más: Cómo optimizar el rendimiento de un servidor Linux paso a paso

# Backup lógico en formato personalizado
pg_dump -U postgres -h localhost -Fc -d mi_base -f /backups/mi_base_$(date +%F).dump

# Backup lógico en SQL plano
pg_dump -U postgres -d mi_base > /backups/mi_base_$(date +%F).sql

# Backup de roles y objetos globales
pg_dumpall -U postgres --globals-only > /backups/globals_$(date +%F).sql

# Restaurar backup personalizado
createdb -U postgres mi_base_restaurada
pg_restore -U postgres -d mi_base_restaurada /backups/mi_base_2026-06-17.dump

# Restaurar SQL plano
psql -U postgres -d mi_base_restaurada -f /backups/mi_base_2026-06-17.sql

Importante: un backup que nunca se restaura es solo una suposición. Programa pruebas de restauración periódicas para confirmar que el respaldo sirve.

# Crear backup físico comprimido
pg_basebackup -h 192.168.1.10 -U replicador \
  -D /backups/base_$(date +%F) \
  -Fp -Xs -P -R

# Parámetros:
# -D  directorio destino
# -Fp formato plain
# -Xs incluye WAL por streaming
# -P muestra progreso
# -R crea configuración para standby

Cuándo usar backup físico

• Bases de datos grandes.
• Recuperación completa del clúster.
• Implementación de réplicas standby.
• Recuperación punto en el tiempo.
• Escenarios donde pg_dump sería demasiado lento.
• Planes de continuidad y desastre.

PITR requiere

• Backup base válido.
• Archivado continuo de WAL.
• Espacio suficiente para conservar WAL.
• Procedimiento documentado de restauración.
• Pruebas periódicas de recuperación.
• Definición clara de RPO y RTO.

# Ejemplo conceptual de configuración WAL en postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

# En recuperación se puede definir un punto objetivo
recovery_target_time = '2026-06-17 10:30:00'

Error grave: tener backup base sin WAL archivados no es PITR completo. Tener WAL sin backup base tampoco resuelve una recuperación completa.

Leer más: Cómo implementar copias de seguridad automáticas en Linux

Elementos de alta disponibilidad

• Nodo primario.
• Uno o más nodos standby.
• Replicación streaming.
• Monitoreo de salud.
• Failover manual o automático.
• IP virtual, balanceador o proxy de conexión.
• Procedimiento de reintegración del antiguo primario.
• Backups independientes de la replicación.

Importante: replicación no reemplaza backups. Si alguien elimina datos por error, esa eliminación también puede replicarse al standby.

-- En el primario: crear usuario replicador
CREATE ROLE replicador WITH REPLICATION LOGIN PASSWORD 'Cambiar_Esta_Clave_Segura';

# Parámetros básicos en postgresql.conf del primario
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on

# En pg_hba.conf del primario
host replication replicador 192.168.1.20/32 scram-sha-256

# En el standby: crear copia base
systemctl stop postgresql

rm -rf /var/lib/postgresql/18/main/*

pg_basebackup -h 192.168.1.10 -U replicador \
  -D /var/lib/postgresql/18/main \
  -Fp -Xs -P -R

systemctl start postgresql

Nota de seguridad: no uses contraseñas débiles ni abras replicación a cualquier IP. Limita origen, usa firewall, autenticación segura y, cuando corresponda, cifrado TLS.

-- En el primario
SELECT client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

-- En el standby
SELECT pg_is_in_recovery();

-- Ver retraso aproximado en standby
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Qué monitorear en replicación

• Estado de conexión del standby.
• Retraso de replicación.
• Crecimiento de WAL.
• Espacio disponible en primario y standby.
• Errores de autenticación.
• Conflictos de consultas en standby.
• Tiempo de promoción durante failover.

# Promover standby manualmente
pg_ctl promote -D /var/lib/postgresql/18/main

# También puede usarse según distribución:
sudo -u postgres pg_ctlcluster 18 main promote

Herramientas comunes para alta disponibilidad

• Patroni: automatización de HA con consenso distribuido.
• repmgr: gestión de replicación y failover.
• HAProxy: balanceo y enrutamiento de conexiones.
• PgBouncer: pool de conexiones.
• Keepalived: IP virtual para alta disponibilidad.
• Pacemaker/Corosync: clúster de alta disponibilidad.

Recomendación: si no tienes experiencia con failover automático, empieza con failover manual documentado y probado. Luego evoluciona hacia herramientas como Patroni o repmgr.

Controles mínimos de seguridad

• Limitar listen_addresses a interfaces necesarias.
• Configurar pg_hba.conf con IPs específicas.
• Usar autenticación segura como SCRAM-SHA-256.
• Aplicar mínimo privilegio a roles.
• No usar el superusuario para aplicaciones.
• Configurar firewall en Linux.
• Activar logs de conexión y errores.
• Cifrar backups sensibles.
• Actualizar PostgreSQL y el sistema operativo.
• Separar ambientes de desarrollo, pruebas y producción.

Leer más: Cómo configurar un firewall en Linux para proteger tu infraestructura

#!/bin/bash
# backup_postgres.sh

FECHA=$(date +%F_%H-%M)
DESTINO="/backups/postgresql"
BASE="mi_base"

mkdir -p "$DESTINO"

pg_dump -U postgres -Fc "$BASE" -f "$DESTINO/${BASE}_${FECHA}.dump"

find "$DESTINO" -type f -name "*.dump" -mtime +14 -delete

# Programar con cron
crontab -e

# Backup diario a las 02:00
0 2 * * * /usr/local/bin/backup_postgres.sh >> /var/log/backup_postgres.log 2>&1

Error común: automatizar backups sin validar si terminaron correctamente. Todo script debe registrar salida, errores, tamaño del archivo y resultado de prueba.

Errores que debes evitar

• No probar restauraciones.
• Confundir replicación con backup.
• Desactivar autovacuum.
• Crear demasiados índices sin análisis.
• No revisar consultas lentas.
• Exponer PostgreSQL directamente a Internet.
• Usar superusuario para aplicaciones.
• No monitorear espacio de disco y WAL.
• No documentar failover.
• No revisar logs después de actualizaciones.
• No separar backups del mismo servidor.
• No definir RPO y RTO.

Arquitectura recomendada

• Servidor primario PostgreSQL.
• Servidor standby por replicación streaming.
• Backups físicos con pg_basebackup.
• Backups lógicos para restauración selectiva.
• Archivado WAL para PITR.
• Servidor o almacenamiento externo para backups.
• Monitoreo con alertas.
• Firewall y acceso restringido.
• Documentación de restauración y failover.
• Pruebas periódicas de recuperación.

• Cómo instalar PostgreSQL 18 en Linux paso a paso
• Las mejores bases de datos para Linux: ventajas, diferencias y casos de uso
• Cómo construir una infraestructura tecnológica 100% con software libre
• Cómo optimizar el rendimiento de un servidor Linux paso a paso
• Cómo configurar un firewall en Linux para proteger tu infraestructura

Tip final: el mejor administrador de PostgreSQL no es quien ejecuta más comandos, sino quien documenta, mide, prueba restauraciones, revisa logs, controla cambios y mantiene una estrategia de continuidad real.

Resumen final

Para administrar PostgreSQL en Linux correctamente, implementa mantenimiento con autovacuum, VACUUM y ANALYZE; optimiza consultas con EXPLAIN ANALYZE; revisa índices, memoria, WAL y checkpoints; monitorea conexiones, bloqueos, espacio, logs y replicación; usa pg_dump para respaldos lógicos, pg_basebackup para respaldos físicos, archivado WAL para PITR y replicación streaming para alta disponibilidad. La clave es probar restauraciones y documentar cada procedimiento antes de una emergencia.