Infra as a code(cloudformation)를 이용하여 디비구축하기
2021-07-31
.
Data_Engineering_TIL(20210731)
[학습자료]
“클라우드 네이티브를 위한 쿠버네티스 실전 프로젝트” 책을 읽고 실습한 내용입니다.
** 동양북스, 아이자와 고지&사토 가즈히코 지음, 박상욱 옮김
참고자료 URL : https://github.com/dybooksIT/k8s-aws-book
[학습내용]
“Cloudformation으로 간단하게 EKS cluster 구동하기”에 이어서 진행을 한 실습을 진행한 내용임
** URL : https://minman2115.github.io/DE_TIL251
- 실습의 최종 목표
서비스의 최종 구현 목표
아키텍처 관점에서 최종목표
- 오늘의 실습목표
infra as a code(클라우드 포메이션)를 이용하여 디비를 구축해본다.
- 실습내용
먼저 10_rds_ope_cfn.yaml라는 클라우드 포메이션 템플릿을 test bucket에 업로드를 하고 아래그림 이거를 이용하여 스텍을 생성한다.
- 10_rds_ope_cfn.yaml
기본 네트워크 인프라를 바탕으로해서 RDS를 생성하는 클라우드 포메이션 템플릿임
AWSTemplateFormatVersion: "2010-09-09"
Parameters:
EksWorkVPC:
Type: AWS::EC2::VPC::Id
WorkerSubnet1Block:
Type: String
Default: 192.168.0.0/24
WorkerSubnet2Block:
Type: String
Default: 192.168.1.0/24
WorkerSubnet3Block:
Type: String
Default: 192.168.2.0/24
OpeSubnetBlock:
Type: String
Default: 192.168.5.0/24
OpeServerRouteTable:
Type: String
ClusterBaseName:
Type: String
Default: eks-work
TargetRegion:
Type: String
Default: ap-northeast-2
AvailabilityZone1:
Type: String
Default: ap-northeast-2a
AvailabilityZone2:
Type: String
Default: ap-northeast-2b
AvailabilityZone3:
Type: String
Default: ap-northeast-2c
RdsSubnet1Block:
Type: String
Default: 192.168.3.0/24
RdsSubnet2Block:
Type: String
Default: 192.168.4.0/24
OpeServerInstanceType:
Type: String
Default: t2.micro
OpeServerImageId:
Type: String
Default: ami-004ca97dc8c3b41f8 # ap-northeast-2, GPU, Amazon Linux 2
# aws ssm get-parameter --name /aws/service/eks/optimized-ami/1.19/amazon-linux-2-gpu/recommended/image_id --region ap-northeast-2 --query "Parameter.Value" --output text
OpeServerVolumeSize:
Type: Number
Default: 8
Resources:
RdsSubnet1:
Type: AWS::EC2::Subnet
Properties:
AvailabilityZone: !Ref AvailabilityZone1
CidrBlock: !Ref RdsSubnet1Block
VpcId: !Ref EksWorkVPC
RdsSubnet2:
Type: AWS::EC2::Subnet
Properties:
AvailabilityZone: !Ref AvailabilityZone2
CidrBlock: !Ref RdsSubnet2Block
VpcId: !Ref EksWorkVPC
RdsSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupDescription: Security Group for RDS
VpcId: !Ref EksWorkVPC
RdsIngressPostgreSQLWorker1:
Type: AWS::EC2::SecurityGroupIngress
Properties:
GroupId: !Ref RdsSecurityGroup
CidrIp: !Ref WorkerSubnet1Block
IpProtocol: tcp
FromPort: 5432 # mysql 3306 mariadb 3396 postgresql 5432
ToPort: 5432
RdsIngressPostgreSQLWorker2:
Type: AWS::EC2::SecurityGroupIngress
Properties:
GroupId: !Ref RdsSecurityGroup
CidrIp: !Ref WorkerSubnet2Block
IpProtocol: tcp
FromPort: 5432
ToPort: 5432
RdsIngressPostgreSQLWorker3:
Type: AWS::EC2::SecurityGroupIngress
Properties:
GroupId: !Ref RdsSecurityGroup
CidrIp: !Ref WorkerSubnet3Block
IpProtocol: tcp
FromPort: 5432
ToPort: 5432
RdsIngressPostgreSQLOpe:
Type: AWS::EC2::SecurityGroupIngress
Properties:
GroupId: !Ref RdsSecurityGroup
CidrIp: !Ref OpeSubnetBlock
IpProtocol: tcp
FromPort: 5432
ToPort: 5432
EksWorkDB:
Type: "AWS::RDS::DBInstance"
Properties:
DBInstanceIdentifier: eks-work-db
Engine: postgres # https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html#aws-properties-rds-database-instance-properties
EngineVersion: 11 # https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html#MySQL.Concepts.VersionMgmt
DBInstanceClass: db.t2.micro
AllocatedStorage: 30
StorageType: gp2
DBName: eksworkdb
MasterUsername: !Join ['', ['' ]]
MasterUserPassword: !Join ['', ['' ]]
DBSubnetGroupName: !Ref EksWorkDBSubnetGroup
PubliclyAccessible: false
MultiAZ: false
PreferredBackupWindow: 18:00-18:30
PreferredMaintenanceWindow: sat:19:00-sat:19:30
AutoMinorVersionUpgrade: false
DBParameterGroupName: !Ref EksWorkDBParameterGroup
VPCSecurityGroups:
- !Ref RdsSecurityGroup
CopyTagsToSnapshot: true
BackupRetentionPeriod: 7
DeletionProtection: false
RdsMasterSecret:
Type: AWS::SecretsManager::Secret
Properties:
GenerateSecretString:
SecretStringTemplate: '{"username": "eksdbadmin"}'
GenerateStringKey: 'password'
PasswordLength: 16
ExcludeCharacters: '"@/\'''
Name: RdsMasterSecret
RdsUserSecret:
Type: AWS::SecretsManager::Secret
Properties:
GenerateSecretString:
SecretStringTemplate: '{"username": "mywork"}'
GenerateStringKey: 'password'
PasswordLength: 16
ExcludeCharacters: '"@/\''{}#%&*<>[]^`|'
Name: RdsUserSecret
RdsSecretAttachment:
Type: AWS::SecretsManager::SecretTargetAttachment
Properties:
SecretId: !Ref RdsMasterSecret
TargetId: !Ref EksWorkDB
TargetType: AWS::RDS::DBInstance
EksWorkDBSubnetGroup:
Type: AWS::RDS::DBSubnetGroup
Properties:
DBSubnetGroupName: subnet-group-eks-work-db
DBSubnetGroupDescription: "DB Subnet Group"
SubnetIds:
- !Ref RdsSubnet1
- !Ref RdsSubnet2
EksWorkDBParameterGroup:
Type: AWS::RDS::DBParameterGroup
Properties:
Family: postgres11 # aws rds describe-db-engine-versions --query "DBEngineVersions[].DBParameterGroupFamily"
Description: Parameter Group for PostgreSQL 11
OpeServerSubnet:
Type: AWS::EC2::Subnet
Properties:
AvailabilityZone: !Ref AvailabilityZone1
CidrBlock: !Ref OpeSubnetBlock
VpcId: !Ref EksWorkVPC
OpeServerSubnetRouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref OpeServerSubnet
RouteTableId: !Ref OpeServerRouteTable
OpeServerSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupDescription: Security Group for Ope
VpcId: !Ref EksWorkVPC
RdsIngressPostgreSQLFromOpe:
Type: AWS::EC2::SecurityGroupIngress
Properties:
GroupId: !Ref RdsSecurityGroup
SourceSecurityGroupId: !Ref OpeServerSecurityGroup
IpProtocol: tcp
FromPort: 5432
ToPort: 5432
OpeServerRole:
Type: AWS::IAM::Role
Properties:
RoleName: !Sub ${ClusterBaseName}-OpeServerRole
ManagedPolicyArns:
- arn:aws:iam::aws:policy/service-role/AmazonEC2RoleforSSM
Path: /
AssumeRolePolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Principal:
Service:
- ec2.amazonaws.com
Action:
- sts:AssumeRole
OpeServerInstanceProfile:
Type: AWS::IAM::InstanceProfile
Properties:
Path: /
Roles:
- !Ref OpeServerRole
OpeServerEIP:
Type: AWS::EC2::EIP
Properties:
Domain: vpc
OpeServerEIPAssociation:
Type: AWS::EC2::EIPAssociation
Properties:
AllocationId: !GetAtt OpeServerEIP.AllocationId
InstanceId: !Ref OpeServerInstance
OpeServerInstance:
Type: AWS::EC2::Instance
Properties:
InstanceType: !Ref OpeServerInstanceType
ImageId: !Ref OpeServerImageId
BlockDeviceMappings:
- DeviceName: /dev/xvda
Ebs:
VolumeType: gp2
VolumeSize: 20 # 원래는 8GB였음. 권고 사항이 20GB라고 함
DeleteOnTermination: true
NetworkInterfaces:
- SubnetId: !Ref OpeServerSubnet
AssociatePublicIpAddress: false
GroupSet:
- !Ref OpeServerSecurityGroup
DeviceIndex: 0
DeleteOnTermination: true
DisableApiTermination: false
IamInstanceProfile: !Ref OpeServerInstanceProfile
UserData:
Fn::Base64:
!Sub |
#!/bin/bash
set -o xtrace
yum install -y https://s3.amazonaws.com/ec2-downloads-windows/SSMAgent/latest/linux_amd64/amazon-ssm-agent.rpm
/opt/aws/bin/cfn-signal --exit-code $? \
--stack ${AWS::StackName} \
--resource NodeGroup \
--region ${AWS::Region}
Outputs:
RDSEndpoint:
Value: !GetAtt EksWorkDB.Endpoint.Address
그러면 아래와 같이 스텍이 만들어지게 되고, 결과 값으로 RDS 엔드포인트를 확인할 수 있고, 해당 RDS가 생성된 것도 확인할 수 있다. RDS 엔드포인트는 별도로 복사해둔다.
생성한 RDS에 접속하기 위해 세션매니저를 이용할 것이다. 아래 그림과 같이 세션매니저를 먼저 접속한다.
세션매니저에서 깃 클라이언트와 PG 클라이언트 설치
sh-4.2$ sudo yum install git -y
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
amzn2-core | 3.7 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package git.x86_64 0:2.32.0-1.amzn2.0.1 will be installed
--> Processing Dependency: perl-Git = 2.32.0-1.amzn2.0.1 for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Processing Dependency: git-core-doc = 2.32.0-1.amzn2.0.1 for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Processing Dependency: git-core = 2.32.0-1.amzn2.0.1 for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Processing Dependency: emacs-filesystem >= 27.1 for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Processing Dependency: perl(Term::ReadKey) for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Processing Dependency: perl(Git::I18N) for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Processing Dependency: perl(Git) for package: git-2.32.0-1.amzn2.0.1.x86_64
--> Running transaction check
---> Package emacs-filesystem.noarch 1:27.1-1.amzn2.0.1 will be installed
---> Package git-core.x86_64 0:2.32.0-1.amzn2.0.1 will be installed
---> Package git-core-doc.noarch 0:2.32.0-1.amzn2.0.1 will be installed
---> Package perl-Git.noarch 0:2.32.0-1.amzn2.0.1 will be installed
--> Processing Dependency: perl(Error) for package: perl-Git-2.32.0-1.amzn2.0.1.noarch
---> Package perl-TermReadKey.x86_64 0:2.30-20.amzn2.0.2 will be installed
--> Running transaction check
---> Package perl-Error.noarch 1:0.17020-2.amzn2 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================
Installing:
git x86_64 2.32.0-1.amzn2.0.1 amzn2-core 126 k
Installing for dependencies:
emacs-filesystem noarch 1:27.1-1.amzn2.0.1 amzn2-core 64 k
git-core x86_64 2.32.0-1.amzn2.0.1 amzn2-core 4.8 M
git-core-doc noarch 2.32.0-1.amzn2.0.1 amzn2-core 2.7 M
perl-Error noarch 1:0.17020-2.amzn2 amzn2-core 32 k
perl-Git noarch 2.32.0-1.amzn2.0.1 amzn2-core 43 k
perl-TermReadKey x86_64 2.30-20.amzn2.0.2 amzn2-core 31 k
Transaction Summary
================================================================================================================================================================
Install 1 Package (+6 Dependent packages)
Total download size: 7.8 M
Installed size: 38 M
Downloading packages:
(1/7): emacs-filesystem-27.1-1.amzn2.0.1.noarch.rpm | 64 kB 00:00:00
(2/7): git-2.32.0-1.amzn2.0.1.x86_64.rpm | 126 kB 00:00:00
(3/7): git-core-doc-2.32.0-1.amzn2.0.1.noarch.rpm | 2.7 MB 00:00:00
(4/7): git-core-2.32.0-1.amzn2.0.1.x86_64.rpm | 4.8 MB 00:00:00
(5/7): perl-Error-0.17020-2.amzn2.noarch.rpm | 32 kB 00:00:00
(6/7): perl-Git-2.32.0-1.amzn2.0.1.noarch.rpm | 43 kB 00:00:00
(7/7): perl-TermReadKey-2.30-20.amzn2.0.2.x86_64.rpm | 31 kB 00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 30 MB/s | 7.8 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : git-core-2.32.0-1.amzn2.0.1.x86_64 1/7
Installing : git-core-doc-2.32.0-1.amzn2.0.1.noarch 2/7
Installing : 1:emacs-filesystem-27.1-1.amzn2.0.1.noarch 3/7
Installing : 1:perl-Error-0.17020-2.amzn2.noarch 4/7
Installing : perl-TermReadKey-2.30-20.amzn2.0.2.x86_64 5/7
Installing : perl-Git-2.32.0-1.amzn2.0.1.noarch 6/7
Installing : git-2.32.0-1.amzn2.0.1.x86_64 7/7
Verifying : perl-TermReadKey-2.30-20.amzn2.0.2.x86_64 1/7
Verifying : git-core-doc-2.32.0-1.amzn2.0.1.noarch 2/7
Verifying : perl-Git-2.32.0-1.amzn2.0.1.noarch 3/7
Verifying : git-2.32.0-1.amzn2.0.1.x86_64 4/7
Verifying : git-core-2.32.0-1.amzn2.0.1.x86_64 5/7
Verifying : 1:perl-Error-0.17020-2.amzn2.noarch 6/7
Verifying : 1:emacs-filesystem-27.1-1.amzn2.0.1.noarch 7/7
Installed:
git.x86_64 0:2.32.0-1.amzn2.0.1
Dependency Installed:
emacs-filesystem.noarch 1:27.1-1.amzn2.0.1 git-core.x86_64 0:2.32.0-1.amzn2.0.1 git-core-doc.noarch 0:2.32.0-1.amzn2.0.1
perl-Error.noarch 1:0.17020-2.amzn2 perl-Git.noarch 0:2.32.0-1.amzn2.0.1 perl-TermReadKey.x86_64 0:2.30-20.amzn2.0.2
Complete!
sh-4.2$ sudo amazon-linux-extras install postgresql11 -y
Installing postgresql
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
Cleaning repos: amzn2-core amzn2extra-docker amzn2extra-postgresql11
12 metadata files removed
4 sqlite files removed
0 metadata files removed
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
amzn2-core | 3.7 kB 00:00:00
amzn2extra-docker | 3.0 kB 00:00:00
amzn2extra-postgresql11 | 3.0 kB 00:00:00
(1/7): amzn2-core/2/x86_64/group_gz | 2.5 kB 00:00:00
(2/7): amzn2-core/2/x86_64/updateinfo | 401 kB 00:00:00
(3/7): amzn2extra-postgresql11/2/x86_64/primary_db | 42 kB 00:00:00
(4/7): amzn2extra-docker/2/x86_64/updateinfo | 76 B 00:00:00
(5/7): amzn2extra-docker/2/x86_64/primary_db | 79 kB 00:00:00
(6/7): amzn2extra-postgresql11/2/x86_64/updateinfo | 76 B 00:00:00
(7/7): amzn2-core/2/x86_64/primary_db | 55 MB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package postgresql.x86_64 0:11.12-1.amzn2.0.1 will be installed
--> Processing Dependency: libpq.so.5(RHPG_9.6)(64bit) for package: postgresql-11.12-1.amzn2.0.1.x86_64
--> Processing Dependency: libpq.so.5(RHPG_10)(64bit) for package: postgresql-11.12-1.amzn2.0.1.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-11.12-1.amzn2.0.1.x86_64
--> Running transaction check
---> Package libpq.x86_64 0:11.5-1.amzn2 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================
Installing:
postgresql x86_64 11.12-1.amzn2.0.1 amzn2extra-postgresql11 1.5 M
Installing for dependencies:
libpq x86_64 11.5-1.amzn2 amzn2extra-postgresql11 179 k
Transaction Summary
================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 1.7 M
Installed size: 6.0 M
Downloading packages:
(1/2): libpq-11.5-1.amzn2.x86_64.rpm | 179 kB 00:00:00
(2/2): postgresql-11.12-1.amzn2.0.1.x86_64.rpm | 1.5 MB 00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 7.5 MB/s | 1.7 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : libpq-11.5-1.amzn2.x86_64 1/2
Installing : postgresql-11.12-1.amzn2.0.1.x86_64 2/2
Verifying : postgresql-11.12-1.amzn2.0.1.x86_64 1/2
Verifying : libpq-11.5-1.amzn2.x86_64 2/2
Installed:
postgresql.x86_64 0:11.12-1.amzn2.0.1
Dependency Installed:
libpq.x86_64 0:11.5-1.amzn2
Complete!
0 ansible2 available \
[ =2.4.2 =2.4.6 =2.8 =stable ]
2 httpd_modules available [ =1.0 =stable ]
3 memcached1.5 available \
[ =1.5.1 =1.5.16 =1.5.17 ]
5 postgresql9.6 available \
[ =9.6.6 =9.6.8 =stable ]
6 postgresql10 available [ =10 =stable ]
9 R3.4 available [ =3.4.3 =stable ]
10 rust1 available \
[ =1.22.1 =1.26.0 =1.26.1 =1.27.2 =1.31.0 =1.38.0
=stable ]
11 vim available [ =8.0 =stable ]
15 php7.2 available \
[ =7.2.0 =7.2.4 =7.2.5 =7.2.8 =7.2.11 =7.2.13 =7.2.14
=7.2.16 =7.2.17 =7.2.19 =7.2.21 =7.2.22 =7.2.23
=7.2.24 =7.2.26 =stable ]
17 lamp-mariadb10.2-php7.2 available \
[ =10.2.10_7.2.0 =10.2.10_7.2.4 =10.2.10_7.2.5
=10.2.10_7.2.8 =10.2.10_7.2.11 =10.2.10_7.2.13
=10.2.10_7.2.14 =10.2.10_7.2.16 =10.2.10_7.2.17
=10.2.10_7.2.19 =10.2.10_7.2.22 =10.2.10_7.2.23
=10.2.10_7.2.24 =stable ]
18 libreoffice available \
[ =5.0.6.2_15 =5.3.6.1 =stable ]
19 gimp available [ =2.8.22 ]
20 docker=latest enabled \
[ =17.12.1 =18.03.1 =18.06.1 =18.09.9 =stable ]
21 mate-desktop1.x available \
[ =1.19.0 =1.20.0 =stable ]
22 GraphicsMagick1.3 available \
[ =1.3.29 =1.3.32 =1.3.34 =stable ]
23 tomcat8.5 available \
[ =8.5.31 =8.5.32 =8.5.38 =8.5.40 =8.5.42 =8.5.50
=stable ]
24 epel available [ =7.11 =stable ]
25 testing available [ =1.0 =stable ]
26 ecs available [ =stable ]
27 corretto8 available \
[ =1.8.0_192 =1.8.0_202 =1.8.0_212 =1.8.0_222 =1.8.0_232
=1.8.0_242 =stable ]
28 firecracker available [ =0.11 =stable ]
29 golang1.11 available \
[ =1.11.3 =1.11.11 =1.11.13 =stable ]
30 squid4 available [ =4 =stable ]
31 php7.3 available \
[ =7.3.2 =7.3.3 =7.3.4 =7.3.6 =7.3.8 =7.3.9 =7.3.10
=7.3.11 =7.3.13 =stable ]
32 lustre2.10 available \
[ =2.10.5 =2.10.8 =stable ]
33 java-openjdk11 available [ =11 =stable ]
34 lynis available [ =stable ]
35 kernel-ng available [ =stable ]
36 BCC available [ =0.x =stable ]
37 mono available [ =5.x =stable ]
38 nginx1 available [ =stable ]
39 ruby2.6 available [ =2.6 =stable ]
40 mock available [ =stable ]
41 postgresql11=latest enabled [ =11 =stable ]
42 php7.4 available [ =stable ]
43 livepatch available [ =stable ]
44 python3.8 available [ =stable ]
45 haproxy2 available [ =stable ]
46 collectd available [ =stable ]
47 aws-nitro-enclaves-cli available [ =stable ]
48 R4 available [ =stable ]
49 kernel-5.4 available [ =stable ]
50 selinux-ng available [ =stable ]
51 php8.0 available [ =stable ]
52 tomcat9 available [ =stable ]
53 unbound1.13 available [ =stable ]
54 mariadb10.5 available [ =stable ]
55 kernel-5.10 available [ =stable ]
56 redis6 available [ =stable ]
57 ruby3.0 available [ =stable ]
58 postgresql12 available [ =stable ]
59 postgresql13 available [ =stable ]
60 mock2 available [ =stable ]
sh-4.2$ pwd
/home/ssm-user
sh-4.2$ git clone https://github.com/dybooksIT/k8s-aws-book.git
Cloning into 'k8s-aws-book'...
remote: Enumerating objects: 446, done.
remote: Counting objects: 100% (446/446), done.
remote: Compressing objects: 100% (281/281), done.
remote: Total 446 (delta 139), reused 401 (delta 97), pack-reused 0
Receiving objects: 100% (446/446), 1.75 MiB | 2.01 MiB/s, done.
Resolving deltas: 100% (139/139), done.
그 다음에는 디비 관리자 비번을 확인해야 한다. 클라우드 포메이션으로 RDS를 구축할때 AWS Secret 매니저가 디비 관리자 비번을 생성하여 데이터 베이스에 등록을 하도록 해서 만들었기 때문에 AWS Secret 매니저로 생성한 비밀번호를 확인해야 한다. AWS secret 매니저에서 ‘보안암호’ 메뉴에서 확인할 수 있다.
확인한 관리자 비번을 갖고 세션매니저에서 아래와 같이 RDS로 접속해 DB를 만들고 테이블을 정의해본다.
# eks-work-db.xxxxxxxxx.ap-northeast-2.rds.amazonaws.com는 클라우드 포메이션으로 만든 RDS의 엔드포인트임
sh-4.2$ createuser -d -U eksdbadmin -P -h eks-work-db.xxxxxxxxx.ap-northeast-2.rds.amazonaws.com mywork
Enter password for new role: 보안암호값 확인한거 복붙
Enter it again: 보안암호값 확인한거 복붙
Password: 보안암호값 확인한거 복붙
sh-4.2$ createdb -U mywork -h eks-work-db.xxxxxxxxxx.ap-northeast-2.rds.amazonaws.com -E UTF8 myworkdb
Password: 보안암호값 확인한거 복붙
sh-4.2$ psql -U mywork -h eks-work-db.xxxxxxxxx.ap-northeast-2.rds.amazonaws.com myworkdb
Password for user mywork: 보안암호값 확인한거 복붙
psql (11.12)
SSL connection (protocol: TLSv1.2, cipher: xxxxxxxxxxxxxxxxxxx, bits: 256, compression: off)
Type "help" for help.
myworkdb=> \i k8s-aws-book/backend-app/scripts/10_ddl.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
myworkdb=> \i k8s-aws-book/backend-app/scripts/20_insert_sample_data.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
myworkdb=> \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------+----------+-------------+-------------+---------------------------
eksworkdb | eksdbadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
myworkdb | mywork | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | eksdbadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin +
| | | | | rdsadmin=CTc/rdsadmin
template1 | eksdbadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/eksdbadmin +
| | | | | eksdbadmin=CTc/eksdbadmin
(6 rows)
myworkdb=> \c myworkdb
SSL connection (protocol: TLSv1.2, cipher: xxxxxxxxxxxxxxxxxxx, bits: 256, compression: off)
You are now connected to database "myworkdb" as user "mywork".
myworkdb=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+--------
public | batch_processing | table | mywork
public | batch_processing_file | table | mywork
public | location | table | mywork
public | region | table | mywork
(4 rows)
myworkdb=> \q
- 10_ddl.sql
CREATE TABLE region
(
region_id SERIAL PRIMARY KEY,
region_name VARCHAR(100) NOT NULL,
creation_timestamp TIMESTAMP NOT NULL
);
CREATE TABLE location
(
location_id BIGSERIAL PRIMARY KEY,
location_name VARCHAR(200) NOT NULL,
region_id BIGINT NOT NULL,
note TEXT,
FOREIGN KEY (region_id) REFERENCES region (region_id)
);
CREATE TABLE batch_processing
(
batch_name VARCHAR(20) PRIMARY KEY,
last_execution_date_time TIMESTAMP
);
CREATE TABLE batch_processing_file
(
batch_processing_file_id BIGSERIAL PRIMARY KEY,
batch_name VARCHAR(20) NOT NULL,
file_name VARCHAR(300) NOT NULL
);
- 20_insert_sample_data.sql
-- REGION
INSERT INTO region (region_name, creation_timestamp)
VALUES ('서울', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('강릉', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('대전', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('광주', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('대구', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('부산', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('여수', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('안동', current_timestamp);
INSERT INTO region (region_name, creation_timestamp)
VALUES ('제주도', current_timestamp);
-- LOCATION
INSERT INTO location (location_name, region_id, note)
VALUES ('테디베어 뮤지엄', (SELECT region_id FROM region WHERE region_name = '제주도'),
'테디베어의 역사는 물론 예술, 세계여행 등의 테마를 제공하는 테마 뮤지엄 브랜드입니다.');
INSERT INTO location (location_name, region_id, note)
VALUES ('성산 일출봉', (SELECT region_id FROM region WHERE region_name = '제주도'),
'유네스코 세계자연유산에 등재된 제주도의 랜드마크.');
-- BATCH_PROCESSING
INSERT INTO batch_processing (batch_name)
values ('SAMPLE_APP_BATCH');
실습 종료후 클라우드 포메이션 스텍을 생성한 역순으로 순차적으로 삭제해준다.