Creating a postgres database on Amazon Web Services(AWS) and connect with Django deployed to Heroku Server

PostgreSQL has become the preferred open-source relational database for many enterprise developers and start-ups, powering leading business and mobile applications. Amazon RDS makes it easy to set up, operate, and scale PostgreSQL deployments in the cloud. With Amazon RDS, you can deploy scalable PostgreSQL deployments in minutes with cost-efficient and resizable hardware capacity.

We will be creating and connecting a postgres database to our Django web app hosted on Heroku. You will learn how to create an environment to run your PostgreSQL database (we call this environment an instance). We will do this using Amazon Relational Database Service (Amazon RDS) and everything that was done in this tutorial is free-tier eligible.

Step 1: Enter the RDS Console

When you click here, the AWS management console will open in a new browser window. Find RDS under the database and click on it.

AWS management console

Step 2: Create a PostgreSQL DB Instance

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.) Click on the Create Database button and follow the below steps leaving the remaining defaults unchanged

b.) In the Engine Options, select PostgreSQL.

c.) In the Templates section, select Free tier.

d.) Create a Master username and Master password.

e.) Under the Connectivity, click on Additional connectivity configuration and set the Publicly accessible to Yes.

f.) In Additional configuration, enter an Initial database name.

g.) Scroll to the bottom and click on Create Database button.

Note: Depending on the DB instance class and storage allocated, it could take several minutes for the new DB instance to become available.

Step 3: AWS Security settings

a.) Once ready, click on the database to see its details. Make a note of the Endpoint URL and Port number(usually it is 5432) under the Connectivity & security.

b.) In the Security group rules section, click on the default group for Inbound type.

c.) Click on Actions > Edit inbound rules.

d.) Change the Type to PostgresSQL and the Source to Anywhere.

This concludes the AWS settings.

Step 4: Connect the above to PostgreSQL in our pgAdmin.

Prerequisites — PostgreSQL and pgAdmin should be installed your system.

You can now start using pgAdmin 4 in web mode at — http://127.0.0.1/pgadmin4

In the pgAdmin right click on Servers. Then click on Create.,then Server Group.

Servers> Create> Server Group

Enter the name of the server group and right-click on the created Server group. Then click on Create, then Server.

Server Group> Create> Server

Enter the Server name.

Under the Connection tab, in Host name enter the AWS endpoint URL, in the Port, Username, and Password section, enter the AWS Port(5432), Master username, and Master password we made a note of when creating the database in AWS. Click on the Save button.

Now the connection is made and the database is created, which can be accessed by clicking on database name> Schemas > Tables. (The tables would be empty as we have not migrated them).

Step 5: Configure the database in Django project settings

Install psycopg2

pip install psycopg2

Psycopg is the most popular PostgreSQL database adapter for the Python programming language.

Install dj-database-url

pip install dj-database-url

It helps to configure database URL environmental variables in Heroku.

Note: Update the two packages in the requirements.txt file

Add the below code under the DATABASES :

import dj_database_url

db_from_env = dj_database_url.config(conn_max_age=600)

DATABASES[‘default’].update(db_from_env)

Step 6: Setting up the database in Heroku.

Now, login to your Heroku account and click on your app. Click on Resources and in the Add-ons section, Delete the default database created by Heroku, as we would be pointing it to our AWS database.

Now, the resources tab will look like this after deleting the default database:

Now, go to the settings tab. Click on Reveal Config Vars.

Add a config var whose KEY is DATABASE_URL

and value is

postgres://USERNAME:PASSWORD@database_url_endpoint:PORT/DB_NAME

Enter your username, password, database URL endpoint, port no. generally 5432, and database name to this.

We can now migrate our tables by running the below command in our command prompt

heroku run python manage.py migrate

Create a superuser by running the command

heroku run python manage.py createsuperuser

Now the database is live and connected to Heroku app. Access the table using pgAdmin.

Software Engineer