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

2. Configure the instance name and the OS image as follows

3. Scroll down and configure the instance type and key pair

4. Create a security group

5. Modify to disk size as 20 GB

6. Check the Summary and click Launch instance (right pane) under the Summary section to launch your EC2 instance.

7.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.

Step 2: Update Amazon Linux 2023 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

Step 3: Installing PostgreSQL

The good news is that you don’t need to add any additional repositories to get PostgreSQL version 15 on your Amazon Linux 2023 because it is available through the default system repository. For a comprehensive list of packages available in the Amazon Linux 2023 repositories, you can refer to this link.

Run the following command to install both the client and server components of the PostgreSQL database system:

sudo dnf install postgresql15.x86_64 postgresql15-server -y

Step 4: 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

Step 5: 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

Step 6: 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

2. Primary Configuration File

Access the primary configuration file of PostgreSQL located at /var/lib/pgsql/data/postgresql.conf. Before making any changes, back up the configuration file:

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;

3. 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 vi /var/lib/pgsql/data/pg_hba.conf

# You can change ident as md5 To allow connections from absolutely any address with password authentication
host     all     all     0.0.0.0/0     md5

OR

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

4. 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

Step 7: 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

How to Uninstall PostgreSQL 15 from Amazon Linux 2023

NOTE: Backup Your Data (Important)

Before proceeding, it’s crucial to back up any data you want to keep, such as databases and configuration files. Uninstalling PostgreSQL will remove these files, and data loss is irreversible.

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.