Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to use the archive_command to send to multiple machines? Is it just as simple as separating with a && since it is essentially just a command line?

command1 && command2

I assume this could have a bad effect if one command failed, for example if command1 failed then command2 wouldn't run; and if command2 failed it would return non-zero for the whole thing and Postgres would re-try to send the WAL which would mean it would re-run command1.

I am hoping there is a more official solution to the problem. My goal is to have one backup inside the data center, and one backup outside the data center.

share|improve this question
    
While not an answer to your question, I suggest you look at using the streaming replication features in newer versions of Pg. You should still use WAL shipping to compliment it, as that allows the replicas to catch up if they go offline and get too far behind the master, though, so your question remains relevant. What server version are you using anywya? –  Craig Ringer Nov 2 '12 at 12:02
add comment

2 Answers

up vote 3 down vote accepted

First, I always create a wrapper script when creating commands like that. That makes it easier to debug the command without having to signal postgres to reload anything. This also makes it easier to include logging from your archiver script.

What you need to figure out first is how you want your data replicated. The way you're doing it now seems ok, at least if you check if the destination file doesn't exist before copying it. That way it can be re-run as many times as you like and it will try to copy it to the external location.

share|improve this answer
2  
Thanks for the answer. Postgres also has the following on backup scripts, which is the recommended practice postgresql.org/docs/current/static/… –  Collin Peters Nov 5 '12 at 17:55
add comment

I believe you could do this with multiple rsync commands instead of using something like cp. That way if one server requires a restart the others would not be affected.

Something like:

archive_command = 'rsync -a %p root@local.com:/var/lib/postgresql/9.1/wals/%f

\&& 

rsync -a %p root@remote.com:/var/lib/postgresql/9.1/wals/%f '

Not official, but theoretically workable.

share|improve this answer
    
I had thought of this, but what if the first rsync command fails? Assuming it returns failure that means that the second rsync isn't even going to run –  Collin Peters Dec 1 '12 at 1:20
add comment

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.