Unknown Territory: Amazon Webservices, PostgreSQL, Python

Already a few months ago, I signed up for the Amazon Web Services (AWS) Free Tier. Unfortunately the free tier option expires after one year. So I decided to discover what's possible and get some reasons why AWS has become so popular.

It's easy to get overwhelmed by the huge variety of options the AWS web console provides. It's a good idea to start with the known inside the unknown like with a relational database. PostgreSQL was my favorite because it has a powerful database programming language similar to Oracle's PL/SQL. Here are the basic steps to get started:

• Create an AWS account seperate from the initial root account. The admin account should only be used for its purpose - administration. I also strongly recommend to set up a billing alert to avoid unwanted high charges.
• Inside the RDS (Relational Database Service) create a PostgreSQL database instance along with a VPC (Virtual Private Cloud) and a security group. Here's the user guide for MySQL but can be easily adapted to PostgreSQL. 
  
  For learning purposes, be sure to create a Free Tier eligible db.t2.micro instance!

It wanted to create an Amazon Aurora serverless database instance first, but they are not accessible from outside AWS as those instances cannot get a public IP address assigned. So it has to be a classical instance.

Once the instance is running with correctly configured network (VPC) and security group, it can be accessed with a database client software like DBeaver. The connection information for each instance is in the AWS web console under RDS - Databases.

Endpoint and Port will be entered as Host and Port in a new DBeaver PostgreSQL connection. Credentials are like provided at setup of the RDS Postgres database.

With this setup, you can access your cloud database directly from your computer. This connection is not secure by default! It is a good idea to limit the inbound traffic to your database to your local IP address in the related security group.

But now that we have everything in place, we can test and use our PostgreSQL database in the Amazon cloud. Let's start with a very simple table in a new schema named awsdemo.

CREATE TABLE awsdemo.newtable (
	id int4 NOT NULL,
	"name" varchar(100) NULL,
	CONSTRAINT newtable_pk PRIMARY KEY (id)
);

The result with a few rows inserted looks like this:

Now we can use the new database and DBeaver to do some PostgreSQL programming. But what about Python? This post is already pretty long, I will get to Python in the next one.