Erori de conexiune la baza de date in n8n: Ghid complet de troubleshooting

# Verifica daca PostgreSQL ruleaza
sudo systemctl status postgresql
# sau pentru Docker
docker ps | grep postgres
 
# Porneste PostgreSQL
sudo systemctl start postgresql
# sau Docker
docker start postgres-container

# docker-compose.yml - Configurare gresita
services:
  n8n:
    environment:
      - DB_POSTGRESDB_HOST=localhost  # ❌ Gresit in Docker
 
  postgres:
    image: postgres:15
 
# Configurare corecta
services:
  n8n:
    environment:
      - DB_POSTGRESDB_HOST=postgres  # ✅ Foloseste numele serviciului
    depends_on:
      - postgres
 
  postgres:
    image: postgres:15

# Verifica daca portul este accesibil
nc -zv database-host 5432
 
# Deschide portul (Ubuntu/Debian)
sudo ufw allow 5432/tcp
 
# Verifica daca PostgreSQL asculta
sudo ss -tlnp | grep 5432

# Testeaza conexiunea PostgreSQL manual
psql -h localhost -U n8n -d n8n_db -W
 
# Testeaza conexiunea MySQL
mysql -h localhost -u n8n -p n8n_db
 
# Testeaza conexiunea MongoDB
mongosh "mongodb://n8n:password@localhost:27017/n8n_db"

-- PostgreSQL
ALTER USER n8n WITH PASSWORD 'new_password';
 
-- MySQL
ALTER USER 'n8n'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

# Editeaza pg_hba.conf
sudo nano /etc/postgresql/15/main/pg_hba.conf
 
# Schimba 'peer' in 'md5' sau 'scram-sha-256' pentru autentificare cu parola
# host    all    all    0.0.0.0/0    scram-sha-256
 
# Reporneste PostgreSQL
sudo systemctl restart postgresql

// Configurare nod n8n - mareste timeout-ul
{
  "operation": "executeQuery",
  "connectionTimeout": 60000,  // 60 secunde
  "requestTimeout": 120000     // 2 minute pentru query
}

-- PostgreSQL: Verifica conexiunile active
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
 
-- MySQL: Verifica query-urile in executie
SHOW PROCESSLIST;
 
-- Opreste query-urile de lunga durata daca e necesar
-- PostgreSQL
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE duration > interval '5 minutes';

// Nod PostgreSQL n8n - dezactiveaza SSL pentru dev local
{
  "ssl": false
}
 
// Sau configureaza SSL corect
{
  "ssl": {
    "rejectUnauthorized": false,
    "ca": "-----BEGIN CERTIFICATE-----..."
  }
}

-- PostgreSQL: Verifica limita curenta
SHOW max_connections;
 
-- Creste in postgresql.conf
-- max_connections = 200
 
-- MySQL: Verifica limita
SHOW VARIABLES LIKE 'max_connections';
 
-- Creste
SET GLOBAL max_connections = 200;

# Variabile de mediu n8n
DB_POSTGRESDB_POOL_SIZE=10  # Implicit este 5

-- PostgreSQL: Gaseste conexiunile inactive
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < now() - interval '10 minutes';
 
-- Termina conexiunile inactive
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < now() - interval '30 minutes';

// Configurare nod PostgreSQL
{
  "ssl": true,
  "sslMode": "require"
}

# Variabila de mediu n8n
DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=require

// Configurare nod cu certificate
{
  "ssl": {
    "ca": "{{ $env.DB_CA_CERT }}",
    "cert": "{{ $env.DB_CLIENT_CERT }}",
    "key": "{{ $env.DB_CLIENT_KEY }}",
    "rejectUnauthorized": true
  }
}

-- PostgreSQL
CREATE DATABASE n8n_production;
GRANT ALL PRIVILEGES ON DATABASE n8n_production TO n8n;
 
-- MySQL
CREATE DATABASE n8n_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON n8n_production.* TO 'n8n'@'%';
FLUSH PRIVILEGES;

-- PostgreSQL: Acorda toate permisiunile pe schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO n8n;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO n8n;
GRANT USAGE ON SCHEMA public TO n8n;
 
-- Pentru tabele viitoare
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO n8n;
 
-- MySQL
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON n8n_db.* TO 'n8n'@'%';
FLUSH PRIVILEGES;

// Function node cu retry
const maxRetries = 3;
let attempt = 0;
 
while (attempt < maxRetries) {
  try {
    // Operatiunea ta de baza de date
    const result = await $node["Postgres"].execute();
    return result;
  } catch (error) {
    if (error.message.includes('serialize') || error.message.includes('deadlock')) {
      attempt++;
      await new Promise(r => setTimeout(r, 100 * Math.pow(2, attempt)));
      continue;
    }
    throw error;
  }
}

-- Obtine lock inainte de sectiunea critica
SELECT pg_advisory_lock(12345);
 
-- Operatiunile tale aici
 
-- Elibereaza lock-ul
SELECT pg_advisory_unlock(12345);

# docker-compose.yml
version: '3.8'
services:
  n8n:
    image: n8nio/n8n
    environment:
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=n8n
      - DB_POSTGRESDB_USER=n8n
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_PASSWORD}
    networks:
      - n8n-network
    depends_on:
      postgres:
        condition: service_healthy
 
  postgres:
    image: postgres:15
    environment:
      - POSTGRES_USER=n8n
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=n8n
    volumes:
      - postgres-data:/var/lib/postgresql/data
    networks:
      - n8n-network
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U n8n"]
      interval: 10s
      timeout: 5s
      retries: 5
 
networks:
  n8n-network:
    driver: bridge
 
volumes:
  postgres-data:

# Verifica daca containerele se vad reciproc
docker exec n8n ping postgres
 
# Verifica rezolutia DNS
docker exec n8n nslookup postgres
 
# Testeaza conexiunea din containerul n8n
docker exec n8n nc -zv postgres 5432

# PostgreSQL
DATABASE_URL=postgres://user:password@host:5432/database?sslmode=require
 
# MySQL
DATABASE_URL=mysql://user:password@host:3306/database
 
# MongoDB
DATABASE_URL=mongodb://user:password@host:27017/database?authSource=admin
 
# Cu caractere speciale in parola (URL encode)
# Parola: p@ss#word! devine p%40ss%23word%21
DATABASE_URL=postgres://user:p%40ss%23word%21@host:5432/database

# Testeaza PostgreSQL
PGPASSWORD=password psql -h host -U user -d database -c "SELECT 1"
 
# Testeaza MySQL
mysql -h host -u user -ppassword database -e "SELECT 1"
 
# Testeaza MongoDB
mongosh "mongodb://user:password@host:27017/database" --eval "db.runCommand({ping: 1})"
 
# Mod debug n8n
N8N_LOG_LEVEL=debug n8n start