Amazon RDS (Relational Database Service) is a managed database service from AWS that makes it easy to set up, operate, and scale relational databases in the cloud.
Instead of manually installing and maintaining a database on a server, RDS handles most of the heavy lifting for you — like: Automatic backups, Software patching, High availability & failover (Multi-AZ), Performance monitoring, Easy scaling (storage & compute).
Using PostgreSQL on RDS gives you the power of a robust open-source database, combined with AWS automation for scaling, backups, and admin tasks.
Prerequisites
AWS account (free to create)
Basic understanding of PostgreSQL
SQL Editor (e.g. PgAdmin, DBeaver) for testing connections
Step 1: Navigate to Amazon RDS
Log into AWS Management Console
Go to RDS service
Step 2: Create a New Database Instance
Click on Databases on the left panel; this page lists all your databases on RDS, and as you can see we currently don’t have any at the moment. So click on “Create database.”
Choose Standard Create and select PostgreSQL from the engine options.
Step 3: Configure Database Settings
In the engine version dropdown, select the version you want to use. In this tutorial, I chose the smaller version which is “PostgreSQL 14.17-R1” then scrolled down to the template section and selected “sandbox” because we just want to get it up and running for demo purposes.
In the settings section, enter a database identifier; this is unique in your account and is used as part of the connection process from the SQL editor.
In this tutorial I’ll change the default DB identifier to “postgresql-01” and set the credentials management to self-managed, then set the password and confirm password. Don’t forget the password, as we’ll use it to connect later.
Step 4: Set Storage and Connectivity
In the instance configuration section, you can set the instance to “db.t3.micro” as we’re currently on demo. Then in the storage section leave all of the options as default values.
The next step is to choose VPC & networking options. The Compute resource, Network type, Virtual private cloud (VPC) and DB subnet group can all remain as default values.
But for the “Public access” option, you can change it to “Yes” so you can connect to it from your computer. You can leave the VPC security group (firewall) and availability zone as default.
Expand the additional configuration and ensure the database port is set to “5432.”
In the database authentication section, leave the default value of the password authentication and also leave the monitoring section as their default values.
Step 5: Launch and Test Connection
It is recommended to keep an eye on the account usage if you plan on using this database, so you don't get any surprises about what you get charged.
Finally, click on "Create Database" at the bottom.
After a few minutes the database list page will be shown, you will see a banner showing your database has been created, and the status will be available.
Now that our Postgres database is created, let's connect to it. In this guide, I’ll use the free SQL editor which is “DBeaver,” but you can use any other editor that can work with Postgres databases.
To get DBeaver you can download from the official website here.
Choose your operating system (Windows, macOS, Linux).
Download and install.
After downloading and installing DBeaver, open it and click on the new connection button in the window that appears, select postgresql as the database and click next.
We will now need to specify the connection details. What hostname should we use. We can find these details on the database page in AWS; so go back to the databases list inside AWS, click on the database name in the list and you’ll see the details page for this database.
The value to use for this hostname is labelled “endpoint” on the screen. It’s in the form of your database instance name (postgresql-01.cd2s88wgqc0p.us-east-2.rds.amazonaws.com). Copy this value and paste it into the host field in DBeaver.
The port should be “5432,” which matches what is on the AWS database screen. The database field can remain as postgres, and the username field can also remain as postgres.
For the password, enter the password that you specified when you created the database on AWS (this is not your AWS account password); it's the password you created for the Postgres database (remember when i told you to keep it and that you'd still need it?). Check the "save password locally" box if you want to store the password and not have to enter it each time you connect.
Next, click “Test Connection” to test that it is working; you’ll probably get an error such as “connection attempt timed out.” First, it could mean that the database has not been created. Check that the status is available in Amazon RDS and try again. If you still get an error, this can be resolved by creating a security group on Amazon RDS; this will allow inbound connections to the database, which is not something that is set up by default.
To resolve this issue, we need to go back to our RDS window and modify the security group. In the list of databases, click on the database to open the details.
On the connectivity and security tab, we will open the VPC security groups. Click on the entry that appears here called "default"; this will show you the VPC section. We have one security group associated with this database, which we can see here. At the bottom of the screen, we can see the details of the security group; click on the inbound rules tab. On the right of this tab, click on Edit Inbound Rules.
We now see the edit inbound rules page; click on the delete button to delete the existing rule. I suggest deleting and creating a new one in order to avoid errors when you try to modify it.
Then click on "Add Rule." In the type dropdown we will select PostgreSQL; the protocol and port range are already populated. Next is to select the source value of anywhere IPv4, then click on save rules. After a moment, the rule will be saved, and you'll be taken back to the security groups page.
We'll see the PostgreSQL rule that we just created at the bottom of the page; now we can retry our connection when we return to DBeaver and click on test connection again.
If all goes well, we should see a connected window to let us know the connection is successful. Click OK, then click finish on the connection window. You'll see a new option in your connection list called postgres.
Let's create a schema on this database, which is probably the next thing you want to do if you want to use this database. Double-click on the connection, and it will expand. When you expand the databases, you’ll see Postgres and then schemas. Right-click on schemas and select "create new schema."
We’ll name ours “newdb,” then click OK. The new database schema is then created. You can now proceed with creating tables on this schema or on the public schema that came with the database.
So that’s how you create a new Postgres database on Amazon RDS.
If you no longer need the database, you can delete it. This also might be a good way to prevent any charges on your account in the future.
To do this, navigate back to RDS. If you're still on the security group section, you can search for RDS in the search bar like we did earlier in this guide. Then click on databases on the left; on the left of the databases is a circle.
Click on the circle to select the database and then in the actions dropdown at the top, select delete.
If you want to create a snapshot, leave the BOX selected, but i don’t want to do that in this guide, so i’ll uncheck it. Tick the acknowledge box that appears, and i’ll also uncheck “retain automated backups,” as I don’t want that either.
Finally type "delete me" into the field at the bottom and click "delete." After a few moments, the database will be deleted.
So that's how you can set up a new Postgres database on Amazon RDS, connect to it from your SQL editor, and delete it if you no longer need it.
That’s it! You’ve successfully deployed PostgreSQL on Amazon RDS.
If you found this guide helpful, follow me here for more beginner-friendly DevOps and AWS tutorials.
Top comments (0)