PostgreSQL errors?

[nle]

I recently experienced SQLITE_BUSY: database is locked error from one of my nodes, and figured I should change to PostgreSQL.

I first exported all workflows and credentials, added the environment variables (using docker), started a clean instance and imported the credentials and workflows.

Everything seem to work, but I get the following in the postgressql logs:

PostgreSQL Database directory appears to contain a database; Skipping initialization

2023-04-13 16:43:19.026 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-04-13 16:43:19.026 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-04-13 16:43:19.028 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-04-13 16:43:19.040 UTC [26] LOG:  database system was shut down at 2023-04-13 16:39:06 UTC
2023-04-13 16:43:19.045 UTC [1] LOG:  database system is ready to accept connections
2023-04-13 16:43:27.398 UTC [42] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:43:27.398 UTC [42] HINT:  Must be superuser to create this extension.
2023-04-13 16:43:27.398 UTC [42] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2023-04-13 16:43:32.023 UTC [52] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:43:32.023 UTC [52] HINT:  Must be superuser to create this extension.
2023-04-13 16:43:32.023 UTC [52] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2023-04-13 16:43:51.944 UTC [91] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:43:51.944 UTC [91] HINT:  Must be superuser to create this extension.
2023-04-13 16:43:51.944 UTC [91] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2023-04-13 16:44:09.611 UTC [116] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:44:09.611 UTC [116] HINT:  Must be superuser to create this extension.
2023-04-13 16:44:09.611 UTC [116] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2023-04-13 16:44:12.002 UTC [126] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:44:12.002 UTC [126] HINT:  Must be superuser to create this extension.
2023-04-13 16:44:12.002 UTC [126] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2023-04-13 16:44:12.333 UTC [124] ERROR:  duplicate key value violates unique constraint "workflow_statistics_pkey"
2023-04-13 16:44:12.333 UTC [124] DETAIL:  Key ("workflowId", name)=(4, data_loaded) already exists.
2023-04-13 16:44:12.333 UTC [124] STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)
2023-04-13 16:44:12.396 UTC [124] ERROR:  duplicate key value violates unique constraint "workflow_statistics_pkey"
2023-04-13 16:44:12.396 UTC [124] DETAIL:  Key ("workflowId", name)=(4, production_success) already exists.
2023-04-13 16:44:12.396 UTC [124] STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)
2023-04-13 16:44:32.037 UTC [161] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:44:32.037 UTC [161] HINT:  Must be superuser to create this extension.
2023-04-13 16:44:32.037 UTC [161] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
2023-04-13 16:44:32.354 UTC [80] ERROR:  duplicate key value violates unique constraint "workflow_statistics_pkey"
2023-04-13 16:44:32.354 UTC [80] DETAIL:  Key ("workflowId", name)=(4, data_loaded) already exists.
2023-04-13 16:44:32.354 UTC [80] STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)
2023-04-13 16:44:32.419 UTC [80] ERROR:  duplicate key value violates unique constraint "workflow_statistics_pkey"
2023-04-13 16:44:32.419 UTC [80] DETAIL:  Key ("workflowId", name)=(4, production_success) already exists.
2023-04-13 16:44:32.419 UTC [80] STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)
2023-04-13 16:44:33.205 UTC [162] ERROR:  permission denied to create extension "uuid-ossp"
2023-04-13 16:44:33.205 UTC [162] HINT:  Must be superuser to create this extension.
2023-04-13 16:44:33.205 UTC [162] STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"

And it keeps on …

Any input on how I can solve this?

EDIT:
Ended up upgrading via dumping and restoring postgresdb from 11 to 15 – also upgraded from 0.222.2 to 0.223.0 (as per this post). That seems to have helped for the uuid-ossp problem, but I still see:

2023-04-13 17:55:42.255 UTC [521] ERROR:  duplicate key value violates unique constraint "workflow_statistics_pkey"
2023-04-13 17:55:42.255 UTC [521] DETAIL:  Key ("workflowId", name)=(4, data_loaded) already exists.
2023-04-13 17:55:42.255 UTC [521] STATEMENT:  INSERT INTO "public"."workflow_statistics"("count", "latestEvent", "name", "workflowId") VALUES ($1, $2, $3, $4)

Information on your n8n setup

• n8n version: 0.223.0
• Database you’re using (default: SQLite): PostgreSQL 15.2
• Running n8n with the execution process: own
• Running n8n via: Docker

[MutedJam]

Hi @nle, I am so sorry for this. I was able to confirm this on my side even on the current n8n version 0.224.1, I’ll once again bring this up to the engineering team and will post an update when I hear back.

[MarioGuimaraes]

Hi @nle
this error:
permission denied to create extension “uuid-ossp”

I solved with this:
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c ‘CREATE EXTENSION “uuid-ossp”;’

my service in docker-compose:
n8n_postgres:
image: postgres:11
container_name: postgres-n8n
restart: always
environment:
- POSTGRES_USER
- POSTGRES_PASSWORD
- POSTGRES_DB
- POSTGRES_NON_ROOT_USER
- POSTGRES_NON_ROOT_PASSWORD
networks:
- internal_network
volumes:
- postgres-data:/var/lib/postgresql/data
- ./init-data.sh:/docker-entrypoint-initdb.d/init-data.sh
healthcheck:
test:
[
‘CMD-SHELL’,
‘pg_isready -h localhost -U ${POSTGRES_USER} -d ${POSTGRES_DB}’
]
interval: 5s
timeout: 5s
retries: 10

Observe this line:

• ./init-data.sh:/docker-entrypoint-initdb.d/init-data.sh
  This file is the secret to solve this problem init-data.sh

Content the 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
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c ‘CREATE EXTENSION “uuid-ossp”;’
else
echo “SETUP INFO: No Environment variables given!”
fi

And my .env:
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_DB=your_db_name
POSTGRES_NON_ROOT_USER=your_user_non_root
POSTGRES_NON_ROOT_PASSWORD=your_password_non_root

There are in the same folder

[MarioGuimaraes]

the second problem:
duplicate key

is being discussed here too:

Duplicate key value violates unique constraint "workflow_statistics_pkey"

Same issue here. n8n v.221.2 & postgres-ha. My n8n has hundreds of thousands of executions per day. I believe what @MutedJam said was the cause, that n8n logs the ‘error’ whenever an execution occurs and write to an existing row, instead of doing something like ‘increment if exists’. I can’t tell if my n8n’s performance is affected, but the log file is getting quite crazy lol. It’d be great if you guys could find a solution to this. Thank you for the hard work!