\COPY command not working in Postgres node

Describe the problem/error/question

Hello,

I had an Postgres to CSV export running with select statement in Postgres node and then writing the output to a CSV file. But as the table is increasing more and more it’s not finishing the job anymore.
So I wanted to switch to the described solution with the COPY or \COPY query (see How to export data from PostgreSQL to CSV – n8n Blog) but it seems that this is not working either (anymore?).

What is the error message (if any)?

COPY:
grafik

COPY to a file is not supported
Anyone can COPY to stdout or from stdin. psql’s “\copy” command also works for anyone.

Okay, after searching around a bit about it, it seems that higher privileges are needed, so then trying to switch to…
\COPY:
grafik

So it seems that the Postgres-Node is somehow not even recognizing the \COPY command at all. But it is working when I am trying the same query directly from an psql client/shell locally or remote from any other system.

( FYI: I had to add those query stuff above as an image as the security policy otherwise was rejecting the creation of my new topic with 403 :wink: )

Information on your n8n setup

  • n8n version: 1.100.1
  • Database (default: SQLite): postgres:17-alpine
  • n8n EXECUTIONS_PROCESS setting (default: own, main): Queue
  • Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
  • Operating system: Docker Bookworm

Question

Is there any way how I can get that \COPY command working within the Postgres node on my own or any hope that it is getting supported via an update version quite soon? :slight_smile:

Thanks in advance!

1 Like
  • COPY TO file requires direct server filesystem access, which n8n’s Postgres node doesn’t have (it connects via network protocol)
  • \COPY is a psql client command, not a SQL statement - the n8n node sends raw SQL without psql interpretation
  • Security context: n8n runs as a different user than your psql client, with restricted filesystem permissions in Docker
  • The Postgres node uses pg library which only supports SQL-level commands, not client-side meta-commands

Here Try this , this is a code from deepseek AI

1 Like

Yes, also thought about doing it via Shell-Node and with having psql used in there but won’t work like described from deepseek AI as it is an external postgres server where I am not having directly access to.

So, ended up with the following which is working quite good & fast:
a) SSH into an instance with psql installed and executing the command from there and also gzip’ing it
b) Get file over SSH

2 Likes