Hosting a SQL on Amazon Web Services RDS

SQL stands for Structured Query Language and is the most widely used database management language. In order to use SQL in your project, you will need a RDBMS (Relational Database Management System) program. RDBMS is the basis for SQL for all modern database management systems such as MySQL, PostgreSQL, IBM DB2 etc.

Amazon Web Service’s RDS:

Imagine the year is 2010 and you have come up with an excellent idea for a website. You’ll have to set up a back-end server, research and install all the software to support your application, and after all these time-consuming tasks you will start developing the application. But wait! What about maintenance? You will have to install a bunch of security software for your backend server and also keep making sure that it is healthy. While you are doing this your website gets popular and is getting more and more traffic daily. So now you need to scale your application up quickly.

Wouldn’t it be great if someone did all these tasks for you and you just get to focus on your application? Well, Amazon Web Services (AWS) offers a service called RDS (Relational Database Service), which does all these database related tasks for you.

You just have to select the database you want to launch and within a few clicks you have a back-end server at your service which will be managed automatically.

People often mistake RDS for a database but it is important to know that Amazon RDS is not a database but a service that manages databases. RDS as of now can manage:

• MySQL

• PostgreSQL

• Oracle

• MariaDB

• Amazon Aurora

• Microsoft SQL Server

The main reason you should use a database hosted on a cloud service like Amazon Web Services is that looking after the wellbeing of your database, taking regular backups and making sure that the data is stored securely, is handled directly by the cloud provider, AWS in this case.

We will be focusing on the DB instances component of the RDS. Db instances are the basic building blocks of RDS. It is an isolated cloud environment that holds our database, and multiple users can access it at the same time using various tools. For each instance you can select to have anywhere from 5GB to 6TB of associated storage capacity. A DB instance can be created using the AWS management console, the amazon RDS API, or the AWS Command-line interface.

Let us host a PostgreSQL database on AWS RDS using the AWS console. Note that if you wish to host any other database like MySQL, MariaDB, Oracle etc. only a few changes are needed which will be mentioned at those steps.

• Head on over to https://aws.amazon.com and Sign In to your account. If you don’t have an account you will need to create one.

• Follow all the steps on the screen until you are on the AWS management console.

• In the search tab above, type in RDS and click on the very first result.

• On the opened page, click on ‘DB Instances’.

• Click on the big orange button to the top right titled Create database. Now you should be able to see the database creation dashboard. Let’s go through all the available options.

• Under database creation method, select Standard create.

Standard create allows us to manually select all the security, backups, maintenance, availability options. Easy create selects the most commonly selected options for your instance. You may use this if you are in a rush but it is not recommended as you should select all your configurations by yourself.

• Next option is Engine options. As the name says and you can see on the screen, here you have to select the database engine you want to use. I will be going with PostgreSQL.

Depending on the engine you choose you will see the options available for that instance. By default, it is set to the latest version but you are free to change them according to your requirement.

• Next you will have to select a Templates option. We have three options:

○ Production

○ Dev/Test

○ Free Tier

If you see just two options Production and Dev/Test it means that the database engine you have selected does not have a free tier option yet. You will have to choose a different version for your engine.

Production is the complete deal offering the best performance by AWS. Dev/Test is meant to be used only in development/production, while free tier as the name suggests is the simplest available template and is free. It is not as high performance as Production but gets the job done for most small projects.

I will be selecting the Free tier option here.

• Next up you will see the Settings options. These are just some basic properties that you need to specify for your database.

○ First you will have to name your DB instance under the DB instance identifier. This is just the name of your instance on the RDS dashboard and will not be reflected anywhere in the database. You can call it whatever you want. I will be using myInstance as the identifier.

○ Under Credentials Settings you will have to select the username and the password for your database. If you have worked with a SQL database before, you know you need a username and a password to connect to your database. You can go with whatever username and password you wish too. For the Master username I will be selecting the default postgres and a Master password that I can remember.

• Under DB instance class, I will be selecting the Burstable classes option and if you chose Free tier previously, this will be the only available option to you. This option just decides the amount of network memory, computation power that AWS will provide us with. Standard is the best option as it provides a good balance of compute, memory and network resources. Memory optimized classes are suitable workloads that process large data sets in memory. Burstable classes provide us the baseline level of CPU performance with the ability to burst above the baseline.

• Next let’s select the Storage options we want our database to have. Under Storage type I will be selecting General Purpose SSD. General purpose storage is suitable for a broad range of database workloads. Provisioned IPOS SSED storage is suitable for I/O-intensive database workloads.

In Allocated storage, you have to select just the memory space that will be allocated to the database. You can select anything from 20GB to 16TB. I will be going with 20GB for this example.

It is recommended to check the Enable storage autoscaling option which will increase the memory allocated if the previously decided memory, 20GB in this case is filled up. The maximum value to which we want it to scale up is entered under the maximum storage threshold. You will be charged if your database auto scales passed the specified threshold.

I will set it to 1000GB.

• If you opted for the Production or the Dev/Test option in the Templates section, you will have to select whether to create a standby instance or not under Availability & durability. This just allows you to have a replica instance in a different zone on standby. Select the option you wish to if you are allowed to.

• Under the Connectivity section, I recommend leaving the Virtual private cloud (VPC) and the Subnet group as the default value. In Public access you get to decide whether you want your database to be accessed from outside the RDS’s VPC. This is necessary if you wish to operate on your database with programming languages other than SQL. This will allow programs in other languages to connect to your database instance like python and java.

• Under the VPC security group, you have to select a security group to allow access to your database. This allows you to select the appropriate traffic coming to your instance. I will be leaving this to default. So, in the end my Connectivity section looks like :

• In Database authentication you select the level of authentication to connect to your database. The available options vary depending on your database engine. I will be selecting Password authentication.

• Last step is to review your database instance’s estimated monthly costs and then click on the orange button Create database.

You will be redirected to your DB instance overview page and you can see your DB instance being created. Wait till the instance’s status changes from Creating to Available.

We have our instance ready, but in order to access it from outside you need to specify certain rules in its security groups.

Click on your instance name and you will see your instance dashboard. Here click on the blue security group name under VPC security group.

Click on your security group ID, scroll down and click on Edit inbound rules.

Here click on Add rule and from the dropdown select PostgreSQL

Finally click on Save rules.

Now if you go back to your instance’s dashboard, you will see the endpoint URL which you can use to access your database. This indicates that the database has been deployed and can now be connected to using the endpoint.

You can go to your Workbench / PgAdmin Dashboard/ any other tool you might be using to connect to the database. Enter the endpoint URL in the Host section of your connection screen. Enter the username and password that you entered during creation of the RDS instance. In the database section, enter the name of the default database identifier that you entered. Finally, click on connect. You should be able to see a connection created on your screen and you can navigate inside the connection to view the databases and their contents.

If you wish to see how you can perform CRUD on a PostgreSQL database using Python, head on over to : https://www.atomicloops.com/post/crud-operations-on-a-postgresql-database-in-python-using-psycopg2