Instalación, psql y pgAdmin

En esta lección instalarás PostgreSQL en Windows, Ubuntu, macOS o Docker, entenderás cómo funciona su autenticación (el famoso archivo pg_hba.conf) y aprenderás a manejar psql, el cliente de terminal, además de pgAdmin y DBeaver.

Concepto

Igual que MySQL, PostgreSQL es un servidor (postgres) que escucha conexiones, por defecto en el puerto 5432. Un servidor de PostgreSQL se llama cluster y puede contener muchas bases de datos.

Cluster PostgreSQL (puerto 5432)
├── base de datos "postgres"   ← creada por defecto (para administración)
├── base de datos "tienda"
│   ├── esquema public
│   │   ├── tabla clientes
│   │   └── tabla productos
│   └── esquema reportes
├── base de datos "template1"  ← plantilla para nuevas bases
└── roles (usuarios): postgres, tienda_app...

Diferencia importante con MySQL: en PostgreSQL te conectas a una base de datos concreta. No puedes hacer un JOIN entre tablas de dos bases distintas en una misma consulta; para separar áreas dentro de una base se usan esquemas (los verás en la siguiente lección).

Instalación

Windows

1. Descarga el instalador de EDB desde postgresql.org/download/windows/.
2. Ejecútalo y deja los componentes: PostgreSQL Server, pgAdmin 4, Command Line Tools (Stack Builder es opcional).
3. Carpeta de datos: deja la sugerida.
4. Define la contraseña del superusuario postgres (guárdala bien).
5. Puerto: 5432. Locale: Default o Spanish, Mexico.
6. Termina la instalación.

Agrega las herramientas al PATH:

[Environment]::SetEnvironmentVariable(
  "Path",
  $env:Path + ";C:\Program Files\PostgreSQL\17\bin",
  "User"
)
# Abre una terminal nueva:
psql --version
psql -U postgres

El servicio se llama postgresql-x64-17:

Get-Service postgresql*
Restart-Service postgresql-x64-17     # PowerShell como administrador

Acentos en la consola de Windows

Si en psql ves caracteres raros o el aviso "Console code page differs from Windows code page", ejecuta chcp 65001 antes de abrir psql para usar UTF-8 en la consola.

Ubuntu / Debian

# Versión de los repositorios de Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# (Opcional) Última versión desde el repositorio oficial de PostgreSQL
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-17

sudo systemctl status postgresql
sudo systemctl enable postgresql

La instalación crea el usuario de sistema postgres y el rol de base de datos postgres. Por defecto se entra con autenticación peer (el usuario del sistema debe coincidir con el rol):

sudo -u postgres psql

-- Crear un rol para ti y una base de datos
CREATE ROLE erick WITH LOGIN CREATEDB PASSWORD 'Clave-Segura-2024!';
CREATE DATABASE tienda OWNER erick;
\q

psql -h localhost -U erick -d tienda

macOS

La forma más sencilla es Postgres.app (postgresapp.com): arrastras la app a Aplicaciones, la abres y pulsas Initialize. Alternativa con Homebrew:

brew install postgresql@17
brew services start postgresql@17
psql postgres

Docker

docker run -d \
  --name pg-tienda \
  -e POSTGRES_USER=tienda \
  -e POSTGRES_PASSWORD=secreto \
  -e POSTGRES_DB=tienda \
  -p 127.0.0.1:5432:5432 \
  -v pg_tienda_datos:/var/lib/postgresql/data \
  postgres:17

docker exec -it pg-tienda psql -U tienda -d tienda

Con docker-compose.yml, incluyendo pgAdmin:

services:
  db:
    image: postgres:17
    restart: unless-stopped
    environment:
      POSTGRES_USER: tienda
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_DB: tienda
      TZ: America/Mexico_City
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pg_datos:/var/lib/postgresql/data
      - ./sql:/docker-entrypoint-initdb.d     # .sql que se ejecutan la primera vez
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U tienda -d tienda"]
      interval: 5s
      retries: 10

  pgadmin:
    image: dpage/pgadmin4:8
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@ejemplo.com
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
    ports:
      - "127.0.0.1:5050:80"
    depends_on:
      - db

volumes:
  pg_datos:

DB_PASSWORD=secreto
PGADMIN_PASSWORD=admin123

docker compose up -d
# pgAdmin en http://localhost:5050 → Add Server → Host: db, usuario tienda

Autenticación: pg_hba.conf

PostgreSQL decide quién puede conectarse, desde dónde y cómo con el archivo pg_hba.conf (host-based authentication).

# ¿Dónde está?
sudo -u postgres psql -c "SHOW hba_file;"
# Ubuntu: /etc/postgresql/17/main/pg_hba.conf

# TIPO   BASE      USUARIO    DIRECCIÓN         MÉTODO
local    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
host     tienda    tienda_app 10.0.0.15/32      scram-sha-256   ← servidor de la app

Método	Significado
peer	El usuario del sistema operativo debe llamarse igual que el rol (solo conexiones locales por socket)
scram-sha-256	Contraseña cifrada (el recomendado)
md5	Contraseña con hash antiguo (evítalo en instalaciones nuevas)
trust	Sin contraseña. Solo para pruebas locales muy controladas
reject	Rechaza explícitamente

Para aceptar conexiones de otra máquina también hay que cambiar listen_addresses en postgresql.conf:

# postgresql.conf
listen_addresses = 'localhost'          # por defecto: solo local
# listen_addresses = 'localhost,10.0.0.10'   # IP privada del servidor

sudo systemctl reload postgresql       # pg_hba.conf se recarga sin reiniciar
sudo systemctl restart postgresql      # listen_addresses requiere reinicio

Nunca uses trust ni 0.0.0.0/0 en producción

Una línea host all all 0.0.0.0/0 trust permite que cualquiera en internet entre sin contraseña. Hay bots que escanean el puerto 5432 constantemente. Limita por IP, usa scram-sha-256 y un firewall; para conectarte tú, usa un túnel SSH.

"FATAL: Peer authentication failed for user..."

Aparece al hacer psql -U tienda_app en Linux sin -h localhost: la conexión va por socket y aplica peer. Solución: psql -h localhost -U tienda_app -d tienda (usa la línea host ... scram-sha-256).

psql: el cliente de terminal

Conectarse

psql -h localhost -p 5432 -U tienda_app -d tienda

# Con una cadena de conexión (URL), como la que dan Supabase o Neon
psql "postgresql://tienda_app:secreto@localhost:5432/tienda"

Para no escribir la contraseña cada vez, crea ~/.pgpass (en Windows %APPDATA%\postgresql\pgpass.conf):

# host:puerto:base:usuario:contraseña
localhost:5432:tienda:tienda_app:secreto

chmod 600 ~/.pgpass

Metacomandos

Los comandos que empiezan con \ son de psql, no de SQL:

Comando	Hace
\l	Lista bases de datos
\c tienda	Conectarse a otra base
\dt	Lista tablas (\dt+ con tamaños)
\d productos	Describe una tabla: columnas, índices, FK, triggers
\dn	Lista esquemas
\du	Lista roles
\df	Lista funciones
\dv / \dm	Vistas / vistas materializadas
\di	Índices
\x	Alterna formato expandido (vertical)
\timing	Muestra el tiempo de cada consulta
\i archivo.sql	Ejecuta un script
\e	Abre la última consulta en tu editor
\copy	Importar/exportar CSV desde tu máquina
\conninfo	Datos de la conexión actual
\?	Ayuda de metacomandos
\h CREATE TABLE	Ayuda de sintaxis SQL
\q	Salir

tienda=> \d productos
                                Table "public.productos"
    Column    |     Type      | Collation | Nullable |           Default
--------------+---------------+-----------+----------+------------------------------
 id           | integer       |           | not null | generated always as identity
 nombre       | text          |           | not null |
 categoria_id | integer       |           |          |
 precio       | numeric(10,2) |           | not null |
 stock        | integer       |           | not null | 0
Indexes:
    "productos_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "productos_categoria_id_fkey" FOREIGN KEY (categoria_id) REFERENCES categorias(id)

El prompt te dice algo útil: tienda=> eres usuario normal; tienda=# eres superusuario (cuidado).

Ejecutar sin entrar

psql -d tienda -c "SELECT COUNT(*) FROM productos;"
psql -d tienda -f 01-esquema.sql
psql -d tienda -f 02-datos.sql -v ON_ERROR_STOP=1    # detenerse en el primer error

Configurar psql: ~/.psqlrc

\set QUIET 1
\pset null '∅'
\x auto
\timing on
\set PROMPT1 '%n@%m:%>/%/ %R%# '
\set HISTSIZE 5000
\unset QUIET

Con esto los NULL se ven como ∅, las filas anchas se muestran verticales automáticamente y siempre ves el tiempo de cada consulta.

pgAdmin

pgAdmin 4 es el cliente gráfico oficial (aplicación de escritorio o web).

1. Abre pgAdmin y define una contraseña maestra.
2. Clic derecho en Servers → Register → Server...
3. Pestaña General: nombre (Local). Pestaña Connection: host localhost, puerto 5432, usuario y contraseña.
4. Navega: Servers → Local → Databases → tienda → Schemas → public → Tables.

Funciones útiles:

• Query Tool (Alt + Shift + Q): editor SQL con autocompletado. F5 ejecuta.
• Explain / Explain Analyze gráfico (F7 / Shift + F7).
• View/Edit Data: ver y editar filas.
• ERD Tool: diagramas entidad-relación.
• Backup / Restore: interfaz para pg_dump y pg_restore.
• Dashboard: sesiones activas, transacciones por segundo, bloqueos.

DBeaver y otras herramientas

• DBeaver: cliente universal (ver Clientes de MySQL); excelente con PostgreSQL, incluidos diagramas y túneles SSH.
• TablePlus, Beekeeper Studio, DataGrip (de pago): alternativas populares.
• VS Code: extensiones PostgreSQL (Microsoft), SQLTools con driver de PostgreSQL.

Verificación final

SELECT version();
SHOW server_encoding;     -- UTF8
SHOW timezone;
SELECT current_user, current_database(), inet_server_port();

Ejercicios

1. Instala PostgreSQL con el método que prefieras y ejecuta SELECT version();.
2. Crea el rol tienda_app con contraseña y la base tienda con ese rol como dueño.
3. Carga el esquema y los datos del curso de SQL con psql -f y verifica con \dt y \d productos.
4. Escribe la línea de pg_hba.conf que permite al rol reportes conectarse a la base tienda solo desde la IP 192.168.1.50 con contraseña segura.

Ver solución

-- 2 (como superusuario: sudo -u postgres psql)
CREATE ROLE tienda_app WITH LOGIN PASSWORD 'Clave-Tienda-2024!';
CREATE DATABASE tienda OWNER tienda_app;

# 3
psql -h localhost -U tienda_app -d tienda -f 01-esquema.sql -v ON_ERROR_STOP=1
psql -h localhost -U tienda_app -d tienda -f 02-datos.sql -v ON_ERROR_STOP=1
psql -h localhost -U tienda_app -d tienda -c "\dt"

# 4
host    tienda    reportes    192.168.1.50/32    scram-sha-256

Después: sudo systemctl reload postgresql (y que listen_addresses incluya una interfaz alcanzable).

Resumen

• PostgreSQL escucha en el puerto 5432; un cluster contiene varias bases de datos y te conectas a una concreta.
• Instálalo con el instalador de EDB (Windows), apt (Ubuntu), Postgres.app/Homebrew (macOS) o Docker con volumen.
• pg_hba.conf controla quién entra y cómo: usa scram-sha-256, IPs concretas y nunca trust en producción.
• psql y sus metacomandos (\l, \c, \dt, \d, \du, \x, \i, \q) son imprescindibles.
• pgAdmin y DBeaver te dan editor SQL, EXPLAIN gráfico, diagramas y respaldos.