Here is an example:
|
1
|
echo "Lots of data" | ssh user@some.example.com 'cat > /directory/big.txt' |
In this case “Lots of data” will be copied over SSH and stored in /directory/big.txt.
The beauty is that we can apply the same technique to PostgreSQL:
|
1
2
3
|
test=# <strong>COPY (</strong>SELECT * FROM pg_available_extensions)TO PROGRAM 'ssh user@some.example.com ''cat > /tmp/result.txt'' ';COPY 43 |
To make this work in real life you have to make sure that SSH keys are in place and ready to use. Otherwise the system will prompt for a password, which is of course not desirable at all. Also keep in mind that the SSH command is executed as “postgres” user (in case your OS user is called “postgres” too).