ssh remote command-fu

I am trying to do a remote backup from behind a firewall. It is really no problem to break this out into its own script that gets called in crontab, but that isn't any fun! What I am trying to do with:

pg_dump -U my_user my_database | ssh me@myserver "> backup.sql"

is to backup a postgresql database (dumps everything to stdout) and redirect that to a remote ssh command that simply places it in backup.sql.

Unfortunately, all I am getting is an empty "backup.sql" on my remote box. Redirection is not my strong point, so any tips would be appreciated. thanks.

try

pg_dump -U my_user my_database | ssh me@myserver "cat > backup.sql"

Even better: compress it before you SSH:

pg_dump -U my_user my_database | gzip -c | ssh me@myserver "cat > backup.sql.gz"

This saves boatloads of bandwidth with SQL dumps.

I actually do it the other way around. I have the backup server log into the server to be backed up. This means that if any one server (besides the backup server, which has some seriously fascist security) were compromised at the root level, the backup server - and thus all other servers! - does not get compromised by default.

My command for doing the SQL dump remotely therefore is a little different:

ssh <server> "pg_dump -U <user> <database> | gzip -c" > /backups/sqlbackup.sql.gz

Pay particular attention to where the quotation marks are - they define what gets executed on the remote end and what gets executed locally.