How To Install PostgreSQL 13 Database Server on CentOS 8

On this short tutorial, we will learn how to install PostgreSQL 13 on CentOS 8 operating system.

Introduction

Beside MySQL and MariaDB, PostgreSQL (also known as Postgres) is a popular a relational database management system which is used by many popular projects, both large and small and found as a key component of many websites and applications. On this article, we will discuss how to install PostgreSQL on Linux CentOS 8 operating system.

PostgreSQL 13 Installation On CentOS 8

Before we are going to start PostgreSQL 13 installation on CentOS 8, there are several prerequisite to be fulfilled. These prerequisites are so important because it will affect the success or failure of the installation process.

Prerequisites

  1. CentOS 8 System with sufficient disk space
  2. non-root user with administrative privileges
  3. firewall configured with firewalld

The PostgreSQL installation will be consist of several steps :

  • Add PostgreSQL Repository To CentOS 8
  • Install PostgreSQL 13 on CentOS 8
  • Initializing and starting database service
  • Set PostgreSQL admin user’s password
  • Enable remote access (Optional)

Each of the above items will be explained in more detail in the following sub-chapters.

1. Add PostgreSQL Repository To CentOS 8

The first step installing PostgreSQL 13 database engine on CentOS 8 is to add PostgreSQL repository to the system. By default CentOS 8 has already had PostgreSQL repo, but it will be the older version, than the one one available on the PosrgreSQL official website. To add PostgreSQL 13 repository we will do following command line :

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

Output :

[ramans@diginetdb01 ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[sudo] password for ramans: 
Last metadata expiration check: 9:06:11 ago on Thu 05 Nov 2020 06:40:20 AM PST.
pgdg-redhat-repo-latest.noarch.rpm                          6.4 kB/s |  11 kB     00:01    
Dependencies resolved.
============================================================================================
 Package                    Architecture     Version           Repository              Size
============================================================================================
Installing:
 pgdg-redhat-repo           noarch           42.0-14           @commandline            11 k

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

Total size: 11 k
Installed size: 11 k
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-14.noarch                                    1/1 
  Verifying        : pgdg-redhat-repo-42.0-14.noarch                                    1/1 
Installed products updated.

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

Complete!

The PostgreSQL 13 repo has been added on our CentOS 8.

2. Install PostgreSQL 13 on CentOS 8

The next step is to install PostgreSQL 13 on CentOS 8, on this step we need to disable the PostgreSQL AppStream repository on CentOS 8 to prevent other version of PostgreSQL. This task will be done by submitting command line :

$ sudo dnf -qy module disable postgresql

After all are set, then we will begin to install PostgreSQL 13 on CentOS 8 by submitting command line :

$ sudo dnf install -y postgresql13-server

Output :

[ramans@diginetdb01 ~]$ sudo dnf -qy module disable postgresql
[ramans@diginetdb01 ~]$ sudo dnf install -y postgresql13-server
Last metadata expiration check: 0:00:13 ago on Thu 05 Nov 2020 03:47:32 PM PST.
Dependencies resolved.
============================================================================================In general, database users are users who have ip addresses that are outside our postgresql database system. and for security reasons it is sometimes on a very different IP address segment. For cases like this, we need to open access to the database from remote access.
 Package                     Architecture   Version                    Repository      Size
============================================================================================
Installing:
 postgresql13-server         x86_64         13.0-1PGDG.rhel8           pgdg13         5.5 M
Installing dependencies:
 postgresql13                x86_64         13.0-1PGDG.rhel8           pgdg13         1.4 M
 postgresql13-libs           x86_64         13.0-1PGDG.rhel8           pgdg13         411 k

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

Total download size: 7.4 M
Installed size: 31 M
Downloading Packages:
(1/3): postgresql13-libs-13.0-1PGDG.rhel8.x86_64.rpm        162 kB/s | 411 kB     00:02    
(2/3): postgresql13-13.0-1PGDG.rhel8.x86_64.rpm             451 kB/s | 1.4 MB     00:03    
(3/3): postgresql13-server-13.0-1PGDG.rhel8.x86_64.rpm      454 kB/s | 5.5 MB     00:12    
--------------------------------------------------------------------------------------------
Total                                                       601 kB/s | 7.4 MB     00:12     
warning: /var/cache/dnf/pgdg13-e81daebfc8b779ec/packages/postgresql13-13.0-1PGDG.rhel8.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
PostgreSQL 13 for RHEL/CentOS 8 - x86_64                    1.6 MB/s | 1.7 kB     00:00    
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                    1/1 
  Installing       : postgresql13-libs-13.0-1PGDG.rhel8.x86_64                          1/3 
  Running scriptlet: postgresql13-libs-13.0-1PGDG.rhel8.x86_64                          1/3 
  Installing       : postgresql13-13.0-1PGDG.rhel8.x86_64                               2/3 
  Running scriptlet: postgresql13-13.0-1PGDG.rhel8.x86_64                               2/3 
  Running scriptlet: postgresql13-server-13.0-1PGDG.rhel8.x86_64                        3/3 
  Installing       : postgresql13-server-13.0-1PGDG.rhel8.x86_64                        3/3 
  Running scriptlet: postgresql13-server-13.0-1PGDG.rhel8.x86_64                        3/3 
  Verifying        : postgresql13-13.0-1PGDG.rhel8.x86_64                               1/3 
  Verifying        : postgresql13-libs-13.0-1PGDG.rhel8.x86_64                          2/3 
  Verifying        : postgresql13-server-13.0-1PGDG.rhel8.x86_64                        3/3 
Installed products updated.

Installed:
  postgresql13-13.0-1PGDG.rhel8.x86_64          postgresql13-libs-13.0-1PGDG.rhel8.x86_64  
  postgresql13-server-13.0-1PGDG.rhel8.x86_64  

Complete!
[ramans@diginetdb01 ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

3. Initializing and Starting Database Service

Until this step, we have completedly install PostgreSQL database engine on CentOS 8, then we will use this database engine for our requirement. Before it can be done, we have ti initialize PostgreSQL database before we use it. To initialize database, we will use the following command line :

$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

Output :

[ramans@diginetdb01 ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

Then we will enable and start PostgreSQL 13 service to server our need as well. To enable and start PostgreSQL 13 database service we will use the following commands line :

$ sudo systemctl enable postgresql-13
$ sudo systemctl start postgresql-13
$ sudo systemctl status postgresql-13 

Output :

[ramans@diginetdb01 ~]$ sudo systemctl enable postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.
[ramans@diginetdb01 ~]$ sudo systemctl start postgresql-13
[ramans@diginetdb01 ~]$ sudo systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: d>
   Active: active (running) since Thu 2020-11-05 15:48:52 PST; 8s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 3433 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=e>
 Main PID: 3439 (postmaster)
    Tasks: 8 (limit: 23800)
   Memory: 18.1M
   CGroup: /system.slice/postgresql-13.service
           ├─3439 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─3440 postgres: logger 
           ├─3442 postgres: checkpointer 
           ├─3443 postgres: background writer 
           ├─3444 postgres: walwriter 
           ├─3445 postgres: autovacuum launcher 
           ├─3446 postgres: stats collector 
           └─3447 postgres: logical replication launcher 

Nov 05 15:48:52 diginetdb01 systemd[1]: Starting PostgreSQL 13 database server...
Nov 05 15:48:52 diginetdb01 postmaster[3439]: 2020-11-05 15:48:52.320 PST [3439] LOG:  redi>
Nov 05 15:48:52 diginetdb01 postmaster[3439]: 2020-11-05 15:48:52.320 PST [3439] HINT:  Fut>
Enabling PostgreSQL 13 service on CentOS 8
Enabling PostgreSQL 13 service on CentOS 8

On this step, we will verify the PostgreSQL 13 by entering command line below :

$ sudo -u postgres psql -c "SELECT version();"

Output :

[ramans@diginetdb01 ~]$ sudo -u postgres psql -c "SELECT version();"
could not change directory to "/home/ramans": Permission denied
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)

4. Setting Up PostgreSQL admin user’s password

For the security reason and strengthen database security we are recommended to change our PostgreSQL admin user’s password. To do this we will enter the following command line from shell script and and enter to sql script and change the password from there.

ramans@diginetdb01 ~]$ sudo su - postgres
[sudo] password for ramans: 
[postgres@diginetdb01 ~]$ psql
psql (13.0)
Type "help" for help.

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)

postgres=# alter user postgres with password 'Otodiginet2020';
ALTER ROLE

5. Enabling remote access (Optional)

If you have a running Firewall service and remote clients should connect to your database server, allow PostgreSQL service. Then we have to enable posrgresql service on firewalld. We will check if firewalld has been running on our system, by submitting command line :

$ sudo systemctl status firewalld

Output :

[ramans@diginetdb01 ~]$ sudo systemctl status firewalld
[sudo] password for ramans: 
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabl>
   Active: active (running) since Thu 2020-11-05 15:36:44 PST; 30min ago
     Docs: man:firewalld(1)
 Main PID: 1088 (firewalld)
    Tasks: 2 (limit: 23800)
   Memory: 33.6M
   CGroup: /system.slice/firewalld.service
           └─1088 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid

Nov 05 15:36:44 diginetdb01 systemd[1]: Starting firewalld - dynamic firewall daemon...
Nov 05 15:36:44 diginetdb01 systemd[1]: Started firewalld - dynamic firewall daemon.
Nov 05 15:36:45 diginetdb01 firewalld[1088]: WARNING: AllowZoneDrifting is enabled. This is>

Then weill add postgresql service permanently, by submitting command line :

$ sudo firewall-cmd --add-service=postgresql --permanent
$ sudo firewall-cmd --reload

Ouput :

[ramans@diginetdb01 ~]$ sudo firewall-cmd --add-service=postgresql --permanent
success
[ramans@diginetdb01 ~]$ sudo firewall-cmd --reload
success

In general, database users are users who have ip addresses that are outside our database system and for security reasons it is sometimes on a very different IP address segment. For this case, we need to enbale remote access to our database system. To enable remote access we have to modify a spescific configuration file. We have to edit the /var/lib/pgsql/13/data/postgresql.conf file and set listen address to our server IP address or “*” for all interfaces.

ramans@diginetdb01 ~]$ sudo vi /var/lib/pgsql/13/data/postgresql.conf
[sudo] password for ramans: 

# -----------------------------
# PostgreSQL configuration file
# -----------------------------

------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '192.168.19.133'             # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories

The next step is to set PostgreSQL to accept remote connections, by updating /var/lib/pgsql/13/data/pg_hba.conf file.

[ramans@diginetdb01 ~]$ sudo vi /var/lib/pgsql/13/data/pg_hba.conf

 TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# Accept connection from anywhere
host    all             all             0.0.0.0/0               md5

After saving the file, we have to restart database service to take effect after updating configuration file.

Conclusion

So far, until this stage, we have already discussed how to install PostgreSQL 13 database server on CentOS 8 operating system properly. I hope this guide will be helpful for anyone who need it.

Add a Comment

Your email address will not be published. Required fields are marked *