ssh user@remotehost 'psql "COPY mytable TO STDOUT"' | psql "COPY mytable FROM STDIN"
Remote device images are also win:
ssh user@remotehost 'dd if=/dev/hda1' > sdb.iso
mount -o loop sdb.iso
/mnt/iso # Win!
Not to mention combining ssh tunnel with more ssh tunneling and then more ssh! Mmm, ssh. Almost as awesome as netcat. - shazow
"The fundamental principle of science, the definition almost, is this: the sole test of the validity of any idea is experiment." -- Richard P. Feynman