Execution data pruning does not work

Perguntas
1

I’m using self-hosted n8n version with postgres database. I’m trying to enable automatic execution pruning using examples in documentation, but it doesn’t work for me. There are still old executions in execution_data in postgres.

This is my env variables in n8n image in docker compose:
- DB_TYPE=postgresdb
- DB_POSTGRESDB_HOST=postgres
- DB_POSTGRESDB_PORT=5432
- DB_POSTGRESDB_DATABASE=${POSTGRES_DB}
- DB_POSTGRESDB_USER=${POSTGRES_USER}
- DB_POSTGRESDB_PASSWORD=${POSTGRES_PASSWORD}
- EXECUTIONS_DATA_PRUNE=true
- EXECUTIONS_DATA_MAX_AGE=1

current size of execution_data table is 21GB, execution_entity 142MB

What is the error message (if any)?

Information on your n8n setup

  • n8n version: docker.n8n.io/n8nio/n8n:1.19.4
  • Database (default: SQLite): postgres:11-alpine
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): docker-compose
  • Operating system: Ubuntu 22.04.2 LTS
2

UPD. All executions older than 1 hour has disappeared after all, but only from GUI. I still have 21GB of them in postgres

3

Hey @Andrey_Bogomolov,

Welcome to the community :raised_hands:

The execution_data table should contain the data from the workflow and only exist if there is a matching execution_entity record, In this case it would be worth checking the execution_entity table for the oldest record or… Check the execution_data table for any records that don’t exist in the execution_entity table with something like…

SELECT
  "executionId"
FROM
  execution_data ed
WHERE
  NOT EXISTS (
    SELECT
    FROM
      execution_entity
    WHERE
      id = ed."executionId"
  );

It could be that the data is being removed or maybe everything is only being soft deleted and the hard delete isn’t kicking. This should in theory tell us a bit more, I have checked on my own local postgres install and everything appears to be working as expected which is a good sign although I am on a newer version of n8n than you.

4

Hello Jon,

Your request returned no data for me. By the way, the request SELECT COUNT(*) FROM execution_data ed; returns 262115 in my case, and SELECT COUNT(*) FROM execution_entity; returns 262255.

5

I can provide full docker-compose.yml if it will help

version: '3.8'

volumes:
  db_storage:
  n8n_storage:

services:
  postgres:
    image: postgres:11-alpine
    restart: always
    command: -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
    environment:
      - POSTGRES_USER
      - POSTGRES_PASSWORD
      - POSTGRES_DB
      - POSTGRES_NON_ROOT_USER
      - POSTGRES_NON_ROOT_PASSWORD
    networks:
      - n8n
    volumes:
      - db_storage:/var/lib/postgresql/data
      - ./init-data.sh:/docker-entrypoint-initdb.d/init-data.sh
      - /root/postgres/server.crt:/var/lib/postgresql/server.crt:Z
      - /root/postgres/server.key:/var/lib/postgresql/server.key:Z
    ports:
      - 5432:5432
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -h localhost -U ${POSTGRES_USER} -d ${POSTGRES_DB}']
      interval: 5s
      timeout: 5s
      retries: 10

  n8n:
    image: docker.n8n.io/n8nio/n8n:1.19.4
    restart: always
    environment:
      - N8N_EMAIL_MODE=smtp
      - N8N_SMTP_HOST=smtp.go1.domain.com
      - N8N_SMTP_PORT=465
      - N8N_SMTP_USER=some_user
      - N8N_SMTP_PASS=some_pass
      - N8N_SMTP_SENDER=no_reply@mydomain.com
      - WEBHOOK_URL=https://mydomain.com
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=${POSTGRES_DB}
      - DB_POSTGRESDB_USER=${POSTGRES_USER}
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_PASSWORD}
      - EXECUTIONS_DATA_PRUNE=true
      - EXECUTIONS_DATA_MAX_AGE=1
    ports:
      - 5678:5678
    networks:
      - n8n
    links:
      - postgres
    volumes:
      - n8n_storage:/home/node/.n8n
    depends_on:
      postgres:
        condition: service_healthy

networks:
  n8n:
    external: true
6

Hey @Andrey_Bogomolov,

In theory if the query returns nothing there is no data there that shouldn’t be and everything has a match although your query output seems somewhat odd as you do have different counts so it looks like you have some execution_entity records without any workflow data.

What is the max age of the records in the database? There is not really any difference between our ocmpose options other than I am using a newer n8n version in which case upgrading would fix it for you, I am on a newer Postgres version as I upgraded from 11 to 14 a while back but I can’t see this being the cause and the other thing is my max age is 30 instead of 1.

For science I have just updated my install to use 1 to see if that is the issue.