postgres_fdw slower then copy + scp + copy (~12 times) foreign server 9.2
local server 9.5
table is 10GB
data transfer performed on same network interface as foreign server works
no indexes set on data
old way:


copy to - 2:36
scp - 08:17
copy from - 10:11

postgres_fdw:


by the time old way finished it has done 800MB of insert into ..select * from foreign_table



Question: Did I miss something in config (meaning I can improve it), or postgres_fdw is just not meant for bulk load (meaning I can't improve it)? (I use it for small data amount reconcile and it works fine. Idea of insert select from fdw instead of running bash commands looked so sweet)

Update I tried psql to remote server from local server and \copy table - six minutes - faster then over ssh

Update2 The fetch_size option, not available prior to 9.6, can be mocked up with dblink_fetch(CURSOR, fetch_size) - look my answer below for sample.
postgres_fdw is certainly not as optimized for bulk transfer as copy to, copy from, and scp are.  After all, bulk transfer is the main reason for the existence of those tools.

But that doesn't mean there is nothing you can do.  If you were running 9.6 on the local server, you could try increasing the fetch_size.