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 rebootStep 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.rpmWhen the above command is executed, below is the output;
[technixleo@centos9 ~]$ 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;
[technixleo@centos9 ~]$ 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 PostgreSQLStep 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-serverCheck the result shown below;
[technixleo@centos9 ~]$ 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;
[technixleo@centos9 ~]$ 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_64Step 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;
[technixleo@centos9 ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OKThis /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;
[technixleo@centos9 ~]$ 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_walYou can then proceed to start the database server as follows;
[technixleo@centos9 ~]$ 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;
[technixleo@centos9 ~]$ 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-19As 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;
[technixleo@centos9 ~]$ sudo su - postgres
[sudo] password for technixleo:
[postgres@centos9 ~]$ psql -c "alter user postgres with password 'passwd123'"
ALTER ROLEStep 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.confScroll 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.confThe 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;
psqlBelow is how the output should look like;
[postgres@centos9 ~]$ psql
psql (13.7)
Type "help" for help.
postgres=#To log out of the Postgres prompt, run the command;
\qTo list the available database, use the command;
\lTo list users on the database run the command;
\duThe 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
[postgres@centos9 ~]$ 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
[postgres@centos9 ~]$ exit
logoutConclusion
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;
- Install and Configure PostgreSQL on Solus Linux
- Install and Configure PostgreSQL on Kubuntu | KDE Neon
- Install and Use MySQL Workbench on Kubuntu/KDE Neon
