with Amazon RDS
In this tutorial, you will learn how to create an environment to run your PostgreSQL database (we call this environment an instance), connect to the database, and delete the DB instance. We will do this using Amazon Relational Database Service (Amazon RDS) and everything done in this tutorial is free-tier eligible.
Manage Your AWS Resources
Sign in to the ConsoleWhen you click here, the AWS management console will open in a new browser window, so you can keep this step-by-step guide open. When this screen loads, find RDS under Database and click to open the Amazon RDS Console.
In this step, we will use Amazon RDS to create a PostgreSQL DB Instance with db.t2.micro DB instance class, 20 GB of storage, and automated backups enabled with a retention period of one day. As a reminder, all of this is free tier eligible.
a. In the top right corner of the Amazon RDS console, select the Region in which you want to create the DB instance.
Note: AWS Cloud resources are housed in highly available data center facilities in different areas of the world. You have the ability to choose which Region to host your Amazon RDS activity in.
f. Your DB Instance is now being created. Click View Your DB Instances.
Note: Depending on the DB instance class and storage allocated, it could take several minutes for the new DB instance to become available.
The new DB instance appears in the list of DB instances on the RDS console. The DB instance will have a status of creating until the DB instance is created and ready for use. When the state changes to available, you can connect to a database on the DB instance.
Feel free to move on to the next step as you wait for the DB instance to become available.
After the database instance creation is complete and the status changes to available, you can connect to a database on the DB instance using any standard SQL client. In this step, we will download SQL Workbench, which is a popular SQL client.
Note: Remember to run SQL Workbench from the same device on the same network from which you created the DB Instance. The security group your database is placed in is configured to allow connection only from the device from which you created the DB instance. If you try to connect from a different network or device, your IP address would have changed. Your database can be configured to be accessed from any IP address (see details here), but for this tutorial we will keep it simple.
a. Go to the SQL Workbench website and click Generic package for all systems including all optional libraries.
b. Next, click here to download the latest JDBC driver from the PostgreSQL website. Save the file in a place where you can easily find it later. This file is needed in the next step.
In this step, we will connect to the database you created using SQL Workbench.
a. After you have completed your download, install SQL Workbench.
Note: You can launch the application using the .exe file (Windows) or the shell script (macOS, Linux) included in the download folder. See the PDF file included in the download for details.
b. Open the program. A dialog box appears. Enter the following:
- Driver: PostgreSQL (org.postgresql.Driver)
Note: When you select a driver from the drop-down menu you will be prompted to edit the driver definition. Select Yes. In the next dialog box, click the folder icon and select the driver you downloaded in the previous step.
- URL: You can find your JDBC URL on the Amazon RDS console as shown in the screenshot to the right. Enter or paste the endpoint (including port) of the DB Instance after "jdbc:postgresql://". Finally, append a forward slash and the name of your data base instance to the end of the URL. For example, our URL could be jdbc:postgresql://postgresql-instance1.cg034hpkmmjt.us-east-1.rds.amazonaws.com/myDatabase.
- Username: Type in the username you created for the Amazon RDS database. In this tutorial, it is 'masterUsername.'
- Password: Enter the password that you used when creating the Amazon RDS database.
Click OK.
c. You are now connected to the database. From your Amazon RDS Console, select your instance from the Databases list and you should see that there is "1 Connection" to your database listed under the Current activity heading.
Note: At this point your database is ready to use. You can start creating tables, insert data, and run queries from SQL Workbench client.
You can easily delete the PostgreSQL DB Instance from the Amazon RDS console. It is a best practice to delete instances that you are no longer using so that you don’t keep getting charged for them.
You have created, connected to, and deleted a PostgreSQL Database Instance with Amazon RDS. Amazon RDS makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business.
Now that you have learned to create and connect to a PostgreSQL Database through Amazon RDS, you can progress to the next tutorial where you will learn to restore a DB Instance from a DB Snapshot.