Skip to content

Instantly share code, notes, and snippets.

@decagondev

decagondev/aws-rds-provisioning.md

Created July 30, 2025 17:33
Show Gist options
  • Save decagondev/f8773b53299410a5bbe3f2efe2194b01 to your computer and use it in GitHub Desktop.
Save decagondev/f8773b53299410a5bbe3f2efe2194b01 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
aws-rds-provisioning.md

AWS RDS Instance Provisioning Guide

This guide provides step-by-step instructions to provision an Amazon RDS instance in AWS for MySQL or PostgreSQL, configure it, and prepare it for deploying applications.

Prerequisites

  • An AWS account with appropriate IAM permissions (e.g., AmazonRDSFullAccess).
  • Basic knowledge of AWS Management Console, VPC, and database concepts.
  • AWS CLI installed and configured (optional for CLI-based setup).

Step 1: Log in to AWS Management Console

  1. Navigate to the AWS Management Console.
  2. Sign in with your AWS account credentials.

Step 2: Navigate to RDS Service

  1. In the AWS Console, search for RDS in the search bar or locate it under Services > Database > RDS.
  2. Click RDS to access the Relational Database Service dashboard.

Step 3: Create an RDS Instance

  1. In the RDS dashboard, click Create database.
  2. Choose the Standard create option for full configuration control.
  3. Select the database engine:
    • MySQL: For a widely-used, general-purpose relational database.
    • PostgreSQL: For advanced features like JSON support and geospatial data.
    • Example: Select MySQL or PostgreSQL.
  4. Choose the database version:
    • MySQL: e.g., MySQL 8.0.32.
    • PostgreSQL: e.g., PostgreSQL 15.3.

Step 4: Configure Database Settings

  1. Templates: Select a template based on your use case:
    • Production: For high availability and scalability.
    • Dev/Test: For development or testing environments.
    • Free Tier: For cost-free testing (if eligible, available for both MySQL and PostgreSQL).
  2. Settings:
    • DB instance identifier: Enter a unique name (e.g., my-rds-mysql or my-rds-postgres).
    • Master username: Set the admin username (e.g., admin for MySQL, postgres for PostgreSQL).
    • Master password: Set a secure password and confirm it.
  3. DB instance class:
    • Choose an instance type (e.g., db.t3.micro for Free Tier or db.m5.large for production).
  4. Storage:
    • Select General Purpose SSD (gp2) or Provisioned IOPS SSD (io1).
    • Set allocated storage (e.g., 20 GiB for Free Tier, 100 GiB for production).
    • Enable Storage autoscaling if needed.
  5. Availability & durability:
    • For production, enable Multi-AZ deployment for high availability.
    • For dev/test, select Single-AZ to save costs.

Step 5: Network and Security Configuration

  1. VPC: Choose an existing Virtual Private Cloud (VPC) or use the default VPC.
  2. Subnet group: Select a DB subnet group or create a new one (ensure at least two subnets in different Availability Zones for Multi-AZ).
  3. Public access:
    • Select Yes for public access (only for testing; use private access for production).
    • Select No for production to restrict access to within the VPC.
  4. Security group:
    • Create or select a VPC security group.
    • Configure inbound rules to allow traffic:
      • MySQL: Port 3306.
      • PostgreSQL: Port 5432.
      • Allow traffic from specific IP addresses or your application’s security group.
  5. Database authentication:
    • Choose Password authentication for both MySQL and PostgreSQL.
    • For PostgreSQL, optionally enable IAM database authentication or Kerberos authentication (if supported).

Step 6: Additional Configuration

  1. Database options:
    • Set the Database name (e.g., mydatabase for MySQL, myappdb for PostgreSQL).
    • Configure the Port:
      • MySQL: Default is 3306.
      • PostgreSQL: Default is 5432.
  2. Backup:
    • Enable Automatic backups and set the retention period (e.g., 7 days).
    • Choose a backup window.
  3. Maintenance:
    • Enable Auto minor version upgrade for automatic updates.
    • Set a maintenance window.
  4. Monitoring:
    • Enable Enhanced Monitoring for detailed metrics (optional).
    • Enable Performance Insights for query performance analysis (optional).
  5. Encryption:
    • Enable Encryption at rest using AWS KMS (recommended for production).
  6. Tags (optional):
    • Add tags for resource management (e.g., Name: my-rds-mysql, Environment: Production).

Step 7: Launch the RDS Instance

  1. Review the configuration summary.
  2. Click Create database to start provisioning.
  3. Wait 5–15 minutes for the instance to become available (status changes to Available in the RDS dashboard).

Step 8: Verify and Connect to the RDS Instance

  1. In the RDS dashboard, locate your instance and note the Endpoint (e.g., my-rds-mysql.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com or my-rds-postgres.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com).
  2. Test connectivity using a database client:
    • MySQL (e.g., MySQL Workbench or CLI): 
      mysql -h <mysql-endpoint> -P 3306 -u admin -p
    • PostgreSQL (e.g., pgAdmin or CLI): 
      psql -h <postgres-endpoint> -p 5432 -U postgres -d myappdb
    • Enter the master password when prompted.
  3. Ensure your security group allows inbound connections from your IP or application.

Step 9: Prepare for Application Deployment

  1. Create database schema:
    • Connect to the RDS instance and create tables or import a schema.
    • Example for MySQL: 
      CREATE DATABASE myapp;
USE myapp;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
    • Example for PostgreSQL: 
      CREATE DATABASE myappdb;
\c myappdb
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
  2. Update application configuration:
    • Update your application’s database connection settings with the RDS endpoint, port, username, password, and database name.
    • Example for a Node.js app using MySQL: 
      const mysql = require('mysql2');
const connection = mysql.createConnection({
    host: '<mysql-endpoint>',
    user: 'admin',
    password: '<master-password>',
    database: 'myapp',
    port: 3306
});
    • Example for a Node.js app using PostgreSQL: 
      const { Client } = require('pg');
const client = new Client({
    host: '<postgres-endpoint>',
    user: 'postgres',
    password: '<master-password>',
    database: 'myappdb',
    port: 5432
});
  3. Test application connectivity:
    • Deploy your application and verify it connects to the RDS instance.
    • Ensure the application’s security group is allowed in the RDS security group’s inbound rules.

Step 10: Best Practices for Production

  1. Security:
    • Use IAM roles instead of hardcoding credentials in your application.
    • Rotate the master password periodically.
    • Restrict security group rules to specific IPs or VPC resources.
  2. High Availability:
    • Enable Multi-AZ for automatic failover.
    • Consider read replicas for read-heavy workloads (supported by both MySQL and PostgreSQL).
  3. Monitoring and Alerts:
    • Set up Amazon CloudWatch alarms for metrics like CPU utilization, storage, or connections.
    • Use AWS Trusted Advisor to check for misconfigurations.
  4. Backups and Snapshots:
    • Regularly test restoring from backups.
    • Create manual snapshots before major changes.
  5. Scaling:
    • Monitor performance and scale vertically (larger instance) or horizontally (read replicas) as needed.
    • For PostgreSQL, consider extensions like PostGIS for geospatial data or pg_cron for scheduled tasks.

Troubleshooting

  • Connection issues: Verify security group rules (port 3306 for MySQL, 5432 for PostgreSQL), VPC settings, and public accessibility.
  • Performance issues: Check CloudWatch metrics and enable Performance Insights to identify slow queries.
  • Authentication errors: Ensure the correct username, password, and database name are used.

Optional: Provisioning via AWS CLI

For automation, use the AWS CLI to create an RDS instance:

  • MySQL: 
    aws rds create-db-instance \
    --db-instance-identifier my-rds-mysql \
    --db-instance-class db.t3.micro \
    --engine mysql \
    --master-username admin \
    --master-user-password <secure-password> \
    --allocated-storage 20 \
    --vpc-security-group-ids <security-group-id> \
    --db-subnet-group-name <subnet-group-name> \
    --backup-retention-period 7 \
    --no-publicly-accessible
  • PostgreSQL: 
    aws rds create-db-instance \
    --db-instance-identifier my-rds-postgres \
    --db-instance-class db.t3.micro \
    --engine postgres \
    --master-username postgres \
    --master-user-password <secure-password> \
    --allocated-storage 20 \
    --vpc-security-group-ids <security-group-id> \
    --db-subnet-group-name <subnet-group-name> \
    --backup-retention-period 7 \
    --no-publicly-accessible

Next Steps

  • Deploy your application and test database interactions.
  • Set up automated backups and monitoring.
  • Explore AWS RDS features like read replicas, Aurora (for MySQL or PostgreSQL), or Parameter Groups for advanced configurations.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment