Hello! We are a writer team from Definer Inc. In this issue, you are wondering how to set up a PostgreSQL database with RDS. Let's take a look at the actual screens and resources to explain in detail.

AWS RDS is suitable for various use cases, including web applications, mobile applications, e-commerce platforms, content management systems, and enterprise applications. It provides a reliable, scalable, and secure solution for hosting and managing your databases in the cloud. This article summarizes information and practices that can be helpful when you want to use a PostgreSQL database in AWS.

First, let's review Amazon RDS.   AWS RDS (Amazon Relational Database Service) is a fully managed database service provided by Amazon Web Services (AWS). It simplifies the process of setting up, operating, and scaling a relational database in the cloud. RDS supports popular relational database engines such as Amazon Aurora, MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server. You can start using the database immediately, with no database installation or backup settings required. Relational databases are represented by two axes, rows and columns, based on which data is searched and edited. In layman's terms, it is like a sophisticated Excel program. Relational databases are suitable for information consistency and management efficiency, and are used to manage many types of data, such as deposit/withdrawal data and employee lists.   Here are some key features and benefits of AWS RDS: - Managed Service: With RDS, AWS takes care of time-consuming database administration tasks, including database setup, patching, backups, and automatic software updates. This allows you to focus more on your application development rather than managing the underlying infrastructure. - Multiple Database Engine Options: RDS offers support for various relational database engines, providing flexibility and enabling you to choose the engine that best suits your application's requirements. You can select from Amazon Aurora (AWS's proprietary high-performance database engine), MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server. - Automated Backups and Point-in-Time Recovery: RDS automatically performs regular backups of your database, allowing you to restore your data to a specific point in time within the retention period. - Scalability and Performance: RDS enables you to scale your database resources up or down based on demand, allowing you to handle increasing workloads or adjust to changing requirements. - High Availability and Replication: RDS supports various high availability configurations to enhance database resilience. RDS also provides replication options for other database engines, such as read replicas and Multi-AZ deployments. - Security and Compliance: RDS incorporates security best practices and includes features like encryption at rest and in transit, network isolation using Amazon VPC, database access control using IAM roles and database users, and integration with AWS Identity and Access Management (IAM). - Monitoring and Metrics: RDS provides built-in monitoring and metrics through Amazon CloudWatch, allowing you to track database performance, storage usage, and other important metrics. - Integration with AWS Services: RDS integrates seamlessly with other AWS services, enabling you to build scalable and robust architectures. For example, you can integrate RDS with AWS Lambda for serverless data processing, Amazon S3 for data backup and export, or Amazon Redshift for data warehousing.   We will also briefly review PostgreSQL:   PostgreSQL is an open source (OSS) database, characterized by its high scalability and rich functionality. It is free to use and is available for Linux and Mac as well as Windows. It has a very flexible license, so there is no obligation to publish your own customizations. For this reason, it is used as a DB for many web systems and applications.

Let's get started and set up a PostgreSQL database using RDS. This time, we will create the RDS by executing commands in the AWS CLI.   (1) Creating DB subnet group First, we will create the DB subnet group necessary for RDS creation. Execute the following command, and if Json is returned, the creation was successful. Two or more subnets must be specified.   (2) Creating an RDS instance Next, use the following command to create an RDS instance. This time, we created an RDS instance with engine "postgreSQL 13.6" and instance type "db.t3.micro". The default is SingleAZ with no public access. Change the user name and password accordingly. If the command execution is successful, Json will be returned.   PostgreSQL is now up and running on RDS.

## Creation of DB subnet group
aws rds create-db-subnet-group \
    --db-subnet-group-name subg-test \
    --db-subnet-group-description test \
    --subnet-ids subnet-xxxxxxxx subnet-yyyyyyy


## Creating RDS
aws rds create-db-instance \
    --db-instance-identifier postgre-test \
    --db-instance-class db.t3.micro \
    --engine postgres \
    --engine-version 13.6 \
    --allocated-storage 20 \
    --master-username root \
    --master-user-password password \
    --backup-retention-period 3 \
    --vpc-security-group-ids sg-xxxxxx \
    --db-subnet-group-name subg-test

## Retrieve endpoint (hostname) that you can use to connect to your PostgreSQL instance
aws rds describe-db-instances --db-instance-identifier <instance-identifier> --query 'DBInstances[0].Endpoint.Address'
                

 

Json returned on successful RDS creation command

{
    "DBInstance": {
        "DBInstanceIdentifier": "postgre-test",
        "DBInstanceClass": "db.t3.micro",
        "Engine": "postgres",
        "DBInstanceStatus": "creating",
        "MasterUsername": "root",
        "AllocatedStorage": 20,
        "PreferredBackupWindow": "13:42-14:12",
        "BackupRetentionPeriod": 3,
        "DBSecurityGroups": [],
        "VpcSecurityGroups": [
            {
                "VpcSecurityGroupId": "sg-xxxxx",
                "Status": "active"
            }
        ],
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "default.postgres13",
                "ParameterApplyStatus": "in-sync"
            }
        ],
        "DBSubnetGroup": {
            "DBSubnetGroupName": "subg-test",
            "DBSubnetGroupDescription": "test",
            "VpcId": "vpc-xxxxxx",
            "SubnetGroupStatus": "Complete",
            "Subnets": [
                {
                    "SubnetIdentifier": "subnet-xxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1a"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                },
                {
                    "SubnetIdentifier": "subnet-xxxxx",
                    "SubnetAvailabilityZone": {
                        "Name": "ap-northeast-1c"
                    },
                    "SubnetOutpost": {},
                    "SubnetStatus": "Active"
                }
            ]
        },
        "PreferredMaintenanceWindow": "mon:14:43-mon:15:13",
        "PendingModifiedValues": {
            "MasterUserPassword": "****"
        },
        "MultiAZ": false,
        "EngineVersion": "13.6",
        "AutoMinorVersionUpgrade": true,
        "ReadReplicaDBInstanceIdentifiers": [],
        "LicenseModel": "postgresql-license",
        "OptionGroupMemberships": [
            {
                "OptionGroupName": "default:postgres-13",
                "Status": "in-sync"
            }
        ],
        "PubliclyAccessible": false,
        "StorageType": "gp2",
        "DbInstancePort": 0,
        "StorageEncrypted": false,
        "DbiResourceId": "db-FxxxxxxxQ",
        "CACertificateIdentifier": "rds-ca-2019",
        "DomainMemberships": [],
        "CopyTagsToSnapshot": false,
        "MonitoringInterval": 0,
        "DBInstanceArn": "arn:aws:rds:ap-northeast-1:xxxxxxxxx:db:postgre-test",
        "IAMDatabaseAuthenticationEnabled": false,
        "PerformanceInsightsEnabled": false,
        "DeletionProtection": false,
        "AssociatedRoles": [],
        "TagList": [],
        "CustomerOwnedIpEnabled": false,
        "BackupTarget": "region",
        "NetworkType": "IPV4"
    }
}                  

High CPU usage of Aurora for Amazon RDS or PostgreSQL ... Frequently Asked Questions - Amazon RDS | AWS How do I use Amazon RDS for PostgreSQL cross-region read replica ... PostgreSQL on Amazon RDS - Amazon Relational Database ... How can I use Amazon Aurora with PostgreSQL Compatibility in the Tokyo Region ...

・PrismScaler is a web service that enables the construction of multi-cloud infrastructures such as AWS, Azure, and GCP in just three steps, without requiring development and operation. ・PrismScaler is a web service that enables multi-cloud infrastructure construction such as AWS, Azure, GCP, etc. in just 3 steps without development and operation. ・The solution is designed for a wide range of usage scenarios such as cloud infrastructure construction/cloud migration, cloud maintenance and operation, and cost optimization, and can easily realize more than several hundred high-quality general-purpose cloud infrastructures by appropriately combining IaaS and PaaS.  

This article provides useful introductory information free of charge. For consultation and inquiries, please contact "Definer Inc".

・Definer Inc. provides one-stop solutions from upstream to downstream of IT. ・We are committed to providing integrated support for advanced IT technologies such as AI and cloud IT infrastructure, from consulting to requirement definition/design development/implementation, and maintenance and operation. ・We are committed to providing integrated support for advanced IT technologies such as AI and cloud IT infrastructure, from consulting to requirement definition, design development, implementation, maintenance, and operation. ・PrismScaler is a high-quality, rapid, "auto-configuration," "auto-monitoring," "problem detection," and "configuration visualization" for multi-cloud/IT infrastructure such as AWS, Azure, and GCP.