Migration Guide SQLite → PostgreSQL

Perfect! Here's a complete guide for migrating n8n from SQLite to PostgreSQL (for future reference):

Prerequisites

• PostgreSQL container running and accessible
• n8n container on same Docker network as PostgreSQL
• Database and user created in PostgreSQL

Step 1: Verify PostgreSQL Setup

Check network connectivity:

# Verify both containers are on the same network
docker network inspect web | grep -E "n8n|postgres"

Verify database exists:

# List databases
docker exec postgres psql -U rootuser -c "\l" | grep n8n_db

# If n8n_db doesn't exist, create it:
docker exec postgres psql -U rootuser -c "CREATE DATABASE n8n_db;"
docker exec postgres psql -U rootuser -c "CREATE USER n8n_user WITH PASSWORD 'N8N_STRONG_PASS';"
docker exec postgres psql -U rootuser -c "GRANT ALL PRIVILEGES ON DATABASE n8n_db TO n8n_user;"
docker exec postgres psql -U rootuser -d n8n_db -c "GRANT ALL ON SCHEMA public TO n8n_user;"

Step 2: Backup Everything

# Stop n8n
cd ~/n8n
docker compose down

# Backup the entire n8n data directory
sudo cp -r /var/lib/docker/volumes/n8n_n8n_data/_data \
           /var/lib/docker/volumes/n8n_n8n_data/_data.backup.$(date +%Y%m%d)

# Verify backup
sudo ls -lh /var/lib/docker/volumes/n8n_n8n_data/

Step 3: Export Data from SQLite

# Start n8n temporarily to export data
docker compose up -d

# Export all workflows and credentials to JSON
docker exec n8n-n8n-1 n8n export:workflow --all --output=/home/node/.n8n/workflows-backup.json
docker exec n8n-n8n-1 n8n export:credentials --all --output=/home/node/.n8n/credentials-backup.json

# Alternative: Use the database export command (if available in your n8n version)
docker exec n8n-n8n-1 n8n export:db --output=/home/node/.n8n/database-backup.json

# Verify exports exist
docker exec n8n-n8n-1 ls -lh /home/node/.n8n/ | grep backup

# Stop n8n
docker compose down

Step 4: Update Configuration

Update .env file:

nano .env

Add PostgreSQL configuration:

# N8n Configuration
N8N_HOST=n8n.yourdomain.com
N8N_PORT=5678
N8N_PROTOCOL=https
WEBHOOK_URL=https://n8n.yourdomain.com/

# Authentication
N8N_BASIC_AUTH_ACTIVE=true
N8N_BASIC_AUTH_USER=admin
N8N_BASIC_AUTH_PASSWORD=CHANGE_ME

# Timezone
TZ=Asia/Kolkata
GENERIC_TIMEZONE=Asia/Kolkata

# Database Configuration - PostgreSQL
DB_TYPE=postgresdb
DB_POSTGRESDB_DATABASE=n8n_db
DB_POSTGRESDB_USER=n8n_user
DB_POSTGRESDB_PASSWORD=N8N_STRONG_PASS
DB_POSTGRESDB_HOST=postgres
DB_POSTGRESDB_PORT=5432

# Execution history cleanup
EXECUTIONS_DATA_PRUNE=true
EXECUTIONS_DATA_MAX_AGE=168

Step 5: Rename SQLite Database

# Rename SQLite file so n8n will use PostgreSQL instead
sudo mv /var/lib/docker/volumes/n8n_n8n_data/_data/database.sqlite \
        /var/lib/docker/volumes/n8n_n8n_data/_data/database.sqlite.old

Step 6: Start n8n with PostgreSQL

# Start n8n - it will now connect to PostgreSQL
docker compose up -d

# Watch logs for connection success
docker compose logs -f n8n

Look for:

• ✅ Successfully connected to database or similar
• ✅ No connection errors
• ❌ Any PostgreSQL connection errors

Step 7: Import Data into PostgreSQL

Option A: Import full database (if export:db worked):

docker exec n8n-n8n-1 n8n import:db --input=/home/node/.n8n/database-backup.json

Option B: Import workflows and credentials separately:

# Import workflows
docker exec n8n-n8n-1 n8n import:workflow --input=/home/node/.n8n/workflows-backup.json

# Import credentials
docker exec n8n-n8n-1 n8n import:credentials --input=/home/node/.n8n/credentials-backup.json

Step 8: Verify Migration

# Check n8n logs
docker compose logs n8n | tail -50

# Access n8n web interface
# Check that all workflows are present
# Test a simple workflow execution

# Verify database is being used
docker exec postgres psql -U n8n_user -d n8n_db -c "\dt"

Step 9: Cleanup (After Verification)

Only do this after confirming everything works!

# Remove old SQLite file
sudo rm /var/lib/docker/volumes/n8n_n8n_data/_data/database.sqlite.old

# Remove backup JSON files
docker exec n8n-n8n-1 rm /home/node/.n8n/workflows-backup.json
docker exec n8n-n8n-1 rm /home/node/.n8n/credentials-backup.json
docker exec n8n-n8n-1 rm /home/node/.n8n/database-backup.json

Rollback (If Something Goes Wrong)

# Stop n8n
docker compose down

# Restore SQLite database
sudo mv /var/lib/docker/volumes/n8n_n8n_data/_data/database.sqlite.old \
        /var/lib/docker/volumes/n8n_n8n_data/_data/database.sqlite

# Remove PostgreSQL config from .env
nano .env
# Comment out or remove all DB_* lines

# Restart n8n
docker compose up -d

Troubleshooting

Connection errors:

# Test network connectivity
docker exec n8n-n8n-1 ping -c 2 postgres

# Check PostgreSQL logs
docker logs postgres | tail -50

# Verify credentials
docker exec postgres psql -U n8n_user -d n8n_db -c "SELECT 1;"

Permission errors:

# Grant all permissions again
docker exec postgres psql -U rootuser -d n8n_db -c "GRANT ALL ON SCHEMA public TO n8n_user;"
docker exec postgres psql -U rootuser -d n8n_db -c "GRANT ALL ON ALL TABLES IN SCHEMA public TO n8n_user;"

Important Notes

1. Backup first! Always backup before migration
2. Test thoroughly before deleting SQLite file
3. Export limitations: Some n8n versions may not support export:db - use workflow/credential exports instead
4. Execution history: Old execution history from SQLite won't be migrated - only workflows and credentials
5. Downtime: Plan for 5-10 minutes of downtime during migration

Save this guide and use it when you're ready to migrate! 📝

Reference