Cómo instalar PostgreSQL en Ubuntu y asegurarlo

Elegir entre los paquetes de Ubuntu y el repositorio PGDG

Para la mayoría de aplicaciones conviene instalar la versión incluida en la edición LTS de Ubuntu. Recibe actualizaciones de seguridad mediante el mismo mecanismo que el resto del sistema y evita mezclar dependencias. El repositorio PostgreSQL Global Development Group, conocido como PGDG, tiene sentido cuando una aplicación exige una versión mayor concreta, necesitas una función reciente o administras varias versiones en paralelo.

No cambies de repositorio por costumbre. Una actualización menor dentro de la misma rama corrige errores sin alterar el formato de datos; saltar de una versión mayor a otra requiere pg_upgrade, replicación lógica o exportación y restauración. Antes de instalar, revisa qué versión espera el framework, el proveedor de extensiones y tu política de mantenimiento.

Instalar PostgreSQL en Ubuntu

Actualiza el índice de paquetes, instala el servidor y las utilidades adicionales, y habilita el servicio:

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo systemctl status postgresql --no-pager

La unidad postgresql.service es un servicio coordinador. En Ubuntu cada clúster tiene además una unidad con su versión y nombre, por ejemplo postgresql@16-main. Consulta los clústeres reales antes de asumir una ruta:

pg_lsclusters
sudo -u postgres psql -c 'SELECT version();'
sudo -u postgres psql -c 'SHOW data_directory;'
sudo -u postgres psql -c 'SHOW config_file;'
sudo -u postgres psql -c 'SHOW hba_file;'

Estos comandos confirman la versión activa, dónde viven los datos y qué archivos está leyendo el proceso. Es importante porque copiar una configuración en el directorio de otra versión no produce ningún cambio aunque el archivo parezca correcto.

Entender la autenticación local antes de crear contraseñas

Una instalación de Ubuntu suele permitir que el usuario Linux postgres entre a PostgreSQL como el rol postgres mediante autenticación peer. Por eso sudo -u postgres psql funciona sin pedir contraseña: el sistema operativo ya verificó la identidad. No significa que la base esté sin protección ni que debas asignar una contraseña al superusuario para usarla desde la aplicación.

Reserva el rol postgres para administración. Si una aplicación usa ese superusuario, una inyección SQL o una credencial filtrada podría leer todas las bases, crear extensiones y modificar roles. El aislamiento correcto consiste en una base, un propietario y, cuando el proyecto lo necesita, roles separados para migraciones y ejecución.

Crear el rol y la base de la aplicación

Entra a la consola administrativa y crea un rol con inicio de sesión. Sustituye la contraseña del ejemplo por una clave larga generada y guárdala fuera del repositorio:

sudo -u postgres psql

CREATE ROLE miapp LOGIN PASSWORD 'CAMBIA_ESTA_CLAVE_LARGA';
CREATE DATABASE miapp OWNER miapp;
REVOKE ALL ON DATABASE miapp FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE miapp TO miapp;
\q

El propietario puede crear objetos dentro de su base, lo cual suele ser suficiente para un proyecto pequeño que ejecuta sus propias migraciones. En equipos mayores conviene separar miapp_migrator, capaz de alterar el esquema, de miapp_runtime, que solo lee y escribe datos. Así una vulnerabilidad en el proceso web no permite borrar tablas ni cambiar funciones.

Prueba la credencial usando exactamente el mismo protocolo que utilizará la aplicación:

psql 'host=127.0.0.1 dbname=miapp user=miapp' -c 'SELECT current_user, current_database();'

Usar 127.0.0.1 obliga a pasar por las reglas de conexión TCP de pg_hba.conf; conectarse sin host utiliza un socket Unix y puede evaluar otra regla. Esta diferencia explica muchos casos donde psql funciona en la terminal pero la aplicación recibe un error de autenticación.

Localizar y editar pg_hba.conf sin abrir accesos de más

pg_hba.conf decide quién puede conectarse, a qué base, con qué usuario, desde qué origen y mediante qué método. Se procesa de arriba hacia abajo y gana la primera regla coincidente. Una regla amplia colocada antes de una específica puede volver inútil la restricción que viene después.

Consulta la ruta activa desde PostgreSQL:

sudo -u postgres psql -Atc 'SHOW hba_file;'

Si la aplicación vive en el mismo VPS, bastan conexiones locales. Un conjunto razonable conserva la administración local por peer y exige autenticación segura a la aplicación por TCP:

local   all             postgres                                peer
local   all             all                                     peer
host    miapp           miapp           127.0.0.1/32            scram-sha-256
host    miapp           miapp           ::1/128                 scram-sha-256

scram-sha-256 evita el esquema de contraseña MD5 heredado y debe acompañarse con password_encryption = 'scram-sha-256' para contraseñas nuevas. Cambiar ese parámetro no convierte hashes antiguos: después del cambio debes reasignar la clave de cada rol que aún utilice MD5.

Valida que PostgreSQL pueda interpretar las reglas antes de recargar:

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

Si la consulta no devuelve filas, no detectó errores de sintaxis. Conserva abierta una sesión administrativa mientras pruebas una regla nueva; así puedes revertirla si bloqueaste por accidente la conexión que necesitas.

Mantener PostgreSQL fuera de Internet

listen_addresses define en qué interfaces escucha PostgreSQL. Para una aplicación instalada en el mismo servidor, localhost es suficiente y elimina la necesidad de exponer el puerto 5432. Confirma el valor y el socket real:

sudo -u postgres psql -c 'SHOW listen_addresses;'
sudo ss -ltnp | grep 5432

Cuando la aplicación está en otro VPS, prefiere una red privada o un túnel WireGuard y escucha solo en esa dirección. En postgresql.conf podría quedar así:

listen_addresses = '127.0.0.1,10.20.0.2'
password_encryption = 'scram-sha-256'

La regla correspondiente de pg_hba.conf debe mencionar una subred pequeña y un par base/usuario concreto:

hostssl miapp miapp 10.20.0.10/32 scram-sha-256

Después limita UFW al origen autorizado:

sudo ufw allow from 10.20.0.10 to any port 5432 proto tcp
sudo ufw status numbered

No uses 0.0.0.0/0 ni confíes en que una contraseña fuerte compensa un servicio público. Cada puerto expuesto recibe intentos automatizados, aumenta ruido en logs y convierte una futura vulnerabilidad del servidor en un riesgo remoto. TLS cifra el tránsito, pero tampoco sustituye el control de origen.

Separar permisos de migración y ejecución

En una aplicación sensible, el proceso web no debería alterar el esquema. Puedes crear un rol de grupo sin login, asignar permisos de datos al usuario de ejecución y reservar el propietario para el pipeline de despliegue:

CREATE ROLE miapp_runtime NOLOGIN;
CREATE ROLE miapp_web LOGIN PASSWORD 'OTRA_CLAVE_LARGA';
GRANT miapp_runtime TO miapp_web;

\connect miapp
GRANT USAGE ON SCHEMA public TO miapp_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO miapp_runtime;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO miapp_runtime;

ALTER DEFAULT PRIVILEGES FOR ROLE miapp IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO miapp_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE miapp IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO miapp_runtime;

Los privilegios predeterminados solo afectan objetos creados en el futuro por el rol indicado. Si las migraciones las ejecuta otro propietario, ajusta FOR ROLE a ese usuario. Verifica los permisos desde miapp_web, no desde el superusuario, porque el superusuario ignora restricciones y puede ocultar un error de diseño.

Configurar conexiones sin saturar la memoria

Cada conexión de PostgreSQL crea un proceso de servidor y consume memoria. Subir max_connections a cientos para silenciar un error puede agotar el VPS antes de que la CPU llegue al límite. En aplicaciones web es preferible un pool de conexiones en el cliente o PgBouncer, con un número controlado de conexiones reales hacia la base.

Comprueba el límite y el uso actual:

SHOW max_connections;
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Muchas conexiones idle durante horas suelen indicar que el pool de la aplicación está sobredimensionado o no libera sesiones. Las conexiones idle in transaction son más peligrosas: mantienen una transacción abierta, retienen versiones de filas y pueden impedir que autovacuum recupere espacio. Define timeouts después de observar la aplicación, no a ciegas:

idle_in_transaction_session_timeout = '5min'
statement_timeout = '60s'
lock_timeout = '10s'

Un statement_timeout global demasiado bajo puede cortar migraciones y reportes legítimos. Si las cargas son distintas, configúralo por rol o por sesión y deja valores específicos en el proceso que ejecuta trabajos largos.

Ajustar memoria y consultas con una línea base

PostgreSQL funciona con sus valores iniciales, pero el rendimiento depende de la RAM disponible, el patrón de consultas y el almacenamiento. shared_buffers reserva una caché propia; work_mem se puede consumir varias veces por consulta y por conexión; maintenance_work_mem se usa en operaciones como índices y vacuum. Por eso multiplicar work_mem por el máximo de conexiones da una idea más honesta del riesgo que leer el número de forma aislada.

Antes de cambiar nada, registra el tamaño de las bases, conexiones y consultas más costosas. Activa pg_stat_statements durante una ventana representativa si puedes instalar la extensión:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Optimiza primero las consultas que acumulan más tiempo, no necesariamente la consulta individual más lenta. Un acceso de 30 milisegundos ejecutado cientos de miles de veces puede consumir más capacidad que un reporte de diez segundos usado una vez al día. Usa EXPLAIN (ANALYZE, BUFFERS) en un entorno seguro para confirmar si falta un índice; ejecutarlo sobre una sentencia de escritura sí realiza el cambio, así que no lo copies sobre producción sin entenderlo.

El almacenamiento NVMe ayuda cuando la carga lee o escribe mucho, pero no repara una consulta que recorre toda una tabla. La comparación NVMe frente a SSD en VPS explica dónde la latencia de disco sí cambia el resultado.

Vigilar autovacuum, crecimiento y bloqueos

PostgreSQL conserva versiones antiguas de filas para mantener el control de concurrencia. Autovacuum elimina las versiones que ya no son visibles y actualiza estadísticas para el planificador. Desactivarlo porque consume recursos traslada el problema al futuro: las tablas se inflan, los planes empeoran y eventualmente aparece riesgo de agotamiento de identificadores de transacción.

Revisa tablas con muchas tuplas muertas y la última actividad de mantenimiento:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Para investigar bloqueos, relaciona sesiones que esperan con las que bloquean:

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS running_for, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

No termines procesos al azar. Identifica primero si la sesión bloqueadora pertenece a una migración, una transacción abandonada o una operación crítica. pg_cancel_backend(pid) intenta cancelar la consulta; pg_terminate_backend(pid) cierra la conexión completa y debe ser el último paso.

Diseñar backups que puedan restaurarse

pg_dump crea un respaldo lógico consistente de una base sin detenerla. El formato personalizado permite restaurar objetos selectivamente y usar varios trabajos en la restauración:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
sudo -u postgres pg_dump --format=custom --file=/var/backups/postgresql/miapp.dump miapp
sudo -u postgres pg_restore --list /var/backups/postgresql/miapp.dump | head

Respalda también objetos globales como roles y tablespaces:

sudo -u postgres pg_dumpall --globals-only > /var/backups/postgresql/globals.sql

Comprobar el listado del archivo detecta corrupción evidente, pero no demuestra una recuperación. La prueba real es restaurar en una base vacía, ejecutar una consulta funcional y medir cuánto tardó:

sudo -u postgres createdb miapp_restore_test
sudo -u postgres pg_restore --dbname=miapp_restore_test /var/backups/postgresql/miapp.dump
sudo -u postgres psql -d miapp_restore_test -c '\dt'
sudo -u postgres dropdb miapp_restore_test

Guarda copias cifradas fuera del VPS y define retención. Si el objetivo exige recuperar hasta minutos antes de una falla, pg_dump nocturno no alcanza: necesitas respaldo físico continuo y archivado WAL para recuperación a un punto en el tiempo. Esa arquitectura exige controlar el ciclo de archivos WAL y ensayar el procedimiento completo.

Actualizar PostgreSQL sin improvisar

Las actualizaciones de paquetes dentro de una misma versión mayor deben entrar en la rutina del sistema, con una ventana que permita reiniciar si el paquete lo requiere. Consulta qué procesos usan binarios antiguos y revisa logs después de aplicar parches.

Una actualización mayor es otro proyecto. Verifica compatibilidad de extensiones, genera un respaldo recuperable, ensaya el tiempo de migración con una copia de tamaño real y prepara el rollback. pg_upgrade --check detecta incompatibilidades antes de tocar los datos, pero no sustituye una prueba funcional de la aplicación.

Los archivos relevantes para documentar son la versión instalada, postgresql.conf, pg_hba.conf, extensiones, roles, tareas de backup y procedimiento de restauración. Esa documentación es específica de la base: debe indicar rutas y responsables reales, no limitarse a decir que “hay backups”.

Errores frecuentes al poner PostgreSQL en producción

Los logs suelen estar bajo /var/log/postgresql/, aunque la ruta efectiva se consulta con SHOW log_directory. Correlaciona el error de la aplicación con la misma hora del servidor; leer cien líneas sin una marca temporal concreta suele mezclar incidentes distintos.

Comprobación final de una instalación segura

Antes de entregar la base a la aplicación, valida el recorrido completo:

sudo -u postgres psql -c 'SELECT now(), version();'
psql 'host=127.0.0.1 dbname=miapp user=miapp' -c 'SELECT current_user;'
sudo ss -ltnp | grep 5432
sudo ufw status numbered
sudo -u postgres psql -c "SELECT datname, usename, client_addr, state FROM pg_stat_activity;"

Después ejecuta una migración controlada o crea una tabla temporal con el rol esperado, comprueba que un rol ajeno no puede acceder y restaura el último backup en un entorno aislado. La instalación queda lista cuando autenticación, permisos, red, consulta y recuperación se han probado por separado y en conjunto.

Si PostgreSQL comparte máquina con la aplicación, vigila la memoria disponible y evita que pools, cachés y workers compitan sin límites. La guía sobre cuánta RAM necesita un VPS ayuda a repartir capacidad antes de que el kernel empiece a cerrar procesos.

Qué monitorear durante la primera semana

Las primeras horas de tráfico real revelan supuestos que una instalación vacía no muestra. Registra cada cinco minutos el número de conexiones activas, la memoria disponible del sistema, el espacio libre, la duración de consultas y el volumen de WAL generado. Compara días laborales y fines de semana antes de concluir que una muestra corta representa la carga normal.

Activa alertas sobre condiciones accionables: disco próximo a llenarse, backups que no terminan, conexiones cerca del límite, réplicas retrasadas y reinicios inesperados. Una alerta por CPU alta durante treinta segundos solo crea ruido; una advertencia sostenida junto con aumento de latencia sí ayuda a intervenir antes de una caída.

df -h /var/lib/postgresql
free -h
sudo -u postgres psql -c "SELECT count(*) AS conexiones FROM pg_stat_activity;"
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('miapp'));"
sudo journalctl -u postgresql --since today --priority warning

Define también el dueño de cada reacción. Si crece el disco, alguien debe saber si ampliar volumen, reducir retención o investigar una tabla. Si aumenta el tiempo de consulta, el siguiente paso es capturar el plan y revisar bloqueos, no reiniciar PostgreSQL. Una semana de observación con decisiones documentadas vale más que una plantilla de parámetros copiada de otro servidor.

Cuándo colocar PgBouncer delante de PostgreSQL

Cada conexión de PostgreSQL corresponde a un proceso del servidor y consume memoria aunque esté inactiva. Una aplicación con muchos workers, funciones que escalan por ráfagas o varios despliegues puede alcanzar max_connections sin ejecutar muchas consultas simultáneas. Antes de aumentar ese valor, suma los pools máximos de todas las instancias: cuatro procesos con un pool de veinte ya pueden solicitar ochenta conexiones.

PgBouncer reduce esa presión reutilizando un grupo pequeño de conexiones hacia PostgreSQL. En modo session, una conexión del servidor queda reservada mientras dura la sesión del cliente y la compatibilidad es alta. En modo transaction, se libera al finalizar cada transacción y la densidad mejora, pero las características que dependen de estado de sesión —tablas temporales de sesión, ciertos SET, cursores o bloqueos de sesión— requieren revisión. No elijas el modo solo por su capacidad teórica; pruébalo con las operaciones reales de tu framework y tus migraciones.

El pooler no arregla consultas lentas ni transacciones olvidadas. De hecho, puede permitir que más solicitudes lleguen a una base ya saturada. Vigila por separado clientes esperando en PgBouncer, conexiones activas en PostgreSQL, duración de transacciones y uso de CPU y disco. Reserva además una vía administrativa directa a PostgreSQL para diagnosticar una saturación del pool.

Cifrar conexiones cuando la aplicación está en otro servidor

Si aplicación y PostgreSQL comparten VPS y se conectan por un socket Unix o 127.0.0.1, publicar el puerto para añadir TLS no mejora el aislamiento. Cuando atraviesan una red entre hosts, combina una red privada o túnel con TLS y reglas HBA restringidas al origen exacto. Una contraseña protege la autenticación, pero por sí sola no cifra consultas ni resultados durante el tránsito.

Genera y administra certificados fuera de improvisaciones en producción: define autoridad, nombres válidos, renovación y permisos de la clave privada. Después verifica desde el cliente que exige cifrado, no solo que el servidor lo acepta. En libpq, sslmode=require cifra pero no valida plenamente la identidad; para evitar conectarte a un impostor necesitas validación de CA y nombre mediante verify-full.

Mantén el puerto fuera de Internet incluso con TLS. El cifrado protege el canal, no corrige una contraseña filtrada, un rol excesivo ni una vulnerabilidad del servicio. La arquitectura segura suma controles: ruta privada, firewall de origen, certificado verificado, rol mínimo y un pool cuyo límite cabe en la memoria de la base.