PostgreSQL desde cero (I): instalación, psql y primeros pasos

Primera entrega de la serie PostgreSQL desde cero a pro. Tiempo de lectura estimado: 10 minutos.

Arranco una nueva serie de cinco posts, esta vez sobre PostgreSQL. Es la base de datos que más uso y la que más respeto. La idea es ir de no haber tocado nunca PostgreSQL a ser capaz de montarlo en producción con replicación, backups y monitorización decentes.

Si quieres una visión transversal del ecosistema de bases de datos para analítica y por qué a veces no basta con PostgreSQL, la serie ClickHouse desde cero a pro es un buen complemento.

Por qué PostgreSQL

En un mundo lleno de bases de datos exóticas, PostgreSQL sigue siendo la opción por defecto para la mayoría de aplicaciones. Motivos:

ACID serio: transacciones, MVCC, aislamiento configurable.
Extensible: tipos propios, funciones, extensiones como PostGIS, TimescaleDB, pgvector.
SQL moderno: CTEs, window functions, LATERAL, JSON/JSONB, arrays nativos.
Fiabilidad probada: más de 25 años de desarrollo, usado en producción desde startups a bancos.
Licencia permisiva (PostgreSQL License, similar a BSD/MIT).

Si tu aplicación no tiene requisitos muy específicos, la respuesta correcta a "¿qué base de datos uso?" es casi siempre PostgreSQL.

Instalación

Hay tres caminos razonables para empezar:

Paquetes oficiales sobre Debian/Ubuntu/RHEL. Lo que usarás en producción.
Docker. Lo mejor para probar en local sin ensuciar el sistema.
Servicios gestionados: RDS, Cloud SQL, Neon, Supabase, Crunchy Bridge...

Para esta serie usamos paquetes nativos y Docker indistintamente. Lo que aprendas vale igual para un servicio gestionado (con matices de configuración que veremos en la entrega V).

Instalación en Debian/Ubuntu

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt install -y postgresql-17 postgresql-client-17
sudo systemctl enable --now postgresql

El script oficial añade el repositorio de la PostgreSQL Global Development Group, que mantiene versiones recientes para distribuciones LTS. Los paquetes del repo base suelen ir una o dos versiones por detrás.

Instalación con Docker

docker run -d \
  --name pg \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17

El volumen pgdata persiste los datos entre reinicios. Puerto 5432 es el estándar.

Para entrar al cliente:

docker exec -it pg psql -U postgres

psql: el cliente que vas a usar cada día

psql es el cliente de consola oficial. Es mucho más que un prompt SQL: tiene meta-comandos, ejecución de scripts, formatos de salida, variables, historial persistente y autocompletado.

Meta-comandos que uso constantemente:

\l               -- listar bases de datos
\c nombre        -- conectarse a una base de datos
\dt              -- listar tablas
\dt+             -- con tamaños y descripciones
\d tabla         -- describir una tabla
\d+ tabla        -- con detalles extra
\di              -- listar índices
\df              -- listar funciones
\du              -- listar roles
\dn              -- listar schemas
\dx              -- listar extensiones instaladas
\timing on       -- mostrar tiempo de ejecución
\x               -- toggle formato expandido (útil para filas anchas)
\e               -- abrir $EDITOR para escribir la consulta
\i fichero.sql   -- ejecutar script
\q               -- salir
\?               -- ayuda de meta-comandos

Mi ~/.psqlrc básico:

\set QUIET 1
\pset null '(null)'
\pset border 2
\set PROMPT1 '%n@%/%R%# '
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\timing on
\set QUIET 0

Con esto ves el nombre de la base de datos en el prompt, los nulos no se confunden con strings vacíos, el historial se separa por base de datos, y tienes el tiempo de cada consulta automáticamente.

Roles, bases de datos y permisos

En PostgreSQL todo es un rol. Un rol puede ser "usuario" (LOGIN) o "grupo" (NOLOGIN). Los permisos se otorgan a roles. Un rol puede heredar permisos de otro.

-- Crear un rol de aplicación sin permisos de login directo
CREATE ROLE app NOLOGIN;

-- Crear un usuario real
CREATE ROLE javier WITH LOGIN PASSWORD 'xxx' IN ROLE app;

-- Crear una base de datos cuyo owner es el rol app
CREATE DATABASE blog OWNER app;

-- Dar permisos
GRANT CONNECT ON DATABASE blog TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;

-- Y lo más importante: que se apliquen a tablas futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;

Separar el rol dueño (propietario de las tablas) del rol de aplicación (el que usa la app en runtime) es una buena práctica: limita el daño si las credenciales se filtran.

pg_hba.conf

El fichero pg_hba.conf controla quién puede conectarse, desde dónde y con qué método de autenticación. Está en /etc/postgresql/17/main/ en Debian/Ubuntu.

Entrada típica para entornos de producción:

# TYPE  DATABASE  USER  ADDRESS         METHOD
local   all       all                   peer
host    all       all   127.0.0.1/32    scram-sha-256
host    all       all   ::1/128         scram-sha-256
hostssl all       all   10.0.0.0/8      scram-sha-256

peer: autenticación por usuario del sistema operativo (solo en socket local).
scram-sha-256: el método moderno de autenticación por contraseña.
hostssl: solo acepta conexiones cifradas.

Tu primera base de datos

Vamos a montar un esquema de blog muy simple. Desde psql:

\c blog

CREATE TABLE authors (
  id         BIGSERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  id           BIGSERIAL PRIMARY KEY,
  author_id    BIGINT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  title        TEXT NOT NULL,
  slug         TEXT NOT NULL UNIQUE,
  body         TEXT NOT NULL,
  published_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX posts_published_at_idx ON posts(published_at DESC) WHERE published_at IS NOT NULL;

Un par de detalles que ya introducen ideas clave:

BIGSERIAL genera una columna BIGINT con una secuencia asociada. En versiones modernas hay una alternativa más estándar, GENERATED ALWAYS AS IDENTITY, que veremos en la entrega II.
TIMESTAMPTZ (timestamp with time zone) es casi siempre lo que quieres. Guarda el instante en UTC y lo convierte al huso horario de la sesión al leer. El TIMESTAMP sin zona suele ser una trampa.
REFERENCES ... ON DELETE CASCADE crea una foreign key: borrar un autor borra sus posts.
El último índice es parcial: solo indexa filas publicadas. Si la mayoría de posts son borradores, el índice es pequeño y más rápido.

Primeros INSERT y SELECT

INSERT INTO authors (name, email)
VALUES ('Javier', '[email protected]')
RETURNING id;
-- Devuelve 1

INSERT INTO posts (author_id, title, slug, body, published_at)
VALUES
  (1, 'Hola mundo', 'hola-mundo', 'Primer post', now()),
  (1, 'En borrador', 'borrador', 'Aún no publicado', NULL);

SELECT id, title, published_at IS NOT NULL AS publicado
FROM posts
ORDER BY created_at DESC;

RETURNING es una de esas pequeñas cosas que hacen que PostgreSQL sea tan agradable: cualquier INSERT, UPDATE o DELETE puede devolver filas. Adiós al SELECT LAST_INSERT_ID().

Transacciones

Toda operación en PostgreSQL ocurre dentro de una transacción (implícita o explícita). Para operaciones multi-statement:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Si algo sale mal
-- ROLLBACK;

COMMIT;

Los savepoints permiten hacer rollback parcial dentro de una transacción grande:

BEGIN;
INSERT INTO ...;
SAVEPOINT sp1;
UPDATE ...;    -- esto puede fallar
ROLLBACK TO SAVEPOINT sp1;
COMMIT;        -- el INSERT inicial sí se guarda

Por defecto el nivel de aislamiento es READ COMMITTED. Para operaciones financieras o cargas analíticas complejas, REPEATABLE READ o SERIALIZABLE son alternativas importantes que conviene conocer.

Los ficheros que importan

Tres ficheros concentran casi toda la configuración:

postgresql.conf — parámetros del servidor: memoria, WAL, logs, conexiones.
pg_hba.conf — autenticación y redes permitidas.
pg_ident.conf — mapeo entre usuarios del SO y roles de PostgreSQL.

En Debian/Ubuntu están en /etc/postgresql/17/main/. El directorio de datos (PGDATA) está en /var/lib/postgresql/17/main/. Ahí viven los WAL, las tablas, los índices.

Para consultar cualquier parámetro desde psql:

SHOW work_mem;
SHOW shared_buffers;
SHOW max_connections;

Y para ver todos:

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE '%mem%';

Por dónde seguir

II: tipos, restricciones y relaciones — el sistema de tipos, claves, CHECK, JSON/JSONB, arrays.
III: CTEs, window functions y consultas avanzadas — lo que diferencia a un desarrollador de PostgreSQL de uno que "sabe SQL".
IV: índices, EXPLAIN y rendimiento — cuando la consulta tarda tres segundos y tu jefe ya no sonríe.
V: replicación, backups y producción — operar PostgreSQL cuando el negocio depende de ello.

Si te gustan las consultas modernas y quieres un aperitivo, el post PostgreSQL: 10 consultas que todo desarrollador debería conocer es un buen complemento a esta serie.