Postgres PGVector Store and SUPERUSER attribute

[riovika]

Describe the problem/error/question

I connected the AI Agent node to the Postgres PGVector Store tool. After several successful test runs, an error message appeared in the Postgres PGVector Store node. The error message is:
remaining connection slots are reserved for roles with the SUPERUSER attribute.

After that, I checked the Postgres connection settings and noticed that the connection was not established.

What is the error message (if any)?

remaining connection slots are reserved for roles with the SUPERUSER attribute.

Please share your workflow/screenshots/recording

My_workflow.json

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)
⚠️ WARNING ⚠️ If you have sensitive data in your workflow (like API keys), please remove it before sharing.

Share the output returned by the last node

Debug info

core

• n8nVersion: 1.76.1
• platform: self-hosted
• database: postgres

storage

pruning

client

• userAgent: chrome
• isTouchDevice: false

My_workflow.json

Generated at: 2025-03-12T11:53:18.583Z}

[Joffcom]

Hey @riovika,

We have created an internal ticket to look into this which we will be tracking as "GHC-1163"

[Joffcom]

Hey @riovika

This doesn't appear to be a bug with n8n, Postgres is returning the error you are seeing.

remaining connection slots are reserved for roles with the SUPERUSER attribute.

A quick Google search shows a lot of others with the same issue and it looks to be resolved with some changes on the database side.

It is also possibly that we are not closing the connections so it could be worth an upgrade to see if any of the recent changes helps

[riovika]

Will updating n8n to the latest version affect the currently running processes in n8n?

[H1bertto]

Same issue here. Exactly one month ago, we changed our workflow to use PGVector instead of Pinecone, and it was running even better. However, starting about three weeks ago, every 4 to 5 days, I’ve been encountering this problem where a certain number of connections are reached, and many unused connections are not being closed.

The solutions that appear in this 'quick Google search' would work better if the database were accessed only by n8n, which is not my case, as they change the idle connection timeout policies. However, these changes could affect other database connections and end up creating a new problem.

The solution that has helped me contain the issue for now, without needing to modify the policies on my database, is to run the following command:

SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
 WHERE usename = '<YOUR_PGVECTOR_USER>' and state = 'idle' and query is Null 
   AND pid <> pg_backend_pid();

Sometimes, query is Null needed to be query = ''.

This ensures that connections not performing any action are terminated, freeing up access to your database for others.
To confirm which database user is occupying all these connections, you can run the following command:

SELECT *  FROM pg_stat_activity WHERE datname = '<YOUR_DB_NAME>'

My n8n version is 1.77.3

[Joffcom]

Hey @riovika

Upgrading shouldn't cause issues for currently running workflows but it has potential to cause problems so it is always worth making a backup before you upgrade so you can restore the older version if something does go wrong.