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
Genius is ten percent inspiration and fifty percent capital gains.