You might ask yourself, what is Amazon RDS? Types of Amazon RDS you can choose from ? Why use Postgresql?
On this article we would not just answer these questions, but also run a lab walkthrough on how to set this up on the cloud and on your local computer.
Amazon RDS stands for Amazon Relational Database Service, it is a collection of managed services that makes it simple to set up, operate, and scale databases in the cloud.
AWS give you seven popular engines to choose from, in no particular order:
- SQL Server
- Amazon Aurora with MySQL compatibility
- Amazon Aurora with PostgreSQL compatibility
- and Oracle
PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset.
Login to your AWS Console, click on services, on the top right corner. Click on Database > RDS
On the RDS landing page, click on Create database.
This would direct you to the RDS Database dashboard.
On the dashboard, click on Create database
Database Method/Engine options
For database creation method, select standard create.
For Engine option, select PostgreSQL.
For version, select your most preferred option depending on your use case, or just leave the default.
In this case, I would be using PostgreSQL 13.4-R1
In the template section, select Free tier
leave as default
DB instance identifier, input postgres
Credentials Settings > Master username, use your preferred username, in my case, I would be using skywalker , insert your password, and confirm password.
DB instance class, Burstable classes (includes t classes) is selected by default. select db.t3.micro
Storage type > General Purpose SSD (gp2)
Allocated storage > 5GB.
Leave everything else on the storage section as default
Virtual private cloud (VPC) > select the default vpc or create a new vpc.
Subnet group > select default subnet or use created subnet.
Public access > Yes
VPC security group > choose existing or create new, if you haven't created one before.
Select Existing VPC security groups.
Leave Availability Zone as No preference.
Database authentication options > select Password authentication.
Click Create Database
You have successfully created a Postgres Database, using the Amazon RDS service on the AWS console.
Edit Inbound Security Rules
After creating the database you would be redirected to the database dashboard.
Scroll down to the connectivity & security section.
Under security, click on the VPC security groups.
When you click on the security groups, it would redirect you to EC2 > Security groups.
click on the security group > click on inbound rules and edit it to the below configuration.
If you don't already have Postbird installed, you can download it here
Now, launch Postbird, input your credentials, use your newly created database endpoint as host. set Port to 5432.
Insert your username and password. leave Database and Start Query empty.
Click on Test Connection, if it returns Successfully connected, then click on save & connect.
Thats not all, we need to be able to access the database using the AWS CLI on the terminal.
Firstly, we need to install psql, if you don't have it installed already, you can get it here
Open your terminal, use the following command template.
psql -h yourRdsEndpointUrl -U skywalker postgres
psql -h postgres.cwunlr23syoo.us-east-1.rds.amazonaws.com -U skywalker postgres
Note: Change yourRdsEndpointUrl, with your original endpoint url.
change skywalker to your username.
Lets explain the above command line.
psql calls the postgresql command, -h means Host, then you insert your RDS endpoint. -U means user, the postgres, is the database name.
After you have entered the above command, if all your credentials are correct, you should have access to the postgres cli.
Enter this command to view a list of databases
We have successfully created a postgresql, resource on the cloud, using the Amazon RDS service.
We also installed, Postbird, inputted the appropriate credentials to access the cloud hosted database using Postbird GUI.
Went a step further to install the psql CLI on our local machine, inputted the correct credentials to access our newly created database, in our terminal.
If this article has helped you gain insight on Amazon RDS (postgresql), please like, share and subscribe.