Continuous Integration (CI) and Continuous Deployment (CD) for databases is a lot harder than those things are for stateless applications. I still regularly meet teams who are deploying their database by hand because automated CD was too hard. Not coincidentally, those teams also need help from a data engineering consultant.
image: FabrĂzio Mello CC Attribution License
You can and should build your data structures from source control. This post will show you how to do so with Amazon Web Services’ Codestar, Redshift, and flyway. The goal is for your database structures to be in a known state at all times, and for all upgrades to be one-click.
A Virtual Private Cloud (VPC), with private subnets and Network Address Translation (NAT) gateways. I presume that most people already have one of these. If not, the following scripts should help:
You’ll need codestar, cloud formation, IAM, and redshift permissions.
There are no special requirements for Code Commit. I used a command line aws codecommit create-repository cfn-flyway
call.
Next we use a Cloudformation script to create the Code Pipeline and its Code Build step.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 |
--- AWSTemplateFormatVersion: "2010-09-09" Description: Create a flyway CodePipeline for database migrations Parameters: DBMasterUsername: Type: String Description: Name for the DB User Default: pipeline_is_dba DBMasterPassword: Type: String Description: Password for the DB NoEcho: true DBPortNumber: Description: The port number on which the database accepts incoming connections. Type: Number Default: 3306 PipelineBucket: Type: String Description: Name of the S3 bucket to store CodePipeline artifacts. RepoName: Type: String Description: Name of the repo being deployed PrivateSubnet: Type: AWS::EC2::Subnet::Id Description: Subnet for codebuild to access DB VPC: Type: AWS::EC2::VPC::Id Description: Existing VPC in which to deploy Resources: CodePipelineRole: Type: "AWS::IAM::Role" Properties: RoleName: Fn::Sub: CodePipelineRole-${AWS::StackName} AssumeRolePolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Principal: Service: - "codepipeline.amazonaws.com" Action: - "sts:AssumeRole" Path: / Policies: - PolicyName: "CodePipelineNestedCFNAccessPolicy" PolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Action: - "s3:DeleteObject" - "s3:GetObject" - "s3:GetObjectVersion" - "s3:ListBucket" - "s3:PutObject" - "s3:GetBucketPolicy" Resource: - Fn::Sub: arn:aws:s3:::${PipelineBucket} - Fn::Sub: arn:aws:s3:::${PipelineBucket}/* - Effect: "Allow" Action: - "codecommit:ListBranches" - "codecommit:ListRepositories" - "codecommit:BatchGetRepositories" - "codecommit:Get*" - "codecommit:GitPull" - "codecommit:UploadArchive" Resource: - Fn::Sub: arn:aws:codecommit:${AWS::Region}:${AWS::AccountId}:${RepoName} - Effect: "Allow" Action: - "codebuild:StartBuild" - "codebuild:BatchGetBuilds" Resource: - Fn::Sub: arn:aws:codebuild:${AWS::Region}:${AWS::AccountId}:project/FlywayCodebuild - Fn::Sub: arn:aws:codebuild:${AWS::Region}:${AWS::AccountId}:build/FlywayCodebuild:* - Effect: "Allow" Action: - "iam:PassRole" Resource: - Fn::Sub: arn:aws:iam::${AWS::AccountId}:role/CloudFormationRole-${AWS::StackName} CodeBuildRole: Type: "AWS::IAM::Role" Properties: RoleName: Fn::Sub: CodeBuildRole-${AWS::StackName} AssumeRolePolicyDocument: Version: "2012-10-17" Statement: - Effect: "Allow" Principal: Service: - "codebuild.amazonaws.com" Action: - "sts:AssumeRole" Path: /service-role/ Policies: - PolicyName: "CodeBuildAccessPolicy" PolicyDocument: Version: "2012-10-17" Statement: - Sid: CodeCommitReadPermissions Effect: "Allow" Action: - "codecommit:ListBranches" - "codecommit:ListRepositories" - "codecommit:BatchGetRepositories" - "codecommit:Get*" - "codecommit:GitPull" Resource: - Fn::Sub: arn:aws:codecommit:${AWS::Region}:${AWS::AccountId}:${RepoName} - Sid: GeneralReadPermissions Effect: "Allow" Action: - "ec2:Describe*" - "cloudformation:ValidateTemplate" - "iam:Get*" - "iam:List*" - "logs:Describe*" - "logs:Get*" - "tag:Get*" Resource: - "*" - Sid: WriteLogsPermissions Effect: "Allow" Action: - "logs:CreateLogGroup" - "logs:CreateLogStream" - "logs:PutLogEvents" Resource: - "*" - Sid: PipelineBucketPermissions Effect: "Allow" Action: - "s3:PutObject" - "s3:GetObject" - "s3:GetObjectVersion" - "s3:ListBucket" Resource: - Fn::Sub: arn:aws:s3:::codepipeline-${AWS::Region}-* - Fn::Sub: arn:aws:s3:::${PipelineBucket}/* - Fn::Sub: arn:aws:s3:::${PipelineBucket} CodeBuildInVPCPolicy: Type: AWS::IAM::Policy Properties: PolicyName: codebuild-in-vpc PolicyDocument: Version: '2012-10-17' Statement: - Effect: Allow Action: - ec2:CreateNetworkInterface - ec2:DescribeDhcpOptions - ec2:DescribeNetworkInterfaces - ec2:DeleteNetworkInterface - ec2:DescribeSubnets - ec2:DescribeSecurityGroups - ec2:DescribeVpcs Resource: "*" - Effect: Allow Action: - ec2:CreateNetworkInterfacePermission Resource: !Sub arn:aws:ec2:${AWS::Region}:${AWS::AccountId}:network-interface/* Condition: StringEquals: ec2:AuthorizedService: codebuild.amazonaws.com Roles: - !Ref CodeBuildRole DBSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupDescription: Group for DB VpcId: Ref: VPC AccessDBSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupDescription: Allow access to DB VpcId: Ref: VPC ToDBRule: Type: AWS::EC2::SecurityGroupEgress Properties: IpProtocol: tcp FromPort: !Ref DBPortNumber ToPort: !Ref DBPortNumber DestinationSecurityGroupId: Fn::GetAtt: - DBSecurityGroup - GroupId GroupId: Fn::GetAtt: - AccessDBSecurityGroup - GroupId DBListenRule: Type: AWS::EC2::SecurityGroupIngress Properties: IpProtocol: tcp FromPort: !Ref DBPortNumber ToPort: !Ref DBPortNumber SourceSecurityGroupId: Fn::GetAtt: - AccessDBSecurityGroup - GroupId GroupId: Fn::GetAtt: - DBSecurityGroup - GroupId HTTPSSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupDescription: Allow outbound HTTPS, for S3, CodeCommit, etc VpcId: Ref: VPC HTTPSOutRule: Type: AWS::EC2::SecurityGroupEgress Properties: IpProtocol: tcp FromPort: 443 ToPort: 443 CidrIp: 0.0.0.0/0 GroupId: Fn::GetAtt: - HTTPSSecurityGroup - GroupId RedshiftSubnetGroup: Type: 'AWS::Redshift::ClusterSubnetGroup' Properties: Description: My ClusterSubnetGroup SubnetIds: - !Ref PrivateSubnet Redshift: Type: AWS::Redshift::Cluster Properties: ClusterSubnetGroupName: Ref: RedshiftSubnetGroup DBName: "mydb" MasterUsername: Ref: DBMasterUsername MasterUserPassword: Ref: DBMasterPassword NodeType: "dc2.large" ClusterType: "single-node" Port: Ref: DBPortNumber VpcSecurityGroupIds: - !GetAtt DBSecurityGroup.GroupId FlywayCodeBuild: Type: AWS::CodeBuild::Project Properties: Name: FlywayCodebuild Description: build a database ServiceRole: Fn::GetAtt: [CodeBuildRole, Arn] Artifacts: Type: NO_ARTIFACTS Environment: Type: LINUX_CONTAINER ComputeType: BUILD_GENERAL1_SMALL Image: aws/codebuild/nodejs:7.0.0 EnvironmentVariables: - Name: FLYWAY_URL Value: !Join ["", ["jdbc:redshift://", !GetAtt Redshift.Endpoint.Address, ":", !Ref DBPortNumber, "/mydb"]] - Name: FLYWAY_USER Value: !Ref DBMasterUsername - Name: FLYWAY_PASSWORD Value: !Ref DBMasterPassword Source: Location: Fn::Sub: https://git-codecommit.${AWS::Region}.amazonaws.com/v1/repos/${RepoName} Type: CODECOMMIT TimeoutInMinutes: 15 EncryptionKey: Fn::Sub: arn:aws:kms:${AWS::Region}:${AWS::AccountId}:alias/aws/s3 Tags: - Key: Name Value: Fn::Sub: FlywayCFN-${AWS::StackName} VpcConfig: SecurityGroupIds: - !Ref AccessDBSecurityGroup - !Ref HTTPSSecurityGroup Subnets: - !Ref PrivateSubnet VpcId: !Ref VPC DeployPipeline: Type: "AWS::CodePipeline::Pipeline" Properties: Name: Fn::Sub: Flyway-demo-${AWS::StackName} RoleArn: Fn::GetAtt: [CodePipelineRole, Arn] Stages: - Name: CodeCommitStage Actions: - Name: CodeCommitAction ActionTypeId: Category: Source Owner: AWS Version: "1" Provider: CodeCommit OutputArtifacts: - Name: GitOut Configuration: BranchName: master RepositoryName: Ref: RepoName RunOrder: 1 - Name: Deploy Actions: - Name: CodeBuild InputArtifacts: - Name: GitOut ActionTypeId: Category: Build Owner: AWS Version: "1" Provider: CodeBuild Configuration: ProjectName: Ref: FlywayCodeBuild RunOrder: 1 ArtifactStore: Type: S3 Location: Ref: PipelineBucket |
There is a lot going on here, but its not too much to understand.
Code Build has been left at its defaults, expecting a buildspec.yml file. I use the same YAML most of the time:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--- version: 0.1 phases: install: commands: - echo "Configuring build files" # Allow execute on the files, -v is verbose, -v -v is more verbose - chmod -v -v +x ./*.sh - ./install.sh build: commands: - ./build.sh |
This script does very little. It delegates the work to install.sh
and build.sh
and makes sure that they are executable. Those two shell scripts do the deployment.
1 2 3 4 5 6 7 8 9 10 |
#!/bin/bash source ./build.env wget "https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${FLYWAY_VERSION}/flyway-commandline-${FLYWAY_VERSION}-linux-x64.tar.gz" tar -xvzf "flyway-commandline-${FLYWAY_VERSION}-linux-x64.tar.gz" wget "https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/${JDBC_VERSION}/RedshiftJDBC42-${JDBC_VERSION}.jar" mv "RedshiftJDBC42-${JDBC_VERSION}.jar" "./flyway-${FLYWAY_VERSION}/drivers/" mv ./sql "./flyway-${FLYWAY_VERSION}/sql/" |
Install downloads and installs flyway and the redshift JDBC driver. It sources build.env
for shared configuration:
1 2 3 4 5 6 7 8 9 10 |
#!/bin/bash if [ "${BASH_SOURCE[0]}" -ef "$0" ] then echo "Source this script, do not execute it!" exit 1 fi export FLYWAY_VERSION="5.2.4" export JDBC_VERSION="1.2.16.1027" |
All that is left is to call flyway and let it build the database:
1 2 3 4 5 6 7 |
#!/bin/bash source ./build.env ls -R "./flyway-${FLYWAY_VERSION}/" "./flyway-${FLYWAY_VERSION}/flyway" migrate -X |
The flyway migrate command does the actual work of creating the database objects. The ls command is for debugging. Its output is written to the Code Build log.
So far we’re migrating no content. We need to create a sql
folder, and start putting flyway compatibly named SQL scripts into it, for example:
1 2 3 4 |
CREATE TABLE first_table (id int primary key ,name varchar(100) ); |
And now you’re done. You have a record of what changed, when, why, and by whom. You can build a correct database from scratch, reliably and with almost no effort. When you’re comfortable, take away everyone’s DDL permissions on the database. And bask in the resulting calm and productivity.
I often hear that people believe that they need permission to type untested commands into production in order to resolve incidents. Typically in an untraceable way. I phrased it differently when I had this permission, but typing untested commands into production is what we’re talking about here. And I’ve never met a database that I didn’t configure where DDL commands were being logged in a way that the auditors can be confident that the log is complete. We don’t typically type untested commands into production because we need to test, because the source control commit message provides the motivation for change, and because we require a reliable audit log. Right?
As for how incidents are fixed, they are fixed the same way that everything else happens. The “ALTER DATABASE SET smoke=back_in” commands that are desperately needed to resolve incidents are committed into source control and deployed by the pipeline. With a test case that demonstrates if they worked or not.
Be the first to comment.