In this blog post, we’ll go through the steps of installing PostgreSQL on an EC2 instance running Ubuntu 22.04, creating a user with superuser privileges, and importing a SQL dump file into the PostgreSQL database.

Installing PostgreSQL on EC2

SSH into your EC2 instance: Use SSH to connect to your Ubuntu 22.04 EC2 instance:

ssh -i yourkey.pem ubuntu@your-instance-ip

Update Package Index: Before installing PostgreSQL, update the package index:

sudo apt update

Install PostgreSQL: Install PostgreSQL and its contrib package:

sudo apt install postgresql postgresql-contrib

Check PostgreSQL Service Status: Verify that PostgreSQL is running:

systemctl status postgresql

Connect to PostgreSQL: Access PostgreSQL:

sudo -u postgres psql

Creating a User with Superuser Privileges

To create a user with superuser privileges, follow these steps:

Connect to PostgreSQL: If not already connected, access PostgreSQL:

sudo -u postgres psql

Create a user and grant superuser privileges:

CREATE USER MY_USER WITH PASSWORD 'my_password' SUPERUSER;

Exit PostgreSQL: Exit the PostgreSQL session:

\q

Importing a SQL Dump

To import a SQL dump file into PostgreSQL, use the following steps:

Connect to PostgreSQL: SSH into your EC2 instance and connect to PostgreSQL:

ssh ubuntu@your-instance-ip
sudo -u postgres psql

Create Database: If the database doesn’t exist, create it:

CREATE DATABASE your_database_name;

Import Dump: Import the SQL dump file into the database:

pg_restore --dbname=your_database_name /path/to/your/dumpfile.sql

Verify Import: Verify that the import was successful by querying the database tables:

\c your_database_name
\dt

Exit PostgreSQL: Exit the PostgreSQL session:

\q

Sumup

By following these steps, you can easily set up PostgreSQL on an EC2 instance, create users with superuser privileges, and import SQL dump files into your database. PostgreSQL is a powerful and reliable relational database management system that can support various applications and use cases.