How To Install PostgreSQL 11 on CentOS 7 / RHEL 7

This guide will help you to install PostgreSQL 11 on CentOS 7 / RHEL 7. PostgreSQL is the World’s most advanced, powerful, open source relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

PostgreSQL 11 was released on 2018-10-18 and you can check its new features from the Release page. Installation of PostgreSQL 11 on CentOS 7 / RHEL 7 is done from the PostgreSQL Yum Repository by following below steps:

For Fedora use: How to install PostgreSQL 11 on Fedora

Step 1: Update system

Ensure system packages are up to date:

sudo yum update -y

Since you may have Kernel updates, it is recommended to reboot your system after an upgrade

sudo reboot

Step 2: Add PostgreSQL Yum Repository

Add PostgreSQL Yum Repository to your CentOS 7 / RHEL 7 system by running the command:

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

When prompted to confirm installation, press the y key

...
Dependencies Resolved

======================================================================================================================================================================================================
 Package                                         Arch                                  Version                                   Repository                                                      Size
======================================================================================================================================================================================================
Installing:
 pgdg-redhat-repo                                noarch                                42.0-24                                   /pgdg-redhat-repo-latest.noarch                                 11 k

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

Total size: 11 k
Installed size: 11 k
Is this ok [y/d/N]: y

Step 3: Install PostgreSQL 11 on CentOS 7 / RHEL 7

After adding PostgreSQL Yum Repository, install PostgreSQL Server / Client packages:

sudo yum -y install postgresql11-server postgresql11

Confirm the installed package:

$ rpm -qi postgresql11-server
Name        : postgresql11-server
Version     : 11.16
Release     : 1PGDG.rhel7
Architecture: x86_64
Install Date: Wed May 18 20:32:25 2022
Group       : Unspecified
Size        : 19804199
License     : PostgreSQL
Signature   : DSA/SHA1, Wed May 11 19:27:21 2022, Key ID 1f16d2e1442df0f8
Source RPM  : postgresql11-11.16-1PGDG.rhel7.src.rpm
Build Date  : Wed May 11 13:54:55 2022
Build Host  : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor      : PostgreSQL Global Development Group
URL         : https://www.postgresql.org/
....

Step 4: Initialize the database and enable automatic start

Now that the database packages have been installed, Initialize the database by running the following command

$ sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK

Then start and enable the service to start on boot

sudo systemctl start postgresql-11
sudo systemctl enable postgresql-11

PostgreSQL 11 config file is/var/lib/pgsql/11/data/postgresql.conf

If you have a running Firewall service and remote clients should connect to your database server, allow PostgreSQL service.

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

Step 5: Enable remote Access to PostgreSQL (Not recommended)

Edit the file /var/lib/pgsql/11/data/postgresql.conf and set Listen address to your server IP address or “*” for all interfaces.

listen_addresses = '*'

Also set PostgreSQL to accept remote connections

$ sudo vim /var/lib/pgsql/11/data/pg_hba.conf
# Accept from anywhere
host all all 0.0.0.0/0 md5

# Accept from trusted subnet
host all all 192.168.18.0/24 md5

Restart service

sudo systemctl restart postgresql-11

Step 6: Set PostgreSQL admin user’s password

Set PostgreSQL admin user

$ sudo su - postgres 
bash-4.2$ psql -c "alter user postgres with password 'StrongPassword'" 
ALTER ROLE
-bash-4.2$

Create a test user and database

createuser test_user
createdb test_db -O test_user
grant all privileges on database test_db to test_user;

Login as a test_user  user try to create a table on the Database.

$ psql -U test_user -h localhost -d test_db

Step 7: Install pgAdmin 4 on CentOS 7 / RHEL 7

Install pgAdmin 4 on CentOS 7 / Fedora to administer your PostgreSQL from a web interface.

Best Video courses to Learn PostgreSQL Database:

• SQL and PostgreSQL: The Complete Developer’s Guide
• The Complete Python/PostgreSQL Course 2.0
• SQL & PostgreSQL for Beginners: Become an SQL Expert
• Learn SQL Using PostgreSQL: From Zero to Hero
• PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours

Also read: Using SQLPad – Web-based SQL editor for MySQL / PostgreSQL / SQL Server

How to Install TimescaleDB on Ubuntu