Install PostgeSQL on Amazon Linux

Install PostgreSQL on Amazon Linux

If you ever wondered about installation of PostgreSQL on Amazon Linux (AWS EC2), this guide is for you. It covers installation, configuration, and enabling remote connections.

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

Update yum cache and installed packages.

sudo yum update -y

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

Install PostgreSQL server:

sudo yum install -y postgresql-server

Initialize the DB:

sudo postgresql-setup initdb

Add the PostgreSQL service to the system startup:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Check the status of PostgreSQL using the following command:

sudo systemctl status postgresql
 

Set Password For Postgres User

Now, let’s set up 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 newP4assword with the required password.

ALTER USER postgres ENCRYPTED PASSWORD 'newP4ssword'

\q 

Fix Local Connections so PostgreSQL Doesn't Rely on Identd

PostgreSQL ships pre-configured to use identd for some reason, let's fix that

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

sudo -u postgres pg_ctl --pgdata=/var/lib/pgsql/data reload

Enable Remote Connections For PostgreSQL on Amazon Linux

By default remote PostgreSQL connections are 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, setup your EC2 security group to allow incoming traffic on port 5432, which is the default port used by PostgreSQL

Important PostgreSQL Server Configurations on Amazon Linux

The following list contains important PostgreSQL configurations on the Amazon Linux ec2 server:

PostgreSQL default port: 5432

Default user: postgres

Config files location (postgresql.conf & pg_hba.conf ): /var/lib/pgsql/data

Default database: postgres

Default data directory: /var/lib/pgsql/data

Created for you by Linuxage

Subscribe to our Telegram channel: https://t.me/linuxage

Join our Telegram chat: https://t.me/linux_age