Amazon Relational Database Operations

Internal

• Amazon Relational Database Service
• PostgreSQL Operations

Create a Database

From AWS Console

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

DB instance identifier: test-db

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

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