Setting up a Postgres test cluster in vagrant

No Comments

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

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:

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:

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):

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.

and put it before the final end. You just need to change the ip address, the hostname and the “scripts/” 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:

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:


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:


The 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!


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’ to test it out and start playing.

Categories: PostgreSQL, Tutorial Tags: Tags: ,

Failover testing of a Postgres cluster

No Comments

Testing failover to one of 2 slaves and reattaching to the new master.

Starting config

Post failover config



You will need Vagrant and Virtualbox to run this tutorial but that should be all.



Turn off master.
Promote slave1 to Master
Attach slave2 as follower to slave1
Attach master is as follower to slave1 (without rebuilding)




Check the cluster stat and replication:


Stop postgres on the master:


Promote slave1


Connect to slave1:

From your local machine – check the status of slave1:

Check replication slots:

There are none there. Lets create some for our other hosts so that we dont lose ant wal files (you may not want to do this on a production system as it will force the master to keep ahold of wall files untill the new slaves can get them. It is necessary to attach the master straight back in though):

(The old master will still be called master so that we can see what has changed, but it wont be the leader in the new config)


Ok. so now we have a new master and the cluster is back up and running. Now its time to plug in the first follower (slave2) so that we have a standby for failover.

Connect to slave2 and stop postgres:


edit the recovery.conf file to point to the new leader – (slave1)
and set recovery_target_timeline = ‘latest’
Note the primary_slot_name = ‘slave2’ controls which replication slot this node will use.

Start postgres back up again:

Now check that the new follower is pointing at the leader and successfully getting data:

(on slave1 – the leader)

As you can see the ‘slave2’ replication slot is now active and connected.
You can further check this by creating something on slave1 and seeing it appear on slave2.


Now, its time to put the old master back into use. We don’t want to use it as the master agian because we already have a leader and switching would mean more downtime so we will put it in as another follower:

Doing that can be tricky because failing over could have put the cluster on a different timeline. This is easy to get around though by telling the old master to use the latest timeline.

Connect to the master node and create a recovery.conf file as above:

Start the instance back up and check that the replication is connected and recieveing changes:

As you can see Everything is connected and changeds are being passed on.

All thats left now is a bit of tydying up. The ‘master’ node still has replication slots configured and thess need to be removed.
Its simple enough to do:

How to Install Virtualbox Guest additions for Oracle Enterprise Linux 6

No Comments


This tutorial leads straight on from ‘How to install Oracle Enterprise Linux 6 on Virtualbox’ and assumes that you have a clean install to work from.

If you haven’t installed OEL6 or have any problems with this tutorial, I recommend that you follow that tutorial before this one.
So, in virtual box, Guest additions gives you some cool stuff like improved mouse pointer integration and having shared folders between your host and your guest operating system and this makes it a lot easier to transfer files to your virtual machine.
All of the subsequent tutorials will assume that you have Guest additions installed.
It should be simple! You just click the menu item, the system mounts some files and runs  them and hey presto, guest additions are installed and working. Unfortunately, in Oracle Linux 6, it doesn’t work. The process completes but the install does not work.
The good news is that once you know how, you can fix this easily.
First, make sure that you can see the Virtialbox menubar at he top of the window.  If youare in scale mode pres R. CTRL and ‘C’ to exit into normal mode.
Next, go to the ‘Devices’ menu and click ‘Install Guest Additions’
Install Virtualbox Guest Additions

Install Virtualbox Guest Additions

Click Autorun

Click Autorun

An icon should appear on your desktop and a dialog will ask you what to do. Just click ‘OK’
Click run at the next prompt then enter your root password to authenticate.
The install script will run but will fail:
The install script will fail to install the guest additions

The install script will fail to install the guest additions

You are told to to look in the log file:  /var/log/vboxadd-install.log
where you will see the error:
/tmp/vbox.0/Makefile.include.header:97: *** Error: unable to find the sources of your current Linux kernel. Specify KERN_DIR=<directory> and run Make again
So, as you may have guessed, you need to install the kernel source.
The script actually gives you the instructions that you need to follow. In this case it suggests that you run 
> yum install kernel-uek-devel-
This might be different for you so just follow the instructions that the script gives you.
> yum install gcc
That should run without a problem, so now you can rerun the ‘Guest Additions’ installer again.
Rerun the guest additions installer

Rerun the guest additions installer

Click run and enter your root password when prompted.
The installer will run again and should succeed this time.
Re run the guest additions installer

Re run the guest additions installer

This time the guest addittions installer should complete successfully.

This time the guest addittions installer should complete successfully.

Thats it. Guest Additions are now installed. Give your VM a reboot and you will find your shared folder is mounted.
Now,shutdown your VM and take a snapshot.
Finally, we will export the VM so that you have a clean install to start other projects from.
Categories: Featured, Tutorial Tags: Tags: ,

How to install OEL6 on Virtualbox

No Comments

Virtualbox is Oracle’s solution to visualization and as such it is very powerful. It is similar to VMware as it has much the same capabilities and you can run servers through it on an enterprise level.

For the purposes of experimenting with operating systems and learning Oracle, it gives us some amazing benefits and makes the whole process easier. (Snapshots, rolling back machines and the ability to quickly stand up new machines using existing resources gives us tremendous flexibility.)

This tutorial is the first part in a series that will walk you through a range of Oracle tasks such as installing a database and setting up a cluster using RAC.

Some tasks will require a powerfull PC, while others won’t tax your hardware at all.


Ok, lets get started

How to install OEL6 on Virtualbox

– First get virtualbox:

The install is straight forward. Just run the installer and follow the instructions on screen. No special instructions should be needed as you can accept the defaults.
Get a copy of Oracle Enterprise Linux 6.3:
Find your nearest mirror here:
Then select your ISO image:
Download the ISO for OEL 6

Download the ISO for OEL 6


Find the specific ISO that you want

Find the specific ISO that you want.


Save the file to a convenient location and let it download.
Download and save the OEL ISO

Download and save the OEL ISO.


Once you have the operating system downloaded, you can start creating your virtual machine. Open up Virtualbox and click on the ‘New’ button:
Create a machine with Virtualbox

Create a machine with Virtualbox.


Next, give your VM a name and select ‘Linux’ for the type and ‘Oracle for the version.

Create a new machine with Virtualbox

Create a new machine with Virtualbox

Click next and allocate some memory to the VM. You can change this later after the VM has been set up but if you are going to be installing Oracle, you will need at least 1 Gb so you may as well set it to that for now:
You are going to need a hard disk for your VM so select ‘Create a virtual hard drive now’ and click on ‘Create’
Select ‘Dynamically Allocated’ so that your VM will only use the space on the physical hard drive as and when it is needed which will save you space if you are not using it.
Set it to whatever amount that you want but remember that while you can add other disks to your VM, you will not be able to grow this disk past it’s initial limit.
I recommend that you set it to at least 10Gb for now.
Give the disk a name so that you can easily identify it and click ‘Create’
You will be taken back to the main Virtualbox window. the VM is created but we are not finished yet as there are some settings to change and we need to add an operating system for this to work!
Click on the settings buton (making sure that your new VM is selected)
Under the ‘General’ section select the ‘Advanced’ tab and set both the ‘shared clipboard’ and ‘Drag ‘n’ Drop’ to ‘Bidirectional’ (this is an optional step which makes it a bit nicer to use inside of your host operating system).
Clisk on system and uncheck ‘Floppy’ as we wont be needing it.
Under storage, click on ‘Empty’ under the ‘IDE Controller’ section
Click on the image of a CD to the right of the ‘CD/DVD Drive’ section under ‘Attributes’
This will allow you to select your operating system DVD ISO from which your VM will boot the first time.
Click on ‘Choose a virtual CD/DVD Disk file and open your ISO.
You will see the ISO in the information section:
Click on the ‘Shared Folders’ section and on the ‘Add a Folder’ button.
Select a folder on your host machine to be a shared folder. This will allow you to easily transfer files between your host machine and your new VM.
Click ‘Auto-Mount’ and then ‘OK’
Click ‘OK again to close the ‘Settings’ window.
Click Start Machine:
The machine boots from the supplioed ISO and starts the install process manually:
The VM should find the installation media and start the install process off. YOu will probably be waiting 1 – 2 minutes.
At the next screen click ‘Next’
Give your VM a name:
Click ‘Configure Network’
Click on the network adapter then on ‘Edit’
Select ‘Connect Automatically’ then click ‘Apply’, ‘Close’ then ‘Next’
Select your timezone:
Enter your password twice:
Leave the option ‘Replace Existing Linux Systems’ checked and click ‘Next’
Let it do its Dependency checks then select ‘Databases’ and uncheck all of the options
Select Desktops and check everything except KDE Desktop
Then click next.
The install will begin and will also update the packages that are selected. This will take some time.
Once the install is finished, click on ‘Reboot’ to finish the process.
We are nearly finished the O/S install – Just some post install setup to do now.
Wait for your VM to reboot and click ‘Forward’
Select the ‘Agree’ option and click ‘Forward’
Click forward again to ignore the Software updates information
You will be prompted to create a non root user. We will use this opportunity to create our ‘Oracle’ user.
Set the username as oracle and enter a password.
On the next screen, you can either set the time manually or select ‘Syncronise date and time over the network’
Then click next.
Ignore the message about kdump and click ‘OK’ then ‘Finish’
Thats it! YOu will now have a login prompt with out Oracle user selected. Login with your password.
Now might be a good time to take a snapshot in case we mess anything up later on and we want to restore to a clean slate.
Login then go to the ‘System’ menu and select ‘shut down’, then click ‘Shut Down’ again.
Once your VM has shut down, its time to take a snapshot.
That way, we have a way to go back to a clean machine state that we know works if we have any problems further down the road.
On the main Virtualbox screen,click on ‘Snapshots on the right hand side.
Then click the camera icon to take a new snapshot.
Give your snapshot a name and a description and click ‘OK’
You can now click on the ‘Start’ button to boot your VM up again.
Log in as the ‘oracle’ user once you get to the login prompt.
Click on ‘Applications’ -> ‘System Tools’ then drag ‘Terminal’ onto your desktop (we are going to be using it a lot)
You will notice that at the top of the screen on the menu bar, there is an icon of two computers with a red cross on it. That is telling you that you have no active network connections
Click on it and select your available network connection:
Click on the icon and it should connect.
Right click on the icon and select edit connections
Select your connection, click edit then check the ‘Connect Automatically’ box
This should now connect without you having to do anything.
You will need to provide your root password to do this:
And that is Basically it. You now have Oracle Enterprise Linux installed on Virtualbox – Ready for you to start experimenting with.
The next article in this series will walk you through installing guest additions on your VM which will make the whole experience much nicer by allowing mouse integration, cut and paste and shared folders with your host OS.

Cleaning up a failed Oracle 11g grid infrastructure install.

No Comments

Unfortunately, the installer or more specifically the script for the Oracle 11g grid (Clusterware) installer can be very flaky.


If it fails and you need to fix something or rerun it for any reason, it will fail the next time if you have not cleaned up the install by de-configuring CRS. I also like to wipe the installation off altogether and restart from a clean base. Here are the steps.

/u01/app/11.2.0/grid/crs/install/ -deconfig -verbose -force

/u01/app/11.2.0/grid/crs/install/ -deconfig -verbose -force -lastnode

At this point, you could rerun (after you have fixed the problem, but if you have closed the installer or just want to restart from a clean base then continue with the steps below.


Get the oracle home from the inventory then delete them both

 cat /etc/oraInst.loc

cd /u01/app/oraInventory/ContentsXML/

cat inventory.xml

Find the Oracle home

Remove it on all nodes

 rm -R /u01/app/11.2.0/

Also remove the inventory

rm -R /u01/app/oraInventory/

rm -R /etc/oracle

rm /etc/oraInst.loc

rm /etc/oratab

rm /usr/local/bin/dbhome

rm /usr/local/bin/oraenv

rm /usr/local/bin/coraenv

Then change the ownership of the /u01/app directory:

chown oracle:dba /u01/app


You could stop there but if you really want to wipe the slate, you could delete your ASM disks  and recreate them fresh before the next install.


Delete your ASM disks on node 1

 oracleasm deletedisk DISK1

oracleasm deletedisk DISK2

oracleasm deletedisk DISK3

oracleasm deletedisk DISK4





On all nodes

oracleasm scandisks

/usr/sbin/oracleasm exit


Now give your node a reboot and you should have a clean base from which to start another install.