Introduction

PostgreSQL is a powerful open-source relational database management system widely used for storing and managing data. If you’ve recently installed Amazon Linux 2023 on your AWS EC2 instance and need guidance on how to install PostgreSQL 15, you’re in the right place.

Prerequisites:

Before you start, ensure you meet the following prerequisites:

An AWS EC2 instance running Amazon Linux 2023 with administrator privileges.

A minimum of 1GB of available hard disk space, 2GB of RAM, and a single-core CPU.

Step 1- Launching and Configuring Your EC2 Instance

1. Log in to AWS services and select EC2.

You can click to “launch instance” button for create a EC2 instance

Configure the instance name and the OS image as follows

Scroll down and configure the instance type and key pair

Modify to disk size as 20 GB

Accessing Your EC2 Instance

Server is ready now select the server and click the connect button

Congratulations! You have successfully set up EC2 Instance Connect

Server is ready we can install the Postgresql . Please follow the below steps.

Update Amazon Linux 2024 Packages

Before installing any packages, it’s essential to update your system to ensure you have the latest updates and refresh the DNF package cache. Open your terminal or connect to your Amazon Linux instance via SSH and run the following command:

sudo dnf update

Installing PostgreSQL

sudo dnf install postgresql15.x86_64 postgresql15-server -y

Initializing PostgreSQL Database

Before starting and enabling the database service, let’s initialize it. Use the initdb command, which will create a new PostgreSQL database cluster referring to a collection of databases managed by a single server instance:

sudo postgresql-setup --initdb

Starting and Enabling PostgreSQL Service

After completing the initialization, start and enable the PostgreSQL server service, so it starts automatically with system boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Configure PostgreSQL

1. Set password for ssh postgres user and admin postgres database password

For security, set a strong password for the system user and default database admin user account. Use the following commands:

# Change the ssh user password:
sudo passwd postgres

# Log in using the Postgres system account:
su - postgres

# Now, change the admin database password:
psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"
exit

sudo cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.bck

Edit this file with a text editor:

sudo vi /var/lib/pgsql/data/postgresql.conf

By default, PostgreSQL only listens to localhost

listen_addresses = 'localhost'

if you want to listen all IP addresses:

listen_addresses = '*' # what IP address(es) to listen on;

Authentication

For authentication, there is a separate file called pg_hba.conf in the same directory as the primary configuration file.

Before making any changes, back up the configuration file:

sudo cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bck

Edit this file with a text editor:

sudo sed -i 's/ident$/md5/' /var/lib/pgsql/data/pg_hba.conf

To apply all the changes, restart the PostgreSQL service using the following command.

sudo systemctl restart postgresql

How to Create a User & Database

Use this section to create a new user and database on PostgreSQL:

# Connect to the PostgreSQL server as the Postgres user:
sudo -i -u postgres psql

# Create a new database user:
CREATE USER yourusername WITH PASSWORD 'password';

# Create a new database:
CREATE DATABASE database_name;

# Grant all privileges on the database to the user:
GRANT ALL PRIVILEGES ON DATABASE database_name TO yourusername;

# To list all available PostgreSQL users and databases:
\l

Accessing the Database

You can access the database you created using the PostgreSQL client command psql. Local or SSH server connection users can use the following syntax:

psql -h localhost -U username -d database_name

Remote users can use:

psql -h server-ip-address -U username -d database_name

Replace username with the user you created and database_name with the name of the database assigned to that user.

Access the postgres account on your server by typing:

sudo -i -u postgres

Now you can immediately access Postgres prompt by typing:

$ psql
postgres.

# To list the databases:
postgres=# \l

# You can exit Postgres prompt by typing:
postgres=# \q

To access your PostgreSQL database from external sources, make sure to configure your Amazon Linux EC2 security group to allow incoming traffic on port 5432, which is the default port used by PostgreSQL

#Step 1: Stop PostgreSQL Service

Before uninstalling PostgreSQL, ensure that the service is stopped:

sudo systemctl stop postgresql

Step 2: Disable PostgreSQL Service

Prevent PostgreSQL from starting at system boot:

sudo systemctl disable postgresql

Step 3: Remove PostgreSQL Packages

Use the following commands to remove PostgreSQL 15 packages. These are the same packages you installed during the installation process:

sudo dnf remove postgresql15.x86_64 postgresql15-server

Step 4: Completely Remove PostgreSQL

After removing the packages, delete all PostgreSQL-related configuration files and data using the following command:

sudo rm -rf /var/lib/pgsql /var/log/postgresql /etc/postgresql

Conclusion

With these steps, you have successfully uninstalled PostgreSQL 15 from your Amazon Linux 2023 instance. Make sure you have backed up any critical data before proceeding with the uninstallation process.

Congratulations Your PRoject is run ....

DELETE AND UNSTALL EC2 INSTANCE AND POSTGRESQL SERVER