n8n migrate SQLite to PostgreSQL - complete command reference with Docker

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

Export SQLite DB.
Convert the dump for PostgreSQL (manual sed or pgloader).
Create a new PostgreSQL DB.
Import the converted dump.
Reconfigure n8n’s .env.
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

Open the n8n web UI (http://localhost:5678).
Navigate to Workflows → All Workflows.
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

Backup the new PostgreSQL DB daily:

pg_dump -Fc -U YOUR_USER -d n8n > backup_$(date +%F).dump

Enable logical replication for zero‑downtime upgrades.

Turn on n8n’s DB health checks:

N8N_DISABLE_DATABASE_HEALTH_CHECK=false

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.

n8n migrate SQLite to PostgreSQL – complete command reference with Docker

Quick Diagnosis

One‑Minute Migration Checklist

1. Export the SQLite Database

1.1 Locate n8n’s SQLite file

1.2 Dump the whole DB

2. Convert the SQLite Dump to PostgreSQL‑compatible SQL

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

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

3. Create & Prepare the PostgreSQL Target Database

4. Import the Converted Dump (manual method)

Verify import success

5. Reconfigure n8n to Use PostgreSQL

5.1 Update the `.env` file

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

6. Restart n8n and Verify the Migration

6.1 Start n8n

6.2 Quick sanity check

6.3 UI verification

7. Troubleshooting Checklist

8. Post‑Migration Best Practices

Leave a Comment Cancel Reply

Sign up for Newsletter

Quick Diagnosis

One‑Minute Migration Checklist

1. Export the SQLite Database

1.1 Locate n8n’s SQLite file

1.2 Dump the whole DB

2. Convert the SQLite Dump to PostgreSQL‑compatible SQL

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

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

3. Create & Prepare the PostgreSQL Target Database

4. Import the Converted Dump (manual method)

Verify import success

5. Reconfigure n8n to Use PostgreSQL

5.1 Update the .env file

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

6. Restart n8n and Verify the Migration

6.1 Start n8n

6.2 Quick sanity check

6.3 UI verification

7. Troubleshooting Checklist

8. Post‑Migration Best Practices

Must Read

Leave a Comment Cancel Reply

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

5.1 Update the `.env` file