Using COPY in psql script does not write to file
I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.


No all of the commands don't have to be on the same line, but that's not the problem here. COPY is saving the file on the server. You want it on the client, that's what the psql command \COPY does. I assume you're connecting to the remote server which is where the file is. Likely you could just scp or rsync it back to the client or the like, but that's a bad idea. You're better off doing it with just a client.

There are numerous ways to put the \copy on a different line. For instance, take this test.psql

CREATE TEMP TABLE t AS VALUES (1,2), (2,3);
\COPY t TO /tmp/1
CREATE TEMP TABLE b AS VALUES (1,2), (2,3);
\COPY t TO /tmp/2


You can run it with psql -d test -f test.psql


COPY is a PostgreSQL server-command that is made to look like SQL.
\COPY is a PostgreSQL client (psql)-command that is made to look like COPY