Aurora PostgreSQL Database with EC2

Launch an Aurora PostgreSQL cluster and connect to it from EC2 using SQL.

Introduction

30 Second Summary

Every app you use stores data somewhere, from your bank balance to your streaming watchlist. Behind the scenes, a database keeps all of that organized and instantly accessible.

In this project, you will create an Aurora PostgreSQL database on AWS, launch an EC2 instance to connect to it, and write real SQL queries against a live database.

What You'll Build

You will SSH into a virtual machine on AWS, open a PostgreSQL command line, and watch your SQL queries pull back real data from a database you created from scratch.

By the end of this project, you'll have:

  • A running Aurora PostgreSQL database cluster you built from scratch, with a writer instance handling your data inside a VPC.
  • A live SQL session where you type commands, create tables, insert rows, and see query results come back from an EC2 terminal.
  • A real networking failure you diagnose and fix, proving you understand how security groups control database access on AWS.
  • Secret Mission: Store and query JSON data using PostgreSQL's JSONB data type, a superpower that sets PostgreSQL apart from MySQL.

Are there any prerequisites?

You need an AWS account with an IAM admin user already set up.

This project uses Aurora PostgreSQL provisioned instances, which are not covered by the AWS Free Tier. Expect under $0.50 if you delete all resources within one hour of completing the project.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Launch Your EC2 Instance

Your Aurora PostgreSQL database will live inside a private network called a VPC. You can't connect to it directly from your laptop because it sits in an isolated cloud environment.

To reach it, you need a virtual machine running inside that same network. In this step, you'll launch an EC2 instance to act as your database workstation.

In this step, get ready to:
  • Launch an EC2 instance named nextwork-aurora-pg with Amazon Linux 2023.
  • Note down the connection details you'll need for SSH access later.
Create the EC2 instance
  • In the AWS Management Console, type EC2 in the search bar at the top and select EC2.
  • Check which AWS Region is shown in the top-right corner of the console and record it here: your AWS Region (e.g. us-east-1). All resources in this project must be in the same Region.
  • Click Launch instances.
  • Under Name and tags, enter nextwork-aurora-pg.
  • Under Application and OS Images (AMI), confirm Amazon Linux 2023 AMI is selected.
  • Under Instance type, select t2.micro.

What is an AMI?

An AMI (Amazon Machine Image) is a pre-configured template for your virtual machine. It determines which operating system and software your instance starts with.

Amazon Linux 2023 is a lightweight Linux distribution optimized for running on AWS. It comes with tools you'll need to install a database client later.

  • Under Key pair (login), click Create new key pair.
  • Enter nextwork-aurora-pg-key for the Key pair name.
  • Select RSA for Key pair type.
  • Select .pem for Private key file format.
  • Click Create key pair.

A file named nextwork-aurora-pg-key.pem downloads to your computer automatically. You'll use this file to SSH into your instance later.

What is a key pair?

A key pair is a set of cryptographic keys that lets you securely connect to your EC2 instance. The .pem file you just downloaded is the private key. AWS keeps the matching public key on the instance.

Keep the .pem file somewhere safe. If you lose it, you won't be able to SSH into your instance.

  • Under Network settings, select the checkbox for Allow SSH traffic from.
  • In the source dropdown next to it, select My IP. If your IP address changes frequently, select Anywhere instead.
  • Select the checkbox for Allow HTTP traffic from the internet.
  • Leave all other settings as their defaults.
  • Click Launch instance.

What do these network settings do?

These checkboxes create rules in your instance's security group, which acts as a virtual firewall. The SSH rule opens port 22 so you can connect from your terminal. The HTTP rule opens port 80 for web traffic.

Restricting SSH to My IP means only your current IP address can connect. This is more secure than allowing access from anywhere.

Confirm the instance is running

The console shows a success message after launch. Now you need to verify the instance is actually running and record the details you'll need later.

  • Click View all instances to go to your instances list.
  • Click on the instance name nextwork-aurora-pg to open its details.
  • In the Details tab, check that Instance state shows Running.

If the state shows Pending, wait a minute or two and refresh the page.

  • Find the Public IPv4 DNS value in the Details tab and record it here: your EC2 Public IPv4 DNS.
  • Confirm the Key pair name in the details shows nextwork-aurora-pg-key.

Instance not showing as Running?

  • Make sure you're looking at the correct Region in the top-right corner of the console. Your instance only appears in the Region where you created it.
  • Try refreshing the page. New instances can take 1-2 minutes to move from Pending to Running.
  • If the instance shows Terminated, your account may have a configuration issue. Help me troubleshoot my terminated EC2 instance.

Your database workstation is ready. Next up, you'll create the Aurora PostgreSQL cluster that this instance will connect to.

Create Your Aurora PostgreSQL Cluster

Your EC2 instance is up and running. Now you need a database for it to connect to.

In this step, you'll create an Aurora PostgreSQL database cluster using the RDS console. You'll also make one deliberate choice during setup that creates a networking challenge you'll solve in the next step.

In this step, get ready to:
  • Create an Aurora PostgreSQL database cluster in the RDS console.
  • Configure the instance class and deliberately skip EC2 connectivity.
  • Explore the cluster's architecture and note the writer endpoint.

This step will cost money

Aurora PostgreSQL provisioned instances are NOT covered by the AWS Free Tier. Expect to pay under $0.50 if you delete all resources within one hour of completing this project. You will delete everything in the Before You Go section.

Create the Aurora PostgreSQL DB cluster
  • In the AWS Console search bar at the top, type RDS and select Amazon RDS.
  • Confirm you are in the same AWS Region as your EC2 instance from the previous step.
  • Choose Databases in the left navigation.
  • Choose Create database.
  • Select Standard create.
  • For Engine type, select Aurora (PostgreSQL Compatible).

What is Aurora PostgreSQL?

Amazon Aurora is a managed relational database service built for the cloud. The PostgreSQL-Compatible edition gives you the performance and availability of Aurora with the familiar tools and SQL syntax of PostgreSQL.

You're using Standard create instead of Easy create so you have full control over the networking and instance settings.

  • Under Templates, choose Dev/Test.
  • For DB cluster identifier, enter nextwork-pg-cluster.
  • Leave Master username as postgres.
  • Under Credentials management, select Self managed.
  • Enter a master password you will remember (for example, n3xtw0rk).
  • Save this password somewhere safe. You will need it to connect to your database later.
Configure instance and connectivity

You are still on the Create database page. Scroll down to configure the instance size and networking.

  • Under Instance configuration, select Burstable classes (includes t classes).
  • Select db.t3.medium.
  • In the Connectivity section, for Compute resource, select Don't connect to an EC2 compute resource.
  • Scroll down and open the Additional configuration section.
  • For Initial database name, enter sample.
  • Leave all other settings as their defaults.
  • Choose Create database.

Why did you skip EC2 connectivity?

The Create database page offers to automatically set up connectivity between your cluster and an EC2 instance. You chose Don't connect to an EC2 compute resource on purpose.

In the next step, you'll see exactly what this means when you try to reach your database from your EC2 instance.

Your cluster is now being created. This can take 5-10 minutes.

  • Stay on the Databases page and wait for the cluster status to change from Creating to Available.

Cluster stuck on Creating?

Aurora clusters typically take 5-10 minutes to become available. If the status has not changed after 15 minutes, refresh the page.

If the cluster shows a Failed status, delete it and try creating it again. Make sure you selected Dev/Test as the template.

My Aurora PostgreSQL cluster is stuck on Creating status. What should I do?

Once the status shows Available, you should see your cluster nextwork-pg-cluster with a writer instance and a reader instance listed underneath it.

Explore your new cluster
  • Select the cluster name nextwork-pg-cluster in the Databases list.

You'll see the cluster detail page with its instances listed underneath.

What are writer and reader instances?

Aurora automatically creates two instances inside your cluster. The writer instance handles all data changes (inserts, updates, deletes). The reader instance is a standby that can serve read-only queries.

This architecture gives you a built-in backup. If the writer goes down, Aurora can promote the reader to take over.

  • Select the Connectivity & security tab.
  • Find the Writer endpoint and note it down: your cluster writer endpoint

What the endpoint looks like

The writer endpoint looks something like nextwork-pg-cluster.cluster-abc123.us-east-1.rds.amazonaws.com. Copy the full value. You will paste it into a connection command in the next step.

Your Aurora PostgreSQL cluster is live and waiting for connections. In the next step, you'll SSH into your EC2 instance and try to connect to this database. Will it work on the first try?

Connect to Your Database from EC2

Your Aurora PostgreSQL cluster is live and your EC2 instance is running. Time to bring them together.

Your EC2 instance and Aurora cluster sit in the same VPC, but they can't communicate yet. In this step, you'll SSH into your EC2 instance, install the PostgreSQL client, and work through the networking to establish a live database connection.

In this step, get ready to:
  • SSH into your EC2 instance and install the PostgreSQL client.
  • Hit and diagnose a security group timeout.
  • Fix the networking and connect to your live database.
SSH into EC2 and attempt the connection

To interact with your Aurora database, you need to be inside the same network. Your EC2 instance lives in the same VPC as your cluster, so it's your database workstation.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Navigate to your key file and set its permissions by running these commands:
cd ~/Downloads
chmod 400 nextwork-aurora-pg-key.pem

What do these commands do?

  • cd ~/Downloads moves you to the folder where your key pair file downloaded.
  • chmod 400 restricts the file so only you can read it. SSH refuses to use a key file that other users can access.
  • SSH into your EC2 instance by running this command:
ssh -i nextwork-aurora-pg-key.pem ec2-user@[[EC2_PUBLIC_DNS="your Public IPv4 DNS"]]

What does this command do?

This command opens a secure shell connection to your EC2 instance. The -i flag points to your private key file, and ec2-user is the default username for Amazon Linux instances.

When prompted about the host fingerprint, type yes and press Enter.

You should see the Amazon Linux banner and a command prompt ending in $. You're now inside your EC2 instance.

Connection refused or permission denied?

  • If you see a permission denied error, make sure you ran chmod 400 on the correct .pem file. SSH will not accept a key file with open permissions.
  • If the connection times out, confirm that the Public IPv4 DNS matches what the EC2 console shows in the Details tab for your nextwork-aurora-pg instance.
  • help me troubleshoot my SSH connection to EC2
  • Update your instance packages and install the PostgreSQL client by running these commands:
sudo dnf update -y

You'll see a stream of update output. Wait for it to finish, then install the PostgreSQL 15 client.

  • Install psql by running:
sudo dnf install postgresql15 -y

You should see output ending with Complete! confirming the install succeeded.

Install command not working?

  • Make sure you are running the command inside your EC2 SSH session, not on your local machine.
  • If you see a package not found error, confirm your instance is running Amazon Linux 2023. Other Linux distributions use different package managers.
  • help me install psql on my EC2 instance

Everything is in place. You have SSH access, and the PostgreSQL client is installed. Time to connect to your database.

Before you run the next command, think about what you expect to happen. Your EC2 instance and Aurora cluster are in the same VPC. Will the connection go through?

  • Try connecting to your Aurora cluster by running this command:
psql --host=[[WRITER_ENDPOINT="your Writer endpoint"]] --port=5432 --dbname=sample --username=postgres

The connection hangs. Nothing happens for a long time, and eventually you see a timeout error. This is expected.

Why did the connection time out?

Even though your EC2 instance and Aurora cluster are in the same VPC, security groups act as virtual firewalls that control which traffic is allowed in and out. By default, the Aurora cluster's security group blocks all inbound traffic.

Your EC2 instance tried to reach the cluster on port 5432 (the PostgreSQL default), but no security group rule exists that allows this traffic. The connection attempt just sits there until it times out.

Fix the networking with Set up EC2 connection

The RDS console has a built-in action that automatically creates the security group rules you need. It adds matching security groups to both your EC2 instance and your Aurora cluster so they can communicate on port 5432.

  • Switch back to the RDS console in your browser.
  • Choose Databases in the left navigation.
  • Select your cluster nextwork-pg-cluster.
  • Open the Actions dropdown and choose Set up EC2 connection.
  • On the Set up EC2 connection page, select your EC2 instance nextwork-aurora-pg.
  • Choose Continue.

The Review and confirm page shows the security group changes RDS will make.

  • Review the proposed changes, then choose Confirm and set up.

What did RDS just do?

RDS created two new security groups behind the scenes:

  • rds-ec2-n is attached to your Aurora cluster. It has an inbound rule that allows traffic from your EC2 instance's security group on port 5432.
  • ec2-rds-n is attached to your EC2 instance. It has an outbound rule that allows traffic to your Aurora cluster's security group.

Together, these two rules open a two-way path between EC2 and Aurora on the PostgreSQL port. The same connection that timed out a minute ago will now succeed.

Reconnect successfully

With the security groups in place, let's try that connection again.

  • Switch back to your SSH terminal.
  • Run the same psql command again:
psql --host=[[WRITER_ENDPOINT="your Writer endpoint"]] --port=5432 --dbname=sample --username=postgres
  • Enter the master password you set when creating the cluster (e.g. n3xtw0rk).

You should see the sample=> prompt. You are connected to your Aurora PostgreSQL database.

What do you think will happen when you run your first SQL query? Will the database respond?

  • Verify the connection is live by running:
SELECT CURRENT_TIMESTAMP;

You should see the current date and time printed back. Your EC2 instance is talking to your Aurora PostgreSQL database.

Still seeing a timeout after the fix?

  • Make sure you ran Set up EC2 connection on the cluster nextwork-pg-cluster, not on an individual instance.
  • Confirm the EC2 instance you selected was nextwork-aurora-pg. If you selected a different instance, the security groups were applied to the wrong resource.
  • Double check that the Writer endpoint you pasted is correct. You can find it on the Connectivity & security tab of your cluster in the RDS console.
  • help me troubleshoot my psql connection to Aurora PostgreSQL

You have a live database connection from your EC2 workstation. In the next step, you'll create tables, insert real data, and run SQL queries against your Aurora PostgreSQL database.

Build a Sample Database with SQL

In the last step, you battled through a security group timeout and came out the other side with a live connection to your Aurora PostgreSQL database. Your sample=> prompt is open and waiting.

The database is empty right now. In this step, you'll learn the core SQL commands that make databases useful: creating a table, inserting rows, running queries, and updating data.

In this step, get ready to:
  • Create a table to store employee records.
  • Insert rows and query them with filters and aggregation.
  • Update data and verify your changes.
Create a table for employee records

Tables are the core structure of a relational database. Each table defines columns with specific data types, and every row you add becomes one record.

  • In your sample=> prompt, create the employees table by running this SQL command:
-- Create a table with four columns for employee records
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50),
  salary NUMERIC(10,2)
);

What does this SQL do?

  • id SERIAL PRIMARY KEY creates an auto-incrementing unique ID for each row. You never have to set this value manually.
  • VARCHAR(100) means a text string up to 100 characters. NOT NULL means the column is required.
  • NUMERIC(10,2) stores numbers with up to 10 digits total and 2 decimal places. Perfect for salary values.

You should see CREATE TABLE printed back as confirmation.

Seeing a syntax error?

  • Make sure every line inside the parentheses ends with a comma, except the last column definition (salary).
  • Check that the statement ends with a semicolon (;). Without it, psql waits for more input instead of executing.
  • If your prompt shows sample(> instead of sample=>, you have an unclosed parenthesis. Type ); to close it, then try again.

Help me fix a CREATE TABLE syntax error in psql

Insert data and query it

Your table exists, but it has zero rows. Time to fill it with data and then pull that data back out using queries.

  • Insert three employees into your table by running this command:
-- Add three employees across two departments
INSERT INTO employees (name, department, salary) VALUES
  ('Alice Chen', 'Engineering', 95000.00),
  ('Bob Martinez', 'Marketing', 72000.00),
  ('Carol Williams', 'Engineering', 105000.00);

What does this SQL do?

  • INSERT INTO employees tells PostgreSQL which table to add data to.
  • The column list (name, department, salary) specifies which columns you're filling. You skip id because SERIAL auto-generates it.
  • Each set of parentheses after VALUES is one row. You're inserting three rows in a single statement.

You should see INSERT 0 3 printed back, confirming three rows were added.

Seeing an error instead?

  • Check that every string value is wrapped in single quotes ('), not double quotes. PostgreSQL uses single quotes for text values.
  • Make sure each row of values is separated by a comma, except the last one.

Help me fix an INSERT statement error in psql

Now for the fun part. Before you run your first query, how many rows do you expect to see?

  • Query all employees by running:
SELECT * FROM employees;

You should see all three rows printed in a formatted table: Alice Chen, Bob Martinez, and Carol Williams, each with their department and salary.

  • Filter to only Engineering employees by running:
SELECT name, salary FROM employees WHERE department = 'Engineering';

What does WHERE do?

The WHERE clause filters rows so only those matching your condition come back. Here, you get Alice and Carol but not Bob, because Bob is in Marketing.

  • Calculate the average salary per department by running:
SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department;

What does GROUP BY do?

GROUP BY groups rows that share the same value in a column, then lets you run calculations on each group. AVG(salary) calculates the average salary within each department.

You should see two rows: one for Engineering (averaging Alice and Carol's salaries) and one for Marketing (Bob's salary alone).

Update and delete data

Querying is only half the picture. In a real database, data changes all the time. The UPDATE command lets you modify existing rows.

  • Give Bob a raise by running this command:
UPDATE employees SET salary = 78000.00 WHERE name = 'Bob Martinez';

What does this SQL do?

UPDATE changes values in existing rows. The SET clause names the column and its new value. The WHERE clause limits which rows get changed. Without WHERE, every row in the table would be updated.

You should see UPDATE 1 confirming one row was changed.

What salary do you expect Bob to have now? Predict it, then check.

  • Verify the update by running:
SELECT * FROM employees WHERE name = 'Bob Martinez';

Bob's salary now shows 78000.00 instead of the original 72000.00. Your UPDATE worked.

Bob's salary didn't change?

  • Check that your WHERE clause matches exactly: WHERE name = 'Bob Martinez'. The name is case-sensitive.
  • Make sure you used single quotes around Bob Martinez, not double quotes.

Help me fix my UPDATE statement in psql

  • Exit psql by running:
\q

You're back at your EC2 terminal prompt. Your data is safely stored in the Aurora database, even after you disconnect.

Secret mission

Store and Query JSON Data with JSONB

PostgreSQL has a superpower that sets it apart from MySQL: the JSONB data type. In this mission, you will create a table with a JSONB column, insert JSON documents, and query them using PostgreSQL-specific operators like ->, ->>, and @>.

Clean Up Your Resources

Clean Up Your Resources

This project uses a provisioned Aurora PostgreSQL cluster that is not covered by the AWS Free Tier. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Cost warning

Your Aurora PostgreSQL cluster uses a db.t3.medium instance class, which incurs charges for every hour it runs. If you are not continuing to another project immediately, delete all resources now to avoid unexpected costs.

Resources you used:

  • Aurora PostgreSQL DB cluster (nextwork-pg-cluster) with writer and reader instances.
  • EC2 instance (nextwork-aurora-pg).
  • EC2 key pair (nextwork-aurora-pg-key).

Keep everything running

No action needed. Choose this if you are continuing to the next project in this series today.

You will be charged

Your Aurora cluster charges for every hour it runs, even when idle. Only keep resources running if you plan to use them in the next few hours.

Pause - I'll come back to this later

You can stop your Aurora cluster and EC2 instance to reduce costs temporarily. This is not recommended for Aurora.

Aurora auto-restarts after 7 days

Aurora clusters automatically restart after 7 days of being stopped. Once restarted, charges resume immediately. If you are not coming back within a week, choose the Delete tab instead.

  • In the RDS console, select your cluster nextwork-pg-cluster.
  • Choose Actions, then Stop temporarily.
  • In the EC2 console, select your instance nextwork-aurora-pg.
  • Choose Instance state, then Stop instance.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is the recommended option to avoid any ongoing charges.

Delete the Aurora cluster
  • Open the RDS console and choose Databases.
  • Select the reader instance first.
  • Choose Actions, then Delete.
  • Enter delete me in the confirmation box, then choose Delete.
  • Wait for the reader instance to finish deleting.
  • Select the writer instance.
  • Choose Actions, then Delete.
  • Enter delete me in the confirmation box, then choose Delete.
  • After both instances are deleted, select the cluster nextwork-pg-cluster.
  • Choose Actions, then Delete.
  • Uncheck Create final snapshot.
  • Uncheck Retain automated backups.
  • Enter delete me in the confirmation box, then choose Delete.

Why delete instances before the cluster?

Deleting the reader first prevents Aurora from triggering an unnecessary failover. Once all instances are removed, the cluster itself and its storage volume can be deleted cleanly.

Delete the EC2 instance
  • Open the EC2 console and choose Instances.
  • Select nextwork-aurora-pg.
  • Choose Instance state, then Terminate instance.
Delete the key pair
  • In the EC2 console, choose Key Pairs in the left navigation.
  • Select nextwork-aurora-pg-key.
  • Choose Actions, then Delete.
  • You can also delete the nextwork-aurora-pg-key.pem file from your local Downloads folder.

Nice Work!

Nice Work!

You just built a live Aurora PostgreSQL database on AWS, connected to it from an EC2 instance, and ran real SQL against it. You also hit a genuine networking wall, diagnosed it, and fixed it yourself.

You've learned how to:

  • Create an Aurora PostgreSQL database cluster with a writer and reader instance in AWS, and connect to it from an EC2 instance using the psql command-line client.
  • Diagnose a security group misconfiguration that blocked your database connection, and fix it using the RDS Set up EC2 connection action.
  • Write SQL to create tables, insert rows, query with filters, update data, and delete rows in a live PostgreSQL database.
  • Secret Mission: Stored JSON documents in a JSONB column and queried them using PostgreSQL's ->, ->>, and @> operators.

Ready to quiz yourself?