Configurar PostgreSQL en Linux

Configurar PostgreSQL en Linux

¿Qué es PostgreSQL?

PostgreSQL es un sistema de gestión de bases de datos relacionales de código abierto, potente y avanzado. Es una alternativa robusta a MySQL, especialmente útil para aplicaciones que requieren características avanzadas como tipos de datos personalizados, funciones complejas y transacciones ACID.

Instalar PostgreSQL

En Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib

En CentOS/RHEL:

sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb

En Fedora:

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

Iniciar y habilitar PostgreSQL

sudo systemctl start postgresql
sudo systemctl enable postgresql

Verificá que esté corriendo:

sudo systemctl status postgresql

Acceder a PostgreSQL

PostgreSQL crea un usuario llamado postgres por defecto. Para acceder:

sudo -u postgres psql

O cambiá al usuario postgres primero:

sudo su - postgres
psql

Configuración inicial

1. Cambiar contraseña del usuario postgres

sudo -u postgres psql
ALTER USER postgres PASSWORD 'tu_contraseña_segura';
\q

2. Crear un nuevo usuario y base de datos

sudo -u postgres createuser --interactive nombre_usuario
sudo -u postgres createdb nombre_base_datos -O nombre_usuario

O desde psql:

sudo -u postgres psql
CREATE USER nombre_usuario WITH PASSWORD 'contraseña';
CREATE DATABASE nombre_base_datos OWNER nombre_usuario;
GRANT ALL PRIVILEGES ON DATABASE nombre_base_datos TO nombre_usuario;
\q

Configurar acceso remoto (opcional)

1. Editar archivo de configuración

sudo nano /etc/postgresql/[versión]/main/postgresql.conf

Buscá y modificá:

listen_addresses = '*'

2. Configurar autenticación

sudo nano /etc/postgresql/[versión]/main/pg_hba.conf

Agregá al final del archivo:

# Permitir conexiones remotas
host    all             all             0.0.0.0/0               md5

3. Reiniciar PostgreSQL

sudo systemctl restart postgresql

4. Configurar firewall

sudo ufw allow 5432/tcp

Comandos básicos de PostgreSQL

Conectarse a una base de datos:

psql -U nombre_usuario -d nombre_base_datos

Comandos útiles dentro de psql:

• \l - Listar todas las bases de datos
• \c nombre_base_datos - Conectarse a una base de datos
• \dt - Listar todas las tablas
• \d nombre_tabla - Describir una tabla
• \du - Listar todos los usuarios
• \q - Salir de psql
• \h - Ayuda de comandos SQL
• \? - Ayuda de comandos psql

Crear y gestionar tablas

-- Crear una tabla
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insertar datos
INSERT INTO usuarios (nombre, email) VALUES ('Juan', '[email protected]');

-- Consultar datos
SELECT * FROM usuarios;

-- Actualizar datos
UPDATE usuarios SET nombre = 'Juan Pérez' WHERE id = 1;

-- Eliminar datos
DELETE FROM usuarios WHERE id = 1;

Gestionar usuarios y permisos

-- Crear usuario
CREATE USER nuevo_usuario WITH PASSWORD 'contraseña';

-- Otorgar privilegios
GRANT ALL PRIVILEGES ON DATABASE nombre_base_datos TO nuevo_usuario;

-- Otorgar privilegios en tablas específicas
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO nuevo_usuario;

-- Revocar privilegios
REVOKE ALL PRIVILEGES ON DATABASE nombre_base_datos FROM nuevo_usuario;

-- Eliminar usuario
DROP USER nombre_usuario;

Hacer backup y restaurar

Backup completo:

sudo -u postgres pg_dump nombre_base_datos > backup.sql

Backup comprimido:

sudo -u postgres pg_dump -Fc nombre_base_datos > backup.dump

Restaurar backup:

sudo -u postgres psql nombre_base_datos < backup.sql

Restaurar backup comprimido:

sudo -u postgres pg_restore -d nombre_base_datos backup.dump

Optimizar PostgreSQL

1. Configurar parámetros de rendimiento

sudo nano /etc/postgresql/[versión]/main/postgresql.conf

Parámetros importantes:

shared_buffers = 256MB          # Memoria compartida
effective_cache_size = 1GB       # Memoria disponible para caché
maintenance_work_mem = 64MB     # Memoria para operaciones de mantenimiento
work_mem = 16MB                 # Memoria para operaciones de ordenamiento

2. Analizar y optimizar tablas

ANALYZE nombre_tabla;
VACUUM nombre_tabla;
VACUUM FULL nombre_tabla;  # Reorganiza completamente la tabla

Verificar estado y logs

Ver procesos activos:

sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"

Ver tamaño de bases de datos:

sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('nombre_base_datos'));"

Ver logs:

sudo tail -f /var/log/postgresql/postgresql-[versión]-main.log

Problemas comunes

Error: "peer authentication failed"

• Modificá pg_hba.conf para usar md5 en lugar de peer.
• Reiniciá PostgreSQL después del cambio.

Error: "could not connect to server"

• Verificá que PostgreSQL esté corriendo: sudo systemctl status postgresql
• Revisá la configuración de listen_addresses.
• Verificá el firewall.

Error: "permission denied"

• Verificá los permisos del usuario en la base de datos.
• Revisá la configuración de pg_hba.conf.

Diferencias principales con MySQL

• PostgreSQL usa SERIAL en lugar de AUTO_INCREMENT
• Los comandos terminan con ; pero también hay comandos especiales que empiezan con \
• PostgreSQL es más estricto con los tipos de datos
• PostgreSQL soporta tipos de datos más avanzados (arrays, JSON, etc.)

¿Necesitás ayuda?

Si tenés problemas configurando PostgreSQL, abrí un ticket desde el panel de billing.baires.host o contactanos por soporte.

También podés escribirnos por nuestras redes:

• Instagram: @baires_host
• Discord (soporte activo): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host