How to Use n8n with Self-Hosted PostgreSQL or MySQL Databases

1-- Connect to PostgreSQL as the admin user
2-- psql -U postgres
3
4-- Create a dedicated user for n8n
5CREATE USER n8n_user WITH PASSWORD 'your_secure_password_here';
6
7-- Create the n8n database
8CREATE DATABASE n8n_db OWNER n8n_user;
9
10-- Grant all privileges on the database to the n8n user
11GRANT ALL PRIVILEGES ON DATABASE n8n_db TO n8n_user;
12
13-- Verify the database was created
14\l

1# Set environment variables in your shell or .env file
2export DB_TYPE=postgresdb
3export DB_POSTGRESDB_HOST=localhost
4export DB_POSTGRESDB_PORT=5432
5export DB_POSTGRESDB_DATABASE=n8n_db
6export DB_POSTGRESDB_USER=n8n_user
7export DB_POSTGRESDB_PASSWORD=your_secure_password_here
8
9# Optional: Set the schema (default is 'public')
10export DB_POSTGRESDB_SCHEMA=public
11
12# Optional: Enable SSL for remote databases
13export DB_POSTGRESDB_SSL_ENABLED=true
14
15# Start n8n with the database configuration
16n8n start

1# Docker run command with PostgreSQL configuration
2docker run -d \
3  --name n8n \
4  -p 5678:5678 \
5  -e DB_TYPE=postgresdb \
6  -e DB_POSTGRESDB_HOST=your-postgres-host \
7  -e DB_POSTGRESDB_PORT=5432 \
8  -e DB_POSTGRESDB_DATABASE=n8n_db \
9  -e DB_POSTGRESDB_USER=n8n_user \
10  -e DB_POSTGRESDB_PASSWORD=your_secure_password_here \
11  -v n8n_data:/home/node/.n8n \
12  docker.n8n.io/n8nio/n8n
13
14# Or use docker-compose.yml (see complete code below)

1# Check n8n logs for database connection status
2# npm installation
3n8n start 2>&1 | head -20
4
5# Docker installation
6docker logs n8n
7
8# Verify tables were created in PostgreSQL
9psql -U n8n_user -d n8n_db -c "\dt"
10
11# Expected tables include:
12# workflow_entity
13# credentials_entity
14# execution_entity
15# webhook_entity
16# tag_entity

1# Step 1: Export workflows from SQLite (before changing DB_TYPE)
2n8n export:workflow --all --output=./workflows-backup.json
3
4# Step 2: Export credentials
5n8n export:credentials --all --output=./credentials-backup.json
6
7# Step 3: Switch to PostgreSQL (set the DB_TYPE and connection vars)
8export DB_TYPE=postgresdb
9export DB_POSTGRESDB_HOST=localhost
10export DB_POSTGRESDB_PORT=5432
11export DB_POSTGRESDB_DATABASE=n8n_db
12export DB_POSTGRESDB_USER=n8n_user
13export DB_POSTGRESDB_PASSWORD=your_secure_password_here
14
15# Step 4: Start n8n to create the PostgreSQL schema
16n8n start &
17sleep 10
18
19# Step 5: Import workflows into PostgreSQL
20n8n import:workflow --input=./workflows-backup.json
21
22# Step 6: Import credentials
23n8n import:credentials --input=./credentials-backup.json