Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

try

pg_dump -U my_user my_database | ssh me@myserver "cat > backup.sql"
share|improve this answer
    
beautiful, thanks –  theman_on_osx Jul 13 '09 at 15:28

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.

share|improve this answer
1  
OR... you could just pass -C to ssh to have the network communication be transparently compressed. –  Juliano Jul 13 '09 at 17:29
2  
Then you'd have to compress the SQL dump on the disk. :) –  Ernie Jul 13 '09 at 18:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.