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 being 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 benefit 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 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 ‘RDS’ inside the ‘Find Service’ search window. Select ‘RDS’ service once it pops up.
2. Once you land up on Amazon RDS page, click on the ‘Create Database’ button.
3. Once you are on Create Database page, choose the options as 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 requirement. Here, we are spinning up the instance for demonstration purpose. Hence, selected ‘Dev/Test’ template.
If you are using AWS Free Tier Account , you will see an additional ‘Free tier’ template to choose for.
Provide the ‘DB cluster identifier’ as per your choice. Here, I am using
dbaguides as the db 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 type beforehand and choose the one that best suits your requirement.
If you want to setup 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 purpose, I will not create a Reader node and choose ‘Don’t create an Aurora Replica’ instead.
6. You will have option to create a new Virtual privet cloud(VPC) or use the default one. 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 setup 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 the 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 issue. 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 organizations approved downtime window. AWS applies latest patches and upgrades on the DB instances during the selected ‘Maintenance window’ which requires 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 applied during that time.
Another important setting that is recommended to turn on is ‘Deletion Protection’. It will prevent an accidental deletion of your instance. You need to explicitly turn off this settings in order to delete you instance. Thus, provides an additional layer of safety.
Finally click 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 db instance is ready to accept connections.
14. Before we proceed for connection verification, let us take a look at a few important instance details.
The ‘Connectivity & security’ tab list down important details about the Endpoint, port, VPC, subnet, availibility zone etc. which you can refer 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 let you see the instance level log and comes in handy while investigating issues like unexpected server reboot, or tracking down other cluster events etc.
17. ‘Tags’ helps 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 it 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, lets 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 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
dbaguidescreated as well. Remember, we specified ‘Initial database name’ as
dbaguidesunder the ‘Additional configuration’ section, for creating an initial database.
20. You Can also verify the databases by running a query against the 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