PostgreSQL guía

Desde el servidor vía usuario postgres

$ sudo -u postgres psql

Conectarse a una base de datos específica

$ psql -U usuario -h <tu-host> -p <puerto> -d nombre_db

Verificar versión instalada

$ psql --version

S: incluir objetos de sistema, +: detalle adicional

Crear base de datos

CREATE DATABASE analytics_db WITH OWNER db_admin;

Eliminar base de datos

DROP DATABASE IF EXISTS analytics_db;

Renombrar base de datos

ALTER DATABASE old_name RENAME TO new_name;

Ver base de datos actual

SELECT current_database();

Crear esquema (cada servicio con su propio esquema)

CREATE SCHEMA IF NOT EXISTS airflow_metadata;
CREATE SCHEMA IF NOT EXISTS superset_data;
CREATE SCHEMA IF NOT EXISTS analytics;

Listar esquemas

SELECT schema_name
FROM information_schema.schemata;

Eliminar esquema

DROP SCHEMA IF EXISTS old_schema CASCADE;

Crear tabla

CREATE TABLE analytics.dim_clientes (
  id         SERIAL PRIMARY KEY,
  nombre     VARCHAR(100) NOT NULL,
  segmento   VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW()
);

Crear tabla con clave foránea

CREATE TABLE analytics.fact_ventas (
  id          SERIAL PRIMARY KEY,
  cliente_id  INT REFERENCES analytics.dim_clientes(id),
  monto       NUMERIC(12,2),
  fecha       DATE NOT NULL
);

Eliminar tabla

DROP TABLE IF EXISTS analytics.fact_ventas CASCADE;

Agregar columna

ALTER TABLE analytics.dim_clientes
ADD COLUMN email VARCHAR(255);

Modificar tipo de columna

ALTER TABLE analytics.dim_clientes
ALTER COLUMN segmento TYPE VARCHAR(100);

Eliminar columna

ALTER TABLE analytics.dim_clientes
DROP COLUMN email;

Renombrar columna

ALTER TABLE analytics.dim_clientes
RENAME COLUMN segmento TO categoria;

Seleccionar todo

SELECT * FROM analytics.dim_clientes;

Filtrar con WHERE

SELECT nombre, segmento
FROM analytics.dim_clientes
WHERE segmento = 'Enterprise';

Ordenar y limitar

SELECT * FROM analytics.fact_ventas
ORDER BY fecha DESC
LIMIT 20;

Agregaciones

SELECT segmento, COUNT(*) AS total, SUM(monto) AS revenue
FROM analytics.fact_ventas v
JOIN analytics.dim_clientes c ON v.cliente_id = c.id
GROUP BY segmento
ORDER BY revenue DESC;

Insertar registros

INSERT INTO analytics.dim_clientes (nombre, segmento)
VALUES ('Acme Corp', 'Enterprise');

Insertar múltiples registros

INSERT INTO analytics.dim_clientes (nombre, segmento)
VALUES
  ('Cliente A', 'SMB'),
  ('Cliente B', 'Enterprise'),
  ('Cliente C', 'Gobierno');

Actualizar registros

UPDATE analytics.dim_clientes
SET segmento = 'VIP'
WHERE nombre = 'Acme Corp';

Eliminar registros

DELETE FROM analytics.dim_clientes
WHERE segmento = 'Inactivo';

Exportar tabla a CSV

\copy analytics.dim_clientes TO '/tmp/clientes.csv' CSV HEADER
\copy (SELECT * FROM analytics.fact_ventas WHERE fecha > '2026-01-01') TO '/tmp/ventas_2026.csv' CSV HEADER

Importar CSV a tabla

\copy analytics.dim_clientes(nombre, segmento) FROM '/tmp/clientes_nuevos.csv' CSV HEADER

Listar roles

SELECT rolname FROM pg_roles;

Crear usuario

CREATE USER analista WITH PASSWORD 'secure_password';

Eliminar usuario

DROP USER IF EXISTS analista;

Cambiar contraseña

ALTER ROLE analista WITH PASSWORD 'new_password';

Ver usuario actual

SELECT current_user;

Permisos completos en base de datos

GRANT ALL PRIVILEGES ON DATABASE analytics_db TO db_admin;

Permisos de lectura para analistas

GRANT CONNECT ON DATABASE analytics_db TO analista;
GRANT USAGE ON SCHEMA analytics TO analista;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analista;

Permisos de escritura para pipelines

GRANT USAGE ON SCHEMA analytics TO pipeline_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA analytics TO pipeline_user;

Permisos en funciones

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA analytics TO analista;

Aplicar permisos a tablas futuras

ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT SELECT ON TABLES TO analista;

Backup completo de todas las bases

$ pg_dumpall -U postgres > /backups/all_databases.sql

Backup de una base específica

$ pg_dump -U postgres -d analytics_db -f /backups/analytics_db.sql

Opciones frecuentes de pg_dump

Restaurar con psql (formato plain)

$ psql -U postgres analytics_db < /backups/analytics_db.sql

Restaurar con pg_restore (formato custom/tar)

$ pg_restore -U postgres -d analytics_db -c /backups/analytics_db.dump

Opciones frecuentes de pg_restore

Localizar archivo de configuración

$ psql -U postgres -c 'SHOW config_file'

Habilitar conexiones remotas en postgresql.conf

listen_addresses = '*'

Agregar reglas en pg_hba.conf

# Acceso desde red interna (ajustar CIDR a tu red)
host  all  all  <tu-red-cidr>  md5

Reiniciar servicio

$ sudo systemctl restart postgresql

Conexiones activas

SELECT pid, usename, datname, state, query
FROM pg_stat_activity
WHERE state = 'active';

Tamaño de bases de datos

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Tamaño de tablas en un esquema

SELECT tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname = 'analytics'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Queries lentas (requiere pg_stat_statements)

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Cada servicio debe usar su propio esquema, nunca public

-- Verificar esquemas existentes
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_catalog', 'information_schema');

Patrón de naming

Verificar que todas las bases existen

$ psql -U postgres -c "\l" | grep -E "airflow|superset|openmetadata|analytics"

Verificar conexión a una base específica

$ psql -U postgres -d airflow_db -c "SELECT 1 AS test;"

Contar tablas por esquema

SELECT schemaname, COUNT(*) AS tablas
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
GROUP BY schemaname
ORDER BY tablas DESC;

Instalar extensión

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";