Welcome to our comprehensive guide on setting up a PostgreSQL database on an AWS Ubuntu instance. This blog will walk you through the process step-by-step, from launching your AWS instance to creating your first database and table. Let’s dive in!

Launching an AWS Ubuntu Instance

First things first, you need an AWS account. Once you’ve logged in, head over to the EC2 dashboard and launch a new instance. Select an Ubuntu Server AMI and choose an instance type – t2.micro works great for starters.

Remember to configure your security group to allow traffic on SSH (port 22) and the PostgreSQL default port 5432. Don’t forget to create or select a key pair for SSH access.

Connecting to Your Instance

After your instance is up and running, connect to it via SSH:

ssh -i /path/to/your-key.pem ubuntu@your-instance-public-dns

This command will get you into your brand-new Ubuntu server.

Installing PostgreSQL

Update your packages and then install PostgreSQL:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

sudo apt-get install postgresql

Configuring PostgreSQL

Switch to the default PostgreSQL user:

sudo -i -u postgres
psql

Creating a New Role and Database

You might want to create a new user role and a database:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';
ALTER ROLE myuser CREATEDB;

Then create a database:

CREATE DATABASE mydb OWNER myuser;

This sets up a new database mydb with myuser as its owner.

Configuring Network and Authentication

Edit the postgresql.conf file to set listen_addresses to '*'. This allows connections from any IP. Then, in pg_hba.conf, add host all all 0.0.0.0/0 md5 to enable connections.

Restart and Enable PostgreSQL

Restart the PostgreSQL service and enable it to start on boot:

sudo systemctl restart postgresql
sudo systemctl enable postgresql

Update AWS Security Group

Modify your security group to allow inbound traffic on port 5432.

Testing Connection

Test the connection from your local machine using a PostgreSQL client.

Creating Your First Table

Now, let’s create a simple table. Access your database:

\c mydb
CREATE TABLE items (
    item_id serial PRIMARY KEY,
    item_name VARCHAR (100),
    item_description TEXT
);

This command creates a table named items with three fields: item_id, item_name, and item_description.

Conclusion

You’ve now set up a PostgreSQL database on an AWS Ubuntu instance, configured it, and even created your first database and table. Remember, security and regular maintenance are crucial for a smooth operation. Happy data managing!