Amazon Relational Database Operations

Internal

• Amazon Relational Database Service
• PostgreSQL Operations

Listing Database Clusters

aws rds describe-db-clusters --no-paginate | jq '.DBClusters[].DBClusterIdentifier'

List Database Instances

aws rds describe-db-instances --no-paginate  | jq '.DBInstances[].DBInstanceIdentifier'

Create a Database

From AWS Console

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.PostgreSQL.html

AWS Console → RDS → Create Database → Standard Create (Easy Create does not let choosing the VPC)

PostgreSQL

Templates: Free tier

DB instance identifier: test-db (this is not an individual database name, and it will show up in the DNS name of the database)

Master username. More details about PostgreSQL master user.

Connectivity

Virtual private cloud (VPC)

Database authentication: Password authentication.

Create a Database Instance with CloudFormation

AWS::RDS::DBInstance

AWS::RDS::DBInstance

Resources:
  PostgresDBInstance: 
  Type: AWS::RDS::DBInstance
  DeletionPolicy: Delete
  Properties: 
    DBName: themyscira01
    DBInstanceIdentifier: !Sub ${EnvironmentName}-${MicroworldName}-postgres
    AllocatedStorage: 8
    DBInstanceClass: db.t2.micro
    Engine: postgres
    EngineVersion: 9.4.20
    MasterUsername: bn73hewc
    MasterUserPassword: ce75h9w0
    #
    # This is what configures the DB instance to be deployed in a specific VPC. See VPC Association.
    #
    DBSubnetGroupName: !Ref PostgresSubnetGroup
    VPCSecurityGroups:
      - !Ref PostgresSecurityGroup
    Tags: 
      - Key: Name
        Value: !Sub ${EnvironmentName}-${MicroworldName}-postgres

For details on DBSubnetGroupName and VPCSecurityGroups, see:

Deploying a RDS Instance into a Specific VPC

Return Values

Ref

Returns instance identifier (DBInstanceIdentifier).

GetAtt

• Endpoint.Address returns the connection endpoint for the database (e.g. mystack-mydb-1apw1j4phylrk.cg034hpkmmjt.us-east-2.rds.amazonaws.com)
• Endpoint.Port

Examples

PostgreSQL

Resources
  PostgresDBInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      #
      # We create just the first database of the environment, if we need more, we need to create them
      # interactively, as CloudFormation does not support this
      #
      DBName: !Ref FirstPostgresDatabaseName
      DBInstanceIdentifier: !Sub ${EnvironmentName}-${MicroworldName}-postgres
      AllocatedStorage: 8
      DBInstanceClass: db.t2.micro
      Engine: postgres
      EngineVersion: 9.4.20
      MasterUsername: !Ref PostgreSQLUsername
      MasterUserPassword: !Ref PostgreSQLPassword
      DBSubnetGroupName: !Ref PostgresSubnetGroup
      VPCSecurityGroups:
        - !Ref PostgresSecurityGroup
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${MicroworldName}-postgres

AWS::RDS::DBSubnetGroup

AWS::RDS::DBSubnetGroup

Resources:
  PostgresSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: !Sub The subnet group used by the ${MicroworldName}/${EnvironmentName} environment PostgreSQL instance
      DBSubnetGroupName: !Sub ${MicroworldName}-${EnvironmentName}-postgres-subnet-group
      SubnetIds:
        - !Ref PostgresSubnet1
        - !Ref PostgresSubnet2
      Tags:
        - Key: Name
          Value: !Sub ${MicroworldName}-${EnvironmentName}-postgres-subnet-group

Delete a Database Cluster

aws rds delete-db-cluster --db-cluster-identifier <db-cluster-identifier> --skip-final-snapshot

where <db-cluster-identifier> is the value of the DBClusterIdentifier from the aws rds describe-db-clusters operations.

The cluster cannot be deleted if it still contains database instances in non-deleting state. The database instances must be deleted individually as shown below in Delete a Database Instance.

Upon a successful deletion attempt, the cluster may spend a while in "Deleting" state.

Delete a Database Instance

aws rds delete-db-cluster --db-instance-identifier <db-instance-identifier> 

<db-instance-identifier> can be pulled from aws rds describe-db-clusters as value of DBClusterMembers.DBInstanceIdentifier

Deploying a RDS Instance into a Specific VPC

Deploying a RDS Instance into a Specific VPC

PostgreSQL Operations

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html

psql --host=test-rds-01.asxqzuh7ivbrb.us-east-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=postgres

More details:

PostgreSQL Operations