PostgreSQL (Postgres) Installation on CentOS 8

On this article we will discuss about PostgreSQL installation on Linux CentOS 8.

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 about PostgreSQL installation on Linux CentOS 8 operating system.

Before we are starting PostgreSQL installation, there are will be prerequisites to be fulfill, as mention below :

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

PostgreSQL installation on CentOS 8

PostgreSQL project provides a repository of packages of all supported versions for the most common Linux distributions. On this tutorial we will be using PostgreSQL Yum Repository for PostgreSQL version 13, the last stable version which was released on 24 September 2020.

The PostgreSQL installaton will be consist of several steps :

1. Add PostgreSQL Repository To CentOS 8
2. Install PostgreSQL 13 on CentOS 8
3. Initialize and start database service
4. Set PostgreSQL admin user’s password
5. Enable remote access

Add PostgreSQL Repository To CentOS 8

The PostgreSQL Yum Repository will integrate with our normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

[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

Install PostgreSQL 13 on CentOS 8

[ramans@diginetdb01 ~]$ sudo dnf -qy module disable postgresql

Than install the Postgresql 13 server, by submitting command line : sudo dnf install -y postgresql13-server as shown below :

[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.
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!

Initialize and start database service

After the installation is completed done, than we stat up its services.

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

[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

Than query the PostgreSQL status by submitting the command line below :

[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>

We also able to verify its version by submitting command line below :

[ramans@diginetdb01 ~]$ sudo -u postgres psql -c "SELECT version();"

The output will be :

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)

If we have implemented the Firewall on database server. then remote clients should connect to our database server, we have to allow PostgreSQL service. The firewall service is running on our side, as firewalld status below :

[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 we enable if the PostgreSQL service is allowed.

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

Set PostgreSQL admin user’s password

For administrative easiness, we have to set the admin user’s password. For this purpose, we have to login to database and setting the password.

ramans@diginetdb01 ~]$ sudo su - postgres
[sudo] password for ramans:
[postgres@diginetdb01 ~]$ psql
psql (13.0)
Type "help" for help.
postgres=# alter user postgres with password 'Otodiginet2020';
ALTER ROLE
postgres=#

Enable remote access

To enable remote access we have to edit the /var/lib/pgsql/13/data/postgresql.conf file.

Just update the listen_address with IP Address where the database is hosted.

CONNECTIONS AND AUTHENTICATION
------------------------------------------------------------------------------
- Connection Settings -
listen_addresses = '192.168.19.133' # what IP address(es) to listen on;

And then setting if the PostgreSQL is also accepting remote connections, by editing /var/lib/pgsql/13/data/pg_hba.conf file.

Accept connection from anywhere
host all all 0.0.0.0/0 md5