Setting Up PostgreSQL on an EC2 Instance Running Ubuntu

Introduction:
In this guide, we’ll take you through the steps to set up PostgreSQL on an Amazon EC2 instance running Ubuntu. PostgreSQL is a robust open-source relational database management system that’s suitable for a wide range of applications. Additionally, we’ll address common connection errors and how to allow remote access to PostgreSQL.

Prerequisites:
Before we begin, ensure you have the following:
- An Amazon EC2 instance running Ubuntu.
- SSH access to the EC2 instance.
- Basic familiarity with the Linux command line.

Step 1: SSH Connection:
To start, establish an SSH connection to your EC2 instance using the following command:

ssh -i your-key.pem ubuntu@your-ec2-instance-ip

Step 2: Update Package List:
Keeping your system up-to-date is essential. Run the following command to update the package list:

sudo apt update

Step 3: Install PostgreSQL:
Use the apt package manager to install PostgreSQL. Enter this command:

sudo apt install postgresql postgresql-contrib

Step 4: Start and Enable PostgreSQL:
Ensure that PostgreSQL is running and set to start on boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 5: Set Password for PostgreSQL Superuser:
Set a password for the postgres superuser:

sudo -u postgres psql
ALTER USER postgres PASSWORD 'your-password';
\q

Step 6: Configure PostgreSQL Authentication (Optional):
To configure authentication, edit the pg_hba.conf file:

sudo nano /etc/postgresql/{version}/main/pg_hba.conf

Modify the file to suit your needs. For remote access, add the following lines:

host target_database target_user your_ip_address/32 md5

- host: Specifies host-based authentication.
- target_database: The name of your target database.
- target_user: The name of your target user.
- your_ip_address/32 : The IP address you want to allow connections from. Use /32 for a single IP address.
- md5: Indicates password-based authentication.

Step 7: Update PostgreSQL Configuration for Remote Access:
To allow remote access to PostgreSQL, edit the postgresql.conf file:

sudo nano /etc/postgresql/{version}/main/postgresql.conf

Set listen_addresses to either of the following options:

listen_addresses = '*' # Listens on all available network interfaces

or

listen_addresses = 'your-ec2-instance-ip' # Listens on the public IP address of your EC2 instance

Step 8: Restart PostgreSQL:
Apply the changes by restarting PostgreSQL:

sudo systemctl restart postgresql

Resolving Connection Errors:
If you encounter the error message “FATAL: no pg_hba.conf entry for host,” edit the pg_hba.conf file as previously mentioned.

Conclusion:
You’ve now successfully set up PostgreSQL on your EC2 instance, configured remote access, and addressed common connection issues. PostgreSQL is ready for use in your applications, and you can securely connect to it from your local machine or other remote locations.

Always prioritize security by using strong passwords and restricting access as needed for your specific use case.