How to Install & Configure PostgreSQL on Amazon Linux

nstall & Configure PostgreSQL on Amazon Linux

If you want to install PostgreSQL on Amazon Linux (AWS ec2), this guide is for you. It covers installation, configuration, and enabling remote connection.

Install PostgreSQL on Amazon Linux

Follow the steps given below to install the latest version of PostgreSQL on RedHat Linux

Step 1: Let’s upgrade the system Yum packages.

sudo yum update -y

Step 2: PostgreSQL is part of the amazon extras library. Install the PostgreSQL amazon extras repository. At the time of writing, PostgreSQL 14 is the latest package available in the extras library.

sudo amazon-linux-extras enable postgresql14

Step 3: Install the PostgreSQL server.

sudo yum install postgresql-server -y

Step 4: Initialize the DB.

sudo postgresql-setup initdb

Step 5: Add the PostgreSQL service to the system startup.

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 6: Check the status of PostgreSQL using the following command.

sudo systemctl status postgresql

Set Password For Postgres User

Now, let’s set a password for the default Postgres user and secure it.

First login to the database using the following command.

sudo -u postgres psql

Set the password for the Postgres user so that we can use it to log in remotely. Replace myPassword with the required password.

ALTER USER postgres PASSWORD 'myPassword';

Enable Remote Connection For PostgreSQL on Amazon Linux

By default the remote PostgreSQL connection is disabled. You need to add the following configuration to enable remote connectivity.

Open the postgresql.conf file in the vi editor.

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

Locate the line that starts with “listen_addresses“. Uncomment and change it to “listen_addresses = ‘*’“. This will allow connections from any IP address.

Next, open /var/lib/pgsql/data/pg_hba.conf file

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

Add the following to the end of the file to allow client connections to all databases.

host    all          all            0.0.0.0/0  md5

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

sudo systemctl restart postgresql

Now, in the ec2 security group of the Amazon Linux ec2 server, allow incoming traffic on port 5432, which is the default port used by PostgreSQL

Important PostgreSQL Server Configurations on Amazon Linux

The following table contains important PostgreSQL configurations on the Amazon Linux ec2 server.

ConfigDetails
PostgreSQL default port5432
Default userpostgres
Config files location (postgresql.conf & pg_hba.conf )/var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/pg_hba.conf
Default databasepostgres
Default data directory/var/lib/pgsql/data

Conclusion

In this guide, we looked at the steps to install PostgreSQL on an Amazon Linux ec2 server.

For the Ubuntu server, Checkout the PostgreSQL installation on Ubuntu.

For the Redhat server, Checkout the PostgreSQL installation on Redhat

Also, if you are using Kubernetes, check out the guide on setting up PostgreSQL statefulset on Kubernetes

If you are starting your DevOps engineer journey, look at the comprehensive guide to becoming a DevOps engineer.

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like