How to Set up a PostgreSQL Database on Amazon RDS

Mendiratta Shanky

Read more posts by this author.

If you need to create and connect to an Amazon RDS Postgres SQL database, you’ve come to the right place. Creating your first Amazon Relational Database Service (RDS) database might be a challenge but in this tutorial, you’re going to learn how to make it happen, step-by-step.

Prerequisites

This post will be a step-by-step tutorial. If you’d like to follow along, please be sure you have the following in place:

You may incur a small charge for creating an RDS instance on Amazon Managed Web Service. When you’re done, be sure to remove the RDS instance.

Creating the RDS PostgreSQL Instance

Before you can use a PostgreSQL RDS instance, you must create it. This tutorial will use the AWS Management Console.

  1. Sign into the AWS Management Console with your AWS account.

2. At the top of the page, you should see a search box where you can search various amazon web services. In the search box, type “RDS” and select the RDS service that comes up. This will bring you to the Amazon RDS page.

Navigating to the RDS management page
Navigating to the RDS management page

3. On the Amazon RDS page, click on Databases and then click on the Create database button on the right.

RDS database pages
RDS database pages

4. Next, choose Standard create. Creating the instance with the Standard create method allows you to customize the instance more than using the Easy create method.

5. Select PostgreSQL as the database engine and the Version. This tutorial will use the latest version at the time of writing which is version 12.5-R1 but you can choose many versions of PostgreSQL here.

6. Choose the Free tier for the Template since you’re just learning at this time.

Defining standard PostgreSQL database options
Defining standard PostgreSQL database options

7. Scroll down a bit and now provide:

  • The database name as the DB instance identifier
  • A username for the Master username
  • A password for the Master passwordOnce you create the database, you can add other database users.

Once you create the database, you can add other database users.

Providing the RDS instance name, instance type and user information
Providing the RDS instance name, instance type and user information

8. You can choose a number of instance classes in the DB instance class depending on the performance you need from the instance. Since this tutorial is just a demonstration, choose any free instance such as db.t2.micro.

Find all available instance types here with various instance size.

Although configurable, leave the Storage type, Allocated storage, and Maximum storage threshold fields default.

Defining RDS instance storage options You can find more information about storage options here.
Defining RDS instance storage options You can find more information about storage options here.

You can find more information about storage options here.

10. Since this is just a test, specify the Default VPC for the Virtual private cloud (VPC). This setting will ensure the RDS instance is available within a specific VPC.

11. Set the Subnet group to default which will use the default subnet associated with the VPC.

12. Ensure the Database port is set to 5432 which is the default for PostgreSQL.

Setting network connectivity to the PostgreSQL RDS instance
Setting network connectivity to the PostgreSQL RDS instance

13. Ensure Password authentication is set for Database authentication. This scenario allows you to authenticate to the database using users defined in the database and is the simplest method.

Creating the RDS database instance
Creating the RDS database instance

14. Finally, click on Create database to begin creating the RDS PostgreSQL instance.

AWS will take a few minutes to create your RDS instance. However, once it does, you will see your instance show up with a Status of Available shown below.

Successful PostgreSQL RDS instance
Successful PostgreSQL RDS instance

Connecting to the RDS Instance

Once you’ve created the RDS instance, it’s time to connect to it. This tutorial will use a free tool call pgAdmin, but you can find many other PostgreSQL clients that will fit your needs.

Assuming you’ve already installed pgAdmin:

  1. Open pgAdmin4 and right-click on the Servers node, click Create —> Server. This will bring up a new server connection window.
pgAdmin4
pgAdmin4

2. On the General Tab, provide a Name for the server. This tutorial will use myrds.

Ensure that the:

  • Host name/address is <DB instance name>.<region identifier>.rds.amazon.com
  • Port is 5432 (the same as defined while creating the instance)
  • Maintenance database is set to postgres. This is the default database that every PostgreSQL RDS instance should have.
  • Username is postgres, and the Password is the Master password provided while creating the instance
Create Server
Create Server
Username and Password
Username and Password

4. Click Save to save the server connection.

pgAdmin should now automatically connect to the PostgreSQL RDS instance, and you will see it displayed under the Servers node as shown below.

Servers node
Servers node

If your client doesn’t automatically connect to the instance, be sure to confirm the security group for the VPC you associated the RDS instance to allows inbound rules on port 5432. Ensure that all ip addresses you intend to connect from are in here. Also make sure your RDS supports publicly accessibility.

Creating a Database

So you’re now connected to the Postgresql database instance. It’s time to create a database!

To do that with pgAdmin:

  1. Inside of the myrds instance, right-click on Databases.
  2. Click on Create —> Database to open the database creation window.
  3. Provide a database name and click Save.
Providing a Database Name
Providing a Database Name

If you look under the myrds instance now, you’ll see your new read only database show up as shown below.

Read Only Database
Read Only Database

Now that you have created a database, you can connect to the database and use it however you need to.

Conclusion

You should now have a simple PostgreSQL RDS instance running in AWS. This tutorial wasn’t meant to cover any in-depth scenarios but more of a simple guide to get you started.

Your PostgreSQL database should now be ready to begin helping you manage your organization’s data.

Looks like you're offline!