How I can copy from local file to remote DB in PostgreSQL? I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?

I am using the following command

\COPY test(user_id, product_id, value) 
      FROM '/Users/testuser/test.tsv' WITH DELIMITER '\t' CSV HEADER;


but this searches the file on the remote DB while I need to do that on my local PC.
There are two possibilities here.


If you are using psql, then \copy is the correct way to do things as you mention.
If you are using a client program you have authored, then you need to 

COPY FROM STDIN.....



And then feed the file in (see the documentation on the COPY command.

The COPY command is a slightly strange beast.  It is the one command PostgreSQL currently supports from Quel and so it is both non-standard, and based on something of a different paradigm than the rest of the commands.  For this reason it's important to read the docs relatively carefully on this command.