Stuck on deploying n8n docker + caddy + external postgresdb/redis

[Nir]

Hi,

I’ve been trying to set up n8n (master/worker) with caddy reverse proxy and external postgresdb and redis on digitalocean without success, I set up 2 managed databases (1 redis(v7) and 1 postgresdb(v15)) and allowed traffic to/from the n8n droplet.
I created a new user for n8n and a new db for n8n with the same name.

I cloned the official docker + caddy git, and followed this guide

Digital Ocean | n8n Docs

(A records + volume creation + caddy config)

I saw that Digitalocean requires SSL connection so I uploaded the CA cert as well

This is my docker-compose:

version: "3.7"

services:
  caddy:
    image: caddy:latest
    restart: unless-stopped
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ${DATA_FOLDER}/caddy_data:/data
      - ${DATA_FOLDER}/caddy_config:/config
      - ${DATA_FOLDER}/caddy_config/Caddyfile:/etc/caddy/Caddyfile

  n8n:
    image: n8nio/n8n
    restart: always
    ports:
      - 5678:5678
    environment:
      - N8N_LOG_LEVEL=debug
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_SSL_CA=/certs/db.crt      
      - DB_POSTGRESDB_HOST=DigitalOceanPGVPCHost
      - DB_POSTGRESDB_PORT=25060
      - DB_POSTGRESDB_DATABASE=dbname
      - DB_POSTGRESDB_USER=dbuser
      - DB_POSTGRESDB_PASSWORD=dbuserpass
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=myuser
      - N8N_BASIC_AUTH_PASSWORD=mypass
      - N8N_ENCRYPTION_KEY=mykey
      - EXECUTIONS_MODE=queue
      - QUEUE_BULL_REDIS_HOST=DigitalOceanRedisVPCHost
      - QUEUE_BULL_REDIS_PORT=25061
      - QUEUE_BULL_REDIS_USERNAME=default
      - QUEUE_BULL_REDIS_PASSWORD=mypass
      - N8N_HOST=${SUBDOMAIN}.${DOMAIN_NAME}
      - N8N_PORT=5678
      - NODE_ENV=production
      - WEBHOOK_URL=https://${SUBDOMAIN}.${DOMAIN_NAME}/
      - GENERIC_TIMEZONE=${GENERIC_TIMEZONE}
    volumes:
      - ${DATA_FOLDER}/local_files:/files
      - ${DATA_FOLDER}/.n8n:/home/node/.n8n      
      - ${DATA_FOLDER}/ca-certificate.crt:/certs/db.crt      

volumes:
  caddy_data:
    external: true
  caddy_config:

and .env:

# Path where you created folders earlier. 
# Change this if you didn't create them in the repo checked out in the root directory.
DATA_FOLDER=/root/n8n-docker-caddy

# The top level domain to serve from, this should be the same as the subdomain you created above
DOMAIN_NAME=mydomain.com

# The subdomain to serve from
SUBDOMAIN=mysubdomain

# DOMAIN_NAME and SUBDOMAIN combined decide where n8n will be reachable from
# above example would result in: https://n8n.example.com

# Optional timezone to set which gets used by Cron-Node by default
# If not set New York time will be used
GENERIC_TIMEZONE=Europe/Berlin

# The email address to use for the SSL certificate creation
[email protected]

Error message:

n8n_1 | 2023-05-18T10:13:43.208Z | error | Last session crashed “{ file: ‘CrashJournal.js’, function: ‘init’ }”
n8n_1 | 2023-05-18T10:13:53.419Z | debug | Lazy Loading credentials and nodes from n8n-nodes-base “{\n credentials: 312,\n nodes: 409,\n file: ‘DirectoryLoader.js’,\n function: ‘loadAll’\n}”
n8n-docker-caddy_n8n_1 exited with code 1

not much to go on, even when logging is set to verbose.

in docker logs, caddy sees the incoming connection but returns connection refused and in the browser I see 502 error

any help would be appreciated.
thanks

[Jon]

Hey @Nir,

Welcome to the community

I would maybe start with a single instance on something like SQlite first just to make sure it works then bring in the other components. For this case though the issue could be a few things, The one I think it is though is we don’t support TLS for Redis at the moment.

If you try dropping the queue mode settings and remove redis and see if that works it will help confirm if that is the case.

[Nir]

Thanks for the quick reply.

I removed the postgres and redis, n8n started up fine.

I tried readding only redis (I think they don’t force TLS for that, at least they dont mention it in the panel).
(if execution_mode is not set, it seems to ignore redis parameters because i put a wrong host on purpose and everything seemed to work fine)
with the execution mode set, its unable to connect, showing:
Redis unavailable - trying to reconnect…
until it exits.

(ufw is disabled, no firewall is enabled on DO droplet and I tried also opening up the DB to all incoming connections, no go)

leaving only the postgresdb, will also crash the n8n,
as far as i see all credentials are correct, its just not able to access the managed DO databases but I have no idea why, is PG 15 and Redis 7 supported or maybe I need an earlier version

[Jon]

Hey @Nir,

The Digital Ocean Postgres database should be ok I have used it in the past but I do know that their Redis service won’t work.

It could just be a TLS issue when talking to Postgres, Have you tried connecting to the database from n8n using sqlite to see if that works

[Nir]

Using the node connecting to the postgres works (only if “Ignore SSL issues” is enabled, i dont see anywhere to insert a ca-certificate in the credentials)

one thing to note, the DB is completely empty, should I initialize it with any tables?

noticed that this was missing
DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED=false

I added it but still crashing

[Jon]

Hey @Nir,

The database tables will be created once it connects so you won’t have to do anything there, can you try setting DB_LOGGING_ENABLED to true and DB_LOGGING_OPTIONS to all then start up the container and see if it logs anything extra? You may also need to set DEBUG to *

[Nir]

Added
- N8N_LOG_LEVEL=verbose
- DB_LOGGING_ENABLED=true
- DB_LOGGING_OPTIONS=all

Now we’re getting somewhere

n8n_1    | query: SELECT * FROM current_schema()
n8n_1    | query: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
n8n_1    | query: SELECT version();
n8n_1    | query: SET search_path TO public;
n8n_1    | query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'migrations'
n8n_1    | query: CREATE TABLE "migrations" ("id" SERIAL NOT NULL, "timestamp" bigint NOT NULL, "name" character varying NOT NULL, CONSTRAINT "xxxxxxxxxxxx" PRIMARY KEY ("id"))
n8n_1    | query failed: CREATE TABLE "migrations" ("id" SERIAL NOT NULL, "timestamp" bigint NOT NULL, "name" character varying NOT NULL, CONSTRAINT "xxxxxxxxxx" PRIMARY KEY ("id"))
n8n_1    | error: error: permission denied for schema public
n8n_1    | 2023-05-19T06:08:54.730Z | error    | Last session crashed "{ file: 'CrashJournal.js', function: 'init' }"
n8n-docker-caddy_n8n_1 exited with code 1

Apparantly, for PG 15, you need to do this first:

Connect to the DB first using

psql -p 25060 "postgresql://doadmin:password@yourdigitaloceandatabase:25060/dbname?sslmode=require"

run (change depending on your db user/name)

GRANT ALL ON DATABASE dbname TO dbuser;
ALTER DATABASE dbname OWNER TO dbuser;
GRANT USAGE, CREATE ON SCHEMA PUBLIC TO dbuser;

and now it works!
thanks

Regarding redis and getting it to work, as far as i can see there is no way to disable TLS on managed database, so I need to set up a separate redis droplet and have all the worker nodes connected to it?
Hope that TLS will be supported soon

[Jon]

Hey @Nir,

For Redis you got it, We do have a PR open to add in TLS support but we have not yet reviewed it.