Instalación y Configuración de PostgreSQL: Guía Completa de Configuración de Base de Datos Empresarial

Introducción

PostgreSQL, a menudo llamado "Postgres", se erige como uno de los sistemas de gestión de bases de datos relacionales de código abierto más avanzados disponibles en la actualidad. Con más de 35 años de desarrollo activo, PostgreSQL se ha ganado su reputación como una base de datos potente, confiable y rica en características que rivaliza con soluciones comerciales en capacidades mientras mantiene sus raíces de código abierto.

A diferencia de muchos sistemas de bases de datos que priorizan la velocidad sobre las características, PostgreSQL adopta un enfoque integral, ofreciendo características avanzadas como consultas complejas, claves foráneas, triggers, vistas actualizables, integridad transaccional y control de concurrencia multiversión (MVCC). Esto lo convierte en una excelente opción para aplicaciones que requieren integridad de datos, consultas complejas y características de nivel empresarial.

Esta guía completa proporciona instrucciones paso a paso para instalar y configurar PostgreSQL en varias distribuciones de Linux, cubriendo todo desde la instalación básica hasta la configuración avanzada, el refuerzo de seguridad y la optimización del rendimiento.

¿Por Qué Elegir PostgreSQL?

PostgreSQL es confiable por organizaciones de todo el mundo, desde startups hasta empresas Fortune 500. Los principales usuarios incluyen Apple, Fujitsu, Red Hat, Cisco e Instagram. La base de datos sobresale en varias áreas clave:

Características Avanzadas:

• Cumplimiento total de ACID
• Consultas SQL complejas y subconsultas
• Soporte JSON y JSONB para almacenamiento de documentos
• Capacidades de búsqueda de texto completo
• Soporte de datos geoespaciales con PostGIS
• Tipos de datos y funciones personalizados
• Métodos avanzados de indexación (B-tree, Hash, GiST, SP-GiST, GIN, BRIN)

Capacidades Empresariales:

• Control de concurrencia multiversión (MVCC)
• Recuperación point-in-time
• Tablespaces para gestión flexible del almacenamiento
• Replicación asíncrona
• Servidores hot standby
• Replicación lógica

Requisitos Previos

Antes de comenzar el proceso de instalación, asegúrese de que su sistema cumple con estos requisitos:

Requisitos del Sistema

Requisitos Mínimos:

• Servidor Linux (Ubuntu 20.04+, Debian 11+, CentOS 8+, Rocky Linux 8+)
• Privilegios root o sudo
• 1GB RAM mínimo
• 10GB de espacio en disco disponible
• Conectividad a Internet para descargas de paquetes

Recomendado para Producción:

• 4+ núcleos de CPU
• 16GB+ RAM
• Almacenamiento SSD con 100GB+ de espacio
• Servidor dedicado para base de datos
• Conectividad de red confiable

Conocimientos Requeridos

• Competencia básica en línea de comandos de Linux
• Comprensión de fundamentos de SQL
• Familiaridad con editores de texto (nano, vim)
• Conceptos básicos de redes

Instalación

Instalando PostgreSQL en Ubuntu/Debian

PostgreSQL proporciona repositorios APT oficiales para distribuciones basadas en Debian:

# Actualizar índice de paquetes
sudo apt update

# Instalar PostgreSQL (última versión del repositorio predeterminado)
sudo apt install postgresql postgresql-contrib -y

# Verificar instalación
sudo systemctl status postgresql

# Habilitar PostgreSQL para iniciar en el arranque
sudo systemctl enable postgresql

Para la última versión de PostgreSQL del repositorio oficial:

# Crear la configuración del repositorio de archivos
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Importar la clave de firma del repositorio
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Actualizar listas de paquetes
sudo apt update

# Instalar PostgreSQL 16 (o especificar la versión deseada)
sudo apt install postgresql-16 postgresql-contrib-16 -y

# Verificar instalación
psql --version

Verificar el estado del servicio:

# Verificar estado de PostgreSQL
sudo systemctl status postgresql

# Ver todos los procesos de PostgreSQL
ps aux | grep postgres

Instalando PostgreSQL en CentOS/Rocky Linux

Para distribuciones basadas en Red Hat:

# Actualizar paquetes del sistema
sudo dnf update -y

# Instalar PostgreSQL del repositorio predeterminado
sudo dnf install postgresql-server postgresql-contrib -y

# Inicializar cluster de base de datos
sudo postgresql-setup --initdb

# Iniciar servicio de PostgreSQL
sudo systemctl start postgresql

# Habilitar PostgreSQL para iniciar en el arranque
sudo systemctl enable postgresql

# Verificar instalación
sudo systemctl status postgresql

Para la última versión de PostgreSQL:

# Instalar RPM del repositorio de PostgreSQL
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Deshabilitar módulo PostgreSQL integrado (si existe)
sudo dnf -qy module disable postgresql

# Instalar PostgreSQL 16
sudo dnf install -y postgresql16-server postgresql16-contrib

# Inicializar la base de datos
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# Iniciar y habilitar servicio
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

# Verificar instalación
/usr/pgsql-16/bin/psql --version

Verificando la Instalación

Confirmar que PostgreSQL se está ejecutando correctamente:

# Verificar versión
psql --version

# Verificar estado del servicio
sudo systemctl status postgresql

# Ver directorio de datos de PostgreSQL
ls -la /var/lib/pgsql/data/
# o para Debian/Ubuntu
ls -la /var/lib/postgresql/16/main/

# Verificar puertos en escucha
sudo ss -tulpn | grep postgres
sudo netstat -tulpn | grep postgres

# Ver logs de PostgreSQL
sudo tail -f /var/log/postgresql/postgresql-16-main.log
# o
sudo journalctl -u postgresql -f

Configuración Inicial

Comprendiendo la Estructura de Directorios de PostgreSQL

Ubuntu/Debian:

• Configuración: /etc/postgresql/16/main/
• Directorio de datos: /var/lib/postgresql/16/main/
• Binarios: /usr/lib/postgresql/16/bin/
• Logs: /var/log/postgresql/

CentOS/Rocky Linux:

• Configuración: /var/lib/pgsql/16/data/
• Directorio de datos: /var/lib/pgsql/16/data/
• Binarios: /usr/pgsql-16/bin/
• Logs: /var/lib/pgsql/16/data/log/

Accediendo a PostgreSQL

Cambiar al usuario postgres y acceder a la base de datos:

# Cambiar a usuario postgres
sudo -i -u postgres

# Acceder al prompt de PostgreSQL
psql

# Ver bases de datos actuales
\l

# Ver usuarios actuales
\du

# Salir de psql
\q

# Salir de la sesión del usuario postgres
exit

Acceso alternativo en una sola línea:

# Ejecutar comando como usuario postgres
sudo -u postgres psql

# Ejecutar comando SQL específico
sudo -u postgres psql -c "SELECT version();"

Configurando la Autenticación

PostgreSQL utiliza dos archivos de configuración principales para la autenticación:

1. postgresql.conf - Archivo de configuración principal 2. pg_hba.conf - Configuración de autenticación del cliente

Editar pg_hba.conf:

# Ubuntu/Debian
sudo nano /etc/postgresql/16/main/pg_hba.conf

# CentOS/Rocky
sudo nano /var/lib/pgsql/16/data/pg_hba.conf

Comprendiendo el formato pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Conexiones locales
local   all             postgres                                peer
local   all             all                                     md5

# Conexiones IPv4
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5

# Conexiones IPv6
host    all             all             ::1/128                 md5

Métodos de autenticación comunes:

• peer: Usa nombre de usuario del sistema operativo (seguro para conexiones locales)
• md5: Autenticación basada en contraseña con hashing MD5
• scram-sha-256: Autenticación moderna de contraseña (recomendado)
• trust: Sin autenticación (nunca usar en producción)
• reject: Rechazar conexión

Configuración de producción recomendada:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Conexiones locales
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# Conexiones locales IPv4
host    all             all             127.0.0.1/32            scram-sha-256

# Conexiones locales IPv6
host    all             all             ::1/128                 scram-sha-256

# Conexiones remotas desde subred específica
host    all             all             192.168.1.0/24          scram-sha-256

# Rechazar todas las demás conexiones
host    all             all             0.0.0.0/0               reject

Configurando la Contraseña del Usuario postgres

Establecer una contraseña para el superusuario postgres:

# Acceder a PostgreSQL como usuario postgres
sudo -u postgres psql

# Establecer contraseña
ALTER USER postgres WITH PASSWORD 'your_strong_password';

# Salir
\q

Creando Usuarios de Base de Datos

Crear usuarios específicos para aplicaciones:

-- Crear nuevo usuario con contraseña
CREATE USER myapp_user WITH PASSWORD 'secure_password';

-- Crear usuario con privilegios específicos
CREATE USER readonly_user WITH PASSWORD 'password';

-- Crear base de datos
CREATE DATABASE myapp_db;

-- Otorgar privilegios al usuario
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Conectar a la base de datos
\c myapp_db

-- Otorgar privilegios de esquema
GRANT ALL ON SCHEMA public TO myapp_user;

-- Otorgar privilegios de tabla (después de crear tablas)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;

-- Hacer privilegios predeterminados para objetos futuros
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO myapp_user;

Crear usuario de solo lectura:

-- Crear usuario de solo lectura
CREATE USER readonly_user WITH PASSWORD 'readonly_password';

-- Otorgar conexión
GRANT CONNECT ON DATABASE myapp_db TO readonly_user;

-- Cambiar a base de datos
\c myapp_db

-- Otorgar uso en esquema
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Otorgar SELECT en todas las tablas existentes
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Otorgar SELECT en tablas futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Habilitando Conexiones Remotas

Editar postgresql.conf:

# Ubuntu/Debian
sudo nano /etc/postgresql/16/main/postgresql.conf

# CentOS/Rocky
sudo nano /var/lib/pgsql/16/data/postgresql.conf

Modificar el parámetro listen_addresses:

# Escuchar en todas las interfaces
listen_addresses = '*'

# O dirección IP específica
listen_addresses = '192.168.1.100'

# O múltiples direcciones
listen_addresses = 'localhost,192.168.1.100'

# Puerto predeterminado (cambiar si es necesario)
port = 5432

Actualizar pg_hba.conf para permitir conexiones remotas:

# Permitir conexiones desde IP específica
host    all             all             192.168.1.50/32         scram-sha-256

# Permitir desde subred
host    all             all             192.168.1.0/24          scram-sha-256

Aplicar cambios:

# Reiniciar PostgreSQL
sudo systemctl restart postgresql

# O recargar configuración sin reiniciar
sudo systemctl reload postgresql

# Verificar direcciones de escucha
sudo ss -tulpn | grep 5432

Refuerzo de Seguridad

Configuración del Firewall

Configurar firewall para restringir el acceso a PostgreSQL:

# UFW (Ubuntu/Debian)
# Permitir desde IP específica
sudo ufw allow from 192.168.1.50 to any port 5432

# Permitir desde subred
sudo ufw allow from 192.168.1.0/24 to any port 5432

# Verificar reglas
sudo ufw status numbered

# firewalld (CentOS/Rocky)
# Permitir desde IP específica
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.50" port protocol="tcp" port="5432" accept'

# Permitir desde subred
sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="5432" accept'

# Recargar firewall
sudo firewall-cmd --reload

# Verificar reglas
sudo firewall-cmd --list-all

Configuración SSL/TLS

Generar certificados SSL:

# Cambiar a usuario postgres
sudo -i -u postgres

# Navegar al directorio de datos
cd /var/lib/postgresql/16/main  # Ubuntu/Debian
# o
cd /var/lib/pgsql/16/data       # CentOS/Rocky

# Generar certificado autofirmado
openssl req -new -x509 -days 365 -nodes -text \
    -out server.crt \
    -keyout server.key \
    -subj "/CN=postgres.example.com"

# Establecer permisos apropiados
chmod 600 server.key
chmod 644 server.crt

# Salir del usuario postgres
exit

Habilitar SSL en postgresql.conf:

# Configuración SSL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

Hacer cumplir SSL en pg_hba.conf:

# Requerir SSL para conexiones remotas
hostssl    all             all             192.168.1.0/24          scram-sha-256

Reiniciar PostgreSQL:

sudo systemctl restart postgresql

# Probar conexión SSL
psql "sslmode=require host=localhost dbname=postgres user=postgres"

Aplicación de Política de Contraseñas

Instalar extensión passwordcheck:

# Ubuntu/Debian
sudo apt install postgresql-16-passwordcheck

# CentOS/Rocky
sudo dnf install postgresql16-contrib

Configurar en postgresql.conf:

# Cargar extensión passwordcheck
shared_preload_libraries = 'passwordcheck'

# Requisitos de contraseña (personalizar según necesidad)
passwordcheck.minimum_length = 12

Reiniciar y probar:

sudo systemctl restart postgresql

# Probar con contraseña débil (debería fallar)
sudo -u postgres psql -c "CREATE USER testuser WITH PASSWORD '123';"

Control de Acceso Basado en Roles

Implementar jerarquía de roles adecuada:

-- Crear roles para diferentes niveles de acceso
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;

-- Otorgar privilegios a roles
GRANT CONNECT ON DATABASE myapp_db TO readonly;
GRANT CONNECT ON DATABASE myapp_db TO readwrite;
GRANT CONNECT ON DATABASE myapp_db TO admin;

-- Cambiar a base de datos
\c myapp_db

-- Privilegios de rol readonly
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Privilegios de rol readwrite
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readwrite;

-- Privilegios de rol admin
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;

-- Crear usuarios y asignar roles
CREATE USER app_user WITH PASSWORD 'password';
GRANT readwrite TO app_user;

CREATE USER report_user WITH PASSWORD 'password';
GRANT readonly TO report_user;

CREATE USER admin_user WITH PASSWORD 'password';
GRANT admin TO admin_user;

Auditoría y Registro

Configurar registro completo en postgresql.conf:

# Configuración de Registro
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# Qué registrar
log_connections = on
log_disconnections = on
log_duration = off
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 0

# Registrar consultas lentas
log_min_duration_statement = 1000  # Registrar consultas que toman > 1 segundo

# Registro de errores
log_min_error_statement = error
log_min_messages = warning

Instalar y configurar pgAudit para auditoría detallada:

# Ubuntu/Debian
sudo apt install postgresql-16-pgaudit

# CentOS/Rocky
sudo dnf install pgaudit_16

Configurar pgAudit:

# Cargar pgaudit
shared_preload_libraries = 'pgaudit'

# Configuraciones de auditoría
pgaudit.log = 'all'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = on

Optimización del Rendimiento

Configuración de Memoria

Editar postgresql.conf con configuraciones de memoria optimizadas:

# Configuración de Memoria
shared_buffers = 4GB                    # 25% de RAM para servidor dedicado
effective_cache_size = 12GB             # 75% de RAM
maintenance_work_mem = 1GB              # Para operaciones de mantenimiento
work_mem = 64MB                         # Memoria por operación de consulta
huge_pages = try                        # Usar páginas grandes si están disponibles

# Configuración WAL
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9

Pautas de cálculo de memoria:

Para servidores de base de datos dedicados:

• shared_buffers: 25% de RAM total (máx 8-16GB)
• effective_cache_size: 75% de RAM total
• maintenance_work_mem: 5% de RAM (máx 2GB)
• work_mem: RAM / (max_connections * 2-3)

Ejemplo para servidor de 32GB RAM:

shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
work_mem = 64MB
max_connections = 100

Planificación y Ejecución de Consultas

Optimizar configuración del planificador de consultas:

# Planificación de Consultas
random_page_cost = 1.1                  # Para almacenamiento SSD
effective_io_concurrency = 200          # Para almacenamiento SSD
default_statistics_target = 100         # Nivel de detalle de estadísticas

Para HDD tradicional:

random_page_cost = 4.0
effective_io_concurrency = 2

Configuración de Conexión y Concurrencia

# Configuración de Conexión
max_connections = 100                   # Ajustar según carga de trabajo
superuser_reserved_connections = 3

# Configuración de Workers en Segundo Plano
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

Optimización de Autovacuum

Autovacuum es crítico para el rendimiento de PostgreSQL:

# Configuración de Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

Operaciones manuales de vacuum:

-- Analizar todas las bases de datos
VACUUM ANALYZE;

-- Vacuum tabla específica
VACUUM ANALYZE table_name;

-- Vacuum completo (reclama espacio, requiere bloqueo exclusivo)
VACUUM FULL table_name;

-- Verificar hinchazón de tabla
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Optimización de Índices

-- Encontrar índices faltantes
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Encontrar índices no utilizados
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

-- Reconstruir índice hinchado
REINDEX INDEX index_name;

-- Reconstruir todos los índices en tabla
REINDEX TABLE table_name;

Monitoreo y Estadísticas

Habilitar seguimiento de actividades:

# Configuración de Estadísticas
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
stats_temp_directory = '/var/run/postgresql/stats_temp'

Consultas útiles de monitoreo:

-- Actividad actual
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Consultas de larga duración
SELECT
    pid,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Tamaños de bases de datos
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Tamaños de tabla con índices
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Relación de aciertos de caché (debería ser > 99%)
SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 AS cache_hit_ratio
FROM pg_statio_user_tables;

Estrategias de Respaldo

Respaldos Lógicos con pg_dump

Comandos básicos de respaldo:

# Respaldar una sola base de datos
pg_dump -U postgres -d myapp_db -f backup_$(date +%Y%m%d).sql

# Respaldo con compresión
pg_dump -U postgres -d myapp_db | gzip > backup_$(date +%Y%m%d).sql.gz

# Respaldo en formato personalizado (comprimido, permite restauración paralela)
pg_dump -U postgres -d myapp_db -Fc -f backup_$(date +%Y%m%d).dump

# Formato de directorio (permite respaldo y restauración paralelos)
pg_dump -U postgres -d myapp_db -Fd -j 4 -f backup_$(date +%Y%m%d)

# Respaldar todas las bases de datos
pg_dumpall -U postgres -f all_databases_$(date +%Y%m%d).sql

# Respaldar solo esquema
pg_dump -U postgres -d myapp_db -s -f schema_$(date +%Y%m%d).sql

# Respaldar solo datos
pg_dump -U postgres -d myapp_db -a -f data_$(date +%Y%m%d).sql

# Respaldar tablas específicas
pg_dump -U postgres -d myapp_db -t table1 -t table2 -f tables_$(date +%Y%m%d).sql

Respaldos Físicos con pg_basebackup

# Crear respaldo base
pg_basebackup -U postgres -D /backup/pgdata_$(date +%Y%m%d) -Ft -z -P

# Respaldo con archivos WAL
pg_basebackup -U postgres -D /backup/pgdata_$(date +%Y%m%d) -Ft -z -Xs -P

# Opciones explicadas:
# -D: Directorio de salida
# -Ft: Formato tar
# -z: Comprimir con gzip
# -Xs: Incluir archivos WAL en respaldo
# -P: Mostrar progreso

Script de Respaldo Automatizado

Crear script de respaldo completo:

sudo nano /usr/local/bin/pg-backup.sh

Agregar contenido:

#!/bin/bash

# Configuración
BACKUP_DIR="/backups/postgresql"
PG_USER="postgres"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup.log"

# Crear directorio de respaldo
mkdir -p $BACKUP_DIR

# Función para registrar mensajes
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# Iniciar respaldo
log_message "Starting PostgreSQL backup"

# Respaldar todas las bases de datos
pg_dumpall -U $PG_USER | gzip > $BACKUP_DIR/all_databases_$DATE.sql.gz

if [ $? -eq 0 ]; then
    log_message "Backup completed successfully: all_databases_$DATE.sql.gz"
else
    log_message "ERROR: Backup failed"
    exit 1
fi

# Respaldar bases de datos individuales
psql -U $PG_USER -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';" | while read dbname; do
    if [ ! -z "$dbname" ]; then
        pg_dump -U $PG_USER -Fc $dbname > $BACKUP_DIR/${dbname}_$DATE.dump
        log_message "Database backup completed: ${dbname}_$DATE.dump"
    fi
done

# Eliminar respaldos antiguos
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete
log_message "Old backups removed (older than $RETENTION_DAYS days)"

# Calcular tamaño del respaldo
TOTAL_SIZE=$(du -sh $BACKUP_DIR | cut -f1)
log_message "Total backup size: $TOTAL_SIZE"

log_message "Backup process completed"

Hacer ejecutable y programar:

# Hacer ejecutable
sudo chmod +x /usr/local/bin/pg-backup.sh

# Probar el script
sudo /usr/local/bin/pg-backup.sh

# Programar con cron (diariamente a las 2 AM)
sudo crontab -e

# Agregar línea:
0 2 * * * /usr/local/bin/pg-backup.sh

Recuperación en un Punto en el Tiempo (PITR)

Configurar archivado WAL:

# Archivado WAL
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal_archive/%f && cp %p /backup/wal_archive/%f'
archive_timeout = 300

Crear directorio de archivo WAL:

sudo mkdir -p /backup/wal_archive
sudo chown postgres:postgres /backup/wal_archive
sudo chmod 700 /backup/wal_archive

Reiniciar PostgreSQL:

sudo systemctl restart postgresql

Operaciones de Restauración

Restaurar desde pg_dump:

# Restaurar formato SQL
psql -U postgres -d myapp_db -f backup_20260111.sql

# Restaurar respaldo comprimido
gunzip < backup_20260111.sql.gz | psql -U postgres -d myapp_db

# Restaurar formato personalizado (con trabajos paralelos)
pg_restore -U postgres -d myapp_db -j 4 backup_20260111.dump

# Restaurar todas las bases de datos
psql -U postgres -f all_databases_20260111.sql

# Crear base de datos antes de restaurar
createdb -U postgres myapp_db
pg_restore -U postgres -d myapp_db backup_20260111.dump

Restaurar desde respaldo base:

# Detener PostgreSQL
sudo systemctl stop postgresql

# Limpiar directorio de datos
sudo rm -rf /var/lib/postgresql/16/main/*

# Extraer respaldo
sudo tar -xzf /backup/base.tar.gz -C /var/lib/postgresql/16/main/

# Crear archivo recovery.signal
sudo touch /var/lib/postgresql/16/main/recovery.signal

# Configurar recuperación (si es necesario)
sudo nano /var/lib/postgresql/16/main/postgresql.auto.conf

# Agregar:
restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-01-11 14:30:00'

# Corregir permisos
sudo chown -R postgres:postgres /var/lib/postgresql/16/main

# Iniciar PostgreSQL
sudo systemctl start postgresql

Resolución de Problemas

Problemas de Conexión

No se puede conectar a PostgreSQL:

# Verificar si PostgreSQL está ejecutándose
sudo systemctl status postgresql

# Verificar puertos de escucha
sudo ss -tulpn | grep postgres

# Verificar logs
sudo tail -f /var/log/postgresql/postgresql-16-main.log
sudo journalctl -u postgresql -f

# Probar conexión local
psql -U postgres

# Probar conexión remota
psql -h 192.168.1.100 -U postgres -d postgres

# Verificar pg_hba.conf
sudo cat /etc/postgresql/16/main/pg_hba.conf | grep -v "^#" | grep -v "^$"

Fallos de autenticación:

# Verificar que el usuario existe
sudo -u postgres psql -c "\du"

# Restablecer contraseña de usuario
sudo -u postgres psql -c "ALTER USER username WITH PASSWORD 'newpassword';"

# Verificar método de autenticación en pg_hba.conf
sudo grep "^host" /etc/postgresql/16/main/pg_hba.conf

Problemas de Rendimiento

Identificar consultas lentas:

-- Habilitar temporización de consultas
\timing

-- Encontrar consultas lentas
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Cancelar consulta lenta
SELECT pg_cancel_backend(pid);  -- Elegante
SELECT pg_terminate_backend(pid);  -- Forzoso

-- Analizar rendimiento de consulta
EXPLAIN ANALYZE
SELECT * FROM large_table WHERE column = 'value';

-- Encontrar tablas que necesitan vacuum
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Problemas de Espacio en Disco

# Verificar uso de disco
df -h

# Verificar directorio de datos de PostgreSQL
sudo du -sh /var/lib/postgresql/16/main/

# Verificar bases de datos individuales
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"

# Limpiar archivos WAL (si no se usa archivado)
sudo -u postgres pg_archivecleanup /var/lib/postgresql/16/main/pg_wal/ $(ls -t /var/lib/postgresql/16/main/pg_wal/ | tail -1)

# Vacuum para reclamar espacio
sudo -u postgres psql -d myapp_db -c "VACUUM FULL;"

# Encontrar tablas grandes
sudo -u postgres psql -d myapp_db -c "
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;"

Problemas de Replicación

Verificar estado de replicación:

-- En servidor primario
SELECT * FROM pg_stat_replication;

-- En servidor de espera
SELECT * FROM pg_stat_wal_receiver;

-- Verificar retraso de replicación
SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

Corrupción de Base de Datos

# Verificar corrupción
sudo -u postgres pg_checksums -D /var/lib/postgresql/16/main/

# Intentar reparación automática
sudo -u postgres psql -d myapp_db -c "REINDEX DATABASE myapp_db;"

# Verificar tabla específica
sudo -u postgres psql -d myapp_db -c "REINDEX TABLE table_name;"

# Si la corrupción persiste, restaurar desde respaldo

Conclusión

PostgreSQL se destaca como uno de los sistemas de base de datos más capaces y confiables disponibles, combinando características de nivel empresarial con accesibilidad de código abierto. Esta guía completa ha cubierto el proceso completo de instalación y configuración, desde la configuración inicial hasta la optimización avanzada y el endurecimiento de seguridad.

Conclusiones Clave

1. Autenticación Adecuada: Utilizar métodos de autenticación fuertes como scram-sha-256 y configurar correctamente pg_hba.conf
2. Ajuste de Rendimiento: Optimizar configuración de memoria basada en hardware y carga de trabajo
3. Mantenimiento Regular: Programar autovacuum y operaciones regulares de VACUUM ANALYZE
4. Respaldos Completos: Implementar múltiples estrategias de respaldo incluyendo respaldos lógicos y físicos
5. Seguridad Primero: Habilitar SSL, usar cortafuegos e implementar control de acceso basado en roles
6. Monitoreo Activo: Rastrear métricas de rendimiento, consultas lentas y utilización de recursos

Resumen de Mejores Prácticas

• Usar siempre la última versión estable de PostgreSQL
• Habilitar SSL/TLS para todas las conexiones remotas
• Implementar control de acceso basado en roles adecuado
• Programar respaldos automatizados regulares
• Probar procedimientos de restauración de respaldos regularmente
• Monitorear rendimiento y optimizar consultas
• Mantener shared_buffers al 25% de RAM para servidores dedicados
• Usar pooling de conexiones (PgBouncer) para aplicaciones de alto tráfico
• Habilitar registro completo para resolución de problemas
• Realizar operaciones regulares de VACUUM y ANALYZE
• Documentar configuración y procedimientos
• Planificar alta disponibilidad con replicación

Próximos Pasos

Después de dominar lo básico, explorar estos temas avanzados:

• Replicación: Configurar replicación de flujo para alta disponibilidad
• Pooling de Conexiones: Implementar PgBouncer o pgpool-II
• Extensiones: Explorar PostGIS, pg_stat_statements y otras extensiones
• Particionamiento: Implementar particionamiento de tablas para conjuntos de datos grandes
• Monitoreo: Desplegar monitoreo completo con Prometheus y Grafana
• Alta Disponibilidad: Configurar conmutación por error automática con Patroni
• Balanceo de Carga: Distribuir consultas de lectura entre réplicas

Recursos Adicionales

• Documentación Oficial de PostgreSQL
• PostgreSQL Wiki
• Optimización de Rendimiento de PostgreSQL
• PGTune - Calculadora de Configuración de PostgreSQL
• Ejercicios de PostgreSQL

La fortaleza de PostgreSQL radica en su confiabilidad, riqueza de características y comunidad activa. Siguiendo esta guía y continuando aprendiendo, estará bien equipado para gestionar bases de datos PostgreSQL en cualquier entorno, desde desarrollo hasta sistemas de producción empresarial.