How to insert CSV data into PostgreSQL database (remote database )

\copy (note the backslash) lets you copy to/from remote databases and does not require superuser privileges.

psql -h remotehost -d remote_mydb -U myuser -c "\copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

If you're using Java, you can use the CopyManager in the JDBC driver: https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html

You can feed the file via STDIN. From the PostgreSQL COPY documentation...

When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

psql -h remotehost -d remote_mydb -U myuser -c \
    "copy mytable (column1, column2) from STDIN with delimiter as ','" \
    < /path/to/local/file.csv

---

I was incorrect about using FROM PROGRAM. It has the same caveats as FROM 'filename'. The server executes the program, not the client.

When PROGRAM is specified, the server executes the given command and reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user.