Migrate n8n from SQLite to PostgreSQL on DigitalOcean

On this page

Changing one database setting is not a migration. Keep a verified recovery point, move the app data, and prove the new database before you retire the old one.

For the restored Standard Edition cluster, download its own CA certificate and replace the certificate path in the psql -W example. -W prompts for the password; do not paste it into the command. verify-full checks the certificate and hostname. See DigitalOcean’s connection instructions, PostgreSQL TLS verification, and n8n database settings. Record the installed n8n version and image digest before migration. Use that version’s Server CLI help for export:entities, import:entities and workflow deactivation; do not present these commands as tested against a different release.

The command blocks below target the n8n 2.x Server CLI. Check the recorded image’s --version and unpublish:workflow --help before continuing. The current Server CLI reference documents entity export/import and unpublish:workflow --all; update:workflow is deprecated from 2.0. If the recorded image is older or lacks a shown command, stop and adapt against that version’s help, without upgrading during recovery. No installed n8n runtime was exercised in this review.

Decide whether to migrate

SQLite works for a quiet n8n installation. Use PostgreSQL when several people administer workflows, execution history matters, or an outage has a real cost. It moves n8n data out of the Droplet. It does not make a risky workflow safe or replace a recovery plan.

An empty PostgreSQL database can let n8n start without your workflows, credentials, or history. Schedule a maintenance window, keep the n8n encryption key, and leave the SQLite data alone until the team accepts the cutover.

You need DigitalOcean administrator access, SSH access to the Linux Droplet running n8n, and a separate place to test a restore. A Droplet is the virtual server. A trusted source is a network rule that allows a server or administrator address to connect to the database.

A tested recovery archive and its matching n8n encryption key.
A maintenance window that pauses incoming webhooks and scheduled production work.
A named owner who can verify credentials and approve the final cutover.

Capture a recovery point before changing the database

Start with a backup you can restore. Record the n8n version, Compose image tag, public URL, encryption-key location, volume name, and final backup timestamp. Read one encrypted archive in the isolated recovery environment from the private n8n guide. Keep the same n8n version for the migration. Do not combine a database move with an n8n upgrade.

If you used the DigitalOcean Marketplace n8n app from the companion recovery guide, the deployment directory is usually /opt/n8n-docker-caddy. If you use another setup, find the Compose file before editing anything. The service name from docker compose config --services replaces n8n in the later commands.

Open a root shell on the Droplet using the action below before these commands. Keep that terminal for the migration and replace N8N_APP_DIR if your path differs.

Shell command

sudo -i

Shell command

sudo find /opt /srv /home -maxdepth 4 -type f \( -name compose.yml -o -name docker-compose.yml \) -print
N8N_APP_DIR=/opt/n8n-docker-caddy
cd "$N8N_APP_DIR"
sudo docker compose config --services

Create a restricted PostgreSQL database

Create the cluster first. Do not change n8n yet. This guide uses a Standard Edition PostgreSQL cluster because its connection details include a CA certificate that n8n can mount. If you choose Advanced Edition, stop here and adapt the TLS setup to its system trust store instead of copying the CA-file steps below.

Then return to the n8n Droplet's SSH terminal. Set the actual local data-volume and backup paths in migration.env. The command uses a subshell so your terminal remains in the deployment directory. Check pwd before exporting.

In your DigitalOcean project, open Databases and click Create Database. You can also use Create, then Managed Database. On the Create Database Cluster page, select PostgreSQL and a version. The engine is fixed. Supported major-version upgrades are possible later, after compatibility checks and a separate recovery plan.
Choose the same datacenter region as the n8n Droplet. Select the plan and storage that fit the current workload, enter n8n-postgres as the cluster name, choose the project that contains n8n, then click Create Database Cluster. Wait for the cluster status to become Online.
Open the new cluster, then select Users & Databases. In Databases, enter n8n in Add new database and click Save. In Users, enter n8n_app in Add new user and click Save. Use n8n_app for n8n. Leave doadmin for administration and recovery work.
Select Network Access, click Add Trusted Sources, choose Quick select Droplets, and select the n8n Droplet. Click Add Trusted Sources. For a one-time command-line check, add your own current IPv4 address from the same dialog. Remove that address after the check. Do not add 0.0.0.0/0.
Return to the cluster Overview and open Connection Details. Copy the host, port, database name, n8n_app user name, and password into a password manager. Download the CA certificate and copy it to the n8n Droplet later. Do not paste any of these values into Git, workflow notes, or screenshots.

Shell command

(
  cd /root
  git clone https://github.com/TylorMayfield/n8n-digitalocean-postgres-migration.git
  cd n8n-digitalocean-postgres-migration
  install -m 600 migration.env.example migration.env
)

Shell command

sudo nano /root/n8n-digitalocean-postgres-migration/migration.env

Shell command

sudo /root/n8n-digitalocean-postgres-migration/scripts/preflight.sh /root/n8n-digitalocean-postgres-migration/migration.env
cd "$N8N_APP_DIR"
pwd

Prove the target application role is ready before downtime

Keep SQLite running until this gate passes. Creating n8n and n8n_app, or passing the companion's local preflight, does not prove that the role can initialize PostgreSQL. Agree the dedicated database and persistent schema ownership with the database administrator. For the public schema used below, the actual n8n_app role needs the minimum rights to create and alter its application objects and read/write their data. Database connectivity, SELECT now() or a temporary-table test alone is insufficient; schema privileges vary with PostgreSQL version and cluster history.

Before the maintenance window, rehearse against an empty disposable target on the same provider/PostgreSQL version, using n8n_app over the actual n8n host's intended TLS/network path. Verify required persistent-schema create/alter/data-access operations with uniquely named disposable objects, collision checks and transaction rollback or explicit cleanup; confirm no test objects remain. Include a negative check showing missing schema-create rights stop the rehearsal. Then rehearse n8n initialization/import with the pinned image; a SQL probe alone does not prove that import works.

This article does not supply validated provider-specific grant/probe SQL. Have the administrator review and test that least-privilege sequence first, then establish readiness for the actual empty target before stopping SQLite. If it is unavailable or fails, stop the migration here. Do not use doadmin as n8n's runtime identity or add broad PUBLIC/cluster-wide grants to make a test pass. See DigitalOcean role privileges and PostgreSQL schema privileges.

Perform a planned cutover

Block inbound traffic and disable scheduled work before starting this sequence. Leave n8n running long enough for the script to read its UID and GID; the script then stops it before export. Keep the same n8n image and encryption key throughout. Export must finish successfully before changing database settings.

Before the maintenance window, choose what happens to execution history. The Server CLI reference says the default entity export omits execution-history data tables.

Migrate history: use the history-preserving export below only if the exact recorded image's help supports --includeExecutionHistoryDataTables=true. Budget extra disk space and export/import time; execution payloads can be large and sensitive. Rehearse on an isolated copy with the same pinned image first.
Archive history in SQLite: omit that flag only after explicitly recording that old executions will not appear in PostgreSQL. Keep and test the SQLite recovery archive under the agreed retention/access policy. Do not call this a history-preserving migration.

Check the running image before the cutover. If the flag is missing or its syntax differs, stop and adapt the procedure to that version; do not upgrade to make the example work.

Shell command

sudo docker compose exec -T n8n n8n --version
sudo docker compose exec -T n8n n8n export:entities --help

The flag includes database history tables; it is not a complete backup. Preserve the matching encryption key, configuration, and any filesystem or external binary/execution storage separately. The n8n backup guide explains the wider recovery scope. This procedure has not been runtime-tested against your pinned image.

Before export, with production writes paused and in-flight executions settled, record relevant source-table counts and a known successful and failed historical execution ID. Include representative input/output payloads and a binary attachment if your storage mode uses one. Keep these verification records private.

The .env file sits beside the Compose file. It holds values that Compose passes into the n8n container. Add the PostgreSQL values there. Then add the matching environment entries and a read-only CA-certificate mount to the n8n service in compose.yml or docker-compose.yml. A .env file by itself does not give a running container new variables unless the Compose file references them.

Shell command

cd "$N8N_APP_DIR"
set -euo pipefail
sudo apt-get update
sudo apt-get install -y unzip
if [[ -f compose.yml ]]; then COMPOSE_FILE=compose.yml; elif [[ -f docker-compose.yml ]]; then COMPOSE_FILE=docker-compose.yml; else echo 'No Compose file'; exit 1; fi
sudo cp .env .env.sqlite-backup
sudo chmod 600 .env .env.sqlite-backup
sudo cp "$COMPOSE_FILE" "$COMPOSE_FILE.sqlite-backup"
N8N_UID=$(sudo docker compose exec -T n8n id -u)
N8N_GID=$(sudo docker compose exec -T n8n id -g)
sudo install -d -o "$N8N_UID" -g "$N8N_GID" -m 700 migration-entities
sudo install -d -m 700 secrets
sudo tee compose.migration.yml > /dev/null <<'YAML'
services:
  n8n:
    volumes:
      - ./migration-entities:/migration
YAML
sudo docker compose stop n8n
sudo docker compose -f "$COMPOSE_FILE" -f compose.migration.yml run --rm --no-deps n8n export:entities --outputDir=/migration --includeExecutionHistoryDataTables=true
sudo test -s migration-entities/entities.zip
sudo unzip -t migration-entities/entities.zip
# Continue only after export exits successfully and the directory contains the exported entities.

Shell command

DB_TYPE=postgresdb
DB_POSTGRESDB_HOST=YOUR_CLUSTER_HOST
DB_POSTGRESDB_PORT=YOUR_CLUSTER_PORT
DB_POSTGRESDB_DATABASE=n8n
DB_POSTGRESDB_USER=n8n_app
DB_POSTGRESDB_PASSWORD=YOUR_N8N_APP_PASSWORD
DB_POSTGRESDB_SCHEMA=public
DB_POSTGRESDB_SSL_ENABLED=true
DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED=true
DB_POSTGRESDB_SSL_CA_FILE=/run/secrets/do-postgres-ca.crt

# Then run sudo nano $COMPOSE_FILE. Inside the existing n8n service, add these entries without deleting its current environment or volumes.
services:
  n8n:
    environment:
      DB_TYPE: ${DB_TYPE}
      DB_POSTGRESDB_HOST: ${DB_POSTGRESDB_HOST}
      DB_POSTGRESDB_PORT: ${DB_POSTGRESDB_PORT}
      DB_POSTGRESDB_DATABASE: ${DB_POSTGRESDB_DATABASE}
      DB_POSTGRESDB_USER: ${DB_POSTGRESDB_USER}
      DB_POSTGRESDB_PASSWORD: ${DB_POSTGRESDB_PASSWORD}
      DB_POSTGRESDB_SCHEMA: ${DB_POSTGRESDB_SCHEMA}
      DB_POSTGRESDB_SSL_ENABLED: ${DB_POSTGRESDB_SSL_ENABLED}
      DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED: ${DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED}
      DB_POSTGRESDB_SSL_CA_FILE: ${DB_POSTGRESDB_SSL_CA_FILE}
    volumes:
      - ./secrets/do-postgres-ca.crt:/run/secrets/do-postgres-ca.crt:ro

Shell command

set -euo pipefail
# Save the DigitalOcean CA certificate as secrets/do-postgres-ca.crt first.
sudo chmod 644 secrets/do-postgres-ca.crt
# The CA is public verification material; keep database passwords in protected .env.
sudo docker compose -f "$COMPOSE_FILE" -f compose.migration.yml run --rm --no-deps n8n import:entities --inputDir=/migration
# Disable imported schedules/webhooks before the first application start.
sudo docker compose -f "$COMPOSE_FILE" run --rm --no-deps n8n unpublish:workflow --all
sudo docker compose -f "$COMPOSE_FILE" up -d

Prove the result before reopening production traffic

If you chose to migrate history, compare those counts in PostgreSQL before the manual test creates new records. Open the recorded historical executions and verify their status, payloads, and applicable binary attachments. Equal row counts alone do not prove that the data is usable. If you chose archival instead, verify those executions in an isolated restore of the SQLite archive and record their intentional absence from PostgreSQL. Do not reopen production traffic if the chosen retention outcome is unverified.

Sign in as an administrator. Open a known workflow, decrypt a known credential, and complete one harmless manual run. Keep production workflows disabled until these checks pass. If a credential fails to decrypt, restore the old service. Do not reset credentials during the investigation.

Keep the original recovery archive until the cutover owner accepts all checks. Rehearse the rollback in the isolated recovery environment while incoming work remains blocked, including access to the recorded old executions and their payloads.

Then test database recovery in the control panel. Open Databases, select the n8n cluster, choose Actions, then Restore from backup. Choose a recent point, give the restored cluster a test name, and select Restore to New Cluster. When it is online, add only your administrator address under Network Access, copy its new connection details, and run a read-only psql check. This proves the database recovery point, not that every workflow is safe to run.

Shell command

# On the n8n Droplet, check the running service and its startup log.
sudo docker compose ps
sudo docker compose logs --tail=100 n8n
# On an administrator machine with the PostgreSQL client installed, use the restored cluster's new Connection Details.
PGSSLMODE=verify-full \
PGSSLROOTCERT="/path/to/restored-cluster-ca.crt" \
psql -W -h RESTORED_CLUSTER_HOST -p RESTORED_CLUSTER_PORT -U n8n_app -d n8n \
  -c 'select current_database(), current_user, now();'
# Then open one workflow, decrypt one credential, and run one harmless manual test. Record the result.

Roll back to SQLite before resuming traffic

Use this rollback only while production traffic is paused and no new production data has been accepted in PostgreSQL. It restores the saved configuration and original SQLite volume. Keep incoming work blocked during verification. If production writes have resumed, stop and plan reconciliation; this rollback would lose the new data.

Shell command

cd "$N8N_APP_DIR"
sudo docker compose -f "$COMPOSE_FILE" stop n8n
sudo test -f .env.sqlite-backup
sudo test -f "$COMPOSE_FILE.sqlite-backup"
sudo cp .env.sqlite-backup .env
sudo cp "$COMPOSE_FILE.sqlite-backup" "$COMPOSE_FILE"
sudo docker compose -f "$COMPOSE_FILE" run --rm --no-deps n8n unpublish:workflow --all
sudo docker compose -f "$COMPOSE_FILE" up -d n8n
sudo docker compose -f "$COMPOSE_FILE" ps

Know what Managed PostgreSQL does not cover

Managed PostgreSQL runs the database and provides backups and infrastructure recovery. You still own access rules, workflow logic, credentials, and the decision to run a workflow after recovery. Review that split after the first restore test, not during an outage.

If n8n handles important customer data or irreversible actions, document a recovery-time target and acceptable data loss. The database is now part of an operational system, not a cosmetic upgrade.

Frequently asked questions

Can I switch n8n from SQLite to PostgreSQL without downtime?

Plan a maintenance window. A safe cutover pauses new execution state, preserves the old SQLite data, exports and imports n8n entities with the installed Server CLI, and verifies credentials before production work resumes.

Does a Managed PostgreSQL backup replace n8n backups?

No. It protects the database service and supports database recovery. Keep the n8n encryption key, deployment configuration, and an isolated application recovery test in the plan as well.

Why should the n8n Droplet be a trusted source instead of opening the database publicly?

A trusted-source rule limits who can open a database connection. Restrict it to the application server and temporary administrator access, then remove temporary access once validation is complete.