PostgreSQL with DevOps for Continuous Delivery

In this chapter, you will learn how to set up DevOps tasks for PostgreSQL databases. DevOps is no longer just about breaking the gap between developers and operations. It is, rather, a software development methodology based on the integration between developers and system administrators. Database changes are always a headache because whether they are improvements, fixes, or new features, these will alter the current environment and perhaps create a new one that could be heaven or a nightmare and therefore deserves to be taken seriously in your DevOps implementation. Hence, we will explain a different perspective for automating database changes. We will not only see why database changes can be difficult, but will also go through some real-world examples of how DevOps can simplify the process.

In the project...

Technical requirements

The examples given in this chapter will take developers around 20-24 hours'work to implement DevOps for the ATM PostgreSQL database.

The code files for this chapter are available at the following link: https://github.com/PacktPublishing/Developing-Modern-Database-Applications-with-PostgreSQL/tree/master/Chapter07.

Setting up PostgreSQL using Vagrant and VirtualBox

In this chapter, we will use Vagrant and VirtualBox. Both tools are widely used and accepted for DevOps.

The idea behind these useful tools is to quickly create and share environments for developers and testers. We will see how to achieve this in the following paragraphs.

Installing VirtualBox

VirtualBox is a well-known virtualization product for enterprise as well as home use. It can create a VM of another OS such as Windows, Ubuntu, Debian, RHEL, Solaris, and OS/2. In the following steps, we will learn how to use it.

Note that we are using a Windows 10 OS and there we will perform these tasks:

1. The first step is to download the VirtualBox installer from their website: https://www.virtualbox.org/wiki/Downloads. Once there, we will download the latest version available at the time of writing this book: https://download.virtualbox.org/virtualbox/6.1.4/VirtualBox-6.1.4-136177-Win.exe.

2. Now, we can start to run this VirtualBox installer as an administrator feature of our Windows 10 interface. The installer's Welcome screen will be displayed.

3. After clicking the Next button, you'll see a Custom Setup window:

Figure 7.1 – VirtualBox features

4. Now, select some options related to features in the Windows interface and...

Installing Vagrant

Vagrant is an open source tool for deploying and managing software development environments. It isolates the dependencies and configuration from your projects into environments called virtual machines (VMs). You could easily reproduce similar VMs on other computers by using the same configuration as on your computer:

1. The first step is to download Vagrant from their website: https://www.vagrantup.com/downloads.html.

The current version at the time of writing this book is vagrant_2.2.7: https://releases.hashicorp.com/vagrant/2.2.7/vagrant_2.2.7_x86_64.msi.

2. We will now execute the Vagrant installer by using the Run as administrator feature and clicking the Run anyway button:

Figure 7.7 – Starting the installation of Vagrant

3.  The setup wizard is presented at this point and we then click on the Next button:

Figure 7.8 – Vagrant Setup Wizard

4. The end user license agreement is shown and...

Selecting a Vagrant box

Now that both VirtualBox and Vagrant are installed on your physical computer, it is time to browse the list of available boxes for Vagrant. Open the list of base boxes from this link: https://app.vagrantup.com/boxes/search: 

Figure 7.15 – List of all available Vagrant boxes

Note that a base box is a template of a VM and is defined on the Vagrant site as follows:"Boxes are the package format for Vagrant environments. A box can be used by anyone on any platform that Vagrant supports to bring up an identical working environment."

We can write "Ubuntu 18" to list all of the Ubuntu 18 boxes because we next use the bento/ubuntu-18.04 box to create a few Ubuntu 18 VMs.

 Spinning up Ubuntu server 18.04 with Vagrant 

Because of the new releases of VirtualBox 6.1, unfortunately, Vagrant 2.2.6 has not yet updated on time to fully work with this new release. I will show here how to fix the issue and then Windows 10 will able to work for the two latest versions of Vagrant and VirtualBox:

1. Add this line to the file on your Windows 10 machine: C:\HashiCorp\Vagrant\embedded\gems\2.2.7\gems\vagrant-2.2.7\plugins\providers\virtualbox\plugin.rb:

module Driver
     autoload :Meta, File.expand_path("../driver/meta", __FILE__)
     autoload :Version_4_0, File.expand_path("../driver/version_4_0", __FILE__)
     autoload :Version_4_1, File.expand_path("../driver/version_4_1", __FILE__)
     autoload :Version_4_2, File.expand_path("../driver/version_4_2", __FILE__)
     autoload :Version_4_3, File.expand_path("../driver/version_4_3", __FILE__)
     autoload :Version_5_0, File.expand_path("../driver/version_5_0...

Creating the Puppet module for PostgreSQL

Ubuntu includes PostgreSQL on its repositories by default. To install PostgreSQL on Ubuntu, we can use the apt-get (or other apt-driving) command, but instead of that, we will modify the Vagrant file to include the shell commands to initialize PostgreSQL through Puppet.

Puppet is a software configuration management tool, but I like to say that it is a powerful infrastructure automation and delivery tool. In the following steps, we will see how to deploy PostgreSQL and how it can be configured easily with the help of this extraordinary tool:

1. Log out of the current vagrant ssh session and then destroy your Vagrant environment:

vagrant destroy

Answer y (=yes) to any questions if prompted:

Figure 7.19 – Destroying a VM

2. Change the vagrant file, C:\Projects\Vagrant\PostgreSQL-Master\Vagrantfile, and change the commented-out lines (near the bottom of the file) that read as follows:

# config.vm.provision...

Working with Jenkins and PostgreSQL

Jenkins is a free, open source automation server to automate the phases of software development, such as building, testing, and deploying. Jenkins facilitates continuous integration and continuous delivery. We will now practice using Jenkins for PostgreSQL automation:

1. Use this Vagrant box to spin up a Jenkins server on an Ubuntu 18.04 VM:

darkwizard242/devopsubuntu1804

2. Set up the Vagrant file from the C:\Projects\Vagrant\Jenkins folder. Open PowerShell as an administrator:

PS C:\Windows\system32> mkdir C:\Projects\Vagrant\Jenkins
PS C:\Windows\system32> cd C:\Projects\Vagrant\Jenkins
PS C:\Projects\Vagrant\Jenkins> bcdedit /set hypervisorlaunchtype off
PS C:\Projects\Vagrant\Jenkins> vagrant init darkwizard242/devopsubuntu1804

3. Edit the Vagrant file, C:\Projects\Vagrant\Jenkins\Vagrantfile, and add the IP address 192.168.0.200:

Vagrant.configure("2") do |config|
    config.vm.box = "darkwizard242/devopsubuntu1804"...

Creating an Ansible playbook for PostgreSQL

Ansible is the simplest IT automation engine that automates infrastructure, applications, and many other needs. Ansible uses YAML (Ain't Markup Language) – a very simple language, to establish Ansible playbooks. An Ansible playbook is composed of one or more 'plays' in an ordered list. The terms 'playbook' and 'play' are sports analogies. Each play executes part of the overall goal of the playbook, running one or more tasks. Each task calls an Ansible module.

We are converting the Jenkins server to be an Ansible server as well. Hence, we can create a Jenkins project named Ansible Installation:

1. This is the Jenkins script to install Ansible: 

sudo apt update -y
echo Y | sudo apt install -y ansible
# set the remote PostgreSQL IP 192.168.0.191
sudo sh -c 'sudo echo "[postgres]" > /etc/ansible/hosts'
sudo sh -c 'sudo echo "192.168.0.191 ansible_user=vagrant...

Managing PostgreSQL by Terraform

Even though Ansible and Terraform both offer configuration automation, the main difference between Ansible and Terraform is that Terraform provides reliable orchestration, while Ansible provides robust configuration management. This means that Ansible will be good for installing and managing software on existing servers while Terraform is more focused on cloud and infrastructure tasks such as load balancers, databases, or networking:

1. To launch a new Terraform server, use this Vagrant box, servetguney/ubuntu-18-04-terraform-git, here: https://app.vagrantup.com/servetguney/boxes/ubuntu-18-04-terraform-git.

2. However, Terraform installation is straightforward, so we now proceed to install Terraform on our Jenkins server as well.

Check the following home page for the latest Terraform version for Ubuntu 18: https://www.terraform.io/downloads.html.

Then we download it into the Jenkins server, decompress the file, and then Terraform can be used right...

Summary

In this chapter, we learned about some good tools for DevOps to implement a single solution with PostgreSQL step by step. So far, system administrators have seen VirtualBox, Vagrant, and Puppet launch PostgreSQL in VMs. They can also apply Jenkins scripts, Ansible playbooks, and Terraform templates to deploy PostgreSQL servers, PostgreSQL schemas, databases, roles, and tables automatically. 

In the next chapter, we will focus on PostgreSQL high availability, so if you are interested to see how our database is always available, even in the event of a disaster, I invite you to read the next chapter.