Author: Masoom Goyal
In this blog we will learn how to connect to Postgresql which is running on Amazon RDS from our Studio.
Amazon Relational Database Service (Amazon RDS) is a collection of managed services that makes it simple to set up, operate, and scale databases in the cloud. It provides you six familiar database engines to choose from, including Amazon Aurora, MySQL, MariaDB, Oracle, Microsoft SQL Server, and PostgreSQL. This means that the code, applications, and tools you already use today with your existing databases can be used with Amazon RDS. Amazon RDS handles routine database tasks such as provisioning, patching, backup, recovery, failure detection, and repair.
We will first learn how to create DB instances on Amazon RDS and then we will look at how to connect it from the local Postgres DB and studio.
How to create DB instances
1. Create account on https://aws.amazon.com/getting-started/
2. Sign in to the AWS Management Console and open the Amazon RDS console at https://us-east-1.console.aws.amazon.com/rds/home?region=us-east-1.
3. Select the Region and click on Create Database.
4. After that Select engine as PostgreSQL.
5. Choose the use case accordingly. Here I am using Dev/Test
6. Specify the DB details as per your Requirement and click Next. Here is what I selected:
7. In Advanced Settings, you may change as per your requirement. Here, I have disabled monitoring,logs,encryption and performance insights.
NOTE : If your instance is not public, VPC setup will be required for the connection. Here, I am making it public.
8. Click on Create Database.
Once, your DB instance is created, you may now connect to your PostgreSQL running on local. For PostGreSQL I am using PGAdmin 4.
How to connect local PostgreSQL to DB Instance
1. In PGAdmin 4, go to dashboard and click on Add New Server:
2. In General Tab, give name as per your requirement and Go to connections and add following details:
Host name: You will find in your amazon RDS under Connectivity and security.
Port : By default postgreSQL uses 5432
Username and password : Which you gave during DB Instance creation.
3. Click Save. Your Db instance is Successfully added to your PGAdmin.
NOTE: If you are getting timeout issue, you have to modify inbound rules on the security group to following:
4. You may create a sample table in your database by navigating the following and run the following command:
create table mytesttable(age int, name text)
insert into mytesttable values (25,’MaxMule’)
Now, it’s time to connect this database with your anypoint studio.
How to Connect DB instance from Anypoint Studio
1. Add database config and add values as follows:
- Connection : Generic Connection
- URL : jdbc:postgresql://{HOST}:5432/{DATABASE_NAME}
- Driver Class : org.postgresql.Driver
- User : {USERNAME OF DB INSTANCE}
- Password : {PASSWORD OF DB INSTANCE}
2. I have created a small Mule application to verify my DB instance:
OUTPUT
So this was all about PostgreSQL hosted on Amazon RDS. Thank you.
