Skip to content

Instantly share code, notes, and snippets.

@CodeAdminDe

CodeAdminDe/migrate_n8n_sqlite_to_postgres.sh

Forked from bl0wfish/migrate_n8n_sqlite_to_postgres.sh
Created January 15, 2026 09:16
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save CodeAdminDe/7b524d26d385c6ef043f280189a71411 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save CodeAdminDe/7b524d26d385c6ef043f280189a71411 to your computer and use it in GitHub Desktop.
Download ZIP
Migrate n8n from SQLite to PostgreSQL
Raw
migrate_n8n_sqlite_to_postgres.sh
#!/usr/bin/env bash
set -euo pipefail
#############################################
# Configuration
#############################################
# Load .env file if present
if [ -f .env ]; then
export $(grep -v '^#' .env | xargs -r)
fi
SQLITE_DB_PATH="${SQLITE_DB_PATH:-/srv/docker/n8n/data/database.sqlite}"
POSTGRES_HOST="${POSTGRES_HOST:-localhost}"
POSTGRES_PORT="${POSTGRES_PORT:-5432}"
POSTGRES_USER="${POSTGRES_USER:?POSTGRES_USER required}"
POSTGRES_PASSWORD="${POSTGRES_PASSWORD:?POSTGRES_PASSWORD required}"
POSTGRES_DB="${POSTGRES_DB:?POSTGRES_DB required}"
POSTGRES_NON_ROOT_USER="${POSTGRES_NON_ROOT_USER:-}"
POSTGRES_NON_ROOT_PASSWORD="${POSTGRES_NON_ROOT_PASSWORD:-}"
N8N_ENCRYPTION_KEY="${N8N_ENCRYPTION_KEY:-}"
TABLES=(
"role"
"scope"
"role_scope"
"user"
"auth_identity"
"user_api_keys"
"settings"
"project"
"folder"
"tag_entity"
"folder_tag"
"variables"
"credentials_entity"
"shared_credentials"
"data_table"
"data_table_column"
"workflow_entity"
"workflow_dependency"
"shared_workflow"
"webhook_entity"
"workflows_tags"
"annotation_tag_entity"
"auth_provider_sync_history"
"chat_hub_messages"
"chat_hub_sessions"
"event_destinations"
"installed_nodes"
"installed_packages"
"project_relation"
)
TMP_DIR="${TMP_DIR:-/tmp/n8n_sqlite_to_pg}"
mkdir -p "$TMP_DIR"
#############################################
# Prerequisites
#############################################
command -v sqlite3 >/dev/null || { echo "sqlite3 missing"; exit 1; }
command -v psql >/dev/null || { echo "psql missing"; exit 1; }
if [ ! -f "$SQLITE_DB_PATH" ]; then
echo "SQLite DB not found at $SQLITE_DB_PATH"
exit 1
fi
export PGPASSWORD="$POSTGRES_PASSWORD"
PG_URI="postgresql://${POSTGRES_USER}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}"
#############################################
# Create non-root DB user if needed
#############################################
if [ -n "$POSTGRES_NON_ROOT_USER" ] && [ -n "$POSTGRES_NON_ROOT_PASSWORD" ]; then
psql "$PG_URI" <<EOF || true
DO \$\$
BEGIN
IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${POSTGRES_NON_ROOT_USER}') THEN
CREATE ROLE ${POSTGRES_NON_ROOT_USER} LOGIN PASSWORD '${POSTGRES_NON_ROOT_PASSWORD}';
END IF;
END
\$\$;
GRANT ALL PRIVILEGES ON DATABASE "${POSTGRES_DB}" TO ${POSTGRES_NON_ROOT_USER};
EOF
fi
#############################################
# Helpers
#############################################
get_columns_for_table_pg() {
local tbl="$1"
psql "$PG_URI" -At <<EOF
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = '${tbl}'
ORDER BY ordinal_position;
EOF
}
create_table_from_sqlite_schema() {
local tbl="$1"
echo " Creating table '$tbl' (not present in Postgres)..."
local pragma_output
pragma_output="$(sqlite3 "$SQLITE_DB_PATH" "PRAGMA table_info('$tbl');")"
[ -z "$pragma_output" ] && { echo "Failed to read schema for $tbl"; return 1; }
local first=1
local columns_sql=""
while IFS='|' read -r cid name type notnull dflt pk; do
[ -z "$name" ] && continue
local t=$(echo "${type}" | tr '[:lower:]' '[:upper:]')
if [[ "$t" == *"INT"* ]]; then pg_type="integer"
elif [[ "$t" == *"REAL"* || "$t" == *"FLOA"* || "$t" == *"DOUB"* ]]; then pg_type="double precision"
elif [[ "$t" == *"BLOB"* ]]; then pg_type="bytea"
else pg_type="text"
fi
col="\"$name\" $pg_type"
[ "${notnull:-0}" -eq 1 ] && col="$col NOT NULL"
[ "${pk:-0}" -eq 1 ] && col="$col PRIMARY KEY"
if [ $first -eq 1 ]; then
columns_sql="$col"
first=0
else
columns_sql="$columns_sql, $col"
fi
done <<< "$pragma_output"
psql "$PG_URI" -c "CREATE TABLE IF NOT EXISTS \"$tbl\" ($columns_sql);" >/dev/null
if [ -n "$POSTGRES_NON_ROOT_USER" ]; then
psql "$PG_URI" -c "ALTER TABLE \"$tbl\" OWNER TO ${POSTGRES_NON_ROOT_USER};" >/dev/null
psql "$PG_URI" -c "GRANT ALL PRIVILEGES ON TABLE \"$tbl\" TO ${POSTGRES_NON_ROOT_USER};" >/dev/null
fi
}
#############################################
# Detect data_table_user_* tables
#############################################
mapfile -t DATA_TABLE_USER_TABLES < <(
sqlite3 "$SQLITE_DB_PATH" ".tables" | tr '[:space:]' '\n' | grep '^data_table_user_' | sort -u
)
if [ "${#DATA_TABLE_USER_TABLES[@]}" -gt 0 ]; then
TABLES+=("${DATA_TABLE_USER_TABLES[@]}")
fi
#############################################
# Migration
#############################################
echo "Starting migration..."
for table in "${TABLES[@]}"; do
echo "----------------------------------------"
echo "Table: $table"
cols="$(get_columns_for_table_pg "$table")"
if [ -z "$cols" ]; then
create_table_from_sqlite_schema "$table"
cols="$(get_columns_for_table_pg "$table")"
fi
cols_quoted=$(printf '"%s",' $cols | sed 's/,$//')
CSV_PATH="${TMP_DIR}/${table}.csv"
echo " [1/3] Exporting SQLite → CSV"
if [ "$table" = "workflow_entity" ]; then
sqlite3 "$SQLITE_DB_PATH" <<EOF
.mode csv
.headers on
.output $CSV_PATH
SELECT $cols_quoted
FROM workflow_entity
WHERE parentFolderId IS NULL
OR parentFolderId IN (SELECT id FROM folder);
.output stdout
EOF
elif [ "$table" = "shared_workflow" ]; then
sqlite3 "$SQLITE_DB_PATH" <<EOF
.mode csv
.headers on
.output $CSV_PATH
SELECT $cols_quoted
FROM shared_workflow
WHERE workflowId IN (
SELECT id FROM workflow_entity
WHERE parentFolderId IS NULL
OR parentFolderId IN (SELECT id FROM folder)
);
.output stdout
EOF
elif [ "$table" = "webhook_entity" ]; then
sqlite3 "$SQLITE_DB_PATH" <<EOF
.mode csv
.headers on
.output $CSV_PATH
SELECT $cols_quoted
FROM webhook_entity
WHERE workflowId IS NOT NULL
AND workflowId IN (
SELECT id FROM workflow_entity
WHERE parentFolderId IS NULL
OR parentFolderId IN (SELECT id FROM folder)
);
.output stdout
EOF
elif [ "$table" = "workflows_tags" ]; then
sqlite3 "$SQLITE_DB_PATH" <<EOF
.mode csv
.headers on
.output $CSV_PATH
SELECT $cols_quoted
FROM workflows_tags
WHERE workflowId IN (
SELECT id FROM workflow_entity
WHERE parentFolderId IS NULL
OR parentFolderId IN (SELECT id FROM folder)
);
.output stdout
EOF
else
sqlite3 "$SQLITE_DB_PATH" <<EOF
.mode csv
.headers on
.output $CSV_PATH
SELECT $cols_quoted FROM "$table";
.output stdout
EOF
fi
echo " [2/3] TRUNCATE Postgres"
psql "$PG_URI" -c "TRUNCATE TABLE \"$table\" RESTART IDENTITY CASCADE;" || true
if [ -n "$POSTGRES_NON_ROOT_USER" ]; then
psql "$PG_URI" -c "GRANT ALL PRIVILEGES ON TABLE \"$table\" TO ${POSTGRES_NON_ROOT_USER};" >/dev/null
fi
echo " [3/3] Import CSV → Postgres"
if [ -s "$CSV_PATH" ]; then
psql "$PG_URI" -c "\COPY \"$table\" ($cols_quoted) FROM '$CSV_PATH' CSV HEADER;" >/dev/null
echo " Import completed."
else
echo " Skipping import (CSV empty)."
fi
done
#############################################
# Global Grants
#############################################
if [ -n "$POSTGRES_NON_ROOT_USER" ]; then
psql "$PG_URI" -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${POSTGRES_NON_ROOT_USER};" >/dev/null
psql "$PG_URI" -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${POSTGRES_NON_ROOT_USER};" >/dev/null
fi
echo "Migration complete. CSVs in $TMP_DIR"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment