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.

I am looking for a way to have a postgresql database (running server version 9.1) replicated. I want the mirroring to be able to have one server (the slave) standing outside our firewall. I have been looking at slony and streaming replication - in both these cases, the slave server needs to talk back to the master, for security reasons, I would like that not to be the case. Ideally, I would like the master just to send the changes off to the slave without expecting any answers. are there any systems that act like this?

(Of course, I know that if the slave goes down, or for some other reason is not able to receive the data, the master will not know, but as we have other tools to keep an eye on server problems, this is acceptable)

share|improve this question
add comment

migrated from stackoverflow.com Oct 23 '12 at 15:14

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 3 down vote accepted

You can do this with the log-shipping standby functions, which is really an extension of continuous archiving.

You first configure PostgreSQL to write all* changes to the Write-Ahead Log by saying:

wal_level = hot_standby
archive_mode = on

Once a log file is full (16 MB of writes) or a configurable timeout occurs (archive_timeout), PostgreSQL starts writing to the next log file, and it calls your archive_command with the filename of the log it just finished writing. Your archive_command would be a script that uses some other appropriate technology to push this log file off the master -- maybe you want to upload it to an object store like Amazon S3, or maybe you want to use SCP to push it straight onto the slave, or maybe you want to do something else entirely.

You'd set up the slave similarly to the master, except its data directory would include a recovery.conf saying standby_mode = on. If the master copies logs straight into the slaves' pg_xlog directory, you're done; if not, the slave will need a restore_command script to act as an inverse of archive_command and retrieve the indicated archive log from wherever you put it.

Lastly, copy over a base backup (as described in the continuous archiving documentation), start PostgreSQL, and your slave will be following the master without needing any direct communication.

share|improve this answer
    
Thank you, I'll look into this! –  MortenSickel Oct 24 '12 at 7:17
    
Worked perfectly - just had to scratch my head a bit to set up the archive_command and restore_command ... –  MortenSickel Oct 24 '12 at 12:49
    
You might want to take a look at github.com/omniti-labs/omnipitr for archiving. That's what I use, and I'm satisfied. –  François Beausoleil Nov 30 '12 at 14:29
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.