How to Create an Amazon RDS Aurora PostgreSQL Database Cluster?

To keep up with the ever-changing and evolving tech industry, we want our databases to be easily scalable, replicable, fault-tolerant, and most importantly without having any administrative hassle.

One of the best options to achieve this is to opt for Amazon Relational Database Service (RDS). RDS supports a number of database engines and Amazon Aurora is one of them.

Amazon Aurora is a cloud-native database engine developed by AWS providing versions compatibility with MySQL and PostgreSQL.

Quoting AWS –

“Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud, that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases.”

Here, for this demonstration, we will focus on the PostgreSQL-compatible edition of Aurora.

Let us quickly take a look at the key highlights of Amazon Aurora PostgreSQL.

• Aurora has a very simple Pay as you go pricing model. You only pay for the storage and IO used.

• Aurora has up to 3x more performance benefits than the standard community edition of PostgreSQL.

• Amazon Aurora is fully managed by Amazon Relational Database Service (RDS) which greatly reduces the burden of administrative tasks from the DBAs.

• Amazon Aurora is designed to offer greater than 99.99% availability, replicating 6 copies of your data across 3 Availability Zones and backing up your data continuously to Amazon S3, enabling granular point-in-time recovery.

• Failover takes only around 30 seconds.

• Monitoring database performance has never been so easier with tools such as Amazon CloudWatch, Enhanced Monitoring or Performance Insights – all thanks to Amazon Web Services (AWS).

For all the curious souls out there, check out the complete list of features available with Aurora PostgreSQL here.

Without further ado, let’s jump into setting up a PostgreSQL-compatible Amazon Aurora instance.

I have tried to provide a very granular level of details here and this post is going to be a long one. Expecting a few TL;DR comments for this. Please feel free to step out and grab a cup of coffee in-between.

Below are the steps to follow for creating and connecting a PostgreSQL database instance:

1. Log in to the AWS Management Console and type RDS inside the Find Service search window. Select RDS service once it pops up.

2. Once you land on the Amazon RDS page, click on the Create Database button.

Click either of the two Create Database button, it will work.

3. Once you are on Create Database page, choose the options highlighted below.

• Choose a database creation method: Selecting Standard Create will let you select the resource as per your require requirement, while Easy Create will go with the best-practice configuration.

• Select the Engine type as Amazon Aurora.

• Edition should be Amazon Aurora with PostgreSQL compatibility since we will be setting up a PostgreSQL instance.

• Select the latest available PostgreSQL Version from the drop-down menu.

Note: If you are planning to access the instance using any application, you may have to select a version that is compatible with your application.

4. Select the ‘Template’ based on the usage and system requirements. Here, we are spinning up the instance for demonstration purposes. Hence, selected Dev/Test template.

If you are using AWS Free Tier Account, you will see an additional Free tier template to choose from.

Provide the DB cluster identifieras per your choice. Here, I am using dbaguides as the database cluster name.

Provide a Master username as per your choice. The name postgres comes inside the filed by default. You may keep it or change it to something like sa, choice is yours.

Provide a strong password, or let RDS generate one for you by selecting the auto genarate a password checkbox.

5. Select a DB instance class as per your resource requirement. You may want to take a look at all the available instance types beforehand and choose the one that best suits your requirement.

If you want to set up an additional Reader node in a different Availability Zone(AZ), select the Create an Aurora Replica or Reader node in a different AZ option. Since this instance is for demonstration purposes, I will not create a Reader node and choose Don’t create an Aurora Replica instead.

6. You will have the option to create a new Virtual privet cloud(VPC) or use the default one. The same goes for Subnet group and VPC security group.

I will use the Default options for this demonstration.

Choose the Publicly accessible option wisely while you set up a DB instance for production usage in your organization.

You can use the default port for PostgreSQL 5432 or use a custom one as per your requirement.

Choose the Database authentication option based on the level of security you want to impose for connecting the instance.

7. You can skip the Additional configuration part initially and do it later as well. If you wish to create any database while creating the instance, fill up the details accordingly. Also, select other options like Backup retention period etc. accordingly.

8. Again, if you don’t want to Enable Encryption, skip this part. In case you do, select the Master Key accordingly.

Also, enabling Performance Insight can be very helpful for troubleshooting various performance-related issues. Check out the pricing for Performance Insight before you enable it.

9. Enable Enhanced monitoring will let you monitor operating system (OS) metrics for your DB instance in real-time. This also helps in monitoring and troubleshooting various performance-related issues. By default, Enhanced Monitoring metrics are stored for 30 days in the CloudWatch Logs. Again, check out the pricing before you enable it.

10. Select the Maintenance window as per your organization's approved downtime window. AWS applies the latest patches and upgrades on the DB instances during the selected Maintenance window which requires at least a 30-minute window and involves downtime. Not specifying a Maintenance window may lead to an unexpected outage during business hours if any maintenance update is applied during that time.

Another important setting that is recommended to turn on is Deletion Protection. It will prevent accidental deletion of your instance. You need to explicitly turn off this setting in case you want to delete your instance. Thus, it provides an additional layer of safety.

Finally, click the Create database button now 🚀

What are you waiting for? Hit the Create database button now 😊

11. Once you click the Create database button, it will redirect you to a page like below. Keep an eye on the Status column for the instance creation status.

Also, in the meantime, you may want to note down the credentials by clicking the View credential details button.

12. Copy the below details which will be required for connecting the instance.

13. Once the setup completes, the status will be changed to Available. At this point, the database instance is ready to accept connections.

14. Before we proceed with connection verification, let us take a look at a few important instance details.

The Connectivity & security tab lists important details about the Endpoint, port, VPC, subnet, availability zone etc. which you can refer to anytime.

15. The Monitoring tab displays various details like CPU, IOPS, Memory etc. that gives you a sense of the current instance performance details.

16. The Log & events tab lets you see the instance level log and come in handy while investigating issues like unexpected server reboot, or tracking down other cluster events etc.

17. Tags help to add metadata to your Amazon RDS resources and organize them. Tags can be used to differentiate between your development, test, and production environments. Cost allocation tags helps to track your AWS costs on a detailed level. For more details, take a look at the AWS documentation.

It is recommended to tag the resources appropriately for better manageability of the resources.

Provide the Tag Key – Value and click on the Add button. Add another Tag will let you add additional tags.

The tags will be listed as below.

18. You need to set a connection security rule properly to allow inbound traffic to interact with the database. You will not be able to access the databases from outside if this setting is not done properly.

Follow the below steps properly:

• Select the Connectivity & security tab and Scroll down the page to Security group rules section.

• Click on the inbound rules as highlighted below. It will take you to another page.

• Now, expand the Action dropdown and click on Edit inbound rules.

• Select the inbound Rule Type, Source etc. properly. Since this is a PostgreSQL instance, we will select the Type as PostgreSQL and will select the Protocol, Port etc. with default value accordingly. I will set the Source as Anywhere as this is a test instance created for this demonstration.

Please be very cautious while setting this rule for your production instances as it may lead to unwanted data access due to improper settings.

19. Now, let's connect to the database and verify if everything is working as expected.

For the installation and configuration part, we have used psql. This time, we will use pgAdmin GUI tool to connect to the database. You may use psql as well.

• Install pgAdmin first, if you don’t have it installed on your system yet. The setup file can be downloaded from the pgAdmin official website.

• Once installed, right-click on the Servers, then select Create and click on the Server next. The Create – Server pop-up window will come up.

• Provide a server Name on the General tab. For better identification, use the hostname itself.

• Click on the Connection tab and provide the Host name, Port, Username, Password accordingly and click on Save.

• And finally, we are connected to the Aurora PostgreSQL instance. 🚀 You can see the database dbaguides created as well. Remember, we specified Initial database name as dbaguides under the Additional configuration section, for creating an initial database.

20. You Can also verify the databases by running a query against them. Right-click on the database and select the Query Tool option. Then, type your query in the Query Editor and press execute() key.

For example, you may use the below code which will return the current database names.

SELECT datname FROM pg_database;

Congratulations🎉 We have successfully created an Amazon Aurora PostgreSQL database instance and connected to it using pgAdmin tool 🙏