Continuous archiving: is it possible to use multiple machines?

Question

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.

Answer 1

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.

Answer 2

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.