I am renting a remote server. On that server, I am running a PostgreSQL database inside a Docker instance. I would like to back up the database directly to my localhost, without having to make a copy on the remote server.
I know I can dump the database from the Docker instance to a local file (sqlbkp.bak) from the remote server using:
docker exec <container_name> pg_dump <schema_name> > /tmp/sqlbkp.bak
Then I could transfer that remote backup to my localhost using Rync from my localhost, like:
rsync -a user@remoteserver:/tmp/sqlbkp.bak /home/sqlbkp.bak
However, using these two steps, I require extra disk space on my remote server for the backup (at least as large as the database). Then, immediately after transferring the backup, I can free the remote space. I find this inconvenient and it seems unnecessary. Is there a way to combine these steps into one, such that I could run pg_dump over SSH (against a database inside a Docker instance)?
You can use ssh to execute any command on the remote, and then that command's std output passes back through the ssh channel to become ssh's std output, and can be redirected to a file on your local machine.
ssh user@dockerhost 'docker exec <container_name> pg_dump <schema_name>' > /home/sqlbkp.bak