How to Install Postgresql 14 on Ec2 Amazon linux 2

PostgreSQL, also widely known as Postgres, is a powerful but open-source relational database management system. It is widely used for web applications, data warehousing, and analytics due to its scalability and robust features. Amazon Linux 2, on the other hand, is a server Linux distro specifically designed to use on Amazon Web Services (AWS) cloud platform.

Here we will go through the steps and learn the commands to install PostgreSQL version 14 on Amazon Linux 2 server running locally or on AWS EC2.

Prerequisites:

Let’s first confirm, you have all the necessary things to follow this tutorial:

• Amazon Linux 2 server either on a local virtual machine, Docker, or AWS EC2 Instance.
• At least 1GB of free Hard disk space, 2GB of RAM, and a single-core CPU
• To install packages we need sudo or root user access
• Ofcourse, an active internet connection to download the PostgreSQL packages

Step 1: Download and Install Updates

The first step in the direction of installing PostgreSQL on Amazon Linux 2 is to make sure our server is up to date. Therefore, in your command terminal use the Yum and run the system update command. This will ensure we have the latest packages and up-to-date repository cache.

sudo yum update

Step 2: Adding Postgresql 14 Yum repository

We don’t need to add an extra Yum repository on Amazon Linux 2 to install PostgreSQL. Because it is available through the default system repository. But the version available using it is PostgreSQL 9, which is quite old. Therefore, those who are interested in Postgresql 14 need to add the PGDG14 repo manually using the command given below:

Copy the whole block of the given command and paste it as it is:

sudo tee /etc/yum.repos.d/pgdg.repo<<EOF

[pgdg14]

name=PostgreSQL 14 for RHEL/CentOS 7 - x86_64

baseurl=https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64

enabled=1

gpgcheck=0

EOF

After adding the repo, run the system update command, once again.

sudo yum update

Step 3: Installing Postgresql 14 on Amazon Linux 2

After adding the repo and updating the package repository, we can install the PostgreSQL package and its dependencies on Amazon Linux like any other software via YUM.

sudo yum install postgresql14 postgresql14-server

Step 4: Initialize the Database

We have followed all the necessary steps so far to have a working PostgreSQL Database on our Linux system. Now, before starting and enabling the service of the Database, let’s initialize it. For that use initdb which will create a new PostgreSQL database cluster refers to a collection of databases managed by a single server instance.

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

In return you will get:

Initializing database ... OK

Step 5: Start and Enable the PostgreSQL Service

If you got Initializing database ... OK that means everything is fine and we can start the Database server services, using:

sudo systemctl start postgresql-14

Well, we should also mark the service of PostgreSQL enabled, so that it can start atomically, if crashed or with system boot.

sudo systemctl enable postgresql-14

To confirm it is working without producing any error, use:

sudo systemctl status postgresql-14

Step 6: Configure PostgreSQL

Few things which a user would like to perform on its newly installed Linux database server.

1. Change the Admin database password

To make sure our PostgreSQL is secured with a strong password, set a password for its system user and default database admin user account using the below-given commands-

Change user password

sudo passwd postgres

Login using Postgres system account-

su - postgres

Now, change the Admin database password-

psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"

Note: Replace “your-password” in the above command with a secure password that you want to set for the admin database user.

2. Primary configuration file

So, far we have learned all the key things to make PostgreSQL up and running without any warnings in Amazon Linux. To perform any further configurations, users need to access the primary configuration file of PostgreSQL located at /var/lib/pgsql/"version"/data/postgresql.conf.

We can edit this file with text editors such as VIM or Nano.

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

Don’t arbitrarily change anything from this file because it may result in the malfunctioning of the Database server. It is good to take a backup of the file before performing any addition.

sudo cp /var/lib/pgsql/14/data/postgresql.conf /var/lib/pgsql/14/data/postgresql.conf.bak

Now, we have the backup, if something happens wrong we can use it. Well, there are several configuration options that you can set in this file, but we will focus on the most important ones.

4. Listen to the Address

By default, the PostgreSQL server will listen to only localhost. But, if you want it to answer for a specific IP address or range or all addresses, we can do that by changing the value of the listen_addresses option in the configuration file:

Find it and remove the # given in front of the option. After that add the address that you want the server to only listen to.

listen_addresses = 'localhost'

or

For all Ip-addresses use:

listen_addresses = '*'

To save the changes press Ctrl+O, and hit the Enter key. To exit use Ctrl+X.

5. Authentication

For Authentication, there is a separate file called “pg_hba.conf” apart from the Primary configuration file in the same directory.

Well, by default; PostgreSQL allows all DB users and hosts locally to connect to the database with a peer or scram-sha-256 method. If you want to change that, we can edit the pg_hba.conf file and set the authentication method to md5.

To edit this file, run the following command:

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

Find the line as given below and replace the scram-sha-256 with any other method you want to use such as md5.

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                      scram-sha-256

Note: Also to connect remotely using PSQL utility, change 127.0.0.1/32 to 0.0.0.0/0 and ::1/128 to ::0/0

How to Create a User & Database

Those who are new to this open-source database can use this section of the tutorial to create a new user and database on PostgreSQL.

1. First, connect to the PostgreSQL server as a postgres user by running the following command:

sudo -u postgres psql

2. Once you’re logged in, you can create a new Database user by running the given syntax:

Replace “your-username” with the user you want to create and password with the whatever you want to set for it.

CREATE USER your-username WITH PASSWORD 'password';

3. After creating the user, let’s create a new database by running the following command:

CREATE DATABASE database_name;

4. We have a new PostgreSQL user and Database, let’s grant all the rights of the created Database to our newly created user. So that it can access it to store data.

GRANT ALL PRIVILEGES ON DATABASE database_name TO your-username;

5. To check whether the database and user are created just run the given command and it will list all the available PostgreSQL users and Databases.

\l

Accessing the Database

Accessing the database you have created on the PostgreSQL server can be done using its client command – pslq

Locally or SSH server connection users, use the given syntax to connect the database:

psql -h localhost -U username -d database_name

Remote users after installing the PostgreSQL client can use:

psql -h server-ip-address --U username  -d database_name

Where username is the username we created earlier or you already have and database_name is the name of the database assigned to that particular user.

FAQs

sudo systemctl status postgresql

sudo systemctl start postgresql
sudo systemctl stop postgresql

sudo systemctl restart postgresql

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

pg_dump -U username my_DB > my-backup_file.sql