n8n migrate SQLite to PostgreSQL – complete command reference with Docker

n8n SQLite errors / Leave a Comment

Step by Step Guide to Migrate n8n from SQLite to PostgreSQL

 

Who this is for: n8n administrators who need a reliable, production‑grade PostgreSQL backend instead of the default SQLite store. For a complete guide on managing SQLite in n8n, including errors, performance, and migration, check out our n8n SQLite pillar guide.

Quick Diagnosis

Step Command / Action Result
1 sqlite3 ~/.n8n/database.sqlite ".dump" > dump.sql Export full SQLite schema + data
2 Edit dump.sql → replace INTEGER PRIMARY KEY AUTOINCREMENT with SERIAL PRIMARY KEY (or use pgloader) Make the dump PostgreSQL‑compatible
3 createdb n8n
psql n8n < dump.sql		 Load schema & data into a fresh PostgreSQL DB
4 Update ~/.n8n/.env → DB_TYPE=postgresdb
DB_POSTGRESDB_HOST=localhost
DB_POSTGRESDB_PORT=5432
DB_POSTGRESDB_DATABASE=n8n
DB_POSTGRESDB_USER=YOUR_USER
DB_POSTGRESDB_PASSWORD=YOUR_PASS		 Point n8n to PostgreSQL
5 npm run start (or restart Docker) n8n boots with PostgreSQL backend
6 Verify → SELECT COUNT(*) FROM workflow_entity; in psql All workflows are present

One‑Minute Migration Checklist

  1. Export SQLite DB.
  2. Convert the dump for PostgreSQL (manual sed or pgloader).
  3. Create a new PostgreSQL DB.
  4. Import the converted dump.
  5. Reconfigure n8n’s .env.
  6. Restart n8n and run a quick SELECT test.

If any step fails, consult the detailed sections below.
Debug logging issues and monitor constraints, compatibility, and migration steps in SQLite for n8n.

1. Export the SQLite Database

1.1 Locate n8n’s SQLite file

Environment Default Path
Linux/macOS (npm) ~/.n8n/database.sqlite
Docker (official image) /home/node/.n8n/database.sqlite
Windows (npm) %USERPROFILE%\.n8n\database.sqlite

EEFA note: Stop the n8n process before exporting to avoid a partially‑written DB. In Docker, run docker stop <container>.

1.2 Dump the whole DB

sqlite3 "$HOME/.n8n/database.sqlite" ".dump" > /tmp/n8n_sqlite_dump.sql

Check the dump size – it should be roughly the same as the original file (± 10 KB).

2. Convert the SQLite Dump to PostgreSQL‑compatible SQL

SQLite and PostgreSQL differ in data‑type syntax, autoincrement handling, and boolean literals.

2.1 Quick‑and‑dirty manual conversion (small DB)

sed -i \
  -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/g' \
  -e 's/TRUE/TRUE/g' \
  -e 's/FALSE/FALSE/g' \
  -e 's/`//g' \
  /tmp/n8n_sqlite_dump.sql
SQLite syntax PostgreSQL equivalent
INTEGER PRIMARY KEY AUTOINCREMENT SERIAL PRIMARY KEY
TEXT VARCHAR (or keep as TEXT)
BOOLEAN (stored as 0/1) BOOLEAN (use TRUE/FALSE)
Back‑ticks ` Double quotes " (or omit)

EEFA warning: Manual sed works for default n8n tables, but custom user tables may need bespoke adjustments. Review the modified dump before importing.

2.2 Automated conversion with pgloader (recommended for > 10 k rows)

# Install pgloader (Debian/Ubuntu)
sudo apt-get install pgloader

# Run conversion
pgloader sqlite:///home/$(whoami)/.n8n/database.sqlite \
        postgresql://YOUR_USER:YOUR_PASS@localhost/n8n

3. Create & Prepare the PostgreSQL Target Database

# Create the database (requires superuser or createdb privilege)
createdb -U YOUR_USER n8n
Parameter Recommended Setting
max_connections 200 (if you expect many concurrent workflows)
shared_buffers 25% of RAM
work_mem 64MB (adjust per workflow complexity)

EEFA tip: Enable pg_stat_statements and log_min_duration_statement for query‑performance monitoring after migration. Ensure smooth operation by addressing version conflicts, unsupported features, and constraint errors in n8n SQLite.

4. Import the Converted Dump (manual method)

psql -U YOUR_USER -d n8n -f /tmp/n8n_sqlite_dump.sql

Verify import success

SELECT table_name, row_estimate
FROM pg_stat_user_tables
ORDER BY row_estimate DESC;

You should see tables such as workflow_entity, execution_entity, and credential_entity with row counts matching the original SQLite DB (run SELECT COUNT(*) FROM workflow_entity; in SQLite beforehand for comparison).

5. Reconfigure n8n to Use PostgreSQL

5.1 Update the .env file

Replace the SQLite settings with PostgreSQL credentials:

# Existing SQLite config (remove or comment out)
# DB_TYPE=sqlite
# DB_SQLITE_DATABASE=/home/node/.n8n/database.sqlite

# New PostgreSQL config
DB_TYPE=postgresdb
DB_POSTGRESDB_HOST=localhost
DB_POSTGRESDB_PORT=5432
DB_POSTGRESDB_DATABASE=n8n
DB_POSTGRESDB_USER=YOUR_USER
DB_POSTGRESDB_PASSWORD=YOUR_PASS
# Optional: SSL mode
# DB_POSTGRESDB_SSL=true

5.2 Docker‑compose example (if you run n8n in Docker)

services:
  n8n:
    image: n8nio/n8n
    environment:
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=db
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=n8n
      - DB_POSTGRESDB_USER=n8n
      - DB_POSTGRESDB_PASSWORD=strong_password
    depends_on:
      - db
  db:
    image: postgres:15
    environment:
      - POSTGRES_USER=n8n
      - POSTGRES_PASSWORD=strong_password
      - POSTGRES_DB=n8n
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

EEFA note: In Docker, the DB_POSTGRESDB_HOST must match the service name (db). Set restart: unless-stopped for production stability.

6. Restart n8n and Verify the Migration

6.1 Start n8n

# If installed via npm
npm run start

# Docker
docker-compose up -d n8n

6.2 Quick sanity check

psql -U YOUR_USER -d n8n -c "SELECT COUNT(*) FROM workflow_entity;"

The count should equal the number of workflows you had in SQLite.

6.3 UI verification

  1. Open the n8n web UI (http://localhost:5678).
  2. Navigate to Workflows → All Workflows.
  3. Confirm that every workflow loads without error.

If you see “Failed to load workflow” errors, consult the troubleshooting checklist below. Understand foreign key constraints, version compatibility, and unsupported feature issues in n8n SQLite.

7. Troubleshooting Checklist

Symptom Likely Cause Fix
ERROR: column “id” of relation “workflow_entity” does not exist Dump still contains AUTOINCREMENT syntax Re‑run sed replacement or use pgloader.
psql: FATAL: password authentication failed for user “n8n” Wrong credentials in .env Verify DB_POSTGRESDB_USER / PASSWORD match PostgreSQL role.
ERROR: duplicate key value violates unique constraint “workflow_entity_pkey” Duplicate rows imported (e.g., ran dump twice) Drop the DB and re‑import cleanly.
n8n throws “Failed to connect to DB” on start PostgreSQL not reachable (Docker network, firewall) Test connectivity: nc -zv localhost 5432.
Missing workflow data after migration Incomplete dump (e.g., .dump interrupted) Re‑export SQLite after stopping n8n.
Performance slowdown after migration PostgreSQL default shared_buffers too low Increase shared_buffers to 25 % of RAM, restart PostgreSQL.

8. Post‑Migration Best Practices

  1. Backup the new PostgreSQL DB daily: 
    pg_dump -Fc -U YOUR_USER -d n8n > backup_$(date +%F).dump
  2. Enable logical replication for zero‑downtime upgrades.
  3. Turn on n8n’s DB health checks: 
    N8N_DISABLE_DATABASE_HEALTH_CHECK=false
  4. If using a managed Postgres service, set DB_POSTGRESDB_SSL=true and provide the appropriate CA bundle.

This guide follows production‑grade practices (EEFA) and assumes you have PostgreSQL 12+ installed. Adjust version‑specific flags accordingly.

Must Read

Leave a Comment

Your email address will not be published. Required fields are marked *