Proper setup tutorial

Questions
1

Has anyone come across an accurate and encompassing tutorial on setting up n8n with traefik and postgres? We’ve self-hosted before and run into issues with database loss, possibly due to not setting it up properly in the first place. All the tutorials we’ve seen don’t deal with postgres and everything I’ve seen in here regarding postgres just points to the very basic n8n provided documentation that doesn’t include anything about traefik. We currently are with n8n.cloud but need to move back to self-hosted due to workflow execution limits. Thank you.

2

Hey @cleveradmin,

There is no guide that I can find that has all 3 components linked up but what I would do is start with this: n8n/docker/compose/withPostgres at master · n8n-io/n8n · GitHub then add the Traefik confg from here: Server Setup | Docs and that should cover off what you need.

A quick example is below for the docker side although untested it should help you piece it together.

Docker Compose 
version: "3"

services:

  postgres:
    image: postgres:11
    restart: always
    environment:
      - POSTGRES_USER
      - POSTGRES_PASSWORD
      - POSTGRES_DB
      - POSTGRES_NON_ROOT_USER
      - POSTGRES_NON_ROOT_PASSWORD
    volumes:
      - ./init-data.sh:/docker-entrypoint-initdb.d/init-data.sh

  traefik:
    image: "traefik"
    restart: always
    command:
      - "--api=true"
      - "--api.insecure=true"
      - "--providers.docker=true"
      - "--providers.docker.exposedbydefault=false"
      - "--entrypoints.web.address=:80"
      - "--entrypoints.web.http.redirections.entryPoint.to=websecure"
      - "--entrypoints.web.http.redirections.entrypoint.scheme=https"
      - "--entrypoints.websecure.address=:443"
      - "--certificatesresolvers.mytlschallenge.acme.tlschallenge=true"
      - "--certificatesresolvers.mytlschallenge.acme.email=${SSL_EMAIL}"
      - "--certificatesresolvers.mytlschallenge.acme.storage=/letsencrypt/acme.json"
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ${DATA_FOLDER}/letsencrypt:/letsencrypt
      - /var/run/docker.sock:/var/run/docker.sock:ro

  n8n:
    image: n8nio/n8n
    restart: always
    ports:
      - "127.0.0.1:5678:5678"
    labels:
      - traefik.enable=true
      - traefik.http.routers.n8n.rule=Host(`${SUBDOMAIN}.${DOMAIN_NAME}`)
      - traefik.http.routers.n8n.tls=true
      - traefik.http.routers.n8n.entrypoints=web,websecure
      - traefik.http.routers.n8n.tls.certresolver=mytlschallenge
      - traefik.http.middlewares.n8n.headers.SSLRedirect=true
      - traefik.http.middlewares.n8n.headers.STSSeconds=315360000
      - traefik.http.middlewares.n8n.headers.browserXSSFilter=true
      - traefik.http.middlewares.n8n.headers.contentTypeNosniff=true
      - traefik.http.middlewares.n8n.headers.forceSTSHeader=true
      - traefik.http.middlewares.n8n.headers.SSLHost=${DOMAIN_NAME}
      - traefik.http.middlewares.n8n.headers.STSIncludeSubdomains=true
      - traefik.http.middlewares.n8n.headers.STSPreload=true
    environment:
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER
      - N8N_BASIC_AUTH_PASSWORD
      - N8N_HOST=${SUBDOMAIN}.${DOMAIN_NAME}
      - N8N_PORT=5678
      - N8N_PROTOCOL=https
      - NODE_ENV=production
      - WEBHOOK_URL=https://${SUBDOMAIN}.${DOMAIN_NAME}/
      - GENERIC_TIMEZONE=${GENERIC_TIMEZONE}
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=${POSTGRES_DB}
      - DB_POSTGRESDB_USER=${POSTGRES_NON_ROOT_USER}
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_NON_ROOT_PASSWORD}
    links:
      - postgres
    volumes:
      - ${DATA_FOLDER}/.n8n:/home/node/.n8n
    command: /bin/sh -c "sleep 5; n8n start"

Then the .env would be something like…

.env 
# Folder where data should be saved
DATA_FOLDER=/root/n8n/

# The top level domain to serve from
DOMAIN_NAME=example.com

# The subdomain to serve from
SUBDOMAIN=n8n

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

# The user name to use for authentication - IMPORTANT ALWAYS CHANGE!
N8N_BASIC_AUTH_USER=user

# The password to use for authentication - IMPORTANT ALWAYS CHANGE!
N8N_BASIC_AUTH_PASSWORD=password

# 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]

POSTGRES_USER=changeUser
POSTGRES_PASSWORD=changePassword
POSTGRES_DB=n8n

POSTGRES_NON_ROOT_USER=changeUser
POSTGRES_NON_ROOT_PASSWORD=changePassword

Then we have the init-data script…

init-data.sh 
#!/bin/bash
set -e;

if [ -n "${POSTGRES_NON_ROOT_USER:-}" ] && [ -n "${POSTGRES_NON_ROOT_PASSWORD:-}" ]; then
	psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
		CREATE USER ${POSTGRES_NON_ROOT_USER} WITH PASSWORD '${POSTGRES_NON_ROOT_PASSWORD}';
		GRANT ALL PRIVILEGES ON DATABASE ${POSTGRES_DB} TO ${POSTGRES_NON_ROOT_USER};
	EOSQL
else
	echo "SETUP INFO: No Environment variables given!"
fi

If it was me I would also add EXECUTIONS_DATA_PRUNE and EXECUTIONS_DATA_MAX_AGE so that it clears up older execution data from the database. Let me know how you get on.

3

I have followed these steps exactly and it all appeared to work. Then after adding in more workflows than I’d like to admit, I stopped the container just to make sure all was good. I restarted the container and everything was gone. Added one workflow and stopped/restarted. Same thing, but this time I didn’t launch it in daemon mode and watched the output. It appears to be creating a new database each time. Any thoughts on why that might be happening? If I run sudo docker-compose up and then ctrl+c it, the data is saved. But if I run sudo docker-compose up -d and then run sudo docker-compose down, the next time it starts the database init seems to be kicked off. What am I missing?

4

Ah I see the problem rookie mistake on my part, Forgot to add the storage volume to postgres so each time that container is updated or shutdown it will need to make the storage again.

So you would need to make a storage volume for the postgres data and under Volumes for it add something like the below.

- volume-name:/var/lib/postgresql/data

That should get you up and running.

5

So this fixes it, but I’m not 100% sure it actually is the resolution. Let me explain. I’ve matched my previous docker-compose.yml file that I’ve had previous discussions around to include the storage volume like this:

  - ${DATA_FOLDER}/postgres-data:/var/lib/postgresql/data

Now, that allows the data to survive reboots and docker downs and docker ups. However, and this is the issue I ran into the last time I self-hosted N8N, there is no postgres-data folder under /root/n8n, which is set as the DATA_FOLDER in .env.

DATA_FOLDER=/root/n8n/

There is a letsencrypt (traefik) folder and a .n8n folder (n8n), but no postgres-data folder. This is the issue I ran into via Update n8n (Docker), but decided against troubleshooting as I moved to n8n cloud.

6

Ok, so hardcoding the path appears to fix the underlying issue.

  - /root/n8n/postgres-data:/var/lib/postgresql/data

Any idea why ${DATA_FOLDER} doesn’t seem to work there?

7

It could be that the Postgres docker image doesn’t expand .env values, to answer that it would need someone to look at the Postgres container documentation.

It could be that for postgres you just need to add env_file: .env to the options for it to pick them up.

1 Like