PostgreSQL es la base de datos relacional de código abierto más completa y una de las más usadas en servidores. Esta guía va de la instalación a dejarla lista para producción: quién puede entrar y desde dónde, qué puede hacer cada usuario, cómo saber si un índice sirve, qué pasa cuando dos personas cambian lo mismo a la vez y cómo hacer respaldos que de verdad se restauran. Cada error que vas a encontrar está provocado a propósito y explicado.
tienda del curso de SQL, así que puedes practicar con los mismos datos. Las contraseñas son de laboratorio: usa las tuyas, largas y únicas.- Qué es PostgreSQL y en qué se distingue de MySQL y SQLite
- El laboratorio de esta guía
- Instalarlo: en Debian o con Docker
- Clúster, bases de datos y esquemas
- Entrar por primera vez y crear la base tienda
- psql y sus metacomandos
- Roles y contraseñas
- Permisos con el mínimo privilegio
- Las tablas nuevas y el esquema public
- Conexiones por red: postgresql.conf y pg_hba.conf
- No abras el 5432: entra por un túnel SSH
- Índices medidos con EXPLAIN ANALYZE
- Transacciones y bloqueos
- Respaldos con pg_dump
- Restaurar y comprobar la restauración
- Respaldo diario automático, con rotación
- VACUUM y autovacuum
- Actualizar a una versión mayor
- Errores típicos y cómo resolverlos
- Hoja de referencia
Qué es PostgreSQL y en qué se distingue de MySQL y SQLite
PostgreSQL guarda los datos en tablas relacionadas y se consulta con SQL, como MySQL o SQLite. Lo que lo distingue es que se toma muy en serio el estándar y la integridad de los datos: tipos estrictos, restricciones que se cumplen siempre, transacciones para todo (también para cambiar la estructura de las tablas) y extensiones para casi cualquier cosa, de datos geográficos con PostGIS a búsqueda de texto y vectores para IA. Lo desarrolla una comunidad, no una empresa, con licencia libre.
- Frente a SQLite. SQLite es una biblioteca: la base es un archivo y no hay servidor, usuarios ni red. Es perfecta para una app de escritorio, un celular o un sitio pequeño con un solo proceso que escribe. Cuando varias personas o servicios escriben a la vez desde otras máquinas, necesitas un servidor como PostgreSQL.
- Frente a MySQL. Los dos son servidores maduros y rápidos. MySQL es muy común en hospedaje compartido y con WordPress; PostgreSQL suele preferirse para aplicaciones con datos complejos, consultas pesadas o reglas estrictas, y tiene más funciones de SQL (las del curso de SQL funcionan todas). Para aprender, lo que sepas de uno te sirve en el otro.
Si todavía no sabes SQL, empieza por el curso de SQL; esta guía trata de administrar el servidor, no del lenguaje.
El laboratorio de esta guía
Para que puedas reproducirlo todo, estas son las piezas. El servidor (IP 172.30.7.10) es un Debian 13 con PostgreSQL instalado desde el paquete de Debian, con systemd y SSH; las órdenes las ejecutó ana, un usuario normal con sudo, como en un servidor en la nube. El cliente (172.30.7.50) es otra máquina con psql y ssh, con el usuario luis. Y pg18 es la imagen oficial de Docker. Ninguna tiene salida a internet.
Instalarlo: en Debian o con Docker
Hay dos caminos sencillos. En Debian o Ubuntu, el paquete de la distribución instala la versión que esa distribución eligió (en Debian 13, la 17), la arranca y la deja configurada para iniciar con el sistema:
sudo apt update sudo apt install postgresql
psql --version; pg_lsclusters
salidapsql (PostgreSQL) 17.11 (Debian 17.11-0+deb13u1) Ver Cluster Port Status Owner Data directory Log file 17 main 5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
pg_lsclusters es una herramienta propia de Debian que lista los servidores instalados: versión, nombre, puerto, si está en marcha, dónde están los datos y el registro. Es un servicio de systemd más:
systemctl status postgresql@17-main --no-pager | head -12
salida● postgresql@17-main.service - PostgreSQL Cluster 17-main
Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled)
Active: active (running) since Sat 2026-11-07 17:53:11 UTC; 14s ago
Invocation: 929783fb0bca4c34862f4bd405696a58
Process: 76 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 17-main start (code=exited, status=0/SUCCESS)
Main PID: 96 (postgres)
Tasks: 6 (limit: 150)
Memory: 36.5M (peak: 36.5M)
CPU: 183ms
CGroup: /system.slice/system-postgresql.slice/postgresql@17-main.service
├─ 96 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
├─ 97 "postgres: 17/main: checkpointer "Si necesitas la versión más reciente en Debian o Ubuntu, el proyecto mantiene su propio repositorio (apt.postgresql.org), que se instala igual y convive con el de la distribución. Cada versión mayor recibe correcciones durante cinco años; la 18 es la estable actual y la 19 tiene prevista su salida el 19 de noviembre de 2026, como contamos en esta noticia.
Con Docker, la imagen oficial postgres trae la última versión de cada rama. Así se arranca de forma razonable:
docker run -d --name pg18 \ -e POSTGRES_PASSWORD='una-contraseña-larga' \ -v pgdatos:/var/lib/postgresql \ -p 127.0.0.1:5432:5432 \ postgres:18
Tres detalles importan. La contraseña de POSTGRES_PASSWORD es la del superusuario postgres. El volumen pgdatos guarda los datos fuera del contenedor, para que no se pierdan al borrarlo; desde la versión 18 la imagen usa /var/lib/postgresql como punto de montaje y guarda los datos en una subcarpeta por versión. Y -p 127.0.0.1:5432:5432 publica el puerto solo para el propio equipo: un -p 5432:5432 a secas lo abre a toda la red, y Docker se salta el firewall del sistema para hacerlo. En el laboratorio:
psql -U postgres -c "SELECT version();"; echo; ls /var/lib/postgresql/
salida version -------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) 18
psql -U postgres -Atc 'SHOW data_directory'; grep -v '^#' $(psql -U postgres -Atc 'SHOW hba_file') | grep -v '^\s*$' | tail -3
salida/var/lib/postgresql/18/docker host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host all all all scram-sha-256
docker logs pg18 | tail -4
salida 2026-11-07 17:56:14.127 UTC [73] LOG: checkpoint starting: immediate force wait 2026-11-07 17:56:14.353 UTC [73] LOG: checkpoint complete: wrote 2516 buffers (15.4%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 4 recycled; write=0.144 s, sync=0.007 s, total=0.226 s; sync files=14, longest=0.003 s, average=0.001 s; distance=67488 kB, estimate=85053 kB; lsn=0/B2DB4F0, redo lsn=0/B2DB498
Fíjate en la última línea de pg_hba.conf de la imagen: host all all all scram-sha-256, es decir, cualquiera desde cualquier dirección con contraseña, y escucha en 0.0.0.0. Es cómodo dentro de una red de contenedores, pero por eso importa tanto no publicar el puerto hacia fuera. Si los contenedores te suenan nuevos, la guía de Docker explica volúmenes y redes.
Clúster, bases de datos y esquemas
Un servidor de PostgreSQL se llama clúster: un proceso, un puerto y una carpeta de datos. No tiene que ver con varias máquinas; es solo el nombre. Dentro de un clúster hay varias bases de datos, y cada conexión entra a una sola: no se puede consultar una tabla de otra base en la misma sentencia. Dentro de cada base, las tablas viven en esquemas, que son como carpetas; si no creas ninguno, todo va a public. Los roles (usuarios y grupos) son del clúster entero.
Por dentro, el servidor es un proceso principal y varios de servicio: el que escribe el diario de cambios (WAL), el que hace limpieza (autovacuum) y el que guarda los datos en disco cada cierto tiempo. Cada conexión abre un proceso más:
ps -u postgres -o pid,cmd --forest
salida PID CMD
96 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
97 \_ postgres: 17/main: checkpointer
98 \_ postgres: 17/main: background writer
100 \_ postgres: 17/main: walwriter
101 \_ postgres: 17/main: autovacuum launcher
102 \_ postgres: 17/main: logical replication launcher En Debian la configuración está en /etc/postgresql/17/main/ y los datos en /var/lib/postgresql/17/main/. Los dos archivos que vas a tocar son postgresql.conf (cómo funciona el servidor) y pg_hba.conf (quién puede conectarse); además, todo lo que pongas en conf.d/ se lee al final de postgresql.conf, así que es el mejor lugar para tus cambios:
ls /etc/postgresql/17/main/; echo; sudo ls /var/lib/postgresql/17/main/
salidaconf.d environment pg_ctl.conf pg_hba.conf pg_ident.conf postgresql.conf start.conf PG_VERSION base global pg_commit_ts pg_dynshmem pg_logical pg_multixact pg_notify pg_replslot pg_serial pg_snapshots pg_stat pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_wal pg_xact postgresql.auto.conf postmaster.opts postmaster.pid
Entrar por primera vez y crear la base tienda
La instalación crea un usuario del sistema y un rol de PostgreSQL, los dos llamados postgres, que es el superusuario. Si intentas entrar con él desde tu usuario, falla:
psql -U postgres
salidapsql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
No es una contraseña equivocada: no hay contraseña. Las conexiones locales, por el socket de Unix, usan el método peer: PostgreSQL le pregunta al sistema operativo quién es el usuario que se conecta y solo lo deja entrar con el rol del mismo nombre. ana no es postgres, así que no pasa. La forma correcta es ejecutar psql como el usuario del sistema postgres:
sudo -u postgres psql -c 'SELECT version();'
salida version --------------------------------------------------------------------------------------------------------------------- PostgreSQL 17.11 (Debian 17.11-0+deb13u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row)
Y si ana ejecuta psql sin más, intenta entrar con el rol ana, que todavía no existe:
psql
salidapsql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "ana" does not exist
Lo práctico es crear un rol con tu nombre de usuario que pueda crear bases, pero que no sea superusuario, y trabajar con él. Así no necesitas sudo para el día a día y no tienes todo el poder a cada momento:
sudo -u postgres createuser --createdb ana && psql -d postgres -c '\du'
salida List of roles Role name | Attributes -----------+------------------------------------------------------------ ana | Create DB postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
Ahora ana crea la base tienda, que queda a su nombre, y carga el archivo del curso de SQL:
dropdb tienda && createdb tienda && psql -d tienda -f tienda.sql | sort | uniq -c
salida 6 CREATE TABLE
1 INSERT 0 12
1 INSERT 0 15
1 INSERT 0 22
1 INSERT 0 41
1 INSERT 0 5
1 INSERT 0 8psql y sus metacomandos
psql es la consola de PostgreSQL. Además de SQL, entiende metacomandos, que empiezan con una barra invertida y no llevan punto y coma. Estos son los que más vas a usar: \l lista las bases, \c cambia de base, \dt lista las tablas, \d describe una, \du lista los roles, \x muestra los resultados en vertical, \timing mide cuánto tarda cada consulta y \q sale.
psql -d tienda -c '\l tienda'
salida List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges --------+-------+----------+-----------------+---------+---------+--------+-----------+------------------- tienda | ana | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | (1 row)
psql -d tienda -c '\dt'
salida List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | categorias | table | ana public | clientes | table | ana public | detalle | table | ana public | empleados | table | ana public | pedidos | table | ana public | productos | table | ana (6 rows)
psql -d tienda -c '\d productos'
salida Table "public.productos"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
id | integer | | not null |
nombre | character varying(60) | | not null |
categoria_id | integer | | |
precio | numeric(10,2) | | not null |
stock | integer | | not null | 0
Indexes:
"productos_pkey" PRIMARY KEY, btree (id)
Check constraints:
"productos_precio_check" CHECK (precio > 0::numeric)
Foreign-key constraints:
"productos_categoria_id_fkey" FOREIGN KEY (categoria_id) REFERENCES categorias(id)
Referenced by:
TABLE "detalle" CONSTRAINT "detalle_producto_id_fkey" FOREIGN KEY (producto_id) REFERENCES productos(id)\d enseña mucho más que las columnas: la clave primaria, las restricciones y quién depende de la tabla. Cuando una fila tiene muchas columnas, \x la muestra en vertical:
psql -d tienda -c '\x on' -c 'SELECT * FROM clientes WHERE id = 3;'
salidaExpanded display is on. -[ RECORD 1 ]---------------- id | 3 nombre | Camila Rojas ciudad | Bogotá pais | Colombia email | camila.r@ejemplo.com alta | 2025-02-27
En la consola interactiva, \? lista todos los metacomandos y \h seguido de una orden de SQL (por ejemplo \h CREATE INDEX) muestra su sintaxis. Con -c, como en esta guía, psql ejecuta una orden y sale.
Roles y contraseñas
En PostgreSQL no hay «usuarios» y «grupos» por separado: todo son roles. Un rol con el atributo LOGIN es lo que llamaríamos un usuario. Creamos dos: reportes, que solo leerá, y app_tienda, que usará la aplicación:
sudo -u postgres psql -c "CREATE ROLE reportes LOGIN PASSWORD 'Lectura-lab-2026';" \
-c "CREATE ROLE app_tienda LOGIN PASSWORD 'App-lab-2026';"salidaCREATE ROLE CREATE ROLE
La contraseña no se guarda tal cual. Desde la versión 14, PostgreSQL usa por defecto SCRAM-SHA-256: guarda una huella derivada con sal y 4,096 rondas, y en el inicio de sesión ni siquiera viaja la contraseña, sino una prueba de que la conoces. Esto es lo que queda guardado (recortado):
sudo -u postgres psql -Atc "SELECT rolname, left(rolpassword, 35) || '…' FROM pg_authid WHERE rolname IN ('reportes','app_tienda') ORDER BY 1"salidaapp_tienda|SCRAM-SHA-256$4096:cq7NxT+XnMiFwOJe… reportes|SCRAM-SHA-256$4096:si1bvfGkhuGx/+RE…
Escribir la contraseña en un CREATE ROLE tiene un problema: la orden puede quedar en el historial de psql o en los registros del servidor. En la consola interactiva usa \password reportes, que la pide sin mostrarla y la envía ya convertida. Si alguna vez ves el método md5 en pg_hba.conf o contraseñas guardadas así, son de versiones antiguas y conviene migrarlas.
Si la contraseña no es la correcta:
PGPASSWORD=otra-cosa psql -h localhost -U reportes -d tienda -c 'SELECT 1'
salidapsql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "reportes" connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "reportes"
El mensaje sale dos veces porque psql primero intenta conectarse cifrado con SSL y, si falla, lo reintenta sin cifrar. No es que lo hayas intentado dos veces.
Permisos con el mínimo privilegio
El principio de mínimo privilegio dice que cada rol debe poder hacer lo que necesita y nada más. Si la aplicación se conecta como superusuario o como dueña de las tablas y alguien encuentra una inyección de SQL en ella, puede borrarlo todo. Con un rol limitado, el daño se queda en lo que ese rol puede tocar.
Un rol nuevo no puede leer nada que no sea suyo:
PGPASSWORD=Lectura-lab-2026 psql -h localhost -U reportes -d tienda -c 'SELECT count(*) FROM clientes'
salidaERROR: permission denied for table clientes
Le damos permiso de lectura sobre todas las tablas que hay ahora:
psql -d tienda -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO reportes;'
salidaGRANT
PGPASSWORD=Lectura-lab-2026 psql -h localhost -U reportes -d tienda -c 'SELECT count(*) FROM clientes' -c "UPDATE productos SET precio = 1 WHERE id = 1"
salida count
-------
12
(1 row)
ERROR: permission denied for table productosLee, pero no puede cambiar nada: justo lo que queremos para un rol de reportes. Para la aplicación vamos más fino: cerramos la base a cualquiera que no tenga permiso expreso, y le damos a app_tienda solo lo que usa, incluso por columna:
-- la base deja de estar abierta a cualquier rol con LOGIN REVOKE CONNECT ON DATABASE tienda FROM PUBLIC; GRANT CONNECT ON DATABASE tienda TO reportes, app_tienda; -- la app lee el catálogo, registra pedidos y solo puede cambiar el stock GRANT SELECT ON productos, categorias TO app_tienda; GRANT SELECT, INSERT ON pedidos, detalle TO app_tienda; GRANT UPDATE (stock) ON productos TO app_tienda;
export PGPASSWORD=App-lab-2026 psql -h localhost -U app_tienda -d tienda \ -c 'UPDATE productos SET stock = stock - 1 WHERE id = 2' \ -c 'UPDATE productos SET precio = 1 WHERE id = 2' \ -c 'DELETE FROM pedidos WHERE id = 1'
salidaUPDATE 1 ERROR: permission denied for table productos ERROR: permission denied for table pedidos
Puede bajar el stock, pero no cambiar precios ni borrar pedidos. Y un rol cualquiera que no está en la lista ya no puede ni entrar a la base, porque quitamos el CONNECT que por defecto tiene PUBLIC (todos los roles):
psql -h localhost -U curioso -d tienda -c 'SELECT 1'
salidapsql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: permission denied for database "tienda" DETAIL: User does not have CONNECT privilege.
\dp muestra los permisos de una tabla. Cada letra es uno: r leer (SELECT), a añadir (INSERT), w escribir (UPDATE), d borrar (DELETE), y lo que va después de la barra es quién lo concedió:
psql -d tienda -c '\dp productos'
salida Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------+-------+-------------------+--------------------+----------
public | productos | table | ana=arwdDxtm/ana +| stock: +|
| | | reportes=r/ana +| app_tienda=w/ana |
| | | app_tienda=r/ana | |
(1 row)Las tablas nuevas y el esquema public
Dos sorpresas que se encuentra casi todo el mundo. La primera: GRANT ... ON ALL TABLES solo vale para las tablas que existen en ese momento. Si después creas una, el rol de reportes no la puede leer:
psql -d tienda -c 'CREATE TABLE cupones (codigo text PRIMARY KEY, descuento int)' \
-c "INSERT INTO cupones VALUES ('BUENFIN', 15)"
psql -h localhost -U reportes -d tienda -c 'SELECT * FROM cupones'salidaCREATE TABLE INSERT 0 1 ERROR: permission denied for table cupones
La solución son los privilegios por defecto: le dices a PostgreSQL que las tablas que cree ana en ese esquema nazcan ya con permiso de lectura para reportes. Ojo, que tampoco son retroactivos: la tabla que ya creaste necesita su propio GRANT:
psql -d tienda -c 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reportes;' \
-c 'GRANT SELECT ON cupones TO reportes;' \
-c 'CREATE TABLE envios (pedido_id int, guia text)'
psql -h localhost -U reportes -d tienda -c 'SELECT count(*) FROM cupones' -c 'SELECT count(*) FROM envios'salidaALTER DEFAULT PRIVILEGES
GRANT
CREATE TABLE
count
-------
1
(1 row)
count
-------
0
(1 row)psql -d tienda -c '\ddp'
salida Default access privileges Owner | Schema | Type | Access privileges -------+--------+-------+------------------- ana | public | table | reportes=r/ana (1 row)
Los privilegios por defecto son por el rol que crea las tablas: si las crea otro rol (por ejemplo, el que ejecuta las migraciones de tu app), hay que declararlos con ALTER DEFAULT PRIVILEGES FOR ROLE ese_rol ....
La segunda sorpresa es el esquema public. Hasta PostgreSQL 14, cualquier rol podía crear tablas en él; desde la 15, solo su dueño, que es el dueño de la base:
psql -h localhost -U reportes -d tienda -c 'CREATE TABLE notas (t text)'
salidaERROR: permission denied for schema public
LINE 1: CREATE TABLE notas (t text)
^Es un cambio de seguridad: evita que un rol con pocos permisos cree objetos con el mismo nombre que otros para engañar a quien los use. Si tu aplicación necesita crear tablas, dale a su rol CREATE en un esquema propio, en lugar de reabrir public.
Conexiones por red: postgresql.conf y pg_hba.conf
Recién instalado, PostgreSQL solo acepta conexiones del propio servidor. Desde el cliente:
psql -h servidor -U reportes -d tienda -c 'SELECT 1'
salidapsql: error: connection to server at "servidor" (172.30.7.10), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?
Connection refused quiere decir que nadie escucha en esa dirección y puerto. Lo controla listen_addresses en postgresql.conf, que por defecto vale localhost:
sudo -u postgres psql -Atc "SHOW listen_addresses"; sudo -u postgres psql -Atc 'SHOW port'; sudo -u postgres psql -Atc 'SHOW password_encryption'; ss -ltn | grep 5432
salidalocalhost 5432 scram-sha-256 LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* LISTEN 0 200 [::1]:5432 [::]:*
La pondremos en un archivo propio dentro de conf.d/, para no editar el original. Este ajuste necesita reiniciar el servidor; la mayoría de los demás basta con recargarlos. Le decimos que escuche también en su IP de la red interna, no en todas ('*'):
echo "listen_addresses = 'localhost, 172.30.7.10'" | sudo tee /etc/postgresql/17/main/conf.d/red.conf sudo systemctl restart postgresql@17-main ss -ltn | grep 5432
salida840:include_dir = 'conf.d' # include files ending in '.conf' from listen_addresses = 'localhost, 172.30.7.10' LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* LISTEN 0 200 172.30.7.10:5432 0.0.0.0:* LISTEN 0 200 [::1]:5432 [::]:*
Ya escucha, pero todavía no deja pasar:
psql -h servidor -U reportes -d tienda -c 'SELECT 1'
salidapsql: error: connection to server at "servidor" (172.30.7.10), port 5432 failed: FATAL: no pg_hba.conf entry for host "172.30.7.50", user "reportes", database "tienda", SSL encryption connection to server at "servidor" (172.30.7.10), port 5432 failed: FATAL: no pg_hba.conf entry for host "172.30.7.50", user "reportes", database "tienda", no encryption
Ahora el portero es pg_hba.conf (host-based authentication). Cada línea dice: tipo de conexión, base, rol, dirección de origen y método. PostgreSQL las lee de arriba abajo y usa la primera que coincide; si ninguna coincide, rechaza. Estas son las de Debian:
sudo grep -v '^#' /etc/postgresql/17/main/pg_hba.conf | grep -v '^\s*$'
salidalocal all postgres peer local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256
local es el socket del propio servidor (con peer); host, cualquier conexión TCP; hostssl, solo las cifradas. Añadimos una línea lo más estrecha posible: solo la base tienda, solo el rol reportes, solo desde la IP del cliente, cifrada y con contraseña. Los cambios de pg_hba.conf se aplican con una recarga, sin cortar a nadie, y la vista pg_hba_file_rules enseña cómo los entendió el servidor (la columna error avisa si una línea está mal escrita):
hostssl tienda reportes 172.30.7.50/32 scram-sha-256
echo 'hostssl tienda reportes 172.30.7.50/32 scram-sha-256' | sudo tee -a /etc/postgresql/17/main/pg_hba.conf 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 ...'
salidahostssl tienda reportes 172.30.7.50/32 scram-sha-256
t
line_number | type | database | user_name | address | auth_method | error
-------------+---------+---------------+------------+-------------+---------------+-------
123 | local | {all} | {all} | | peer |
125 | host | {all} | {all} | 127.0.0.1 | scram-sha-256 |
127 | host | {all} | {all} | ::1 | scram-sha-256 |
130 | local | {replication} | {all} | | peer |
131 | host | {replication} | {all} | 127.0.0.1 | scram-sha-256 |
132 | host | {replication} | {all} | ::1 | scram-sha-256 |
133 | hostssl | {tienda} | {reportes} | 172.30.7.50 | scram-sha-256 |
(7 rows)psql -h servidor -U reportes -d tienda -c '\conninfo' -c 'SELECT count(*) FROM productos'
salidaYou are connected to database "tienda" as user "reportes" on host "servidor" (address "172.30.7.10") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
count
-------
15
(1 row)Entró, y \conninfo confirma que la conexión va cifrada con TLS 1.3 (el paquete de Debian trae un certificado propio de fábrica; en producción conviene uno tuyo). Si el cliente pide ir sin cifrar, la línea hostssl no coincide y se queda fuera:
psql 'host=servidor user=reportes dbname=tienda sslmode=disable' -c 'SELECT 1'
salidapsql: error: connection to server at "servidor" (172.30.7.10), port 5432 failed: FATAL: no pg_hba.conf entry for host "172.30.7.50", user "reportes", database "tienda", no encryption
Si en vez de una IP pones 0.0.0.0/0 y en vez de un rol pones all, cualquiera en internet puede intentar adivinar contraseñas de cualquier rol. Las líneas de pg_hba.conf deben ser tan concretas como puedas.
No abras el 5432: entra por un túnel SSH
Aunque sepas configurar pg_hba.conf, lo más seguro es que el puerto 5432 no sea alcanzable desde internet. Hay robots que recorren todas las direcciones buscando servidores de PostgreSQL para probar contraseñas o aprovechar fallos, igual que con SSH. Si necesitas entrar desde tu computadora, la forma limpia es un túnel SSH: PostgreSQL sigue escuchando solo en localhost, y tú llegas a través de la conexión SSH que ya usas para administrar el servidor.
Primero dejamos el servidor como estaba, solo en local:
sudo rm /etc/postgresql/17/main/conf.d/red.conf sudo systemctl restart postgresql@17-main ss -ltn | grep 5432
salidaLISTEN 0 200 127.0.0.1:5432 0.0.0.0:* LISTEN 0 200 [::1]:5432 [::]:*
Y desde el cliente abrimos el túnel: el puerto 5433 de la máquina de luis lleva, por dentro de SSH, al 5432 del servidor. Para PostgreSQL la conexión llega desde el propio servidor, así que la atienden las líneas de localhost:
ssh -f -N -L 5433:localhost:5432 ana@servidor ss -ltn | grep 5433 psql -h localhost -p 5433 -U reportes -d tienda -c '\conninfo' -c 'SELECT nombre, precio FROM productos ORDER BY precio DESC LIMIT 3'
salidaLISTEN 0 128 127.0.0.1:5433 0.0.0.0:*
LISTEN 0 128 [::1]:5433 [::]:*
You are connected to database "tienda" as user "reportes" on host "localhost" (address "::1") at port "5433".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
nombre | precio
------------------+---------
Portátil Nova 16 | 1849.00
Portátil Aero 14 | 1299.99
Monitor 27" 4K | 429.90
(3 rows)-L 5433:localhost:5432 es «lo que llegue a mi 5433, mándalo al 5432 visto desde el servidor»; -N no abre una consola y -f lo deja en segundo plano. Los programas gráficos, como DBeaver o pgAdmin, traen la opción de túnel SSH en la ventana de conexión. Si es una aplicación en otra máquina la que necesita la base, la alternativa es una red privada entre las dos (la red interna de tu proveedor, una VPN como WireGuard o Tailscale) y una línea de pg_hba.conf solo para esa IP.
Índices medidos con EXPLAIN ANALYZE
Un índice es una estructura aparte, ordenada por una columna, que permite encontrar filas sin leer la tabla entera, como el índice de un libro. Para verlo con números creamos una tabla de un millón de visitas:
psql -d tienda -c '\timing on' -c "CREATE TABLE visitas AS
SELECT g AS id, (g % 50000) + 1 AS cliente_id, 1 + (g % 15) AS producto_id,
timestamp '2026-01-01' + (g % 300) * interval '1 day' + (g % 86400) * interval '1 second' AS momento
FROM generate_series(1, 1000000) AS g;" -c 'ANALYZE visitas;'salidaTiming is on. SELECT 1000000 Time: 909.284 ms ANALYZE Time: 72.766 ms
EXPLAIN ANALYZE ejecuta la consulta y cuenta cómo la resolvió y cuánto tardó cada paso. Buscamos las visitas de un cliente:
psql -d tienda -c 'EXPLAIN ANALYZE SELECT count(*) FROM visitas WHERE cliente_id = 4321;'
salida QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=12608.57..12608.58 rows=1 width=8) (actual time=16.512..19.034 rows=1 loops=1)
-> Gather (cost=12608.35..12608.56 rows=2 width=8) (actual time=16.424..19.029 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=11608.35..11608.36 rows=1 width=8) (actual time=14.083..14.084 rows=1 loops=3)
-> Parallel Seq Scan on visitas (cost=0.00..11608.33 rows=8 width=0) (actual time=1.130..14.080 rows=7 loops=3)
Filter: (cliente_id = 4321)
Rows Removed by Filter: 333327
Planning Time: 0.215 ms
Execution Time: 19.100 ms
(10 rows)Parallel Seq Scan significa que leyó la tabla entera, repartida entre tres procesos: cada uno descartó unas 333,000 filas para quedarse con siete. Tardó 19.1 ms. Creamos el índice y repetimos:
psql -d tienda -c '\timing on' -c 'CREATE INDEX visitas_cliente_idx ON visitas (cliente_id);'
salidaTiming is on. CREATE INDEX Time: 207.260 ms
psql -d tienda -c 'EXPLAIN ANALYZE SELECT count(*) FROM visitas WHERE cliente_id = 4321;'
salida QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=81.53..81.54 rows=1 width=8) (actual time=0.332..0.333 rows=1 loops=1)
-> Bitmap Heap Scan on visitas (cost=4.58..81.48 rows=20 width=0) (actual time=0.072..0.326 rows=20 loops=1)
Recheck Cond: (cliente_id = 4321)
Heap Blocks: exact=20
-> Bitmap Index Scan on visitas_cliente_idx (cost=0.00..4.58 rows=20 width=0) (actual time=0.051..0.051 rows=20 loops=1)
Index Cond: (cliente_id = 4321)
Planning Time: 0.407 ms
Execution Time: 0.427 ms
(8 rows)Ahora va directo a las 20 filas que cumplen (Bitmap Index Scan): 0.43 ms, unas 44 veces menos. El índice ocupa espacio y hace un poco más lentas las escrituras, porque cada INSERT o UPDATE también lo actualiza:
psql -d tienda -c "SELECT pg_size_pretty(pg_relation_size('visitas')) AS tabla, pg_size_pretty(pg_relation_size('visitas_cliente_idx')) AS indice;"salida tabla | indice -------+--------- 50 MB | 7600 kB (1 row)
Un detalle que rompe muchos índices: si en la condición la columna aparece dentro de una operación o una función, el índice ya no sirve, porque está ordenado por cliente_id, no por cliente_id + 0. Lo mismo pasa con lower(email) o date(momento), que necesitan un índice sobre esa expresión:
psql -d tienda -c "EXPLAIN ANALYZE SELECT count(*) FROM visitas WHERE cliente_id + 0 = 4321;"
salida QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=13655.42..13655.43 rows=1 width=8) (actual time=17.975..20.522 rows=1 loops=1)
-> Gather (cost=13655.21..13655.42 rows=2 width=8) (actual time=17.865..20.517 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=12655.21..12655.22 rows=1 width=8) (actual time=15.532..15.533 rows=1 loops=3)
-> Parallel Seq Scan on visitas (cost=0.00..12650.00 rows=2083 width=0) (actual time=1.091..15.529 rows=7 loops=3)
Filter: ((cliente_id + 0) = 4321)
Rows Removed by Filter: 333327
Planning Time: 0.343 ms
Execution Time: 20.649 ms
(10 rows)Reglas prácticas: indexa las columnas que usas para buscar, unir tablas (JOIN) y ordenar; las claves primarias y las UNIQUE ya llevan el suyo, pero las claves foráneas no; y mide antes y después con EXPLAIN ANALYZE, porque el planificador a veces prefiere leer la tabla entera, y con razón. En una tabla en uso, crea los índices con CREATE INDEX CONCURRENTLY, que tarda más pero no bloquea las escrituras. El curso de SQL tiene una lección sobre índices y rendimiento con más casos.
Transacciones y bloqueos
Una transacción agrupa varias órdenes para que se apliquen todas o ninguna. Entre BEGIN y COMMIT los cambios son provisionales y solo los ve tu sesión; con ROLLBACK desaparecen:
psql -d tienda -c 'BEGIN' -c 'DELETE FROM detalle' -c 'SELECT count(*) FROM detalle' -c 'ROLLBACK' -c 'SELECT count(*) FROM detalle'
salidaBEGIN
DELETE 41
count
-------
0
(1 row)
ROLLBACK
count
-------
41
(1 row)Cuando dos sesiones quieren cambiar la misma fila, la segunda espera a que la primera termine. Lo provocamos: la sesión 1 cambia el stock del producto 1 y tarda seis segundos en confirmar; un segundo después, la sesión 2 intenta cambiar la misma fila; y una tercera mira qué pasa con pg_stat_activity:
# Dos sesiones cambian el mismo producto: la segunda espera a que la primera termine. cd ~ psql -d tienda -c 'BEGIN' -c 'UPDATE productos SET stock = stock - 1 WHERE id = 1' -c 'SELECT pg_sleep(6)' -c 'COMMIT' > s1.txt 2>&1 & sleep 1 psql -d tienda -c '\timing on' -c 'UPDATE productos SET stock = stock - 1 WHERE id = 1' > s2.txt 2>&1 & sleep 1 echo '== mientras tanto, una tercera sesión mira quién espera a quién ==' psql -d tienda -c "SELECT pid, pg_blocking_pids(pid) AS bloqueado_por, wait_event_type AS espera, state, left(query, 48) AS consulta FROM pg_stat_activity WHERE datname = 'tienda' AND pid <> pg_backend_pid() ORDER BY pid" wait echo '== sesión 1 =='; cat s1.txt; echo '== sesión 2 =='; cat s2.txt; rm -f s1.txt s2.txt
salida== mientras tanto, una tercera sesión mira quién espera a quién ==
pid | bloqueado_por | espera | state | consulta
-----+---------------+---------+--------+--------------------------------------------------
648 | {} | Timeout | active | SELECT pg_sleep(6)
651 | {648} | Lock | active | UPDATE productos SET stock = stock - 1 WHERE id
(2 rows)
== sesión 1 ==
BEGIN
UPDATE 1
pg_sleep
----------
(1 row)
COMMIT
== sesión 2 ==
Timing is on.
UPDATE 1
Time: 5007.490 ms (00:05.007)La columna bloqueado_por dice que la 651 espera a la 648, y la sesión 2 tardó 5 segundos en un UPDATE que normalmente es instantáneo. Leer no bloquea: un SELECT nunca espera a un UPDATE ni al revés, porque PostgreSQL guarda varias versiones de cada fila (eso se llama MVCC). Las esperas vienen de escribir lo mismo a la vez o de cambiar la estructura de una tabla mientras se usa.
Si una sesión prefiere fallar a esperar indefinidamente, lock_timeout pone un límite:
psql -d tienda -c "SET lock_timeout = '2s'" -c '\timing on' -c 'UPDATE productos SET stock = stock - 1 WHERE id = 1'
salidaSET Timing is on. ERROR: canceling statement due to lock timeout CONTEXT: while updating tuple (0,18) in relation "productos" Time: 2002.464 ms (00:02.002)
Dos consejos que evitan la mayoría de los problemas: las transacciones deben ser cortas (nada de abrir una y esperar a que el usuario haga clic), y una sesión que se quedó «idle in transaction» en pg_stat_activity retiene bloqueos hasta que termine; SELECT pg_terminate_backend(pid) la corta si hace falta.
Respaldos con pg_dump
pg_dump saca una copia coherente de una base mientras está en uso, sin detener a nadie. Usa el formato custom (-Fc): va comprimido y luego permite restaurar todo o solo partes:
mkdir -p respaldos && pg_dump -Fc -d tienda -f respaldos/tienda.dump && ls -lh respaldos/
salidatotal 7.5M -rw-r--r-- 1 ana ana 7.5M Nov 7 17:55 tienda.dump
pg_restore -l lista lo que contiene sin restaurar nada. Fíjate en las líneas ACL: el respaldo también guarda los permisos de cada tabla:
pg_restore -l respaldos/tienda.dump
salida217; 1259 16387 TABLE public categorias ana 3492; 0 0 ACL public TABLE categorias ana 218; 1259 16394 TABLE public clientes ana 3493; 0 0 ACL public TABLE clientes ana 223; 1259 16451 TABLE public cupones ana 3494; 0 0 ACL public TABLE cupones ana 221; 1259 16423 TABLE public detalle ana 3495; 0 0 ACL public TABLE detalle ana 222; 1259 16439 TABLE public empleados ana 3496; 0 0 ACL public TABLE empleados ana 224; 1259 16459 TABLE public envios ana 3497; 0 0 ACL public TABLE envios ana 220; 1259 16413 TABLE public pedidos ana 3498; 0 0 ACL public TABLE pedidos ana 219; 1259 16401 TABLE public productos ana 3499; 0 0 ACL public TABLE productos ana 3500; 0 0 ACL public COLUMN productos.stock ana 225; 1259 16465 TABLE public visitas ana 3501; 0 0 ACL public TABLE visitas ana 3476; 0 16387 TABLE DATA public categorias ana
Dos variantes útiles: una sola tabla, y solo la estructura, sin datos (para crear una base vacía igual o revisar cambios):
pg_dump -Fc -d tienda -t clientes -f respaldos/clientes.dump pg_restore -l respaldos/clientes.dump
salida218; 1259 16394 TABLE public clientes ana 3452; 0 0 ACL public TABLE clientes ana 3444; 0 16394 TABLE DATA public clientes ana 3296; 2606 16400 CONSTRAINT public clientes clientes_email_key ana 3298; 2606 16398 CONSTRAINT public clientes clientes_pkey ana
pg_dump -d tienda --schema-only | grep -E '^CREATE (TABLE|INDEX)'
salidaCREATE TABLE public.categorias ( CREATE TABLE public.clientes ( CREATE TABLE public.cupones ( CREATE TABLE public.detalle ( CREATE TABLE public.empleados ( CREATE TABLE public.envios ( CREATE TABLE public.pedidos ( CREATE TABLE public.productos ( CREATE TABLE public.visitas ( CREATE INDEX visitas_cliente_idx ON public.visitas USING btree (cliente_id);
Un detalle que se olvida: pg_dump respalda una base, pero los roles no son de ninguna base, son del clúster. Se respaldan aparte con pg_dumpall --globals-only (aquí ocultamos las huellas de las contraseñas):
sudo -u postgres pg_dumpall --globals-only
salidaCREATE ROLE ana; ALTER ROLE ana WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE app_tienda; ALTER ROLE app_tienda WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$…'; CREATE ROLE curioso; ALTER ROLE curioso WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$…'; CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS; CREATE ROLE reportes; ALTER ROLE reportes WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$…';
Restaurar y comprobar la restauración
Un respaldo que nunca se ha restaurado no es un respaldo, es una esperanza. Lo restauramos en una base nueva, sin tocar la original:
createdb tienda_prueba pg_restore -d tienda_prueba respaldos/tienda.dump echo "código de salida: $?"
salidacódigo de salida: 0
Que no dé errores no basta. Comparamos lo que importa: cuántas filas tiene cada tabla y una suma de dinero, en las dos bases:
SELECT 'categorias' AS tabla, count(*) FROM categorias UNION ALL SELECT 'clientes', count(*) FROM clientes UNION ALL SELECT 'productos', count(*) FROM productos UNION ALL SELECT 'pedidos', count(*) FROM pedidos UNION ALL SELECT 'detalle', count(*) FROM detalle UNION ALL SELECT 'visitas', count(*) FROM visitas UNION ALL SELECT 'total vendido', round(sum(cantidad * precio_unitario)) FROM detalle;
for b in tienda tienda_prueba; do psql -d $b -Atf comparar.sql > $b.txt; done diff tienda.txt tienda_prueba.txt && echo 'IGUALES: la restauración tiene los mismos datos'
salidatienda | tienda_prueba categorias|5 · categorias|5 clientes|12 · clientes|12 productos|15 · productos|15 pedidos|22 · pedidos|22 detalle|41 · detalle|41 visitas|1000000 · visitas|1000000 total vendido|15212 · total vendido|15212 IGUALES: la restauración tiene los mismos datos
Y los permisos venían en el respaldo: reportes puede leer la copia igual que la original:
psql -h localhost -U reportes -d tienda_prueba -c 'SELECT count(*) FROM clientes'
salida count
-------
12
(1 row)Restaurar en otro servidor tiene una trampa: los roles. Llevamos el respaldo al servidor con PostgreSQL 18, que todavía no tenía los roles de la tienda:
createdb -U postgres tienda2 pg_restore -U postgres -d tienda2 tienda.dump 2> errores.txt echo "código de salida: $?"
salidacódigo de salida: 1 pg_restore: error: could not execute query: ERROR: role "ana" does not exist Command was: ALTER TABLE public.categorias OWNER TO ana; pg_restore: error: could not execute query: ERROR: role "ana" does not exist … pg_restore: warning: errors ignored on restore: 20
Los datos se cargan, pero las tablas quedan a nombre de postgres y se pierden los permisos: veinte errores que se pasan por alto si no se mira el código de salida. El orden correcto es restaurar primero los roles y después la base (el error de postgres es normal: ese rol ya existía):
psql -U postgres -f globales.sql createdb -U postgres -O ana tienda pg_restore -U postgres -d tienda tienda.dump echo "código de salida: $?" psql -U postgres -d tienda -Atc 'SELECT version()' -Atc 'SELECT count(*) FROM visitas' -Atc 'SELECT round(sum(cantidad * precio_unitario)) FROM detalle'
salidapsql:globales.sql:22: ERROR: role "postgres" already exists código de salida: 0 PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit 1000000 15212
Si en el destino no quieres los mismos dueños y permisos, pg_restore --no-owner --no-acl deja todo a nombre del rol que restaura.
Respaldo diario automático, con rotación
Un respaldo manual se olvida. Lo automatizamos con un script, un servicio y un temporizador de systemd, como en la guía de systemd y cron. El script guarda la copia con la fecha en el nombre, comprueba que el archivo se puede leer antes de darlo por bueno y borra las de más de siete días:
#!/bin/bash # Respaldo diario de la base «tienda»: formato custom, comprobación mínima y se quedan los últimos 7 días. set -euo pipefail DIR=/var/backups/postgresql ARCH="$DIR/tienda-$(date +%F-%H%M).dump" pg_dump -Fc -d tienda -f "$ARCH.tmp" pg_restore -l "$ARCH.tmp" > /dev/null # que el archivo se pueda leer mv "$ARCH.tmp" "$ARCH" find "$DIR" -name 'tienda-*.dump' -mtime +7 -print -delete echo "respaldo listo: $ARCH ($(du -h "$ARCH" | cut -f1))"
El servicio lo ejecuta como el usuario postgres, que entra por el socket con peer sin contraseña guardada en ningún archivo:
[Unit] Description=Respaldo de la base tienda After=postgresql.service [Service] Type=oneshot User=postgres ExecStart=/usr/local/bin/respaldo-tienda.sh
Y el temporizador lo lanza cada día a las 3:30, con hasta diez minutos de margen al azar; con Persistent=true, si el servidor estaba apagado a esa hora, lo hace al encender:
[Unit] Description=Respaldo diario de la base tienda [Timer] OnCalendar=*-*-* 03:30:00 RandomizedDelaySec=10min Persistent=true [Install] WantedBy=timers.target
sudo install -m 755 respaldo-tienda.sh /usr/local/bin/ sudo install -m 644 respaldo-tienda.service respaldo-tienda.timer /etc/systemd/system/ sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql sudo systemctl daemon-reload sudo systemctl enable --now respaldo-tienda.timer systemctl list-timers respaldo-tienda.timer
salidaCreated symlink '/etc/systemd/system/timers.target.wants/respaldo-tienda.timer' → '/etc/systemd/system/respaldo-tienda.timer'. NEXT LEFT LAST PASSED UNIT ACTIVATES Sun 2026-11-08 03:38:59 UTC 9h - - respaldo-tienda.timer respaldo-tienda.service 1 timers listed. Pass --all to see loaded but inactive timers, too.
No esperes a las 3:30 para saber si funciona: lánzalo a mano y revisa su registro:
sudo systemctl start respaldo-tienda.service sudo journalctl -u respaldo-tienda.service -o cat sudo ls -l /var/backups/postgresql
salidaStarting respaldo-tienda.service - Respaldo de la base tienda... respaldo listo: /var/backups/postgresql/tienda-2026-11-07-1756.dump (7.5M) respaldo-tienda.service: Deactivated successfully. Finished respaldo-tienda.service - Respaldo de la base tienda. total 7596 -rw-r--r-- 1 postgres postgres 7776993 Nov 7 17:56 tienda-2026-11-07-1756.dump
Para probar la rotación, fabricamos tres respaldos viejos, de hace 9, 8 y 3 días, y lo lanzamos otra vez. Borró los dos de más de siete días y dejó el de tres:
sudo ls /var/backups/postgresql sudo systemctl start respaldo-tienda.service sudo journalctl -u respaldo-tienda.service -o cat -n 7 sudo ls /var/backups/postgresql
salidatienda-2026-10-29-0330.dump tienda-2026-10-30-0330.dump tienda-2026-11-04-0330.dump tienda-2026-11-07-1756.dump Starting respaldo-tienda.service - Respaldo de la base tienda... /var/backups/postgresql/tienda-2026-10-30-0330.dump /var/backups/postgresql/tienda-2026-10-29-0330.dump respaldo listo: /var/backups/postgresql/tienda-2026-11-07-1757.dump (7.5M) respaldo-tienda.service: Deactivated successfully. Finished respaldo-tienda.service - Respaldo de la base tienda. tienda-2026-11-04-0330.dump tienda-2026-11-07-1756.dump tienda-2026-11-07-1757.dump
Falta una pieza: estas copias están en el mismo disco que la base. Si el servidor se pierde, se pierden con él. Añade un paso que las copie a otra máquina o a un almacenamiento externo (por ejemplo, con rsync sobre SSH) y, cada cierto tiempo, restaura una en una base de prueba como hicimos arriba. Para bases grandes o si necesitas volver a un minuto exacto, existen los respaldos físicos con archivado del WAL (pg_basebackup y herramientas como pgBackRest), que quedan fuera de esta guía.
VACUUM y autovacuum
Por cómo funciona MVCC, un UPDATE no sobrescribe la fila: escribe una versión nueva y deja la vieja marcada como muerta, por si alguna transacción todavía la necesita. Un DELETE, igual. Para verlo, apagamos un momento la limpieza automática de la tabla y cambiamos sus un millón de filas:
psql -d tienda -c 'ALTER TABLE visitas SET (autovacuum_enabled = off);'
psql -d tienda -c 'UPDATE visitas SET producto_id = producto_id;'
psql -d tienda -c "SELECT n_live_tup AS vivas, n_dead_tup AS muertas, pg_size_pretty(pg_relation_size('visitas')) AS tamano
FROM pg_stat_user_tables WHERE relname = 'visitas'"salidaALTER TABLE tamano -------- 50 MB (1 row) Timing is on. UPDATE 1000000 Time: 3242.451 ms (00:03.242) Timing is off. vivas | muertas | tamano ---------+---------+-------- 1000000 | 1000000 | 100 MB (1 row)
Un millón de filas vivas y un millón de muertas: la tabla pasó de 50 a 100 MB sin tener un solo dato nuevo. VACUUM recoge las filas muertas para que su espacio se reutilice:
psql -d tienda -c 'VACUUM (VERBOSE) visitas;'
psql -d tienda -c "SELECT n_live_tup AS vivas, n_dead_tup AS muertas, pg_size_pretty(pg_relation_size('visitas')) AS tamano, last_vacuum ..."salidaINFO: vacuuming "tienda.public.visitas" INFO: finished vacuuming "tienda.public.visitas": index scans: 1 pages: 0 removed, 12739 remain, 12739 scanned (100.00% of total) tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable removable cutoff: 846, which was 0 XIDs old when operation ended new relfrozenxid: 845, which is 76 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 6370 pages from table (50.00% of total) had 1000000 dead item identifiers removed index "visitas_cliente_idx": pages: 1896 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 1.458 MB/s, avg write rate: 1.603 MB/s buffer usage: 33779 hits, 20 misses, 22 dirtied WAL usage: 27368 records, 0 full page images, 7708909 bytes vivas | muertas | tamano | last_vacuum ---------+---------+--------+------------------------------- 1000000 | 0 | 100 MB | 2026-11-07 17:58:02.348106+00 (1 row)
Las muertas bajaron a cero, pero la tabla sigue midiendo 100 MB: VACUUM deja el espacio libre dentro de la tabla para las próximas escrituras, no se lo devuelve al disco. Eso solo lo hace VACUUM FULL, que reescribe la tabla entera y la bloquea por completo mientras tanto, así que se reserva para casos excepcionales:
psql -d tienda -c 'VACUUM FULL visitas;'
psql -d tienda -c "SELECT pg_size_pretty(pg_relation_size('visitas'))"
psql -d tienda -c 'ALTER TABLE visitas RESET (autovacuum_enabled);'salidaTiming is on. VACUUM Time: 560.662 ms Timing is off. tamano -------- 50 MB (1 row) ALTER TABLE
En el día a día no tienes que hacer nada de esto: el proceso autovacuum, que vimos en la lista de procesos, revisa cada minuto las tablas y limpia las que acumularon suficientes filas muertas (por defecto, 50 más el 20 % de la tabla). También actualiza las estadísticas que usa el planificador para elegir índices. Lo importante es no apagarlo y vigilar n_dead_tup si una tabla crece sin motivo:
psql -d tienda -Atc "SELECT name || ' = ' || setting FROM pg_settings WHERE name IN ('autovacuum', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_naptime') ORDER BY name"salidaautovacuum = on autovacuum_naptime = 60 autovacuum_vacuum_scale_factor = 0.2 autovacuum_vacuum_threshold = 50
Actualizar a una versión mayor
Las versiones menores (de 17.10 a 17.11) solo traen correcciones y se instalan con la actualización normal del sistema y un reinicio. Las mayores (de 17 a 18) cambian el formato interno de los datos y necesitan un paso de migración. Hay dos caminos:
- Volcar y restaurar. Respaldar con
pg_dumpall --globals-onlyypg_dump, instalar la versión nueva y restaurar, justo lo que hicimos arriba de la 17 a la 18. Es lo más sencillo y deja todo limpio, pero la base está fuera de servicio el tiempo que tarde, que con muchos gigas pueden ser horas. La documentación recomienda usar elpg_dumpde la versión nueva para leer la vieja. - pg_upgrade. Convierte la carpeta de datos en el sitio, en minutos aunque la base sea enorme (con
--linko--cloneni siquiera copia los archivos). Se ejecuta primero con--check, que solo revisa. En Debian y Ubuntu, con el repositorio del proyecto, el paquete traepg_upgradecluster 17 main, que hace todo el proceso y reconfigura los puertos.
En cualquiera de los dos: haz un respaldo antes, lee las notas de la versión nueva (sección «Migration»), prueba tu aplicación contra ella en otra máquina y ten a mano cómo volver atrás.
Errores típicos y cómo resolverlos
Todos estos mensajes salieron en el laboratorio. PostgreSQL también los anota en su registro, en Debian en /var/log/postgresql/, que es lo primero que hay que mirar cuando algo falla. La guía de logs y archivos de configuración explica cómo leer este tipo de registros:
sudo grep -E 'FATAL|ERROR' /var/log/postgresql/postgresql-17-main.log | tail -5
salida2026-11-07 17:54:34.059 UTC [481] reportes@tienda FATAL: no pg_hba.conf entry for host "172.30.7.50", user "reportes", database "tienda", SSL encryption 2026-11-07 17:54:34.062 UTC [482] reportes@tienda FATAL: no pg_hba.conf entry for host "172.30.7.50", user "reportes", database "tienda", no encryption 2026-11-07 17:54:34.530 UTC [501] reportes@tienda FATAL: no pg_hba.conf entry for host "172.30.7.50", user "reportes", database "tienda", no encryption 2026-11-07 17:55:20.768 UTC [673] ana@tienda ERROR: canceling statement due to lock timeout 2026-11-07 17:58:16.974 UTC [1102] reportes@tienda FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
Peer authentication failed for user "postgres": te conectas por el socket local con un rol distinto de tu usuario del sistema. Usasudo -u postgres psql, crea un rol con tu nombre de usuario, o conéctate por TCP con-h localhosty contraseña.role "ana" does not exist:psqlusa tu nombre de usuario del sistema como rol si no le dices otro con-U. Crea el rol o indica el correcto. Al restaurar en otro servidor, significa que faltan los roles: restaura antespg_dumpall --globals-only.password authentication failed for user: la contraseña no coincide, o el rol no tiene ninguna (los roles creados sinPASSWORDno pueden entrar por contraseña). Cámbiala con\password rolcomo superusuario.permission denied for table,for schemaofor database: el rol entró, pero le falta una de las cuatro puertas. El mensaje dice cuál; revisa con\dp tabla,\dn+y\l. Recuerda queGRANT ... ON ALL TABLESno incluye las tablas futuras.Connection refused: nadie escucha en esa dirección y puerto. O el servidor está detenido (systemctl status postgresql), olisten_addressesno incluye esa interfaz, o el puerto es otro. Si el mensaje habla del socket (No such file or directory), el servidor no está corriendo en esa máquina.no pg_hba.conf entry for host: llegó, pero ninguna línea depg_hba.confcoincide con esa combinación de base, rol, dirección y cifrado. El mensaje trae los cuatro datos; compáralos conpg_hba_file_rulesy recarga después de cambiar el archivo.remaining connection slots are reservedosorry, too many clients already: se acabaron las conexiones (max_connections, 100 por defecto, de las que unas pocas se reservan para el superusuario).
Así se ve el último, con max_connections bajado a 6 para provocarlo: tres reservadas para superusuario y tres para los demás, así que la cuarta conexión de reportes ya no entra:
bash conexiones.sh # abre tres conexiones de reportes que esperan 8 s y prueba una cuarta
salida== la cuarta conexión de reportes == psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute == quién está conectado (como postgres) == usename | count ----------+------- postgres | 1 reportes | 3 (2 rows)
Subir max_connections rara vez es la solución, porque cada conexión es un proceso con su propia memoria. Lo habitual es que la aplicación abra conexiones y no las cierre, o que no use un pool; para muchas conexiones cortas existe PgBouncer, que reparte pocas conexiones reales entre muchos clientes.
Y por último, el servidor detenido, por socket y por TCP:
sudo systemctl stop postgresql@17-main psql -d tienda -c 'SELECT 1' psql -h localhost -U reportes -d tienda -c 'SELECT 1'
salidapsql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket? psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? active
Hoja de referencia
# entrar sudo -u postgres psql # como superusuario, por el socket (peer) psql -h servidor -U reportes -d tienda # por red, con contraseña \l \c tienda \dt \d tabla \du \dp tabla \ddp \x \timing \conninfo \password \q # roles y permisos CREATE ROLE app LOGIN PASSWORD '…'; -- mejor: \password app REVOKE CONNECT ON DATABASE tienda FROM PUBLIC; GRANT CONNECT ON DATABASE tienda TO app; GRANT USAGE ON SCHEMA public TO app; -- solo si no es el dueño de la base GRANT SELECT, INSERT ON pedidos TO app; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reportes; # configuración (Debian: /etc/postgresql/17/main/) SHOW config_file; SHOW hba_file; SELECT * FROM pg_hba_file_rules; SELECT pg_reload_conf(); # pg_hba.conf y casi todo postgresql.conf sudo systemctl restart postgresql@17-main # listen_addresses, max_connections, shared_buffers # rendimiento y bloqueos EXPLAIN ANALYZE SELECT …; CREATE INDEX CONCURRENTLY idx ON tabla (columna); SELECT pid, pg_blocking_pids(pid), wait_event_type, query FROM pg_stat_activity; SET lock_timeout = '2s'; # respaldos pg_dump -Fc -d tienda -f tienda.dump # una base pg_dump -Fc -d tienda -t clientes -f c.dump # una tabla pg_dump -d tienda --schema-only # solo la estructura sudo -u postgres pg_dumpall --globals-only > roles.sql createdb tienda_prueba && pg_restore -d tienda_prueba tienda.dump pg_restore -l tienda.dump # ver qué contiene # mantenimiento SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY 2 DESC; VACUUM (VERBOSE, ANALYZE) tabla;
Si administras el servidor por tu cuenta, te servirán también las guías de comandos de Linux, SSH y systemd y cron; para practicar consultas, el curso de SQL y su editor en el navegador, y para levantar PostgreSQL en contenedores, la de Docker.
Fuentes: documentación de PostgreSQL sobre autenticación de clientes y pg_hba.conf, GRANT, ALTER DEFAULT PRIVILEGES, esquemas y el cambio de public en la versión 15, EXPLAIN, bloqueos, pg_dump, pg_restore, VACUUM y actualizar de versión; política de versiones; imagen oficial de Docker. Laboratorio: Debian 13, PostgreSQL 17.11 y 18.6, OpenSSH y systemd 257, en contenedores desechables.
Imagen de portada: «Elephants rushing to water (Etosha, 2013)», de Yathin S Krishnappa, con licencia CC BY-SA 3.0; el elefante es la mascota de PostgreSQL. Postgres, PostgreSQL y el logotipo Slonik son marcas registradas de la PostgreSQL Community Association of Canada; esta guía no está afiliada a ella.