Vagrant: Postgres 9.1 Hot Standby Streaming Replication (Master/Slave)
This project comprises scripts to set up a Vagrant environment running PostgreSQL 9.1 Hot Standby Streaming Replication (Master/Slave).
After looking over a number of articles and blog posts regarding how to do this, including the PostgreSQL wiki (http://wiki.postgresql.org/wiki/Hot_Standby), I found them all wanting in various ways. Nothing's better than working scripts to see how something can be set up: So here it is. Perhaps some of the work here can be the basis for some Puppet or Chef.
As much as possible, this script uses stock files, setup, and packages, and mucks about
with the system only enough to get the script running unattended. Additionally,
and the vagrant share are avoided during setup so that the scripts might work against a traditional VPS.
On occasion on OS/X, a
vagrant destroymay crash the system. This may be related to low memory resources on the host system, the host being hibernated, or
vagrant destroyrunning against "cold" VMs.
vagrant destroyseems now to be quite stable for single instance setups, but this one uses two. Maybe that's the problem.
The setup of the master and slave allows ssh connections via public keys in both directions. The only connection that is really important is the one from the master-to-slave to rsync the archive data over to the slave.
To make the psql scripting easier, in the
setup-masterscript, the setup user is given the most liberal settings in
This is not suitable for a production install. If you want to try it, update the keys in the
ssh/directory with your own, and restore the sudoers settings.
Install VirtualBox from here: https://www.virtualbox.org/wiki/Downloads (tested with 4.1.8 only; 4.1.10 and later: not tested.)
The wait-for-reboot script uses netcat to check the availability of the ssh port (netcat seems to work better than the stock nc).
brew install netcat
To your /etc/hosts file, add
184.108.40.206 master 220.127.116.11 slave
To build the systems
bundle bundle exec vagrant box add lucid64 http://files.vagrantup.com/lucid64.box bundle exec vagrant up ./setup
One way to verify
vagrant ssh master sudo -u postgres psql -d postgres -c "create database sampledb;" sudo -u postgres psql -d sampledb -c "create table tab (val int); insert into tab values (42);" exit vagrant ssh slave sudo bash su postgres -c 'psql -d sampledb -c "select * from tab;"' exit exit
setup-genericthere are lines that are commented out. These lines will conduct an update/upgrade of the system, and will ensure that the VirtualBox 4.1.8 Guest Additions are installed.
Understanding what's going on
The process followed here is essentially the one outlined on the PostgreSQL wiki (http://wiki.postgresql.org/wiki/Hot_Standby). The main things you want to look at are the config
recovery.conf. To make it easy to compare the files that
are shipped with a vanilla install, I've put original copies of those files into the
so that you can diff them with the ones that have been edited for the systems. Also, in the edited
files, I've added comments for each line that has been added.
In the setup, there are two gotchas that seem to slow everyone down. One is that the
file goes in the PostgreSQL data directory, not in the main config directory (if they are different
on your system). Second, getting the WAL files over via rsync is a pain. See the scripts for one way
to do it.
Add notes that demonstrate failover / promotion of slave to master.
The scripts could be even less dependent on the file layout from the Debian PostgresSQL installs.