How to Install PostgreSQL on AWS Linux

PostgreSQL is a powerful, open-source object-relational database system known for its robustness, scalability, and performance. If you’re running an AWS Linux instance and looking to set up PostgreSQL, you’ve come to the right place. This guide will walk you through the process step by step.

Prerequisites

Before we begin, ensure that you have:

• An AWS EC2 instance running Amazon Linux AMI.
• Administrative rights on the instance.
• At least 1GB of free disk space, 2GB of RAM, and a single-core CPU.

Step 1: Update the System

sudo yum update

This command updates the system’s package index. It ensures that all the software on the system is up-to-date with the latest patches and improvements.

Step 2: Install PostgreSQL

sudo dnf install postgresql16-server postgresql16

The dnf command installs the PostgreSQL database server and its dependencies. Here, postgresql16 and postgresql15-server are the specific packages for PostgreSQL version 16.

Step 3: Initialize the Database

sudo postgresql-setup --initdb

This initializes the PostgreSQL database cluster, creating the default directory structure and settings.

Step 4: Start and Enable PostgreSQL Service

sudo systemctl start postgresql
sudo systemctl enable postgresql

These commands start the PostgreSQL service and enable it to start automatically at boot time.

Step 5: Set PostgreSQL User Password

sudo passwd postgres

This command sets a password for the postgres user, which is the default administrative user for PostgreSQL.

Step 6: Switch to the PostgreSQL User

su - postgres

This switches the current session to the postgres user.

Step 7: Change PostgreSQL User Password (Optional)

psql -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

This command changes the password for the postgres user within the PostgreSQL environment. The newpassword replace it with your desired password.

Step 8: Enable Remote Connection

Edit the postgresql.conf file to allow connections from any IP address:

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

Locate the line that starts with listen_addresses. Uncomment it by removing # and change it to listen_addresses = '*'. Also, uncomment the port = 5432.

Step 9: Configure Host-Based Authentication

sudo nano /var/lib/pgsql/data/pg_hba.conf

Add the following line to allow MD5-based authentication from any IP:

host    all          all            0.0.0.0/0  md5

Step 10: Restart PostgreSQL Service

sudo systemctl restart postgresql

This restarts the PostgreSQL service to apply the changes made to the configuration files.

Step 11: Create a New User and Database

createuser myfirstuser
createdb myfirstdb

These commands create a new user myfirstuser and a new database myfirstdb. You can change these whatever you need.

Step 12: Connect to the Database

psql -U myfirstuser -d myfirstdb

This command connects to the myfirstdb database as the myfirstuser user.

PostgreSQL Important Server Configurations on Amazon Linux

Final Thoughts

By following these steps, you can successfully install and configure PostgreSQL on an AWS Linux server. Each command plays a crucial role in setting up the environment, securing access, and preparing the database for use.

Remember to replace newpassword, myfirstuser, and myfirstdb with your actual desired password, user name, and database name respectively.