Automated backups for a self-hosted n8n PostgreSQL database (that restore)

A pg_dump of your n8n database is necessary for recovery. It is not sufficient. This is the single fact that separates a backup that restores from one that returns your workflows with every credential dead.

The reason is that n8n encrypts every saved credential with a key that does not live in the database. The key sits in a file (or an environment variable) next to the instance. Dump the database, restore it under a different key or no key, and your workflows come back while every credential fails to decrypt. So the unit you are protecting is not “the database”. It is the database dump, plus the encryption key, plus enough configuration to bring an instance up against both. And the only proof that the set works is a restore you have run with your own hands.

This guide builds that backup, automates it, gets it off the server, and ends where it has to: a restore drill. The stack referenced throughout is self-hosted n8n on the 2.x line with PostgreSQL, typically via Docker Compose on a single VPS. The approach holds for any supported PostgreSQL major (14 through 18; 18.4 is the current minor at the time of writing). This is not for n8n Cloud users, where the provider backs up the database for you.

What n8n stores, and where

n8n splits its persistent state across two places, and that split is the whole reason a database-only backup is a trap.

The database

Postgres holds the things that change as you build and run: workflow definitions, credentials (encrypted at rest), execution history, and user accounts. For self-hosting, n8n’s supported databases are SQLite (the default) and PostgreSQL (recommended for production). MySQL and MariaDB support was deprecated with n8n 1.0; do not start new production work on it.

You select Postgres with database environment variables, starting with DB_TYPE=postgresdb and the DB_POSTGRESDB_* connection set. A common copy-paste error is DB_TYPE=postgres, which is wrong; the value is postgresdb.

The encryption key and ~/.n8n/config

On first start, n8n generates an encryption key and writes it to a config file in the ~/.n8n directory. In the official Docker image that path is /home/node/.n8n/config, and the key is the encryptionKey field inside it. This key is not in the database. It is the second half of your backup, and it is the half almost everyone forgets.

What a wrong key produces

n8n encrypts each credential before writing it to the database and decrypts it only in memory, at execution time. Restore a database under a different key and you get the error every n8n operator eventually meets: Credentials could not be decrypted. The likely reason is that a different "encryptionKey" was used to encrypt the data. There is no workaround for a key that is simply gone. The credentials are not recoverable; they have to be re-entered by hand. The workflows survive, because they are not encrypted. The connections they depend on do not.

Scope the job: what a complete backup includes

Before choosing a tool, decide what you are backing up and to what targets. Skipping this step is how people end up with a perfect dump of half the system.

The minimum set

A logical dump of the n8n database, and a separate copy of the value of N8N_ENCRYPTION_KEY. Miss either and recovery is partial or impossible.

The full set for a bare-metal restore

To rebuild from nothing on a fresh host you also need the things that define the instance: your compose.yaml, your .env, and the named volume mounted at /home/node/.n8n. Add external binary data if you have configured it. By default n8n keeps binary data in memory rather than in the database (mode default); but if you set N8N_DEFAULT_BINARY_DATA_MODE to filesystem or s3, those payloads live outside the database, so they are not in the dump and need their own backup.

Define RPO and RTO first

Two numbers drive every later decision. Recovery point objective (RPO) is how much execution data you can afford to lose, measured in time. Recovery time objective (RTO) is how long you can take to bring the instance back. If losing a day of execution history is acceptable, a scheduled daily dump is the right tool. If your RPO is measured in minutes, a daily dump is the wrong tool, and the next section explains what replaces it.

Choose a method: logical versus physical

This is the core decision, and for almost every self-hosted n8n the answer is a logical dump with pg_dump. The database is small, the dump is portable across Postgres versions, and the restore is simple. Continuous archiving and point-in-time recovery (PITR) are an upgrade path, not a default, and it is worth being honest about where the line sits.

pg_dump custom format

pg_dump takes an internally consistent snapshot as of the moment it starts, without blocking readers or writers. The custom format (-Fc) is compressed by default and supports selective and parallel restore through pg_restore. Dumps are forward-portable: a dump taken from an older server restores into a newer one. This is the backbone of the article.

pg_dumpall for roles and globals

pg_dump dumps a single database. It does not capture roles or other cluster-wide objects. If you run n8n under a dedicated role, or your cluster has globals you care about, add a pg_dumpall --globals-only (or --roles-only) alongside the per-database dump. The SQL dump documentation covers the split.

When to graduate to WAL archiving and PITR

The PostgreSQL documentation is direct on this point: beyond simple cases, pg_dump is not the right tool for routine production backups. For a low RPO you use continuous archiving, a base backup plus a stream of write-ahead log (WAL) segments, which lets you restore to any point in time. Two constraints matter. First, pg_dump and pg_dumpall cannot be part of a continuous-archiving setup; a logical dump contains no WAL to replay. Second, PITR restores the whole cluster, not one database, and it costs more storage and operational complexity. If you need it, reach for a tool built for it: pgBackRest, Barman, or WAL-G. For a single-VPS n8n with a daily RPO, this is overkill, and the dump wins.

Take the dump: the command that matters

The command is short. The detail that bites people is in the flags and, on Docker, in a single character.

Native or host Postgres

pg_dump -Fc --no-owner --no-privileges -U n8n n8n > "n8n-$(date +%Y%m%d-%H%M%S).dump"

Docker Compose

docker compose exec -T postgres pg_dump -Fc --no-owner --no-privileges -U n8n n8n > "n8n-$(date +%Y%m%d-%H%M%S).dump"

The -T is not optional, and leaving it out is the most common way to produce a dump that will not restore. docker compose exec allocates a pseudo-TTY by default. A TTY rewrites the byte stream, injecting carriage-return characters that corrupt a binary -Fc dump as it is redirected to a file. The damage is silent at dump time; it surfaces later when pg_restore fails, often as an “implied data-only restore” error or a crash. -T disables the pseudo-TTY and the stream stays clean.

The plain docker exec command behaves differently: it does not allocate a TTY unless you pass -t, so docker exec postgres pg_dump -Fc ... > file is already safe. The dangerous forms are -it and a bare docker compose exec without -T.

One version rule applies regardless of how you invoke it: the pg_dump client must be the same major version as the server, or newer. In a Docker setup the Postgres image tag fixes the client version, so dumping from inside the database container guarantees a match.

Flags worth setting and why

-Fc selects the custom format for pg_restore. --no-owner and --no-privileges drop ownership and grant statements, which makes the dump portable to an instance where the role names differ; without them a restore can fail on a missing role. Compression level is a -Z knob on the size-versus-CPU trade-off and the default is reasonable. For n8n you are almost always dumping one database, so a single pg_dump is enough.

Automate it: cron, systemd timer, or a sidecar

Three schedulers work. Pick by how much control and visibility you want.

Host cron

The simplest option. A crontab line runs the dump on a schedule and appends to a log:

30 3 * * * docker compose -f /opt/n8n/compose.yaml exec -T postgres pg_dump -Fc --no-owner --no-privileges -U n8n n8n > /opt/n8n/backups/n8n-$(date +\%Y\%m\%d).dump 2>> /opt/n8n/backups/backup.log

Note the escaped % characters; cron treats a bare % as a newline.

systemd timer

A timer gives you better failure visibility than cron, because output and exit status land in journald where systemctl status and journalctl can show them. You write a .service unit that runs the dump and a .timer unit that schedules it. The operational win is that a failed run is queryable rather than buried in a log file you have to remember to read.

Sidecar backup container

The lowest-effort path inside a Compose stack is a dedicated backup service. prodrigestivill/postgres-backup-local runs scheduled dumps with rotation and a health endpoint built in:

services:
  postgres:
    image: postgres:18
    restart: unless-stopped
    environment:
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n
      POSTGRES_PASSWORD: REPLACE_WITH_STRONG_PASSWORD
    volumes:
      - pgdata:/var/lib/postgresql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U n8n -d n8n"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgbackups:
    image: prodrigestivill/postgres-backup-local:18
    restart: unless-stopped
    user: postgres:postgres
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      POSTGRES_HOST: postgres
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n
      POSTGRES_PASSWORD: REPLACE_WITH_STRONG_PASSWORD
      SCHEDULE: "@daily"
      BACKUP_KEEP_DAYS: 7
      BACKUP_KEEP_WEEKS: 4
      BACKUP_KEEP_MONTHS: 6
      HEALTHCHECK_PORT: 8080
    volumes:
      - /opt/n8n/backups:/backups

volumes:
  pgdata:

Two current-stack details are baked into that file. The Postgres image is pinned to 18, and the data volume is mounted at /var/lib/postgresql, not the older /var/lib/postgresql/data. The official Postgres image changed its volume location for major 18 and above; guides that still mount /var/lib/postgresql/data predate that change. Pin the backup image tag to match your Postgres major (:18 here) so the dump client is not older than the server.

One honest caveat: by default this container writes a gzipped plain-SQL dump (.sql.gz), not a -Fc custom-format archive, so you restore it with zcat file.sql.gz | psql, not pg_restore. To get custom format you set POSTGRES_EXTRA_OPTS to include -Fc and adjust the suffix. Match your restore procedure to whichever format you produce.

The trap of backing up n8n from inside n8n

It is tempting to build an n8n workflow that backs up its own database. As the only mechanism, it fails on two counts.

First, if n8n is down, the workflow that backs it up is down with it. A backup that requires the thing it protects to be healthy is not a backup.

Second, memory. n8n holds execution data in the Node.js V8 heap, whose ceiling is set by Node’s --max-old-space-size (the V8 default lands near 2 GB unless you raise it). If the dump passes through workflow memory, an Execute Command node that captures stdout, or a Read/Write Files node that pulls the dump into binary data, a large dump triggers JavaScript heap out of memory and crashes the instance; under Docker it then restarts. The memory-errors documentation describes the failure. This specific crash is avoidable: if pg_dump writes straight to a file with -f and nothing streams through stdout, the heap stays clear. But the first objection stands regardless, so treat an in-n8n backup as an extra channel at most, never the primary.

Back up the encryption key and config: the step everyone skips

This is the half of the backbone that the database dump does not cover. Do it explicitly and store it apart from the dumps.

Find the current key

docker compose exec n8n cat /home/node/.n8n/config

The encryptionKey field is the value you must preserve.

Pin it as N8N_ENCRYPTION_KEY

Rather than leaving the key to live only in an auto-generated file, set it explicitly as an environment variable so it is part of your configuration and survives a volume loss:

N8N_ENCRYPTION_KEY=REPLACE_WITH_GENERATED_ENCRYPTION_KEY

Generate a strong value once with openssl rand -base64 32, set it before the first production boot, and keep it constant across restarts and upgrades. In queue mode the same key is mandatory on the main instance and every worker; a worker with a different key cannot decrypt the credentials it is asked to use.

Where to store it

Put the key in a secret manager, or at least in an encrypted location that is not the same place as your database dumps. The point is blast radius: if one leak exposes both the dump and the key that unlocks it, the encryption protected nothing. Lock the file down with chmod 600 and keep it out of the backup bucket that holds the dumps.

Retention, rotation, and getting backups off the server

A backup on the same disk as the database is not a backup. It dies with the disk.

Rotation scheme

Use grandfather-father-son (GFS): keep several daily backups, fewer weekly, fewer still monthly. The sidecar container implements this directly through BACKUP_KEEP_DAYS, BACKUP_KEEP_WEEKS, and BACKUP_KEEP_MONTHS, hard-linking the latest backup of each period so older categories cost almost no extra space. Choose retention by how far back you would ever want to restore.

Off-server copy

Apply the 3-2-1 rule, a long-standing operational baseline: three copies, on two kinds of media, with one off-site. In practice that means shipping each dump to object storage (any S3-compatible target) or over SFTP to a separate host, on a schedule, so a lost server does not take the only copy with it.

Encrypt at rest and lock permissions

Encrypt the archives before they leave, so a compromised bucket does not hand over your data. Restrict who can read the bucket. And keep the permission discipline from the previous section: chmod 600 on the .env and the key file, minimal access everywhere the dump or the key can be read.

Restore and prove it: the only test that counts

Everything above is theory until you restore. A backup you have never restored is a hypothesis. Two contexts need different handling, and conflating them is how a restore drill turns into an outage.

In-place restore for disaster recovery

When you restore into the live database, a running n8n holds open connections and keeps writing new executions. Open connections block DROP DATABASE: Postgres refuses to drop a database while sessions are connected to it (PostgreSQL 13 and later offer DROP DATABASE ... WITH (FORCE) to terminate them). And a pg_restore --clean collides with active writes. So stop the instance first:

docker compose stop n8n
docker compose exec -T postgres pg_restore --clean --if-exists --no-owner --no-privileges -U n8n -d n8n < n8n-20260601.dump
docker compose start n8n

For a training drill into a throwaway database, none of this applies, and you do not stop production. The distinction is in-place recovery versus an isolated drill.

Restore a custom-format dump

Use pg_restore for archive formats (-Fc and the directory format). For a plain-SQL dump, use psql instead, and create the target database from template0 first so no local modifications to the default template leak into the restore:

createdb -T template0 n8n_restore
psql -U n8n -d n8n_restore < dump.sql

Bring n8n up against it

Start an n8n instance pointed at the restored database with the matching N8N_ENCRYPTION_KEY. Then verify the part that fails when a backup is incomplete: open a few credentials and confirm they decrypt, and run a workflow that uses one. Workflows loading is not proof. Credentials decrypting is.

Cross-key recovery

If the key is lost or differs between instances, the dump alone will not save the credentials, but n8n’s CLI provides an escape hatch. A decrypted export produces a portable (and plaintext) file that a new instance re-encrypts with its own key:

docker compose exec -u node -T n8n n8n export:credentials --all --decrypted > credentials.json

A few details in that command earn their place. -T strips the TTY artifacts that would otherwise corrupt the redirected output, the same reason it appears in the dump command. -u node names the user explicitly; the official image already runs as node (UID 1000), so this matters only if you have overridden the container to run as root with user: root, which is exactly the case where writing into /home/node/.n8n as root would fail with permission denied. And the > redirect writes the file on the host as your host user, not inside the container. The same --backup pattern works for workflows with n8n export:workflow --backup, and the matching import:* commands load them back. For moving between database types, export:entities and import:entities carry the full set across. Treat the decrypted file as a secret and delete it once the new instance has imported it.

Schedule a restore drill

Make the restore recurring, not a one-time event you did during setup and never repeated. Schedules drift, dumps silently break, formats get misremembered. A periodic drill into an isolated database is the only thing that keeps “we have backups” from being a sentence you find out is false at the worst possible moment.

Monitor the backups: silent failure is the default

Backups fail quietly. A zero-byte dump, a full disk, an expired credential on the backup target; none of these announce themselves, and the failure is invisible until you reach for a backup that is not there.

Liveness ping

Add a dead-man’s switch. A service like Healthchecks.io or a self-hosted Uptime Kuma expects a ping at a set interval; the absence of the ping is what raises the alert. The backup job pings on success, so a job that stops running stops pinging and you hear about it. The sidecar container’s HEALTHCHECK_PORT exposes the same signal for a scheduler that polls it.

Sanity checks

Have the job check that the dump is larger than a sane floor (a few kilobytes is already suspicious for a real n8n database) and that pg_dump exited zero. A dump that is technically a file but is in fact an error message is the failure mode these two checks catch.

Edge cases and boundaries

A few situations sit outside the main line and deserve explicit handling.

Managed Postgres

On RDS, Cloud SQL, or similar, the provider’s snapshots and PITR cover the database, and you should use them; consult the provider’s own documentation for specifics rather than trusting a remembered procedure. What the provider never covers is the n8n encryption key. That is still yours to back up. A logical pg_dump remains useful even here for portability and granular restore between instances.

Queue mode

The same N8N_ENCRYPTION_KEY must be present on the main instance and every worker. This is the most common queue-mode credential failure: a worker with the wrong key cannot decrypt, and executions fail on it while succeeding elsewhere.

Execution data pruning

Before treating a huge execution_entity table as a backup problem, prune it. Pruning is enabled by default and driven by environment variables: EXECUTIONS_DATA_PRUNE (default true), EXECUTIONS_DATA_MAX_AGE (default 336 hours, 14 days), EXECUTIONS_DATA_PRUNE_MAX_COUNT (default 10000), and EXECUTIONS_DATA_HARD_DELETE_BUFFER (default 1 hour). There is no execute:prune CLI command; the CLI has execute, the export:* and import:* families, export:entities/import:entities, and others, but not a prune verb. To shrink a database that ran a long time without pruning, set these variables and restart so the prune cycle runs, delete old runs from the Executions tab in the UI, or issue a direct DELETE FROM execution_entity WHERE .... Deleting rows shrinks the next dump immediately, because the dump only contains rows that exist; VACUUM and VACUUM FULL are about reclaiming disk on the live database, and VACUUM FULL takes an exclusive lock while it rewrites the table. The durable state you most need to protect is workflows, credentials, and settings, not execution logs.

External binary data

If binary data lives outside the database, in filesystem or S3 mode, it is not in the dump and needs its own backup with its own schedule. Restoring the database without it leaves references to payloads that are not there.

Which method when

The one thing to keep

If you remember a single sentence from this, keep this one: the database dump is the centerpiece, but a restore without the encryption key, and without a test, is worth nothing. Back up the dump, back up the key separately, ship both off the server, and restore the pair on a schedule. That is the difference between a backup strategy and a folder of files you hope are good.