How to Install PostgreSQL on Linux?

PostgreSQL is a robust and highly flexible open-source database server that uses the SQL language. It provides a powerful system for managing large amounts of data and supporting multiple users simultaneously. It can be used in clusters to handle even higher volumes of data.
With PostgreSQL, you can ensure the integrity of your data, create fault-tolerant environments, and develop applications with ease. It allows you to extend the functionality of the database by adding your own custom data types, functions, or code in various programming languages, without requiring any recompilation of the database software.

To learn about PostgreSQL 15's new features, follow this article PostgreSQL 15's Must-Know Features for Database Enthusiasts

In this article, we will cover how to install and configure PostgreSQL on a RHEL (Red Hat Enterprise Linux 8.8) system.

Installing PostgreSQL

In RHEL 8.8, PostgreSQL 10,12, 13 and 15 are available as the initial versions within the Application Stream. Installing PostgreSQL 15 is straightforward using the RPM package.
If you haven't set up a Yum repository yet, please refer to the tutorial How to configure a local Yum repository in RHEL Linux for guidance.

Execute the following command to check the available PostgreSQL packages:

[root@dbdocs ~]# sudo yum module list | grep postgresql
postgresql           9.6             client, server [d]                       PostgreSQL server and client module                                                       
postgresql           10 [d]          client, server [d]                       PostgreSQL server and client module                                                       
postgresql           12              client, server [d]                       PostgreSQL server and client module                                                       
postgresql           13              client, server [d]                       PostgreSQL server and client module                                                       
postgresql           15              client, server [d]                       PostgreSQL server and client module    

In the previous step, we noticed that RHEL 8.8 came with five Application Streams for the PostgreSQL server. By default, PostgreSQL 10 will be installed if no specific version is specified during the installation process.

To install the latest version, PostgreSQL 15, please run the following command:

[root@dbdocs ~]# sudo yum install @postgresql:15
Last metadata expiration check: 2:57:18 ago on Sun 25 Jun 2023 10:37:34 AM EDT.
Dependencies resolved.
========================================================================================================================================================================
 Package                                  Architecture            Version                                                 Repository                               Size
========================================================================================================================================================================
Installing group/module packages:
 postgresql-server                        x86_64                  15.2-1.module+el8.8.0+18155+992b6275                    InstallMedia-AppStream                  6.0 M
Installing dependencies:
 postgresql                               x86_64                  15.2-1.module+el8.8.0+18155+992b6275                    InstallMedia-AppStream                  1.7 M
 postgresql-private-libs                  x86_64                  15.2-1.module+el8.8.0+18155+992b6275                    InstallMedia-AppStream                  132 k
Installing module profiles:
 postgresql/server
Enabling module streams:
 postgresql                                                       15

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

Total size: 7.8 M
Installed size: 31 M
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       : postgresql-private-libs-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                            1/3
  Installing       : postgresql-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                                         2/3
  Running scriptlet: postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                                  3/3
  Installing       : postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                                  3/3
  Running scriptlet: postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                                  3/3
  Verifying        : postgresql-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                                         1/3
  Verifying        : postgresql-private-libs-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                            2/3
  Verifying        : postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64                                                                                  3/3
Installed products updated.

Installed:
  postgresql-15.2-1.module+el8.8.0+18155+992b6275.x86_64                          postgresql-private-libs-15.2-1.module+el8.8.0+18155+992b6275.x86_64
  postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64

Complete!
[root@dbdocs ~]#

If you encounter the following error during the installation of the PostgreSQL database, please reset the PostgreSQL module and attempt the installation again.

[root@dbdocs ~]# sudo yum install @postgresql:15
Last metadata expiration check: 2:54:56 ago on Sun 25 Jun 2023 10:37:34 AM EDT.
Dependencies resolved.
The operation would result in switching of module 'postgresql' stream '10' to stream '15'
Error: It is not possible to switch enabled streams of a module unless explicitly enabled via configuration option module_stream_switch.
It is recommended to rather remove all installed content from the module, and reset the module using 'yum module reset "module_name" command. After you reset the module, you can install the other stream.

[root@dbdocs ~]# dnf module reset postgresql
Last metadata expiration check: 2:56:27 ago on Sun 25 Jun 2023 10:37:34 AM EDT.
Dependencies resolved.
========================================================================================================================================================================
 Package                                 Architecture                           Version                                   Repository                               Size
========================================================================================================================================================================
Resetting modules:
 postgresql

Transaction Summary
========================================================================================================================================================================

Is this ok [y/N]: y
Complete!
  
    

Initialize the Database and Enable Automatic Start

To initialize the PostgreSQL database and add services to the server startup, follow these steps and execute the provided commands:

[root@dbdocs var]# postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@dbdocs var]#

Red Hat recommends storing the data in the default /var/lib/pgsql/data directory.

[root@dbdocs var]# systemctl start postgresql.service

[root@dbdocs var]# systemctl enable postgresql.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[root@dbdocs var]#

To check the stauts of postgresql service

[root@dbdocs var]#  systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2023-06-25 14:17:30 EDT; 4min 14s ago
 Main PID: 55158 (postmaster)
    Tasks: 7 (limit: 11003)
   Memory: 23.4M
   CGroup: /system.slice/postgresql.service
           ├─55158 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─55160 postgres: logger
           ├─55161 postgres: checkpointer
           ├─55162 postgres: background writer
           ├─55164 postgres: walwriter
           ├─55165 postgres: autovacuum launcher
           └─55166 postgres: logical replication launcher

Jun 25 14:17:29 dbdocs systemd[1]: Starting PostgreSQL database server...
Jun 25 14:17:30 dbdocs postmaster[55158]: 2023-06-25 14:17:30.600 EDT [55158] LOG:  redirecting log output to logging collector process
Jun 25 14:17:30 dbdocs postmaster[55158]: 2023-06-25 14:17:30.600 EDT [55158] HINT:  Future log output will appear in directory "log".
Jun 25 14:17:30 dbdocs systemd[1]: Started PostgreSQL database server.

Connecting to PostGreSQL DB

[root@dbdocs var]# su - postgres
[postgres@dbdocs ~]$ psql
psql (15.2)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

postgres=#

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

Creating PostgreSQL users

In PostgreSQL, there are different types of users with specific roles and permissions. Let's break them down:

The postgres Linux system user: This user is meant to be used exclusively for running the PostgreSQL server and client applications like pg_dump. It is not recommended to perform interactive PostgreSQL administration tasks, such as creating databases or managing users, using this system user.

Database Superuser: The default superuser in PostgreSQL is called postgres," but it is not directly associated with the postgres system user. You can limit the access of the superuser in the pg_hba.conf file. By default, there are no other limitations on the permissions of the superuser. However, you can create additional superusers with different names.

By running this SQL command, you will create a new user and assign it both the CREATEROLE and CREATEDB permissions.

-- Create a new user
postgres=# CREATE USER db_user;
CREATE ROLE

-- Set a password for the user           
postgres=# ALTER USER db_user WITH PASSWORD 'db_user';
ALTER ROLE

-- Grant CREATEROLE and CREATEDB permissions to the user
postgres=# ALTER USER db_user WITH CREATEROLE CREATEDB;
ALTER ROLE
postgres=#

Creating a PostgreSQL Database

postgres=# create database dbdocs with owner db_user;
CREATE DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 dbdocs    | db_user  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)

postgres=# \q

Set the password hashing algorithm to scram-sha-256.

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

FROM 
    #password_encryption = md5              # md5 or scram-sha-256
TO  
    password_encryption = scram-sha-256     # scram-sha-256 or md5

Change the following line for the IPv4 local connections

/var/lib/pgsql/data/pg_hba.conf
FROM
    host    all             all             127.0.0.1/32            ident
TO
    host    all             all             127.0.0.1/32            scram-sha-256

Restart the service

-
systemctl stop postgresql.service
systemctl start postgresql.service

Connect to the new database

[postgres@dbdocs ~]$ psql -U db_user -h 127.0.0.1 -d dbdocs
Password for user db_user:
psql (15.2)
Type "help" for help.

dbdocs=> \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 dbdocs    | db_user  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)

dbdocs=> \conninfo
You are connected to database "dbdocs" as user "db_user" on host "127.0.0.1" at port "5432".
dbdocs=>

Rate Your Experience

: 0 : 0