CDK Entest

Setup#

Let install postgresqlfor Amazon Linux 2023

sudo dnf update
sudo dnf install postgresql15.x86_64 postgresql15-server
sudo yum install postgresql15-contrib.x86_64

Command to check installed package and find package

yum search all postgresql15-contrib
rpm -qa | grep postgresql15-contrib
yum list installed | grep postgres

Basic Query#

Clear screen

\! clear

List database

\list;

List table

\dt;

List user

\du

Check current user

\conninfo

Check where files are stored

SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;

Generate Data#

psql -h localhost -P 5432 -U demo -d dvdrental

Check the generate_series function.

psql select * from generate_series(1, 10);

Create a student table

CREATE TABLE if not EXISTS student (
  id SERIAL PRIMARY KEY,
  name TEXT,
  login TEXT,
  age INT,
  gpa REAL
);

Insert a student in to the table

INSERT INTO student(name, login, age, gpa) VALUES('mike','mike@gmail.com',24,4.0);

Insert 1000 students into the table using generate_series

INSERT INTO student(name, login, age, gpa) VALUES('mike','mike@gmail.com',generate_series(1,1000),4.0)

Extension and Pages#

Install pg_freespacemap extension

CREATE EXTENSION 'pg_freespacemap';

Check available space (pages)

SELECT *, round(100 * avail/8192, 2) as "freespace ratio" FROM pg_freespace('student');

Check the pg_freespace table

SELECT * FROM pg_freespace;

Show available extensions

SELECT * FROM pg_extension;

Locate where files of database stored

dvdrental=# select pg_relation_filepath('student');
 pg_relation_filepath
----------------------
 base/16388/16731
(1 row)

Monitor Connection#

Check the max connection configuration.

SELECT * FROM pg_settings WHERE name = 'max_connections';

Edit the max connection from configuration file.

/var/lib/pgsql/{version_number}/data/postgresql.conf

Query current number of connection to database.

SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null;
SELECT count(*) FROM pg_stat_activity WHERE datname is not null;

Count Idle Connection#

Query from pg_stat_activity table
Analyze from Amazon RDS prformance insights and CloudWatch

Let count number of active session.

select count(*) from (select state from pg_stat_activity where state='active')  x;

Let count number of idle session.

select count(*) from (select state from pg_stat_activity where state='idle')  x;

For more information about the pg_stat_activity table

Troubleshooting#

Search package

yum search all postgresql15-contrib

Then install

yum install postgresql15-contrib.x86_64

Search installed package

rpm -qa | grep postgres

Search installed packages

yum list installed | grep postgres

Golang Simple Test#

Let write go script to test connection:

ping connection
simple query
concurrent user and connection

database_connection_test.go

package test

import (
	"database/sql"
	"entest/postgresql/config"
	"fmt"
	_ "github.com/lib/pq"
	"testing"
	"time"
)

func TestPingDatabase(t *testing.T) {
	fmt.Println("test sending connections to database")

	// connection string to database postgresql
	connStr := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s", config.HOST, config.PORT, config.USER, config.DB_NAME, config.PASSWORD)

	// ping database
	db, error := sql.Open("postgres", connStr)

	if error != nil {
		panic(error)
	}

	error = db.Ping()

	if error != nil {
		panic(error)
	}

	fmt.Println("Successfully connected to database")
}

func TestQuerySelectStudent(t *testing.T) {
	fmt.Println("test sending connections to database")

	// connection string to database postgresql
	connStr := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s", config.HOST, config.PORT, config.USER, config.DB_NAME, config.PASSWORD)

	// ping database
	db, error := sql.Open("postgres", connStr)

	if error != nil {
		panic(error)
	}

	// query a table
	rows, error := db.Query("SELECT * FROM student LIMIT 100;")

	if error != nil {
		panic(error)
	}

	// parse rows response
	for rows.Next() {
		var id int
		var name string
		var login string
		var age int
		var gpa float32

		error = rows.Scan(&id, &name, &login, &age, &gpa)

		if error != nil {
			panic(error)
		}

		fmt.Println(id, name, login, age, gpa)
	}
}

func TestConcurrentConnection(t *testing.T) {
	var num_user int = 500
	// create a channel
	channel := make(chan int, num_user)
	// loop to run 10 connections using goroutine
	for i := 0; i < num_user; i++ {
		go func(user int) {
			// connection string to database postgresql
			connStr := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s", config.HOST, config.PORT, config.USER, config.DB_NAME, config.PASSWORD)
			// ping database
			db, error := sql.Open("postgres", connStr)
			if error != nil {
				panic(error)
			}
			// infinite loop
			var count int = 0
			for {
				// query a table
				rows, error := db.Query("SELECT count(*) FROM student;")
				if error != nil {
					panic(error)
				}
				// close db connection
				// defer rows.Close()
				// defer db.Close()
				// parse number of student from query result
				var n_student int
				for rows.Next() {
					rows.Scan(&n_student)
				}
				fmt.Printf("loop: %d, user: %d num student: %d\n", count, user, n_student)
				// sleep few seconds
				time.Sleep(3 * time.Second)
				count += 1
			}
			// send a message to channel
			channel <- 1
		}(i)
	}
	// retrieve values from the channel to wait for go routine completed
	for i := 0; i < num_user; i++ {
		fmt.Println(<-channel)
	}
}

CloudFormation Template#

Network stack.

1-network.yaml

AWSTemplateFormatVersion: "2010-09-09"
#------------------------------------------------------
# Mappings
#------------------------------------------------------
Mappings:
  CidrMappings:
    public-subnet-1:
      CIDR: 10.0.0.0/24
    public-subnet-2:
      CIDR: 10.0.2.0/24
    public-subnet-3:
      CIDR: 10.0.4.0/24
    private-subnet-1:
      CIDR: 10.0.1.0/24
    private-subnet-2:
      CIDR: 10.0.3.0/24
    private-subnet-3:
      CIDR: 10.0.5.0/24
#------------------------------------------------------
# Parameters
#------------------------------------------------------
Parameters:
  CidrBlock:
    Type: String
    Description: CidrBlock
    Default: 10.0.0.0/16
  InternetCidrBlock:
    Type: String
    Description: UserCidrBlock
    Default: 0.0.0.0/0
#------------------------------------------------------
# Resources: VPC, Subnets, NAT, Routes
#------------------------------------------------------
Resources:
  VPC:
    Type: AWS::EC2::VPC
    Metadata:
      cfn_nag:
        rules_to_suppress:
          - id: W60
            reason: VPC flow logs not required for this example
    Properties:
      CidrBlock: !Ref CidrBlock
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-vpc
  #------------------------------------------------------
  # Resources: internet gateway
  #------------------------------------------------------
  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-ig

AttachGateway:
Type: AWS::EC2::VPCGatewayAttachment
Properties:
VpcId: !Ref VPC
InternetGatewayId: !Ref InternetGateway

#------------------------------------------------------

# Resources: public and private subnets

#------------------------------------------------------
PublicSubnet1:
Type: AWS::EC2::Subnet
Properties:
MapPublicIpOnLaunch: false
AvailabilityZone:
Fn::Select: - 0 - Fn::GetAZs:
Ref: AWS::Region
VpcId: !Ref VPC
CidrBlock:
Fn::FindInMap: - CidrMappings - public-subnet-1 - CIDR
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-public-subnet-1

PublicSubnet2:
Type: AWS::EC2::Subnet
Properties:
MapPublicIpOnLaunch: false
AvailabilityZone:
Fn::Select: - 1 - Fn::GetAZs:
Ref: AWS::Region
VpcId: !Ref VPC
CidrBlock:
Fn::FindInMap: - CidrMappings - public-subnet-2 - CIDR
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-public-subnet-2

PublicSubnet3:
Type: AWS::EC2::Subnet
Properties:
MapPublicIpOnLaunch: false
AvailabilityZone:
Fn::Select: - 2 - Fn::GetAZs:
Ref: AWS::Region
VpcId: !Ref VPC
CidrBlock:
Fn::FindInMap: - CidrMappings - public-subnet-3 - CIDR
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-public-subnet-3

PrivateSubnet1:
Type: AWS::EC2::Subnet
Properties:
MapPublicIpOnLaunch: false
AvailabilityZone:
Fn::Select: - 0 - Fn::GetAZs:
Ref: AWS::Region
VpcId: !Ref VPC
CidrBlock:
Fn::FindInMap: - CidrMappings - private-subnet-1 - CIDR
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-private-subnet-1

PrivateSubnet2:
Type: AWS::EC2::Subnet
Properties:
MapPublicIpOnLaunch: false
AvailabilityZone:
Fn::Select: - 1 - Fn::GetAZs:
Ref: AWS::Region
VpcId: !Ref VPC
CidrBlock:
Fn::FindInMap: - CidrMappings - private-subnet-2 - CIDR
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-private-subnet-2

PrivateSubnet3:
Type: AWS::EC2::Subnet
Properties:
MapPublicIpOnLaunch: false
AvailabilityZone:
Fn::Select: - 2 - Fn::GetAZs:
Ref: AWS::Region
VpcId: !Ref VPC
CidrBlock:
Fn::FindInMap: - CidrMappings - private-subnet-3 - CIDR
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-private-subnet-3
#------------------------------------------------------

# Resources: nat gateway

#------------------------------------------------------
NatGatewayEIP:
Type: AWS::EC2::EIP
Properties:
Domain: vpc

NatGateway:
Type: AWS::EC2::NatGateway
Properties:
AllocationId:
Fn::GetAtt: [NatGatewayEIP, AllocationId]
SubnetId: !Ref PublicSubnet1
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-nat-gateway

#------------------------------------------------------

# Resources: public route table

#------------------------------------------------------
PublicRouteTable:
Type: AWS::EC2::RouteTable
Properties:
VpcId: !Ref VPC
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-public-rt

RouteInternetGateway:
Type: AWS::EC2::Route
DependsOn: AttachGateway
Properties:
RouteTableId: !Ref PublicRouteTable
DestinationCidrBlock: !Ref InternetCidrBlock
GatewayId: !Ref InternetGateway

#------------------------------------------------------

# Resources: private route table

#------------------------------------------------------
PrivateRouteTable:
Type: AWS::EC2::RouteTable
Properties:
VpcId: !Ref VPC
Tags: - Key: Name
Value: !Sub ${AWS::StackName}-private-rt

PrivateRoute:
Type: AWS::EC2::Route
Properties:
RouteTableId: !Ref PrivateRouteTable
DestinationCidrBlock: !Ref InternetCidrBlock
NatGatewayId: !Ref NatGateway

#------------------------------------------------------

# Resources: route table subnet associations

#------------------------------------------------------
PublicSubnet1RouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref PublicSubnet1
RouteTableId: !Ref PublicRouteTable

PublicSubnet2RouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref PublicSubnet2
RouteTableId: !Ref PublicRouteTable

PublicSubnet3RouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref PublicSubnet3
RouteTableId: !Ref PublicRouteTable

PrivateSubnet1RouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref PrivateSubnet1
RouteTableId: !Ref PrivateRouteTable

PrivateSubnet2RouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref PrivateSubnet2
RouteTableId: !Ref PrivateRouteTable

PrivateSubnet3RouteTableAssociation:
Type: AWS::EC2::SubnetRouteTableAssociation
Properties:
SubnetId: !Ref PrivateSubnet3
RouteTableId: !Ref PrivateRouteTable
#------------------------------------------------------

# WebServerSecurityGroup:

#------------------------------------------------------
WebServerSecurityGroup:
Type: AWS::EC2::SecurityGroup
Metadata:
cfn_nag:
rules_to_suppress: - id: W5
reason: return traffic to user from internet for testing - id: W40
reason: egress open all port for the internet
Properties:
GroupDescription: web server security group
VpcId: !Ref VPC

WebServerSecurityGroupEgress:
Type: AWS::EC2::SecurityGroupEgress
Properties:
Description: Egress for environment
GroupId: !Ref WebServerSecurityGroup
IpProtocol: -1
FromPort: -1
ToPort: -1
DestinationSecurityGroupId: !Ref DatabaseSecurityGroup

#------------------------------------------------------

# LoadBalancerSecurityGroup:

#------------------------------------------------------
LoadBalancerSecurityGroup:
Type: AWS::EC2::SecurityGroup
Metadata:
cfn_nag:
rules_to_suppress: - id: W5
reason: return traffic to user from internet for testing - id: W40
reason: egress open all port for the internet
Properties:
GroupDescription: load balancer security group
VpcId: !Ref VPC

LoadBalancerSecurityGroupEgress:
Type: AWS::EC2::SecurityGroupEgress
Properties:
Description: Egress for environment
GroupId: !Ref LoadBalancerSecurityGroup
IpProtocol: -1
FromPort: -1
ToPort: -1
DestinationSecurityGroupId: !Ref WebServerSecurityGroup

LoadBalancerSecurityGroupIngress:
Type: AWS::EC2::SecurityGroupIngress
Properties:
Description: open port 80 for web server
GroupId: !Ref LoadBalancerSecurityGroup
IpProtocol: tcp
FromPort: 80
ToPort: 80
CidrIp: !Ref InternetCidrBlock

#------------------------------------------------------

# DatabaseSecurityGroupIngress:

#------------------------------------------------------

# DatabaseSecurityGroup:

#------------------------------------------------------
DatabaseSecurityGroup:
Type: AWS::EC2::SecurityGroup
Metadata:
cfn_nag:
rules_to_suppress: - id: W5
reason: return traffic to user from internet for testing - id: W40
reason: egress open all port for the internet
Properties:
GroupDescription: database security group
VpcId: !Ref VPC

DatabaseSecurityGroupPostgreSQLIngress:
Type: AWS::EC2::SecurityGroupIngress
Properties:
Description: open port 5432 for web server
GroupId: !Ref DatabaseSecurityGroup
IpProtocol: tcp
FromPort: 5432
ToPort: 5432
SourceSecurityGroupId: !Ref WebServerSecurityGroup
SourceSecurityGroupOwnerId: !Ref AWS::AccountId

DatabaseSecurityGroupMySQLIngress:
Type: AWS::EC2::SecurityGroupIngress
Properties:
Description: open port 3306 for web server
GroupId: !Ref DatabaseSecurityGroup
IpProtocol: tcp
FromPort: 3306
ToPort: 3306
SourceSecurityGroupId: !Ref WebServerSecurityGroup
SourceSecurityGroupOwnerId: !Ref AWS::AccountId

DatabaseSecurityGroupEgress:
Type: AWS::EC2::SecurityGroupEgress
Properties:
Description: Egress for environment
GroupId: !Ref DatabaseSecurityGroup
IpProtocol: -1
FromPort: -1
ToPort: -1
CidrIp: !Ref InternetCidrBlock
#------------------------------------------------------

# Export:

#------------------------------------------------------
Outputs:
VPC:
Value: !Ref VPC
Export:
Name: !Sub ${AWS::StackName}-vpc
PublicSubnet1:
Value: !Ref PublicSubnet1
Export:
Name: !Sub ${AWS::StackName}-public-subnet-1
PublicSubnet2:
Value: !Ref PublicSubnet2
Export:
Name: !Sub ${AWS::StackName}-public-subnet-2
PublicSubnet3:
Value: !Ref PublicSubnet3
Export:
Name: !Sub ${AWS::StackName}-public-subnet-3
PrivateSubnet1:
Value: !Ref PrivateSubnet2
Export:
Name: !Sub ${AWS::StackName}-private-subnet-1
PrivateSubnet2:
Value: !Ref PrivateSubnet2
Export:
Name: !Sub ${AWS::StackName}-private-subnet-2
PrivateSubnet3:
Value: !Ref PrivateSubnet3
Export:
Name: !Sub ${AWS::StackName}-private-subnet-3
PrivateRouteTable:
Value: !Ref PrivateRouteTable
Export:
Name: !Sub ${AWS::StackName}-private-route-table
InternetGateway:
Value: !Ref InternetGateway
Export:
Name: !Sub ${AWS::StackName}-igw
DatabaseSecurityGroup:
Value: !Ref DatabaseSecurityGroup
Export:
Name: !Sub ${AWS::StackName}-database-security-group
WebServerSecurityGroup:
Value: !Ref WebServerSecurityGroup
Export:
Name: !Sub ${AWS::StackName}-web-server-security-group

RDS database instance stack.

2-rds-instance-postgresql.yaml

AWSTemplateFormatVersion: 2010-09-09
Description: AWS CloudFormation Sample Template for creating a Aurora PostgreSQL DB cluster.
Parameters:
  NetworkStackName:
    Description: Stack name of the network stack
    Type: String
    Default: network-stack
  DBName:
    Default: demo
    Description: My database
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: Must begin with a letter and contain only alphanumeric characters.
  MasterUsername:
    NoEcho: 'true'
    Description: Username for database access
    Type: String
    MinLength: '1'
    MaxLength: '16'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: Must begin with a letter and contain only alphanumeric characters.
  MasterUserPassword:
    NoEcho: 'true'
    Description: Password for database access
    Type: String
    MinLength: '8'
    MaxLength: '41'
    AllowedPattern: '[a-zA-Z0-9]*'
    ConstraintDescription: Must contain only alphanumeric characters.
  DBClusterIdentifier:
    Type: String
    Default: 'aurora-cluster-postgresql'
    Description: The DB cluster identifier
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: '[a-zA-Z0-9-]*'
    ConstraintDescription: Must contain only alphanumeric characters and hyphens.
  DBClusterInstanceClass:
    Default: db.r5.large
    Description: DB instance class
    Type: String
    ConstraintDescription: Must select a valid DB instance type.
    AllowedValues:
      - db.r5.large
  DBInstanceClass:
    Default: db.r5.large
    Description: DB instance class
    Type: String
    ConstraintDescription: Must select a valid DB instance type.
    AllowedValues:
      - db.r5.large
  Engine:
    Default: "aurora-postgresql"
    Type: String
    AllowedValues:
      - aurora-mysql
      - aurora-postgresql
  EngineVersion:
    Default: "16.1"
    Type: String
    Description: Aurora MySQL engine version
    AllowedValues:
      - "16.1"
      - "16.2"
      - "16.3"
      - "16.4"
Resources:
  #------------------------------------------------------
  # iam role for rds push logs to cloudwatch:
  #------------------------------------------------------
  MonitoringRole:
    Type: 'AWS::IAM::Role'
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - monitoring.rds.amazonaws.com
            Action:
              - sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole
  #------------------------------------------------------
  # database subnet group:
  #------------------------------------------------------
  DatabaseSubnetGroup:
    Type: 'AWS::RDS::DBSubnetGroup'
    Properties:
      DBSubnetGroupDescription: Subnet group for database instance
      SubnetIds:
        - Fn::ImportValue: !Sub ${NetworkStackName}-public-subnet-1
        - Fn::ImportValue: !Sub ${NetworkStackName}-public-subnet-2
        - Fn::ImportValue: !Sub ${NetworkStackName}-public-subnet-3
  #------------------------------------------------------
  # aurora cluster parameter group
  #------------------------------------------------------
  RDSDBClusterParameterGroup:
    Type: 'AWS::RDS::DBClusterParameterGroup'
    Properties:
      Description: CloudFormation Sample Aurora Cluster Parameter Group
      Family: aurora-postgresql16
      Parameters:
        timezone: UTC
        max_locks_per_transaction: 64
  #------------------------------------------------------
  # aurora parameter group
  #------------------------------------------------------
  RDSDBParameterGroup:
    Type: 'AWS::RDS::DBParameterGroup'
    Properties:
      Description: CloudFormation Sample Aurora Parameter Group
      Family: aurora-postgresql16
      Parameters:
        log_timezone: UTC
        max_stack_depth: 6144
  #------------------------------------------------------
  # aurora cluster
  #------------------------------------------------------
  RDSCluster:
    Type: 'AWS::RDS::DBCluster'
    Metadata:
      cfn_nag:
        rules_to_suppress:
          - id: F26
            reason: disable encryption for demo purpose
          - id: F27
            reason: disable encryption for demo purpose
          - id: F80
            reason: disable delete protection for demo purpose
          - id: F22
            reason: enable public access for demo purpose
    Properties:
      BackupRetentionPeriod: 7
      DBSubnetGroupName: !Ref DatabaseSubnetGroup
      DeletionProtection: false
      DBClusterParameterGroupName: !Ref RDSDBClusterParameterGroup
      DBClusterIdentifier: !Ref DBClusterIdentifier
      DatabaseName: !Ref DBName
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterUserPassword
      Engine: !Ref Engine
      EngineMode: "provisioned"
      EngineVersion: !Ref EngineVersion
      EnableCloudwatchLogsExports:
        - postgresql
      Port: 5432
      VpcSecurityGroupIds:
        - Fn::ImportValue: !Sub ${NetworkStackName}-database-security-group
      StorageEncrypted: false

#------------------------------------------------------

# aurora instance 1

#------------------------------------------------------
RDSDBInstance1:
Type: 'AWS::RDS::DBInstance'
Metadata:
cfn_nag:
rules_to_suppress: - id: F26
reason: disable encryption for demo purpose - id: F27
reason: disable encryption for demo purpose - id: F80
reason: disable delete protection for demo purpose - id: F22
reason: enable public access for demo purpose
Properties:
DBParameterGroupName: !Ref RDSDBParameterGroup
DBInstanceClass: !Ref DBInstanceClass
DBClusterIdentifier: !Ref RDSCluster
DBInstanceIdentifier: !Sub ${DBClusterIdentifier}-instance-1
DBSubnetGroupName: !Ref DatabaseSubnetGroup
EnablePerformanceInsights: true
Engine: !Ref Engine
PubliclyAccessible: true
PerformanceInsightsRetentionPeriod: 7
StorageEncrypted: false
MonitoringInterval: 60
MonitoringRoleArn: !GetAtt [MonitoringRole, Arn] # EngineLifecycleSupport: "open-source-rds-extended-support"
#------------------------------------------------------

# exported output:

#------------------------------------------------------
Outputs:
DatabaseArn:
Description: Database Cluster ARN
Value: !GetAtt RDSCluster.DBClusterArn
Export:
Name: !Sub ${AWS::StackName}-database-cluster-arn
DatabasePort:
Description: Database port
Value: !GetAtt RDSCluster.Endpoint.Port
Export:
Name: !Sub ${AWS::StackName}-database-port
DatabaseEndpointAddress:
Description: Database endpoint address
Value: !GetAtt RDSCluster.Endpoint.Address
Export:
Name: !Sub ${AWS::StackName}-database-cluster-endpoint-address

RDS Aurora cluster stack.

2-aurora-cluster-postgresql.yaml

AWSTemplateFormatVersion: 2010-09-09
Description:
  Create a database parameter group
  Create a database subnet group
  Create a database instance PostgreSQL
Parameters:
  NetworkStackName:
    Description: Stack name of the network stack
    Type: String
    Default: network-stack
  DBEngine:
    Default: "16.1"
    Description: PostgresQL
    Type: String
    AllowedValues:
      - "14.10"
      - "14.11"
      - "14.12"
      - "14.13"
      - "15.4"
      - "15.5"
      - "15.6"
      - "15.7"
      - "15.8"
      - "16.1"
      - "16.2"
      - "16.3"
      - "16.4"
  ParameterGroupFamily:
    Default: postgres16
    Description: Parameter group family
    Type: String
    AllowedValues:
      - postgres14
      - postgres15
      - postgres16
  DBInstanceID:
    Default: DemoVector
    Description: My database instance
    Type: String
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: >-
      Must begin with a letter and must not end with a hyphen or contain two
      consecutive hyphens.
  DBName:
    Default: DemoVector
    Description: My database
    Type: String
    MinLength: '1'
    MaxLength: '64'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: Must begin with a letter and contain only alphanumeric characters.
  DBInstanceClass:
    Default: db.m5.large
    Description: DB instance class
    Type: String
    ConstraintDescription: Must select a valid DB instance type.
    AllowedValues:
      - "db.m5.large"
      - "db.m5.xlarge"
      - "db.m5.2xlarge"
      - "db.m5.4xlarge"
      - "db.m5.8xlarge"
  DBAllocatedStorage:
    Default: '64'
    Description: The size of the database (GiB)
    Type: Number
    MinValue: '20'
    MaxValue: '65536'
    ConstraintDescription: must be between 20 and 65536 GiB.
  DBUsername:
    NoEcho: 'true'
    Description: Username for MySQL database access
    Type: String
    MinLength: '1'
    MaxLength: '16'
    AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*'
    ConstraintDescription: must begin with a letter and contain only alphanumeric characters.
  DBPassword:
    NoEcho: 'true'
    Description: Password MySQL database access
    Type: String
    MinLength: '8'
    MaxLength: '41'
    AllowedPattern: '[a-zA-Z0-9]*'
    ConstraintDescription: must contain only alphanumeric characters.

Resources:
  #------------------------------------------------------
  # database subnet group:
  #------------------------------------------------------
  DatabaseSubnetGroup:
    Type: 'AWS::RDS::DBSubnetGroup'
    Properties:
      DBSubnetGroupDescription: Subnet group for database instance
      SubnetIds:
        - Fn::ImportValue: !Sub ${NetworkStackName}-public-subnet-1
        - Fn::ImportValue: !Sub ${NetworkStackName}-public-subnet-2
  #------------------------------------------------------
  # database instance parameter group
  #------------------------------------------------------
  RDSDBParameterGroup:
    Type: 'AWS::RDS::DBParameterGroup'
    Properties:
      Description: database parameter group
      Family: !Ref ParameterGroupFamily
      Parameters:
        archive_timeout: 300
  #------------------------------------------------------
  # iam role for rds push logs to cloudwatch:
  #------------------------------------------------------
  MonitoringRole:
    Type: 'AWS::IAM::Role'
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - monitoring.rds.amazonaws.com
            Action:
              - sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole

  #------------------------------------------------------
  # database rds instance:
  #------------------------------------------------------
  RDSDatabaseInstance:
    Type: 'AWS::RDS::DBInstance'
    Metadata:
      cfn_nag:
        rules_to_suppress:
          - id: F27
            reason: disable encryption for demo purpose
          - id: F80
            reason: disable delete protection for demo purpose
          - id: F22
            reason: enable public access for demo purpose
    Properties:
      DBInstanceIdentifier: !Ref DBInstanceID
      DBName: !Ref DBName
      DBInstanceClass: !Ref DBInstanceClass
      DBParameterGroupName: !Ref RDSDBParameterGroup
      AllocatedStorage: !Ref DBAllocatedStorage
      Engine: postgres
      EngineVersion: !Ref DBEngine
      MasterUsername: !Ref DBUsername
      MasterUserPassword: !Ref DBPassword
      EnablePerformanceInsights: true
      PerformanceInsightsRetentionPeriod: 7
      MultiAZ: false
      DBSubnetGroupName: !Ref DatabaseSubnetGroup
      PubliclyAccessible: true
      EnableCloudwatchLogsExports:
        - postgresql
        - upgrade
      MonitoringInterval: 60
      MonitoringRoleArn: !GetAtt [MonitoringRole, Arn]
      BackupRetentionPeriod: 1
      AutoMinorVersionUpgrade: false
      DeletionProtection: false
      StorageType: "gp3"
      StorageEncrypted: false
      # EngineLifecycleSupport: "open-source-rds-extended-support"
      VPCSecurityGroups:
        - Fn::ImportValue: !Sub ${NetworkStackName}-database-security-group
  #------------------------------------------------------
  # exported output:
  #------------------------------------------------------
Outputs:
  DatabaseArn:
    Description: Database ARN
    Value: !GetAtt RDSDatabaseInstance.DBInstanceArn
    Export:
      Name: !Sub ${AWS::StackName}-database-arn
  DatabasePort:
    Description: Database port
    Value: !GetAtt RDSDatabaseInstance.Endpoint.Port
    Export:
      Name: !Sub ${AWS::StackName}-database-port
  DatabaseEndpointAddress:
    Description: Database endpoint address
    Value: !GetAtt RDSDatabaseInstance.Endpoint.Address
    Export:
      Name: !Sub ${AWS::StackName}-database-endpoint-address

Deploy script.

deploy.sh

# create network stack
aws cloudformation create-stack \
 --stack-name network-stack \
 --template-body file://1-network.yaml \
 --capabilities CAPABILITY_NAMED_IAM

# update network stack
aws cloudformation update-stack \
 --stack-name network-stack \
 --template-body file://1-network.yaml \
 --capabilities CAPABILITY_NAMED_IAM

aws cloudformation update-stack \
 --stack-name network-database \
 --template-body file://1-network-database.yaml \
 --capabilities CAPABILITY_NAMED_IAM

# create rds mysql instance
aws cloudformation create-stack \
 --stack-name rds-instance-postgresql-stack \
 --template-body file://2-rds-instance-postgresql.yaml \
 --capabilities CAPABILITY_NAMED_IAM \
 --parameters '[{"ParameterKey":"NetworkStackName","ParameterValue":"network-database"},{"ParameterKey":"DBUsername","ParameterValue":"demo"},{"ParameterKey":"DBPassword","ParameterValue":"Password"}]'


# create aurora postgresql cluster
aws cloudformation create-stack \
 --stack-name aurora-cluster-postgresql-stack \
 --template-body file://42-aurora-cluster-postgresql.yaml \
 --capabilities CAPABILITY_NAMED_IAM \
 --parameters '[{"ParameterKey":"NetworkStackName","ParameterValue":"network-database"},{"ParameterKey":"MasterUsername","ParameterValue":"demo"},{"ParameterKey":"MasterUserPassword","ParameterValue":"Demo@2024"}]'

Reference#

postgresql install linux guide