(Last Updated On: November 13, 2018)

This guide will help you to install PostgreSQL 11 on CentOS 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 is done from the PostgreSQL Yum Repository by following below steps:

For Fedora use: How to install PostgreSQL 11 on Fedora 29 / Fedora 28

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 system by running the command

sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

When prompted to confirm installation, press the key

.......................
Dependencies Resolved

======================================================================================================================================================
 Package                             Arch                         Version                      Repository                                        Size
======================================================================================================================================================
Installing:
 pgdg-centos11                       noarch                       11-2                         /pgdg-centos11-11-2.noarch                       2.7 k

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

Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgdg-centos11-11-2.noarch                                                                                                          1/1 
  Verifying  : pgdg-centos11-11-2.noarch                                                                                                          1/1 

Installed:
  pgdg-centos11.noarch 0:11-2                                                                                                                         

Complete!

Step 3: Install PostgreSQL Server and Client packages

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

sudo yum -y install postgresql11-server postgresql11

Confirm the installed package:

$ sudo rpm -qi postgresql11-server  
Name        : postgresql11-server
Version     : 11.0
Release     : 1PGDG.rhel7
Architecture: x86_64
Install Date: Sat 03 Nov 2018 04:43:38 PM UTC
Group       : Applications/Databases
Size        : 19382677
License     : PostgreSQL
Signature   : DSA/SHA1, Thu 18 Oct 2018 09:06:24 AM UTC, Key ID 1f16d2e1442df0f8
Source RPM  : postgresql11-11.0-1PGDG.rhel7.src.rpm
Build Date  : Tue 16 Oct 2018 02:28:25 AM UTC
Build Host  : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor      : PostgreSQL Global Development Group
URL         : https://www.postgresql.org/
Summary     : The programs needed to create and run a PostgreSQL server
Description :
PostgreSQL is an advanced Object-Relational database management system (DBMS).
The postgresql11-server package contains the programs needed to create
and run a PostgreSQL server, which will in turn allow you to create
and maintain PostgreSQL databases.

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 Acess to PostgreSQL

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 = '192.168.18.9'

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

-bash-4.2$ createuser test_user
-bash-4.2$ createdb test_db -O test_user
-bash-4.2$ 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 / Fedora 29 / Fedora 28

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

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

How to Install TimescaleDB on Ubuntu 18.04 LTS