Looking for a way to connect to AWS RDS Postgres instance? We can help you.

Amazon Relational Database Service allows users to quickly create databases in the AWS cloud.

But users often experience problems while connecting to these databases.

At Bobcares, we resolve database connection errors as part of our AWS Support Services.

Today, we’ll see the different methods to connect to AWS Postgres instances.

 

Few facts on AWS RDS Postgres instance

To begin with, let’s check more details on Amazon RDS.

Amazon RDS is a managed database service. In this way, users do not have to maintain a database server. It comes with automated backups, options to scale up as per need, etc. Amazon RDS supports all popular databases like MySQL, Postgres, etc. Thus it allows hosting the databases of your application.

 

Why connect to AWS RDS Postgres

Till now, we saw the details of AWS RDS Postgres instances. But, why would we need access to Postgres instance?

We require access to the Postgres database to make direct modifications in the database. The database tables may require changes according to the requirements of the applications. Likewise, we may need to do select queries to get the desired results as well.

 

Top methods to connect to AWS RDS Postgres

By default, AWS RDS does not provide shell access to database instances.

Then how can we connect to the Postgres database running on AWS RDS?

Let’s check the top ways to connect.

 

1. Using pgAdmin

One of the quick ways to connect to the Postgres database is by using pgAdmin. The pgAdmin is an open-source tool that allows connection to the database running in AWS.

For connecting to PostgreSQL DB using PGAdmin tool,

1. We launch the pgAdmin application on your client computer.
2. On the Dashboard tab, choose Add New Server.
3. In the Create – Server dialog box, type a name on the General tab to identify the server in pgAdmin.
4. On the Connection tab, type the details of the DB instance:
   – Enter the Host as the endpoint name: “pg-prod.xxxxx-1.rds.amazonaws.com” (Can retrieve information from AWS console)
   – For Port, type the assigned port: 5432
   – Type the user name that you entered when you created the DB instance.
   – For Password, type the password that you entered when you created the DB instance.

2. Connect via psql

Likewise, we can connect to the Postgres databases using psql as well.

Psql is a command-line utility to connect to a PostgreSQL DB instance.

For this, we install the psql client on the home computer. Here, to connect to the PostgreSQL DB instance using psql, we need to provide host information and access credentials.

To connect to the PostgreSQL database mydb located in RDS server pg-prod.chnxxxxx.rds.amazonaws.com as user myuser, we use the command as

psql -h pg-prod.chnxxxxx.rds.amazonaws.com --port=5432 -U myuser --dbname=mydb

 

 

Common errors in AWS RDS Postgres connection

Although the steps for connection looks fairly simple, users often experience trouble while connecting to the PostgreSQL database.

Let’s check how our Support Engineers resolve them.

 

Public accessibility privileges

For an RDS DB instance to be publicly accessible, it should have the necessary privileges.

Recently, a customer reported problems with his RDS Postgres connection.

On further checking, we could see that the public accessibility of databases was set to NO. To fix the problem, we changed it to YES.

Final settings appear as:

 

Security group restrictions

Further, security group restrictions also can trigger connectivity errors with RDS instances.

To make the connection, the security group assigned to the DB instance must allow access to port 5432. This also should allow outbound connections from the computer’s firewall.

Moreover, if the DB instance was created in a VPC, our Support Engineers ensures that it has a VPC security group that authorizes connections.

Last, and not the least, we confirm that the security group’s access rules are assigned to the DB instance correctly.

 

[Trouble connecting to your PostgreSQL database in AWS RDS? Let’s help you.]

 

Conclusion

In this write-up, we saw the ways to connect to AWS RDS Postgres database. We also saw how our Support Engineers troubleshoot connection errors and make it work.