In this article, I will take you through the steps to install PostgreSQL DB on RHEL/CentOS 7/8. PostgreSQL is a very popular free and open source relational database management system designed for handling variety of workloads ranging from a single machine to data warehouses with many concurrent users. It is powerful, fast and reliable currently in use by the database administrators, developers and all the people working on databases from last 25 years. It is default database in MAC based Systems. Check official documentation for more information.

Features of PostgreSQL

• Robust database for dynamic websites
• Highly reliable with disaster recovery available
• Highly recommended for Server Side Programming
• Fully supported with Programming languages like C++, Java, Python, Ruby, Perl etc
• It has user defined data types
• It supports table inheritance
• It is ACID Compliant
• It has fault tolerance and high availability features
• It has multi-version concurrency control(MVCC)
• It supports locking mechanism

How to Install PostgreSQL DB on RHEL/CentOS 7/8

Also Read: How to Install kubectl on Ubuntu 20.04 LTS (Focal Fossa)

Step 1: Prerequisites

a) You should have a running RHEL/CentOS 7/8 Server.

b) You should have sudo or root access to run privileged commands.

c) You should have yum or dnf utility available in your System.

d) Make sure PostgreSQL default Port 5432 allowed from Firewall if you have any.

 

Step 2: Update Your Server

Before moving on with the installation, let's sync the system with the latest available updates by using yum update or dnf update command as shown below. In case any packages requires an upgrade then it can be done by using yum upgrade or dnf upgrade command.

[root@cyberithub ~]# yum update
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: centos-hcm.viettelidc.com.vn
* epel: mirrors.bestthaihost.com
* extras: centos-hcm.viettelidc.com.vn
* updates: centos-hcm.viettelidc.com.vn
Resolving Dependencies
--> Running transaction check
---> Package powershell.x86_64 0:7.2.1-1.rh will be updated
---> Package powershell.x86_64 0:7.2.2-1.rh will be an update
---> Package snap-confine.x86_64 0:2.54.3-1.el7 will be updated
---> Package snap-confine.x86_64 0:2.54.4-1.el7 will be an update
---> Package snapd.x86_64 0:2.54.3-1.el7 will be updated
---> Package snapd.x86_64 0:2.54.4-1.el7 will be an update
---> Package snapd-selinux.noarch 0:2.54.3-1.el7 will be updated
---> Package snapd-selinux.noarch 0:2.54.4-1.el7 will be an update
--> Finished Dependency Resolution
.......................................

 

Step 3: Install PostgreSQL DB

There are multiple ways to install PostgreSQL DB Server on Linux. Here we will install it through from the default repository using yum install postgresql-server postgresql-contrib command as shown below.

[root@cyberithub ~]# yum install postgresql-server postgresql-contrib
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: centos-hcm.viettelidc.com.vn
* epel: mirrors.bestthaihost.com
* extras: centos-hcm.viettelidc.com.vn
* updates: centos-hcm.viettelidc.com.vn
Resolving Dependencies
--> Running transaction check
---> Package postgresql-contrib.x86_64 0:9.2.24-7.el7_9 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-7.el7_9 for package: postgresql-contrib-9.2.24-7.el7_9.x86_64
--> Processing Dependency: postgresql(x86-64) = 9.2.24-7.el7_9 for package: postgresql-contrib-9.2.24-7.el7_9.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64
---> Package postgresql-server.x86_64 0:9.2.24-7.el7_9 will be installed
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.24-7.el7_9 will be installed
---> Package postgresql-libs.x86_64 0:9.2.24-7.el7_9 will be installed
---> Package uuid.x86_64 0:1.6.2-26.el7 will be installed
--> Finished Dependency Resolution
..................................

 

Step 4: Check PostgreSQL Version

After successful installation, you can check the current installed version by using postgres -V command as shown below.

[root@cyberithub ~]# postgres -V
postgres (PostgreSQL) 9.2.24

 

Step 5: Initialize Database

Next step is to initialize the database by using postgresql-setup initdb command as shown below.

[root@cyberithub ~]# postgresql-setup initdb
Initializing database ... OK

 

Step 6: Start DB Service

After initializing the database we need to start the service by using systemctl start postgresql command as shown below. You can also check the service status by using systemctl status postgresql command.

[root@cyberithub ~]# systemctl start postgresql
[root@cyberithub ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2022-03-23 08:34:33 EDT; 1s ago
Process: 11584 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 11574 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 11587 (postgres)
Tasks: 7
CGroup: /system.slice/postgresql.service
├─11587 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─11588 postgres: logger process
├─11590 postgres: checkpointer process
├─11591 postgres: writer process
├─11592 postgres: wal writer process
├─11593 postgres: autovacuum launcher process
└─11594 postgres: stats collector process

Mar 23 08:34:32 cyberithub systemd[1]: Starting PostgreSQL database server...
Mar 23 08:34:33 cyberithub systemd[1]: Started PostgreSQL database server.

 

Step 7: Enable Service

If you want this service to be started automatically after every system reboot then you need to enable the service by using systemctl enable postgresql command as shown below.

[root@cyberithub ~]# systemctl enable postgresql
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.

 

Step 8: Open PSQL Prompt

If you are logged in as root user then first you need to switch to non-root user as psql prompt cannot be opened from the root account. Here we are switching to default postgres user and then starting the prompt using psql command as shown below.

[root@cyberithub ~]# su - postgres
-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.

postgres=#

 

Step 9: Check List of Databases

You can now use the psql prompt to run the PostgreSQL queries. For example, if you want to check the list of databases then you need to use \l meta-command as shown below.

postgres=# \l
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
(3 rows)

 

Step 10: Check Connection Info

Similarly, if you want to check the database connection information then you need to use \conninfo meta-command as shown below. This will give you the DB name, user through which you are connected and the port on which the database is running. In our case, it is default Port 5432.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

 

Step 11: Create a Database

You can also create a sample database and test the PostgreSQL queries. In this example we are creating a database called cyberithub using create database cyberithub query as shown below. Same can be verified by checking the list of databases using \l meta-command.

postgres=# create database cyberithub;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+-------------+-------------+-----------------------
cyberithub | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(4 rows)

 

Step 12: Exit PSQL

To exit psql prompt you need to use \q meta-command as shown below.

postgres-# \q
-bash-4.2$

 

Step 13: Uninstall PostgreSQL DB

Finally once you are done using PostgreSQL DB then you can also choose to uninstall it from your System by using yum remove postgresql-server postgresql-contrib command as shown below.

[root@cyberithub ~]# yum remove postgresql-server postgresql-contrib
Loaded plugins: fastestmirror, langpacks
Resolving Dependencies
--> Running transaction check
---> Package postgresql-contrib.x86_64 0:9.2.24-7.el7_9 will be erased
---> Package postgresql-server.x86_64 0:9.2.24-7.el7_9 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================
Removing:
postgresql-contrib x86_64 9.2.24-7.el7_9 @updates 1.8 M
postgresql-server x86_64 9.2.24-7.el7_9 @updates 16 M

Transaction Summary
=============================================================================================================================================================
Remove 2 Packages

Installed size: 18 M
Is this ok [y/N]: y
.............................