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 Console

When 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.

AWS Console Image

(click to zoom)

1-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: Amazon cloud computing 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. 

2-rds-region

(click to zoom)

2-rds-region

b.   In the navigation pane on the left, click Instances. Then click Launch DB Instance.

 

 

3-rds-launch-instance

(click to zoom)

3-rds-launch-instance

c.  You now have options to select your engine. For this tutorial, click the PostgreSQL icon and then click Select next to PostgreSQL.

4-rds-select-postgresql

(click to zoom)

4-rds-select-postgresql

d. The next screen gives you options to choose your environment. Select the PostgreSQL option under Dev/Test and click Next Step.

4-rds-dev-test

(click to zoom)

4-rds-dev-test

e. You will now configure your DB instance. To avoid charges, select the checkbox next to 'Only show options that are eligible for RDS Free Tier'. The list below shows the example settings you can use for this tutorial:

Instance Specifications:

  • License Model: postgresql-license
  • DB Engine Version: Select the default version.
  • DB Instance Class: Select db.t2.micro --- 1vCPU, 1 GIB RAM. This equates to 1 GB memory and 1 vCPU. To see a list of supported instance classes, see Amazon RDS Product Details.
  • Multi-AZ Deployment: Select No to create your DB instance in a single Availability Zone for this tutorial. Using a Multi-AZ Deployment will automatically provision and maintain a synchronous standby replica in a different Availability Zone. Note that you will have to pay for Multi-AZ Deployment. For more information, see High Availability Deployment
  • Storage Type: Select General Purpose (SSD). For more information about storage, see Storage for Amazon RDS.
  • Allocated Storage: Type 20 to allocate 20 GB of storage for your database. You can scale up to a maximum of 244 GB with Amazon RDS.

Settings:

  • DB Instance Modifier: Type a name for the DB instance that is unique for your account in the Region you selected. For this tutorial, we will name it rds-postgresql-10minTutorial.
  • Master Username: Type a username that you will use to log in to your DB instance. We will use masterUsername in this example.
  • Master Password: Type a password that contains from 8 to 41 printable ASCII characters (excluding /,", and @) for your master user password.
  • Confirm Password: Retype your password.

Click Next Step.

5-rds-details

(click to zoom)

5-rds-details

f. You are now on the Configure Advanced Settings page where you can provide additional information that RDS needs to launch the PostgreSQL DB instance. The list below shows settings for our example DB instance.

Network & Security
  • Publicly Accessible: Choose Yes. This will allocate an IP address for your database instance so you can directly connect to the database from your own device.
  • VPC Security Group(s): Select Create New Security Group. This will create a security group that will allow connection from the IP address of the device you are currently using, to the database created.

Database Options

  • Database Name: Type a database name that is 1 to 64 alpha-numeric characters. If you do not provide a name, Amazon RDS will not automatically create a database on the DB instance you are creating.
  • Database Port: Leave the default value of 5432.
  • Option Group: Select the default value of default.postgres-9.4. Amazon RDS uses option groups to enable and configure additional features.  For more information, see Working with Option Groups.

Backup

  • Backup Retention Period: You can choose the number of days to retain the backup you take. For this tutorial, set this value to 1.
  • Backup Window: Use the default of No Preference.

Monitoring

  • Enable Enhanced Monitoring: Use the default of No to stay within the free tier. Enabling Enhanced Monitoring will give you metrics in real time for the operating system (OS) that your DB instance runs on. For more information, see Viewing DB Instance Metrics.

Maintenance

  • Auto Minor Version Upgrade: Select Yes to receive automatic updates when they become available.
  • Maintenance Window: Select No Preference.

Click Launch DB Instance.

ms-sql-7

(click to zoom)

ms-sql-7

g. Your DB Instance is now being created.  Click View Your DB Instance.

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.

ms-sql-8

(click to zoom)

ms-sql-8

Once 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 use 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.

  • PC/Linux

    a. Go to the SQL Workbench website and click Download generic package for all systems

    Note: To launch the application use the .exe file included in the download folder.

     

    8-rds-download-sql-workbench

    (click to zoom)

    8-rds-download-sql-workbench

    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.

    8-rds-download-driver

    (click to zoom)

    8-rds-download-driver
  • Mac

    a. Go to the SQL Workbench website and click Download package for MacOS

    8-rds-download-sql-workbench

    (click to zoom)

    8-rds-download-sql-workbench

    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.

    8-rds-download-driver

    (click to zoom)

    8-rds-download-driver

In this step, we will connect to the database you created using SQL Workbench.


a. Once you have completed your download, install and open the program. A dialogue box appears.  Enter the following:

  • Driver: PostgreSQL

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 dialogue 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/dataBaseNameHere.
  • Username: Type in the username you created for the Amazon RDS database. Our example is 'masterUsername.'
  • Password: Enter the password you used while creating the Amazon RDS database. 

Click OK.

 

9-rds-sql-connection-profile

(click to zoom)

9-rds-sql-connection-profile

b. You are now connected to the database. In the instances tab of the RDS Dashboard you should see that there is "1 Connection" to your database listed under the "Current Activity" column. 

Note: At this point your database is ready to use. You can start creating tables, insert data, and run queries from SQL Workbench client. 

10-rds-connection

(click to zoom)

10-rds-connection

You can easily delete the PostgreSQL DB Instance from the Amazon RDS console. In fact, it is a best practice to delete instances you are no longer using so you don’t keep getting charged for them.


a. Go back to your Amazon RDS Console.  Select Instance Actions and click Delete from the dropdown menu.

 

11-rds-delete

(click to zoom)

11-rds-delete

b. You are asked to create a final snapshot. For our example, select No in the drop-down, check the acknowledgment box, and click Delete. 

    Note: Deleting your DB Instance may take a few minutes.

12-rds-confirm-delete

(click to zoom)

12-rds-confirm-delete

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.

Create a Web Server and an Amazon RDS Database »

Yes
No