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
3xmore 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
6copies of your data across
3Availability Zones and backing up your data continuously to Amazon S3, enabling granular point-in-time recovery.
Failover takes only around
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:
- Log in to the AWS Management Console and type
Find Servicesearch window. Select
RDSservice 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 Createwill let you select the resource as per your require requirement, while
Easy Createwill go with the best-practice configuration.
Amazon Aurora with PostgreSQL compatibilitysince we will be setting up a PostgreSQL instance.
Select the latest available PostgreSQL
Versionfrom the drop-down menu.
Note: If you are planning to access the instance using any application, you may have to select a
versionthat 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
If you are using AWS Free Tier Account, you will see an additional
Free tier template to choose from.
DB cluster identifieras per your choice. Here, I am using
dbaguides as the database cluster name.
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.
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.
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.
Performance Insight can be very helpful for troubleshooting various performance-related issues. Check out the pricing for
Performance Insight before you enable it.
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.
Connectivity & security tab lists important details about the
availability zone etc. which you can refer to anytime.
Monitoring tab displays various details like
Memory etc. that gives you a sense of the current instance performance details.
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.
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.
Tag Key – Value and click on the
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:
Connectivity & securitytab and Scroll down the page to
Security group rulessection.
Click on the inbound rules as highlighted below. It will take you to another page.
- Now, expand the
Actiondropdown and click on
Edit inbound rules.
- Select the inbound Rule
Sourceetc. properly. Since this is a PostgreSQL instance, we will select the
PostgreSQLand will select the
Portetc. with default value accordingly. I will set the
Anywhereas 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.
pgAdminfirst, 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
Createand click on the
Create – Serverpop-up window will come up.
- Provide a server
Generaltab. For better identification, use the hostname itself.
- Click on the
Connectiontab and provide the
Passwordaccordingly and click on
- And finally, we are connected to the Aurora PostgreSQL instance. 🚀 You can see the database
dbaguidescreated as well. Remember, we specified
Initial database nameas
Additional configurationsection, 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 🙏