I have two PostgreSQL databases on two different machines and the network connection is slow.
I would like to copy few selected rows from the first instance to the second. Unfortunately, the full dump of single table is too big. A CLI solution would be fine.
Off the top of my head, here are a few ways to do it without resorting to 3rd-party tools.
If you are using PostgreSQL 9.3, you can use the extension postgres_fdw.
If you are using an older version of PostgreSQL have a look at dblink
If you want a command-line only solution, and you are using Linux (or Cygwin etc), you could try something like the following:
psql -d yourdb -qtAXc "COPY (select col1, col2, col3 from your_table where id between 123 and 456) TO STDOUT CSV HEADER" -U youruser -h localhost -p 5432 \ | psql -d yourremotedb -U youruser -h your_remote_host -qtAXc "COPY yourtable FROM STDIN CSV HEADER"