Postgresql – Postgres replication/replication to server outside firewall

postgresql

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)

Best Answer

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.

Related Question