desaiyang
commented
2 years ago psql -c "select * from shopping-cart"
Open desaiyang opened 2 years ago
desaiyang
commented
2 years ago psql -c "select * from shopping-cart"
desaiyang
commented
2 years ago export RDSHOST="mypostgres-cluster.cluster-123456789012.us-west-2.rds.amazonaws.com" export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username jane_doe )"
psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=/sample_dir/global-bundle.pem dbname=DBName user=jane_doe password=$PGPASSWORD"
desaiyang
commented
2 years ago desaiyang
commented
2 years ago Deploy, track, and roll back RDS database code changes using open source tools Liquibase and Jenkins
by Harish Shenoy | on 08 MAY 2020 | in Amazon Aurora, Amazon EC2, Amazon RDS, Amazon Simple Email Service (SES), AWS CodeCommit, Database, Expert (400), Intermediate (200), Open Source, RDS For MySQL, RDS For Oracle, RDS For PostgreSQL, RDS For SQL Server, Technical How-To | Permalink | Comments | Share 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.
In the AWS console navigate to AWS Secrets Manager, Store a new Secret.
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.
Select Next and fill in the Secret name and Description fields.
Select Next and then Enable Automatic rotation (optional, but recommended).
Select Next and Save.
Step 2: Set up a CodeCommit repository To create a CodeCommit repository:
Open the CodeCommit console.
In the region selector, choose the AWS Region where you want to create the repository: Us-east-1.
On the Repositories page, choose Create repository.
On the Create repository page, enter a Repository name: DBDevopsDemoRepo.
In Description, enter a description for the repository: This repo is for Demo of DB deployment automation.
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.
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 Bash 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; servername ;
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 Bash 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" Bash 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.
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 Bash Then save this script and exit the editor.
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 Bash 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.
Create role JenkinsEC2DevopsRole.
Attach the AWS policy AWSCodeCommitPowerUser to the role.
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
Log on to the AWS console for SES and select the Region as us-east-1.
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.
Log in to Jenkins through a browser and navigate to Manage Jenkins, Configure System.
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.
Enter the SMTP server name under Email Notification (for example email-smtp.us-east-1.amazonaws.com from step 4.2).
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
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.
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) ); SQL 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.
Log in to Jenkins through the browser using the existing credentials.
Click on New Item and select FreeStyle Project.
Enter Name for the item as LiquibaseDeploymentDemo and then select OK.
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
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
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.
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
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.
Log in to Jenkins through the browser using the existing credentials.
Click on New Item and select FreeStyle Project.
Enter Name for the item as LiquibaseRollbackDemo and then press OK.
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
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:
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
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
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
Deploy the changes Now that the infrastructure and deployment jobs are set up, let’s complete the deployment.
Point your browser to the public DNS name of your EC2 instance and use the credentials created earlier to log in.
Click on the project LiquibaseDeploymentDemo.
Build the project by clicking Build with Parameters.
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.
Selecting Build deploys the changes to the database.
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.
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.
Point your browser to the public DNS name of your EC2 instance and use the credentials created earlier to log in.
Click on the project LiquibaseRollbackDemo.
Now build the project by clicking Build with Parameters.
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).
Select the Build button and the changes will be rolled back from the database.
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.
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.
Connecting to your DB cluster using IAM authentication from the command line: AWS CLI and psql client PDF RSS
You can connect from the command line to an Aurora PostgreSQL DB cluster with the AWS CLI and psql command line tool as described following.
Prerequisites
The following are prerequisites for connecting to your DB cluster using IAM authentication:
Enabling and disabling IAM database authentication
Creating and using an IAM policy for IAM database access
Creating a database account using IAM authentication
Note For information about connecting to your database using pgAdmin with IAM authentication, see the blog post Using IAM authentication to connect with pgAdmin Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL.
Topics
Generating an IAM authentication token Connecting to an Aurora PostgreSQL cluster Generating an IAM authentication token The authentication token consists of several hundred characters so it can be unwieldy on the command line. One way to work around this is to save the token to an environment variable, and then use that variable when you connect. The following example shows how to use the AWS CLI to get a signed authentication token using the generate-db-auth-token command, and store it in a PGPASSWORD environment variable.
export RDSHOST="mypostgres-cluster.cluster-123456789012.us-west-2.rds.amazonaws.com" export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username jane_doe )" In the example, the parameters to the generate-db-auth-token command are as follows:
--hostname – The host name of the DB cluster (cluster endpoint) that you want to access
--port – The port number used for connecting to your DB cluster
--region – The AWS Region where the DB cluster is running
--username – The database account that you want to access
The first several characters of the generated token look like the following.
mypostgres-cluster.cluster-123456789012.us-west-2.rds.amazonaws.com:5432/?Action=connect&DBUser=jane_doe&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Expires=900...
Connecting to an Aurora PostgreSQL cluster The general format for using psql to connect is shown following.
psql "host=hostName port=portNumber sslmode=verify-full sslrootcert=full_path_to_ssl_certificate dbname=DBName user=userName password=authToken" The parameters are as follows:
host – The host name of the DB cluster (cluster endpoint) that you want to access
port – The port number used for connecting to your DB cluster
sslmode – The SSL mode to use
When you use sslmode=verify-full, the SSL connection verifies the DB cluster endpoint against the endpoint in the SSL certificate.
sslrootcert – The full path to the SSL certificate file that contains the public key
For more information, see Securing Aurora PostgreSQL data with SSL/TLS.
To download an SSL certificate, see Using SSL/TLS to encrypt a connection to a DB cluster.
dbname – The database that you want to access
user – The database account that you want to access
password – A signed IAM authentication token
The following example shows using psql to connect. In the example, psql uses the environment variable RDSHOST for the host and the environment variable PGPASSWORD for the generated token. Also, /sample_dir/ is the full path to the SSL certificate file that contains the public key.
export RDSHOST="mypostgres-cluster.cluster-123456789012.us-west-2.rds.amazonaws.com" export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-west-2 --username jane_doe )"
psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=/sample_dir/global-bund