How to Install PostgreSQL on AlmaLinux 9

How to Install PostreSQL on AlmaLinux 9

PostgreSQL is an object-relational database management system written in C and C++, also known as Postgres. It can store structured and unstructured data in a single product.

This tutorial will guide you through the simple steps of installing PostgreSQL on AlmaLinux 9 OS.

Prerequisites

  • A server with AlmaLinux 9 as OS
  • User privileges: root or non-root user with sudo privileges

Step 1. Update the System

After a fresh installation of AlmaLinux, we need to update the packages to the latest versions available:

dnf update -y && sudo dnf upgrade -y

Step 2. Install PostgreSQL

The latest PostgreSQL version 14 is not available in the default repository of AlmaLinux 9, so we need to add  the official PostgreSQL Repository for the AlmaLinux system.

Let us Install the repository RPM with the following command:

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Then, you need to update your local package index with the following command:

dnf update -y

At this point, you can install the PostgreSQL  server with the following command:

dnf install postgresql14 postgresql14-server

Once the installation is complete, initialize the PostgreSQL database with the following command:

/usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

Start and enable the PostgreSQL service.

systemctl enable postgresql-14 && sudo systemctl start postgresql-14

Verify your PostgreSQL installation by checking its version:

psql -V
Output
psql (PostgreSQL) 14.5

Check if the service is up and running:

systemctl status postgresql-14

You should receive the following output:

● postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
     Active: active (running) since Fri 2022-09-23 22:21:44 CEST; 1min 25s ago
       Docs: https://www.postgresql.org/docs/14/static/
   Main PID: 26248 (postmaster)
      Tasks: 8 (limit: 5738)
     Memory: 17.1M
        CPU: 154ms
     CGroup: /system.slice/postgresql-14.service
             ├─26248 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
             ├─26249 "postgres: logger "
             ├─26251 "postgres: checkpointer "
             ├─26252 "postgres: background writer "
             ├─26253 "postgres: walwriter "
             ├─26254 "postgres: autovacuum launcher "
             ├─26255 "postgres: stats collector "
             └─26256 "postgres: logical replication launcher "

Another way to check about the PostgreSQL service and port is to run the command below:

netstat -tunlp | grep 5432

You should receive the following output:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      26248/postmaster

Step 3: Set the PostgreSQL user password

You can change or create the user password for PostgreSQL. Using the following command, you can change the default user password for PostgreSQL:

passwd postgres

The prompt will ask you to enter the new password and then again retype the new password.

# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

A confirmation notification will be displayed ‘all authentication tokens updated successfully.’

After successfully changing the new password, now, on each PostgreSQL access, you need to enter a new reset password for confirmation.

Step 4: Allow Remote Access

By default, PostgreSQL is configured to “localhost”.

To allow remote access so PostgreSQL can be accessed from everywhere,  you need to open the /var/lib/pgsql/14/data/postgresql.conf file and replace the line:

listen_addresses = 'localhost'

with

listen_addresses = '*'

Then you need to edit the /var/lib/pgsql/14/data/pg_hba.conf file and add the following entry at the end of the file.

host    all             all             0.0.0.0/0                md5

Save the file, close it, and restart the PostgreSQL service for the changes to take effect.

systemctl restart postgresql-14

Step 5: Accessing the PostgreSQL console

To access the PostgreSQL console execute the following command on your server:

sudo -u postgres psql

You should receive the following output:

psql (14.5)
Type "help" for help.

postgres=#

If you want to see details of the connection, use the command:

\conninfo

The output displays the database name, the account you are logged in to, the socket path, and the port number.

Output:
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

You can create a new user and database using the following command:

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# CREATE USER testuser WITH ENCRYPTED PASSWORD 'Strong_Password';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
GRANT

You can list the databases with the “\l” command, and the output will be similar to the output below:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | testuser=CTc/postgres

(4 rows)

To quit from the PostgreSQL shell, just type “\q“.

That’s it. You successfully installed and configured the latest PostgreSQL 14 version on AlmaLinux 9. You can check the official PostgreSQL documentation to learn more about PostgreSQL installation.

Of course, you don’t have to install PostgreSQL on AlmaLinux 9 if you use one of our PostgreSQL VPS Hosting plans, in which case you can simply ask our expert Linux admins to install PostgreSQL 14 on AlmaLinux 9 for you. They are available 24×7 and will take care of your request immediately.

If you liked this post on how to install PostgreSQL on AlmaLinux 9, please share it with your friends on social networks or simply leave a reply below. Thanks.

Leave a Comment

To prove you are human please solve the following *