Amazon Relational Database Operations: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(25 intermediate revisions by the same user not shown)
Line 2: Line 2:


* [[Amazon Relational Database Service#Subjects|Amazon Relational Database Service]]
* [[Amazon Relational Database Service#Subjects|Amazon Relational Database Service]]
* [[PostgreSQL Operations]]


=Create a Database Instance with CloudFormation=
=Listing Database Clusters=
==AWS::RDS::DBInstance==
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==
{{External|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_Concepts#Master_User|PostgreSQL master user]].
 
Connectivity
 
Virtual private cloud (VPC)
 
Database authentication: Password authentication.
 
==Create a Database Instance with CloudFormation==
===AWS::RDS::DBInstance===
{{External|[https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html AWS::RDS::DBInstance]}}
{{External|[https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html AWS::RDS::DBInstance]}}


  Resources:
  Resources:
   RelationalDatabase:  
   PostgresDBInstance:  
   Type: AWS::RDS::DBInstance
   Type: AWS::RDS::DBInstance
   [[AWS_CloudFormation_Concepts#DeletionPolicy|DeletionPolicy]]: Delete
   [[AWS_CloudFormation_Concepts#DeletionPolicy|DeletionPolicy]]: Delete
Line 20: Line 46:
     [[Amazon_Relational_Database_Concepts#Master_Username|MasterUsername]]: bn73hewc
     [[Amazon_Relational_Database_Concepts#Master_Username|MasterUsername]]: bn73hewc
     MasterUserPassword: ce75h9w0
     MasterUserPassword: ce75h9w0
    #
    # This is what configures the DB instance to be deployed in a specific VPC. See [[Amazon_Relational_Database_Concepts#VPC_Association|VPC Association]].
    #
    DBSubnetGroupName: !Ref PostgresSubnetGroup
    VPCSecurityGroups:
      - !Ref PostgresSecurityGroup
     Tags:  
     Tags:  
       - Key: Name
       - Key: Name
         Value: !Sub ${EnvironmentName}-${MicroworldName}-postgres
         Value: !Sub ${EnvironmentName}-${MicroworldName}-postgres


===Return Values===
For details on DBSubnetGroupName and VPCSecurityGroups, see: {{Internal|Deploying a RDS Instance into a Specific VPC|Deploying a RDS Instance into a Specific VPC}}
 
====Return Values====


====Ref====
=====Ref=====


Returns [[Amazon_Relational_Database_Concepts#Instance_Identifier|instance identifier]] (DBInstanceIdentifier).
Returns [[Amazon_Relational_Database_Concepts#Instance_Identifier|instance identifier]] (DBInstanceIdentifier).
====GetAtt====
=====GetAtt=====


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


==AWS::RDS::DBSubnetGroup==
====Examples====
 
=====PostgreSQL=====
<syntaxhighlight lang='yaml'>
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
</syntaxhighlight>
 
=====SQL Server=====
<syntaxhighlight lang='yaml'>
</syntaxhighlight>
 
===AWS::RDS::DBSubnetGroup===


{{External|[https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-dbsubnet-group.html AWS::RDS::DBSubnetGroup]}}
{{External|[https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-dbsubnet-group.html AWS::RDS::DBSubnetGroup]}}


  Resources:
  Resources:
   PortgreSQLSubnetGroup:
   PostgresSubnetGroup:
     Type: AWS::RDS::DBSubnetGroup
     Type: AWS::RDS::DBSubnetGroup
      Properties:  
    Properties:
        DBSubnetGroupDescription: A subnet group used by the environment's PostgreSQL instance
      DBSubnetGroupDescription: !Sub The subnet group used by the ${MicroworldName}/${EnvironmentName} environment PostgreSQL instance
        DBSubnetGroupName: some-subnet-group
      DBSubnetGroupName: !Sub ${MicroworldName}-${EnvironmentName}-postgres-subnet-group
        SubnetIds:
      SubnetIds:
          - !Ref PortgreSQLSubnet
        - !Ref PostgresSubnet1
         Tags:
         - !Ref PostgresSubnet2
          - Key: Name
      Tags:
              Value: some-subnet-group
        - 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 <code>aws rds describe-db-clusters</code> 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|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 <code>aws rds describe-db-clusters</code> as value of DBClusterMembers.DBInstanceIdentifier


=Deploying a RDS Instance into a Specific VPC=
=Deploying a RDS Instance into a Specific VPC=


{{Internal|Deploying a RDS Instance into a Specific VPC|Deploying a RDS Instance into a Specific VPC}}
{{Internal|Deploying a RDS Instance into a Specific VPC|Deploying a RDS Instance into a Specific VPC}}
=PostgreSQL Operations=
{{External|https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html}}
<syntaxhighlight lang='bash'>
psql --host=test-rds-01.asxqzuh7ivbrb.us-east-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=postgres
</syntaxhighlight>
More details: {{Internal|PostgreSQL_Operations|PostgreSQL Operations}}

Latest revision as of 00:52, 4 May 2023

Internal

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
SQL Server

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