p24-infra Docs

n8n-postgresql-operations

10 min read

n8n PostgreSQL Operations

Architecture

Design: one postgres per VPS

Each VPS runs a single postgres:16-alpine container shared by all local services that need a relational database. n8n gets its own database (n8n) inside that container. Future services on the same VPS get their own databases in the same container — not separate postgres instances.

This is different from giving each service its own postgres container, which wastes RAM and makes cross-service maintenance harder.

Why not production Supabase

n8n is write-heavy: every workflow execution writes multiple rows to the executions table. Routing that traffic to the shared Supabase production instance creates lock contention risk and could affect the operational platform (et-operational-platform). n8n failures should not cascade to production.

Why not SQLite

SQLite was the default and caused the original problem on vps-h1: the database.sqlite file grew to 20.3 GB because pruning was never enabled. Beyond disk space, SQLite has structural limitations for n8n:

  • File-level lock during prune operations blocks all other writes
  • No concurrent writes — execution bursts queue up behind each other
  • VACUUM to reclaim space after deletions rewrites the entire file on disk

Why not self-hosted Supabase

Self-hosted Supabase requires roughly 1 GB RAM overhead and pulls in PostgREST, GoTrue, Kong, and other components that n8n does not use. On a 2-vCPU / 8 GB VPS already running n8n, Traefik, WAHA, and node-exporter, that overhead is not justified.

Fresh Installation on a New VPS

Use this procedure when adding n8n to a new VPS that does not yet have a postgres container.

All commands run from a local PowerShell terminal via SSH to the target VPS.

Step 1 — Generate the database password

ssh root@<vps-ip> "openssl rand -hex 32"

Copy the output. This is the value for N8N_DB_PASSWORD.

Step 2 — Add the password to /root/.env

ssh root@<vps-ip> 'echo "N8N_DB_PASSWORD=<paste-value-here>" >> /root/.env'

Verify it was appended:

ssh root@<vps-ip> "grep N8N_DB_PASSWORD /root/.env"

Step 3 — Add n8n-postgres to docker-compose.yml

Add the following service and volume to the VPS compose file (tracked in hostinger/docker-compose.yml for vps-h1, or the equivalent file for other VPSes):

services:
  n8n-postgres:
    image: postgres:16-alpine
    container_name: n8n-postgres
    restart: unless-stopped
    environment:
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n
      POSTGRES_PASSWORD: ${N8N_DB_PASSWORD}
    volumes:
      - n8n_postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U n8n -d n8n"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - traefik-net
 
volumes:
  n8n_postgres_data:

The traefik-net network name should match whatever network n8n is already on. Replace if your VPS uses a different network name.

Step 4 — Add DB environment variables to the n8n service

In the n8n service definition, add or update:

environment:
  DB_TYPE: postgresdb
  DB_POSTGRESDB_HOST: n8n-postgres
  DB_POSTGRESDB_PORT: "5432"
  DB_POSTGRESDB_DATABASE: n8n
  DB_POSTGRESDB_USER: n8n
  DB_POSTGRESDB_PASSWORD: ${N8N_DB_PASSWORD}
  EXECUTIONS_DATA_PRUNE: "true"
  EXECUTIONS_DATA_MAX_AGE: "168"
  EXECUTIONS_DATA_PRUNE_MAX_COUNT: "10000"
  N8N_DEFAULT_BINARY_DATA_MODE: filesystem

Also add a depends_on block to the n8n service so it waits for postgres to be healthy before starting:

depends_on:
  n8n-postgres:
    condition: service_healthy

Step 5 — Start postgres and verify it is healthy

ssh root@<vps-ip> "cd /root && docker compose up -d n8n-postgres"

Wait ~15 seconds, then check:

ssh root@<vps-ip> "cd /root && docker compose ps n8n-postgres"

The STATUS column must show healthy before proceeding. If it shows starting, wait and re-run.

Step 6 — Start n8n and verify logs

ssh root@<vps-ip> "cd /root && docker compose up -d n8n"
ssh root@<vps-ip> "cd /root && docker compose logs n8n --tail=40"

Look for a line containing Editor is now accessible or n8n ready. There should be no database connection errors.

Verify the health endpoint responds:

ssh root@<vps-ip> "curl -s http://localhost:5678/healthz"

Expected response: {"status":"ok"}

Migration from SQLite (completed 2026-06-14, vps-h1)

This procedure was run once on vps-h1 to migrate from a 20.3 GB SQLite file to PostgreSQL. It is documented here for reference and in case it needs to be repeated on another machine.

Important: only execution history is lost. All workflow definitions are stored as JSON files in the n8n_data volume and are unaffected by the database migration.

Step 1 — Stop n8n

ssh root@72.60.32.61 "cd /root && docker compose stop n8n"

Step 2 — Generate password and update .env

ssh root@72.60.32.61 "openssl rand -hex 32"

Copy output, then:

ssh root@72.60.32.61 'echo "N8N_DB_PASSWORD=<paste-value-here>" >> /root/.env'

Step 3 — Pull docker-compose changes from repo

The updated hostinger/docker-compose.yml with the postgres service and n8n DB env vars must already be committed to the repo and synced to the VPS:

ssh root@72.60.32.61 "cd /opt/p24-infra && git pull"
ssh root@72.60.32.61 "cp /opt/p24-infra/hostinger/docker-compose.yml /root/docker-compose.yml"

Step 4 — Start postgres and wait for healthy

ssh root@72.60.32.61 "cd /root && docker compose up -d n8n-postgres"

Poll until healthy (run repeatedly until STATUS shows healthy):

ssh root@72.60.32.61 "cd /root && docker compose ps n8n-postgres"

Step 5 — Delete the SQLite file

This frees the 20.3 GB. Confirm the command before running — it is irreversible.

ssh root@72.60.32.61 'docker run --rm -v n8n_data:/data alpine sh -c "rm -f /data/database.sqlite && echo DELETED"'

Expected output: DELETED

Verify the space was freed:

ssh root@72.60.32.61 "df -h /var/lib/docker"

Step 6 — Start n8n

ssh root@72.60.32.61 "cd /root && docker compose up -d n8n"

Step 7 — Watch logs

ssh root@72.60.32.61 "cd /root && docker compose logs -f n8n --tail=30"

Press Ctrl+C to exit once n8n reports it is ready.

Step 8 — Verify n8n is up

ssh root@72.60.32.61 "curl -s http://localhost:5678/healthz"

Expected: {"status":"ok"}

Monitoring Database Size

Connect to postgres:

ssh root@72.60.32.61 "docker exec -it n8n-postgres psql -U n8n -d n8n"

Inside psql:

-- Total database size
SELECT pg_size_pretty(pg_database_size('n8n')) AS db_size;
 
-- Largest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

The execution_entity table will typically be the largest. If it exceeds a few hundred MB with pruning enabled, check that the prune settings are active (see Troubleshooting below).

Maintenance — Cleaning Old Executions

n8n prunes automatically when EXECUTIONS_DATA_PRUNE=true is set. The current retention is 7 days (EXECUTIONS_DATA_MAX_AGE=168) with a cap of 10,000 records.

If the database has grown large before pruning was enabled, or after a prune failure, run manual cleanup inside psql:

-- Count rows older than 7 days
SELECT COUNT(*) FROM execution_entity WHERE "startedAt" < NOW() - INTERVAL '7 days';
 
-- Delete in batches to avoid long lock holds
DELETE FROM execution_entity
WHERE id IN (
  SELECT id FROM execution_entity
  WHERE "startedAt" < NOW() - INTERVAL '7 days'
  LIMIT 10000
);
-- Re-run the DELETE until COUNT returns 0.
 
-- Reclaim space after bulk delete
VACUUM ANALYZE execution_entity;

PostgreSQL autovacuum handles space reclamation automatically for normal prune-sized deletes. Manual VACUUM is only needed after a large one-time bulk delete.

Backup and Restore

Backup — full database dump

Run on the VPS. Creates a timestamped compressed dump in /root/backups/:

ssh root@72.60.32.61 "mkdir -p /root/backups && docker exec n8n-postgres pg_dump -U n8n n8n | gzip > /root/backups/n8n-db-\$(date +%Y%m%d).sql.gz"

Verify the dump was created:

ssh root@72.60.32.61 "ls -lh /root/backups/n8n-db-*.sql.gz"

Restore

Stop n8n before restoring to avoid writes during import:

ssh root@72.60.32.61 "cd /root && docker compose stop n8n"
ssh root@72.60.32.61 "gunzip -c /root/backups/n8n-db-20260614.sql.gz | docker exec -i n8n-postgres psql -U n8n -d n8n"
ssh root@72.60.32.61 "cd /root && docker compose start n8n"

Troubleshooting

n8n fails to start — postgres not ready

Check postgres status first:

ssh root@72.60.32.61 "cd /root && docker compose ps n8n-postgres"
ssh root@72.60.32.61 "cd /root && docker compose logs n8n-postgres --tail=20"

n8n will not connect if postgres health check has not passed. Ensure the depends_on / service_healthy condition is present in the compose file.

Connection refused — network mismatch

If n8n logs show ECONNREFUSED when connecting to n8n-postgres, the containers are on different networks. Both services must declare the same network:

networks:
  - traefik-net

Verify:

ssh root@72.60.32.61 "docker network inspect traefik-net | grep -A2 n8n"

Both n8n and n8n-postgres must appear in the output.

Database too large despite pruning being enabled

Confirm the environment variables are actually set inside the running container:

ssh root@72.60.32.61 "docker exec n8n env | grep EXECUTIONS"

Expected output must include:

EXECUTIONS_DATA_PRUNE=true
EXECUTIONS_DATA_MAX_AGE=168
EXECUTIONS_DATA_PRUNE_MAX_COUNT=10000

If the values are missing, the container was not restarted after the env vars were added. Restart n8n:

ssh root@72.60.32.61 "cd /root && docker compose up -d n8n"

If values are present but the table is still large, run the manual bulk delete in the Maintenance section.

Postgres OOM on low-RAM VPS

The default max_connections=100 in postgres allocates shared memory per connection. On a 2-vCPU VPS, reduce it:

Add to the n8n-postgres service in docker-compose.yml:

command: postgres -c max_connections=50 -c shared_buffers=128MB

Then restart:

ssh root@72.60.32.61 "cd /root && docker compose up -d n8n-postgres"

n8n uses at most a few connections, so 50 is more than sufficient.

Check postgres connection from n8n container

ssh root@72.60.32.61 "docker exec n8n nc -zv n8n-postgres 5432"

Expected: Connection to n8n-postgres 5432 port [tcp/postgresql] succeeded!

External Task Runner — bms-4 (queue mode)

n8n on bms-4 runs in queue mode with 3 workers. Code node execution is handled by a dedicated n8n-task-runner container. This is the production-recommended setup for n8n 2.x.

Architecture

n8n (main, :5678)
  └─ task broker (:5679 internal)
       └─ n8n-task-runner  ← executes Code nodes (JS/Python)
n8n-worker-1/2/3  ← execute workflow logic via Redis queue

Workers also have N8N_RUNNERS_ENABLED=true to forward Code node tasks to the external runner rather than executing them inline.

Grant token retrieval

n8n auto-generates a grant token at startup. Retrieve it after the first docker compose up:

ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose logs n8n 2>&1 | grep -i 'grant token'"

The token appears in a log line like:

Grant token: <TOKEN_VALUE>

Initial deployment procedure

Step 1 — Add grant token to /root/.env on bms-4:

# Start n8n first (without the task runner) to get the grant token
ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose up -d n8n"
 
# Wait for n8n to be healthy, then retrieve the token
ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose logs n8n 2>&1 | grep -i 'grant'"
 
# Add to .env (replace <TOKEN> with actual value)
ssh ubuntu@54.36.123.110 'echo "N8N_RUNNERS_GRANT_TOKEN=<TOKEN>" >> /root/.env'

Step 2 — Apply the compose update:

ssh ubuntu@54.36.123.110 "cd /opt/p24-infra && git pull"
ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose up -d"

Step 3 — Verify runner connected:

ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose logs n8n 2>&1 | grep -i 'runner'"

Look for: Task runner connected in the n8n logs.

Verify worker configuration

Workers must also have N8N_RUNNERS_ENABLED=true set to forward Code node tasks to the external runner:

ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose exec n8n-worker-1 env | grep N8N_RUNNERS"

Expected output must include N8N_RUNNERS_ENABLED=true.

Troubleshooting

Runner not connecting — “Offer expired” errors:

If N8N_RUNNERS_GRANT_TOKEN_TTL=120 is still too short (e.g., very slow DB migrations on startup), increase it:

N8N_RUNNERS_GRANT_TOKEN_TTL=300

Runner exits with “connection refused”:

The task runner connects to http://n8n:5679 (n8n’s task broker port). Verify n8n is healthy:

ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose ps n8n"

If n8n is healthy but runner still fails, check that N8N_RUNNERS_ENABLED=true is actually set on the main n8n container:

ssh ubuntu@54.36.123.110 "cd /opt/p24-infra/bms-4 && docker compose exec n8n env | grep N8N_RUNNERS_ENABLED"

Verify Code node execution uses external runner:

In n8n logs, a successful Code node execution via external runner produces:

Task offered to runner
Task accepted by runner
Task completed by runner

Graph View

Backlinks