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
"Why waste negative entropy on comments, when you could use the same entropy to create bugs instead?" -- Steve Elias