May 09 2020
Off

Customers across industries and verticals deal with relational database code deployment. In most cases, developers rely on database administrators (DBAs) to perform the database code deployment. This works well when the number of databases and the amount of database code changes are low. As organizations scale, however, they deal with different database engines—including Oracle, SQL Server, PostgreSQL, and MySQL—and hundreds or even thousands of databases. This creates a huge load on the DBAs to deploy, track, and perhaps roll back the database changes as and when developers need them to. Moreover, waiting for DBAs to make changes for the developers in an agile environment creates a bottleneck.

In this blog post, I will walk through an example of building a cost effective, database-independent solution in AWS using open source tools Liquibase and Jenkins to solve these database problems. I will not take a dive deep into how Liquibase or Jenkins work; rather, I will focus on how to deploy these open source tools on AWS services Amazon Elastic Compute Cloud (Amazon EC2), AWS CodeCommit, AWS Secrets Manager, and Amazon Simple Email Service (Amazon SES) to achieve the desired goal.

Overview

Liquibase is database-independent library for tracking, managing, and applying database schema changes that allows easier tracking of database changes. You can easily define changes in SQL, XML, JSON, or YAML.

Jenkins is an open source automation server that enables developers to reliably build, deploy, and roll back code changes. Jenkins can be installed in any machine with a Java Runtime Environment (JRE) installed.

Prerequisites

For the purpose of this walkthrough, you will need the following:

  • AWS account
  • Amazon Aurora RDS database (PostgreSQL)
  • Database user that can connect to the Amazon Aurora RDS database and create/drop table
  • Local machine where the Git client is installed and configured to connect to the CodeCommit repository and push the database code

 

Architecture

Walkthrough

Let’s walk through an example in which I build this solution using an Amazon EC2 instance running on Amazon Linux AMI to deploy database schema changes on an Amazon Aurora PostgreSQL RDS database.

Step 1: Store the database credentials in the AWS Secrets Manager

In the prerequisites section, I mentioned the need for a database user to deploy database changes. This database user will be used to deploy and roll back the schema changes to and from the database. I will use the Secrets Manager to store these database user credentials. A Jenkins job will be configured to retrieve them as and when needed using the AWS Command Line Interface (AWS CLI).

In the example below, I store the credentials of my RDS Aurora database named dbdevopsaurora.

1. In the AWS console navigate to AWS Secrets Manager, Store a new Secret.

2. Select the secret type as Credentials for RDS database and then enter the database username and password.

Screenshot: Select the secret type as Credentials for RDS database and then enter the database username and password.

3. Select Next and fill in the Secret name and Description fields.

4. Select Next and then Enable Automatic rotation (optional, but recommended).

5. Select Next and Save.

Step 2: Set up a CodeCommit repository

To create a CodeCommit repository:

1. Open the CodeCommit console.

2. In the region selector, choose the AWS Region where you want to create the repository: Us-east-1.

3. On the Repositories page, choose Create repository.

4. On the Create repository page, enter a Repository name: DBDevopsDemoRepo.

5. In Description, enter a description for the repository: This repo is for Demo of DB deployment automation.

6. Optional step: Choose Add tag to add one or more repository tags (a custom attribute label that helps you organize and manage your AWS resources) to your repository.

7. Create.

Step 3: Launch the EC2 instance to host Jenkins and Liquibase

For the build environment, I will launch an Amazon EC2 instance running on Amazon Linux AMI to host Liquibase, Jenkins, and other packages needed for this solution.

Install and configure Jenkins, Java, Nginx, jq, Git

Connect to your instance using your private key and switch to the root user. First, let’s update the repositories and install Jenkins, Nginx, Git, Java, and jq.

To install Jenkins on Amazon Linux, we need to add the Jenkins repository and install Jenkins from there:

wget -O /etc/yum.repos.d/jenkins.repo http://pkg.jenkins-ci.org/redhat/jenkins.repo
rpm --import http://pkg.jenkins-ci.org/redhat/jenkins-ci.org.key
yum install -y jenkins
yum install -y java
yum install -y nginx1
yum install -y jq
yum install -y git-all

As Jenkins typically uses port TCP/8080, we’ll configure Nginx as a proxy. Edit the Nginx config file (/etc/nginx/nginx.conf) and change the server configuration to look like this:

server { listen 80; server_name _; location / { proxy_pass http://127.0.0.1:8080; }
}

Jenkins typically uses port TCP/8080 open that port in the security group to those machines that will need access.

Start the Jenkins and Nginx services and make sure they are configured to run even after reboot:

service jenkins start
service nginx start
chkconfig jenkins on
chkconfig nginx on

Configure the Git on EC2 instance

Make sure you use your own email address:

sudo -u jenkins git config --global credential.helper '!aws codecommit credential-helper $@‘
sudo -u jenkins git config --global credential.useHttpPath true
sudo -u jenkins git config --global user.email "email@domain.com"
sudo -u jenkins git config --global user.name (http://user.name/) "MyJenkinsServer"

Unlock Jenkins

Point your browser to the public DNS name of your EC2 instance (for example, http://ec2-54-221-39-132.compute-1.amazonaws.com/) and you should be able to see the Jenkins home page and the instructions to unlock:

On the Customize Jenkins page, choose Install suggested plugins.

Wait until Jenkins installs all the suggested plugins. When the process completes, check marks will display beside installed plugins.

On the Create First Admin User page, enter a user name, password, full name, and email address for the Jenkins user.

Choose Save and continue, Save and finish, and Start using Jenkins.

Download and install Liquibase on EC2 instance

Download the Liquibase Community version as a .zip folder. Log on to the EC2 instance using Putty or any other SSH tool and move to the Jenkins installation directory cd /var/lib/Jenkins.

Create a folder called liquibase and copy the contents of the Liquibase .zip folder into this folder. The directory should look like this:

Download and install JDBC driver on EC2 instance

Install the JDBC driver for the Liquibase to connect to the RDS database. In this example we are using Aurora PostgreSQL, so we will need a PostgreSQL JDBC driver.

Download the JDBC JAR file and place it in the Liquibase folder. The directory should look like this:

Create Bash shell scripts for deployment and rollback

Jenkins will be calling these shell scripts in the background. These Bash shell scripts call the secrets manager via IAM roles assigned to the EC2 instance to fetch the database credentials, run the Liquibase utility, and deploy and roll back changes.

1. Create the script for deployment. Please note that this script is specific for PostgreSQL. Change the driver and classpath as appropriate for a different database. Use an editor such as vi to create this file.

cd /var/lib/jenkins
vi callLiquibaseDemoDeployment.sh
export lquser=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."username"`
export lqpassword=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."password"`
export hostname=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."host"`
export portnumber=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."port"` bash $JENKINS_HOME/liquibase/liquibase --changeLogFile=$1 --url=jdbc:postgresql://$hostname:$portnumber/devopsdb --username=$lquser --password=$lqpassword --driver=org.postgresql.Driver --classpath=$JENKINS_HOME/liquibase/postgresql-42.2.8.jar update

Then save this script and exit the editor.

2. Create the script for rollback using Count. Please note that this script is specific for PostgreSQL. Change the driver and classpath as appropriate for a different database.

cd /var/lib/jenkins
vi callLiquibaseDemoRollback.sh
export JENKINS_HOME=/var/lib/jenkins/
export lquser=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."username"`
export lqpassword=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."password"`
export hostname=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."host"`
export portnumber=`aws secretsmanager get-secret-value --secret-id dbdevopsAuroraCreds --region us-east-1 | jq --raw-output .SecretString | jq -r ."port"` bash $JENKINS_HOME/liquibase/liquibase --changeLogFile=$1 --url=jdbc:postgresql://$hostname:$portnumber/devopsdb --username=$lquser --password=$lqpassword --driver=org.postgresql.Driver --classpath=$JENKINS_HOME/liquibase/postgresql-42.2.8.jar rollbackCount $2

Then save this script and exit the editor.

Step 3: Set up IAM role for an EC2 instance

Create an IAM role for an EC2 instance to access the CodeCommit repository and the secrets manager.

1. Create role JenkinsEC2DevopsRole.

2. Attach the AWS policy AWSCodeCommitPowerUser to the role.

3. Create a custom policy SecretsManagerRead to read the secrets from the secrets manager and attach it to the role. The JSON for this custom policy would look like this:

{
"Version": "2012-10-17",
"Statement": [ {
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"secretsmanager:GetRandomPassword",
"secretsmanager:GetResourcePolicy",
"secretsmanager:GetSecretValue",
"secretsmanager:DescribeSecret",
"secretsmanager:ListSecretVersionIds" ],
"Resource": "*" } ]
}

Attach the role JenkinsEC2DevopsRole to the EC2 instance running Jenkins.

Step 4: Set up an SMTP server for Jenkins using SES

1. Log on to the AWS console for SES and select the Region as us-east-1.

2. Navigate to SMTP settings and click on Create My SMTP Credentials. This will download the credentials to the local machine; the next steps require the contents of this file.

3. Log in to Jenkins through a browser and navigate to Manage Jenkins, Configure System.

4. Enter the email address of your choice in the System Admin e-mail address, which will be used for sending Jenkins notifications. This email address should be a verified email address from SES. Refer to the documentation on how to verify an email address in SES.

5. Enter the SMTP server name under Email Notification (for example email-smtp.us-east-1.amazonaws.com from step 4.2).

6. Click the Advanced button and then click the checkbox next to the Use SMTP Authentication option. Then set the following fields:

  • Username: Username from the downloaded SMTP credentials (from step 4.2)
  • Password: Password from downloaded SMTP credentials (from step 4.2)
  • Use SSL: Uncheck this checkbox
  • SMTP Port: 587

6. Check the email notification functionality by clicking the checkbox next to the Test configuration by sending Test e-mail recipient option. Enter a valid email id and click the Test configuration button to see if you receive a test message in the configured mail box. Note that the recipient email address should be verified by SES for this to work. Refer to the documentation on how to verify an email address in SES.

7. Save.

Step 5: Push the database code to CodeCommit repository

In the prerequisites, I mentioned a local machine that can connect to the CodeCommit repository through a Git client. I do not want to elaborate on that topic, which is covered in many other blog posts. Refer to the CodeCommit documentation to learn how to push files to a CodeCommit repository.

For Liquibase, the changes can be specified in SQL, XML, and JSON formats. In this example, I will walk through the SQL format. The screenshot below shows a Liquibase-formatted SQL file pushed into the master branch of repository DBDevopsDemoRepo. The file name is changeset.sql. This file is holding the code for deployment into the database.

How this file is formatted for Liquibase

Liquibase-formatted SQL files use comments to provide Liquibase with metadata. Each SQL file must begin with the following comment:

--liquibase formatted sql

Each changeset in a formatted SQL file begins with the following comment:

--changeset author:id

In the changeset.sql you can see the author is public and an arbitrary ID of 3 is used to designate the changeset number. This ID should be unique. These values help with tracking the changes in the database.

Then comes the DDL to deploy, which is:

create table test3 ( id int primary key,
name varchar(255)
);

Finally, you can specify a rollback command making use of the rollback comments. Here is an example for rolling back table test3 by dropping it:

--rollback drop table test3;

Step 6: Create a Jenkins project for deployment and rollback

Create the deployment project

Now let’s create a Jenkins project to deploy the DDL changes to the RDS database.

1. Log in to Jenkins through the browser using the existing credentials.

2. Click on New Item and select FreeStyle Project.

3. Enter Name for the item as LiquibaseDeploymentDemo and then select OK.

4. In the project, create a parameter to specify a filename to deploy.

In the general section, check the box This project is parameterized. In the dropdown menu, select Add Parameter and String Parameter.Populate the fields as below:

Click on Dropdown 'Add Parameter' and select 'String Parameter' .Populate the fields as shown in this screenshot: filename, changeset.sql, and description

5. Specify the CodeCommit repository from where Jenkins can fetch the files. In the Source Code Management section check the option Git. Populate the field Repository URL and Branch Specifier with the repository details. For Additional Behaviours, select the Check out to a sub-directory and enter JENKINS_HOME/Code as below:

In the ‘Source Code Management’ section check the option ‘Git’. Populate the field ‘Repository URL’ and ‘Branch Specifier’ with the repository details.In the ‘Additional Behaviours’ select the drop down ‘Check out to a sub-directory’ and enter 'JENKINS_HOME/Code' as shown in this screenshot

6. To set up the build, select Execute Shell from the dropdown menu and enter the command to build, which will be:

bash $JENKINS_HOME/callLiquibaseDemoDeployment.sh $JENKINS_HOME/Code/$filename

The script callLiquibaseDemoDeployment.sh that was created earlier is referenced along with the filename parameter from this Jenkins project.

The script ‘callLiquibaseDemoDeployment.sh’ which was created earlier is referenced along with the filename parameter from this Jenkins project.

7. Set up Post Build action to send mail on Build Failure. Select E-mail Notification from the dropdown and enter the email addresses to which the notification needs to be sent, and select the checkbox Send e-mail for every unstable build as below:

Select ‘E-mail Notification’ from the dropdown and enter the E-mail addresses to which the notification needs to be sent and select the checkbox ‘Send e-mail for every unstable build’ as shown in this screenshot

8. Save and your deployment project is all set.

Create the rollback project

Let’s create a Jenkins project to roll back the DDL changes from the RDS database if the need should arise. There are different ways to roll back using Tag, number of changes, etc. I am going to use the rollback count feature from Liquibase in this example.

1. Log in to Jenkins through the browser using the existing credentials.

2. Click on New Item and select FreeStyle Project.

3. Enter Name for the item as LiquibaseRollbackDemo and then press OK.

4. Create a parameter to specify the filename to roll back. In the General section check the box This project is parameterized. Click on Add Parameter in the dropdown and select String Parameter. Populate the fields as below:

Click on Dropdown 'Add Parameter' and select 'String Parameter' .Populate the fields as shown in this screenshot: filename, changeset.sql, and description

5. Create a parameter to specify the number of changesets to roll back. In the General section check the box This project is parameterized. Click on Add Parameter in the dropdown and select String Parameter. Populate the fields as below:

6. Specify the CodeCommit repository from where Jenkins could fetch the files.

In the Source Code Management section check the option Git. Populate the field Repository URL and Branch Specifier with the repository details. For Additional Behaviours select the dropdown Check out to a sub-directory as below:

In the ‘Source Code Management’ section check the option ‘Git’. Populate the field ‘Repository URL’ and ‘Branch Specifier’ with the repository details.In the ‘Additional Behaviours’ select the drop down ‘Check out to a sub-directory’ and enter 'JENKINS_HOME/Code' as shown in this screenshot

7. To set up the build, select Execute Shell from the dropdown and enter the details. The command for the build will be:

bash $JENKINS_HOME/callLiquibaseDemoRollback.sh $JENKINS_HOME/Code/$filename $JENKINS_HOME/Code/$rollbackcount

The script callLiquibaseDemoRollback.sh that was created earlier is referenced along with the filename parameter from the Jenkins project.

The script ‘callLiquibaseDemoDeployment.sh’ which was created earlier is referenced along with the filename parameter from this Jenkins project. screenshot

8. Set up the Post Build action to send mail on Build Failure.

Select E-mail Notification from the dropdown and enter the email addresses to which the notification needs to be sent, and select the checkbox Send e-mail for every unstable build as below:

Select ‘E-mail Notification’ from the dropdown and enter the E-mail addresses to which the notification needs to be sent and select the checkbox ‘Send e-mail for every unstable build’ as shown in this screenshot

9. Save, and your rollback job is all set.

Deploy the changes

Now that the infrastructure and deployment jobs are set up, let’s complete the deployment.

1. Point your browser to the public DNS name of your EC2 instance and use the credentials created earlier to log in.

2. Click on the project LiquibaseDeploymentDemo.

3. Build the project by clicking Build with Parameters.

4. In the screen below, enter the name of the SQL file stored in the CodeCommit repository that you want to deploy. In the example, we created the SQL file in step 5. We will deploy the file changeset.sql.

5. Selecting Build deploys the changes to the database.

6. In the Build History you will be able to see a unique number for the created job. Click on the job number and Console output to see the job details.

7. Because we have configured Jenkins to notify on failure, we will receive an email with details if the build fails.

Track and verify the changes

Liquibase uses the table databasechangelog to track all the database changes. It holds the tracking data on the file that was deployed along with the time, author, change set, and more details. A DBA or developer can track all the changes applied in the database by querying the databasechangelog file.

In this example, I am using PostgreSQL client to log in to the database. See the screenshot below for the query and the details on the deployed changeset. The changes are represented by a row in this table:

Roll back the changes

Developers must roll back changes occasionally for reasons outside our control. Because we use Liquibase, rolling back the changes is easier.

1. Point your browser to the public DNS name of your EC2 instance and use the credentials created earlier to log in.

2. Click on the project LiquibaseRollbackDemo.

3. Now build the project by clicking Build with Parameters.

4. In the screen below, enter the name of the SQL file used to deploy the change that is stored in the CodeCommit repository. In the example, we will roll back one single change from the file changeset.sql. Note that the changes are deployed in LIFO order (i.e., the last changeset deployed will be rolled back first).

5. Select the Build button and the changes will be rolled back from the database.

6. In the Build History you will be able to see a unique number for the job created. Click on the job number and then Console output to see the job details.

7. Because we have configured Jenkins to notify on failure, we will receive an email with details if the build fails.

Track and verify the rollback

Liquibase uses the table databasechangelog to track all the database changes. Once we did a rollback, it deleted the row that was created earlier after the deployment. We can log in to the database to view these details.

I am using PostgreSQL client again to log in to the database. See the screenshot below for the query. You can see an empty table, which means the row representing the change was deleted from this table following the Liquibase rollback.

Conclusion

In this blog, I explained how to make use of AWS services such as EC2, SES, Secrets Manager, and CodeCommit, along with open source tools Jenkins and Liquibase to deploy, track, and roll back database schema changes into an RDS Aurora PostgreSQL database. This solution is cost effective and can be run on EC2 Spot instances to further reduce costs.

With the appropriate JDBC driver installed and minor modification to the scripts, the solution can be easily modified to work with other RDS databases, such as SQL Server, MySQL, and Oracle. By making minor tweaks to the solution, the Jenkins project also can be modified to poll the repository at specific intervals or to build a deployment job when the developer commits the code.

Hopefully you found this post informative and the solution useful, and we welcome your feedback and comments.

Posted inAWS News