Looking for a way to connect to AWS RDS Postgres instance? At Bobcares, with our AWS Support Services, we can help you.
Overview
-
- More on AWS RDS Postgres Instance
- Why Connect to AWS RDS Postgres?
- Various Methods to Connect to AWS RDS Postgres
- Common Errors in AWS RDS Postgres Connection
- Conclusion
More on AWS RDS Postgres Instance
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.
Various 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,
- We launch the pgAdmin application on your client computer.
- On the Dashboard tab, choose Add New Server.
- In the Create – Server dialog box, type a name on the General tab to identify the server in pgAdmin.
- 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.
0 Comments