On this tutorial we will look at how to install PostgreSQL 13 on CentOS 9|AlmaLinux 9|RHEL 9. PostgreSQL is a relational database management system that is based on POSTGRES 4.2. PostgreSQL 13 is considered to be the most current, open source and advanced database. With PostgreSQL 13, data and workloads whether small or big are better managed. Some of the improvements made with PostgreSQL 13 include, indexing and look up systems that benefit huge databases, better querying with enhanced statistics, and a faster query response time. It is much easier to work with other data types when it comes to PostgreSQL 13, it is also known to run on all major Linux operating systems. The guide below will provide you with the necessary insights on how to install PostgreSQL 13 on CentOS 9|AlmaLinux 9|RHEL 9.

The PostgreSQL team comes with pre-built packages namely YUM and APT repository for RHEL and Debian based systems respectively. Distributions supported include all Red Hat family namely Fedora, Scientific Linux, CentOS, RHEL, and Oracle Linux. Let us now embark on the installation process of PostgreSQL 13 on CentOS 9|AlmaLinux 9|RHEL 9.

Install PostgreSQL 13 on CentOS 9|AlmaLinux 9|RHEL 9 

Our article will not only cover how to install PostgreSQL on CentOS 9|AlmaLinux 9|RHEL 9, but also how to use it. On the use, we will cover how to change user password, how to create a test database, how to create a table and how to input data on the table as well as how to delete both the data and the table.

Step 1. Do a System Update

A system update is necessary so as to pull the latest updates on to your system. To do a system update on on your CentOS 9|AlmaLinux 9|RHEL 9 systems, you can run the following commands to do so;

sudo yum -y update
sudo systemctl reboot

Step 2. Add PostgreSQL YUM Repository to your System

The YUM repository is added for purposes of use by the CentOS and RHEL family. Often it serves the aim of providing automatic updates for all versions of PostgreSQL through their support lifetime.

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

When the above command is executed, below is the output;

[[email protected] ~]$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:00:32 ago on Wed 20 Jul 2022 11:22:30 AM EAT.
pgdg-redhat-repo-latest.noarch.rpm                                16 kB/s |  12 kB     00:00
Dependencies resolved.
=================================================================================================
 Package                     Architecture      Version             Repository               Size
=================================================================================================
Installing:
 pgdg-redhat-repo            noarch            42.0-24             @commandline             12 k

Transaction Summary
=================================================================================================
Install  1 Package

Total size: 12 k
Installed size: 12 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                         1/1
  Installing       : pgdg-redhat-repo-42.0-24.noarch                                         1/1
  Verifying        : pgdg-redhat-repo-42.0-24.noarch                                         1/1

Installed:
  pgdg-redhat-repo-42.0-24.noarch

Complete!

Check whether PostgreSQL 13 is available using the command shown below;

[[email protected] ~]$ sudo yum search postgresql13
Last metadata expiration check: 0:02:10 ago on Wed 20 Jul 2022 11:25:31 AM EAT.
============================== Name Exactly Matched: postgresql13 ===============================
postgresql13.x86_64 : PostgreSQL client programs and libraries
================================== Name Matched: postgresql13 ===================================
postgresql13-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql13-devel.x86_64 : PostgreSQL development header files and libraries
postgresql13-docs.x86_64 : Extra documentation for PostgreSQL
postgresql13-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql13-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql13-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql13-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql13-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql13-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql13-test.x86_64 : The test suite distributed with PostgreSQL

Step 3. Install PostgreSQL 13 on CentOS 9|AlmaLinux 9|RHEL 9 

Once you have confirmed that PostgreSQL 13 repositories are available on your system. Then, you can proceed to install PostgreSQL 13 on on CentOS 9|AlmaLinux 9|RHEL 9. To do so, run the command shown below on your terminal, below the command, there is a representation on how the output will look like;

sudo dnf install postgresql13 postgresql13-server

Check the result shown below;

[[email protected] ~]$ sudo dnf install postgresql13 postgresql13-server
Last metadata expiration check: 0:02:54 ago on Wed 20 Jul 2022 11:25:31 AM EAT.
Dependencies resolved.
=================================================================================================
 Package                      Architecture    Version                      Repository       Size
=================================================================================================
Installing:
 postgresql13                 x86_64          13.7-1PGDG.rhel9             pgdg13          1.4 M
 postgresql13-server          x86_64          13.7-1PGDG.rhel9             pgdg13          5.7 M
Installing dependencies:
 postgresql13-libs            x86_64          13.7-1PGDG.rhel9             pgdg13          780 k

Transaction Summary
=================================================================================================
Install  3 Packages

Total download size: 7.8 M
Installed size: 32 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql13-libs-13.7-1PGDG.rhel9.x86_64.rpm             621 kB/s | 780 kB     00:01
(2/3): postgresql13-13.7-1PGDG.rhel9.x86_64.rpm                  1.0 MB/s | 1.4 MB     00:01
(3/3): postgresql13-server-13.7-1PGDG.rhel9.x86_64.rpm           3.3 MB/s | 5.7 MB     00:01
-------------------------------------------------------------------------------------------------
Total                                                            4.5 MB/s | 7.8 MB     00:01
PostgreSQL 13 for RHEL / Rocky 9 - x86_64                        1.6 MB/s | 1.7 kB     00:00
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <[email protected]>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                         1/1
  Installing       : postgresql13-libs-13.7-1PGDG.rhel9.x86_64                               1/3
  Running scriptlet: postgresql13-libs-13.7-1PGDG.rhel9.x86_64                               1/3
  Installing       : postgresql13-13.7-1PGDG.rhel9.x86_64                                    2/3
  Running scriptlet: postgresql13-13.7-1PGDG.rhel9.x86_64                                    2/3
  Running scriptlet: postgresql13-server-13.7-1PGDG.rhel9.x86_64                             3/3
  Installing       : postgresql13-server-13.7-1PGDG.rhel9.x86_64                             3/3
  Running scriptlet: postgresql13-server-13.7-1PGDG.rhel9.x86_64                             3/3
  Verifying        : postgresql13-13.7-1PGDG.rhel9.x86_64                                    1/3
  Verifying        : postgresql13-libs-13.7-1PGDG.rhel9.x86_64                               2/3
  Verifying        : postgresql13-server-13.7-1PGDG.rhel9.x86_64                             3/3

Installed:
  postgresql13-13.7-1PGDG.rhel9.x86_64             postgresql13-libs-13.7-1PGDG.rhel9.x86_64
  postgresql13-server-13.7-1PGDG.rhel9.x86_64

Complete!

proceed to check the repository list, using the command below;

[[email protected] ~]$ sudo dnf repolist
repo id                      repo name
appstream                    CentOS Stream 9 - AppStream
baseos                       CentOS Stream 9 - BaseOS
crb                          CentOS Stream 9 - CRB
epel                         Extra Packages for Enterprise Linux 9 - x86_64
epel-next                    Extra Packages for Enterprise Linux 9 - Next - x86_64
extras-common                CentOS Stream 9 - Extras packages
pgdg-common                  PostgreSQL common RPMs for RHEL / Rocky 9 - x86_64
pgdg10                       PostgreSQL 10 for RHEL / Rocky 9 - x86_64
pgdg11                       PostgreSQL 11 for RHEL / Rocky 9 - x86_64
pgdg12                       PostgreSQL 12 for RHEL / Rocky 9 - x86_64
pgdg13                       PostgreSQL 13 for RHEL / Rocky 9 - x86_64
pgdg14                       PostgreSQL 14 for RHEL / Rocky 9 - x86_64

Step 4. Initialize PostgreSQL Database Service

Before using the PostgreSQL server, you need to first initialize the database service using the command shown in the command section below, the output is also shown on it;

[[email protected] ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

This /var/lib/pgsql/13/data/postgresql.conf is the database main configuration file. Check below for a better display of the same by running the command;

[[email protected] ~]$ sudo ls  /var/lib/pgsql/13/data/
[sudo] password for technixleo:
base	      pg_dynshmem    pg_multixact  pg_snapshots  pg_tblspc    pg_xact
global	      pg_hba.conf    pg_notify	   pg_stat	 pg_twophase  postgresql.auto.conf
log	      pg_ident.conf  pg_replslot   pg_stat_tmp	 PG_VERSION   postgresql.conf
pg_commit_ts  pg_logical     pg_serial	   pg_subtrans	 pg_wal

You can then proceed to start the database server as follows;

[[email protected] ~]$ sudo systemctl enable --now postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.

Confirm if the just started service above is running by checking its status using the command;

[[email protected] ~]$ systemctl status postgresql-13
postgresql-13.service - PostgreSQL 13 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disa>
     Active: active (running) since Wed 2022-07-20 11:47:37 EAT; 1min 35s ago
       Docs: https://www.postgresql.org/docs/13/static/
    Process: 5839 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exit>
   Main PID: 5844 (postmaster)
      Tasks: 8 (limit: 48777)
     Memory: 16.8M
        CPU: 87ms
     CGroup: /system.slice/postgresql-13.service
             ├─5844 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
             ├─5845 "postgres: logger "
             ├─5847 "postgres: checkpointer "
             ├─5848 "postgres: background writer "
             ├─5849 "postgres: walwriter "
             ├─5850 "postgres: autovacuum launcher "
             ├─5851 "postgres: stats collector "
             └─5852 "postgres: logical replication launcher "

lines 1-19

As from the above output we are now contented that the service is active and running.

Step 5. Provide Postgres Admin User Password

It is very important the update the password of the Postgres Admin user, to do so, first switch to Postgres server account, then proceed to alter the user password by running the commands shown below;

[[email protected] ~]$ sudo su - postgres
[sudo] password for technixleo:
[[email protected] ~]$ psql -c "alter user postgres with password 'passwd123'"
ALTER ROLE

Step 6. Enable Remote Connection on Database

The first thing is to do is logout of postgres prompt, to do so, just type exit on the command prompt. Change listen address and authentication method for remote login, to do so, you need the edit the main database configuration file /var/lib/pgsql/13/data/postgresql.conf utilize the commands shown in the code section below;

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

Scroll down to line #60 using the downward arrow, then edit localhost to *.The following are changes that you need to make, you either use ‘*‘ or ‘your IP address’. Check below for more insights;

You can input your ip address s follows;

To prompt local users to provide passwords. Locate the line local, under the Unix domain socket connections only, then change peer to md5, check the output below the code ;

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

The final look should be like;

Step 6. Create test db, table, input data and clean it

From the Postgres Server Account, we can easily switch to Postgres prompt while utilizing the command shown below;

psql

Below is how the output should look like;

[[email protected] ~]$ psql
psql (13.7)
Type "help" for help.

postgres=#

To log out of the Postgres prompt, run the command;

\q

To list the available database, use the command;

\l

To list users on the database run the command;

\du

The following code output representation covers all the following areas;

  • Switching to Postgres prompt
  • Listing users
  • Changing Postgres user Password
  • Creating new user on Postgres
  • Assigning roles to new user created
  • Deleting the created new user
  • Creating a test database
  • Granting privileges to a new user on test database
  • Connecting to the new test database
  • Creating a new table
  • Inputting data to new table
  • Checking data on the table
  • Deleting data from the table
  • Deleting the table
[[email protected] ~]$ psql
psql (13.7)
Type "help" for help.
#Changing Postgres user Password

postgres=# ALTER USER postgres WITH PASSWORD 'password123';
ALTER ROLE
#Listing users

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
#Creating new user on Postgres

postgres=# create user user_1 with password 'test321';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_1    |                                                            | {}
#Assigning roles to new user created

postgres=# alter user user_1 with superuser;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_1    | Superuser                                                  | {}
#Deleting the created new user

postgres=# drop user user_1;
DROP ROLE
#Create Another User

postgres=# create user user_2 with password 'test321';
CREATE ROLE
#Creating a test database

postgres=# create database test_db1;
CREATE DATABASE
# Granting privileges to a new user on test database

postgres=# grant all privileges on database test_db1 to user_2;
GRANT
#Connecting to the new test database

postgres=# \c test_db1 user_2;
You are now connected to database "test_db1" as user "user_2".
#Creating a new table

test_db1=> create table course(name CHAR(20), code CHAR(5));
CREATE TABLE
test_db1=> \d course
                  Table "public.course"
 Column |     Type      | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
 name   | character(20) |           |          |
 code   | character(5)  |           |          |
#Inputting data to new table

test_db1=> insert into course values('Eng', '001');
INSERT 0 1
test_db1=> \d course
                  Table "public.course"
 Column |     Type      | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
 name   | character(20) |           |          |
 code   | character(5)  |           |          |
#Checking data on the table

test_db1=> select * from public.course;
         name         | code
----------------------+-------
 Eng                  | 001
(1 row)

test_db1=> insert into course values('Chem', '002');
INSERT 0 1
test_db1=> insert into course values('Bio', '003');
INSERT 0 1
test_db1=> insert into course values('Geo', '004');
INSERT 0 1
test_db1=> insert into course values('Math', '005');
INSERT 0 1
test_db1=> select * from public.course;
         name         | code
----------------------+-------
 Eng                  | 001
 Chem                 | 002
 Bio                  | 003
 Geo                  | 004
 Math                 | 005
(5 rows)
#Deleting data from the table

test_db1=> truncate course;
TRUNCATE TABLE
test_db1=> select * from course;
 name | code
------+------
(0 rows)
#Deleting the table

test_db1=> drop table course;
DROP TABLE
#Quit Test database

test_db1=> \q
[[email protected] ~]$ exit
logout

Conclusion

This marks the end of our article on how to install and use PostgreSQL 13 on CentOS 9|AlmaLinux 9|RHEL 9. On this guide we managed to provide a brief introduction on what PostgreSQL is all about and what it does. We also looked at the whole installation process of PostgreSQL on the three provided Operating systems. We were able to show you on how to establish a remote connection with database and also how to ensure local users provide valid passwords. Eventually we also covered the topics on how to create a test database, create table, input data on the table, how to check the data, how to delete the data and finally how to delete the table. Check below for some of our other articles;

LEAVE A REPLY

Please enter your comment!
Please enter your name here