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:
- An AWS account
- A client to connect to the PostgreSQL database. This tutorial will be using the pgAdmin utility.
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.
- 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.
3. On the Amazon RDS page, click on Databases and then click on the Create database button on the right.
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.
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.
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.
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.
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.
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.
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:
- Open pgAdmin4 and right-click on the Servers node, click Create —> Server. This will bring up a new server connection window.
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
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.
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:
- Inside of the myrds instance, right-click on Databases.
- Click on Create —> Database to open the database creation window.
- Provide a database name and click Save.
If you look under the myrds instance now, you’ll see your new read only database show up as shown below.
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.
