RDS Demo

In this tutorial, you will learn how to deploy a PostgreSQL instance using Amazon RDS. By using RDS, AWS manages the underlying database infrastructure, letting you focus on your application rather than manual database management tasks.

Step 1: Creating a Database

Begin by accessing the AWS console and searching for RDS. In the RDS dashboard, click on the Create database button. Depending on your history with the service, the interface might present the options as a toolbar at the top or bottom, or as a pop-up if it's your first visit. You will be presented with two options:

1. Standard Create – Offers full control over configuration.
2. Easy Create – Automatically applies AWS best practices.

For this demo, choose Standard Create to explore all available configuration options.

Engine Selection

Select the database engine you want to deploy. In this demonstration, we choose PostgreSQL, although other engines like Aurora, MySQL, MariaDB, Oracle, and Microsoft SQL Server are available. Once PostgreSQL is selected, you will have an option to choose a version; the default option is generally sufficient.

Next, choose a template according to your deployment needs:

• Production: High availability and resilience.
• Dev/Test: Ideal for development and testing environments.
• Free Tier: Qualifies for free usage under AWS guidelines.

For this walkthrough, select Dev/Test.

Instance Configuration

Provide a name for your database instance (e.g., "my-first-db") and set the master username (default is "postgres"). You will then enter a password, though you can opt for AWS to generate a secure password automatically.

In the Instance Configuration section, select the EC2 instance type on which your database will run. For this demonstration, sticking to the default option is recommended.

Proceed to configure the storage settings where you can specify:

• The type and amount of allocated storage.
• Storage autoscaling (a good cost optimization feature, especially for free tier users).

Connectivity and Security

Under the Connectivity section, you can skip the compute settings if you're not testing connectivity with an external EC2 instance. Select the default VPC and subnet. For production deployments, it's recommended to set "Public access" to No and restrict connectivity to your backend or API. However, for this demo, enable "Public access" so you can connect to the database from your local machine.

Select your VPC and create a new security group (for example, "my DB security group") to control traffic. The availability zone can be selected or left as default if no specific preference is required.

Additional Configuration

In the additional configuration section, you can modify settings such as:

• The default PostgreSQL port (modifiable if necessary).
• Authentication methods (default is password authentication).
• Monitoring and backup configurations.

For the purposes of this demonstration, it is safe to leave these settings at their defaults. Once you have completed all configuration steps, click Create database. The creation process might take a few moments until the status displays as "available".

Step 2: Connecting to Your Database

After your database instance is running, select it from the AWS console to view connectivity details. The key elements you need are the endpoint (IP/domain name) and the port.

The connection string typically includes the following information:

• Host: The database endpoint.
• Port: Default PostgreSQL port, usually 5432.
• User: The master username (e.g., "postgres").
• Password: The password you configured.
• Database: The default database, typically named “postgres.”

Below are two common methods to connect to your PostgreSQL instance.

Method 1: Connecting via Code

The following JavaScript snippet uses the Knex query builder to connect to the PostgreSQL instance:

const knex = require("knex")({
  client: "pg",
  connection: {
    host: "my-first-db.cidipbxuwdgl.us-east-1.rds.amazonaws.com",
    port: 5432,
    user: "postgres",
    password: "password",
    database: "postgres",
  },
});

Method 2: Connecting with pgAdmin

pgAdmin is a widely used GUI tool for managing PostgreSQL databases. Follow these steps to connect:

1. Open pgAdmin and create a new server.
2. In the Create - Server dialog:
   • Enter a name (e.g., "AWS - RDS").
   • Provide the server connection details including the host (the endpoint copied from AWS), port, username, and password.
3. Save the configuration.

After saving, you will see the default PostgreSQL database in pgAdmin. You can create additional databases as needed for your application. For example, you might create a new database called "my_app". In case of an error, it may appear similar to the following:

(sqlite3.IntegrityError) UNIQUE constraint failed: database.id, database.server
[SQL: INSERT INTO "database" (id, schema_res, server) VALUES (?, ?, ?)]
[parameters: (16402, None, 1)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Resolve any errors accordingly and verify that your new database is visible in the pgAdmin interface.

Step 3: Managing Your RDS Instance

Once connected, you can manage your RDS instance through the AWS console.

Modifying the Instance

If you need to update any configurations (such as engine version, instance type, or security settings), select the instance and click Modify. This option allows you to adjust various settings for your running PostgreSQL instance.

Deleting the Instance

When you are ready to remove the database instance, select it and click Delete. AWS will prompt you to confirm the deletion and ask if you wish to retain snapshots and automated backups.

After confirming, the database instance will be deleted, concluding the demonstration process.

Conclusion

You have now successfully deployed, connected to, and managed a PostgreSQL database using Amazon RDS. This hands-on demonstration showcased how to configure your instance and perform key operations such as connecting via code and pgAdmin, modifying settings, and safely deleting the instance.

Happy coding!

Additional Resources