Most of the time its fine to test things out on your local machine and local installation of Postgres but often, you want more flexibility, the ability to quickly reset to a known starting point and the ability to try out different and more complex server architectures.
That is where Vagrant comes in. I use it to quickly set up Postgres clusters at different versions and different configurations.
You can find all of these in my repo at https://github.com/philmcc/postgres_clusters
For example here is a set up to allow you to set up a 3 node cluster with 1 leader and 2 followers all on postgres 9.4 and using replication slots:
If you want to try it out, all you need to do (once you have Vagrant installed ) is:
vagrant box add centos65-x86_64-20140116 https://github.com/2creatives/vagrant-centos/releases/download/v6.5.3/centos65-x86_64-20140116.box git clone https://github.com/philmcc/postgres_clusters.git cd postgres_clusters/postgres94/3-node-master-2_slave_94_rep_slots_and_recieve vagrant up
Then if you want to reset the cluster, you just do a ‘vagrant destroy’ then vagrant up again and you have a clean starting point.
You could also try out the same config on Postgres 10 (beta2) which is the latest version right now using the config here:
But what if the config you want isn’t there and you want to set up you own? Well fortunately its really easy to set up.
Lets take a look at the most basic configuration of 1 master and one read only slave using the new Postgres 10 beta release:
https://github.com/philmcc/postgres_clusters/tree/master/postgres10/2-node-master-slave_10
The first point is the Vagrant file. That tells Vagrant which nodes to configure, what networking they need and then the script name that will set up the software once the VM is up and running.
This is the only important part (that you need to know about):
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config| config.vm.box = "centos65-x86_64-20140116" config.vm.define "master", primary: true do |server| server.vm.hostname = "master.pg" server.vm.network "private_network", ip: "192.168.4.2" server.vm.provision "shell", path: "scripts/master.sh", args: PG_DATABASE_NAME end config.vm.define "slave" do |server| server.vm.hostname = "slave.pg" server.vm.network "private_network", ip: "192.168.4.3" server.vm.provision "shell", path: "scripts/slave.sh", args: PG_DATABASE_NAME end end
That sets up 2 servers, one called master and one called slave. If you wanted to add another node in, all you would need to do is add something like the slave config section again e.g.
config.vm.define "slave" do |server| server.vm.hostname = "slave2.pg" server.vm.network "private_network", ip: "192.168.4.4" server.vm.provision "shell", path: "scripts/slave.sh", args: PG_DATABASE_NAME end
and put it before the final end. You just need to change the ip address, the hostname and the “scripts/slave.sh” script if you want to use a different script to set it up.
In the master dir, you have the postgresql.conf and the pg_hba.conf files for the master (the slave doesn’t need them because it will get them from the master when we start it up as a slave). That just allows replication connections in the pg_hba.conf and sets up a couple of parameters that differ from the default in the postgresql.conf file:
listen_addresses = '*' wal_level = 'hot_standby' archive_mode = on archive_command = 'cd .' max_wal_senders = 2 hot_standby = on
Most of the defaults i postgres are prety good so for this basic set up we can leave everything else as is.
So the only other part of the whole configuration is the provisioning scripts. There is one for the master and one or the slave node that runs once the VM’s have been set up. The order is Master VM – master provisioning script then slave VM – slave provisioning script.
The 2 scripts are very similar. They first download the yum repos and install the necessary software:
yum install -y -q atool wget ping nano telnet yum localinstall -y https://download.postgresql.org/pub/repos/yum/testing/10/redhat/rhel-6-x86_64/pgdg-redhat10-10-1.noarch.rpm yum install -y postgresql10 postgresql10-contrib postgresql10-server
Next it creates some dirs and links then initializes a postgres cluster. Once that is done, postgres is shut down so that the conf files can be copied over and its started up again:
ln -s /var/lib/pgsql/10/data /database cd /database /etc/init.d/postgresql-10 initdb /etc/init.d/postgresql-10 start sudo -u postgres -H sh -c "psql -c 'CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 2;'" cp /vagrant/master/pg_hba.conf /database/pg_hba.conf cp /vagrant/master/postgresql.conf /database/postgresql.conf chown postgres:postgres /database/pg_hba.conf chown postgres:postgres /database/postgresql.conf /etc/init.d/postgresql-10 restart
The slave.sh script is the same up to the point where it installs the software but the actual initializing of the database is unnecessarty as it is all handled by the pg_basebackup command that copieds the master database over and sets up replication in one handy command!
pg_basebackup -h 192.168.4.2 -D /var/lib/pgsql/10/data -U rep -v -P --wal-method=stream -R
Finally there is some tidying up by creating a link to the data directory and also priming the recovery.conf file with a trigger file in case it needs to be easily promoted later.
That really is it.
Once vagrant up finishes, you can connect easily with ‘psql -U postgres -h 192.168.4.2’ to test it out and start playing.