Databases

How To Install PostgreSQL 11.x on CentOS 7

How To Install PostgreSQL 11.x on CentOS 7

Based on Postgres 4.2, PostgreSQL was developed at Berkeley Computer Science Development, University of California. It was created to be a powerful open source relational database system able to run across multiple platforms such as Linux, Windows, Solaris, Mac, etc. The code is available for anyone to modify and distribute under PostgreSQL license.

 

PostgreSQL is not the only contender in the database industry but what makes this a superior choice is that due to high-level stability and lower management overhead, the cost of PostgreSQL ownership is much lower in the long run. PostgreSQL is also the first database platform to implement Multi-Version Concurrency Control or MVCC.

In this tutorial, we are going to learn how to install and configure PostgreSQL 11.x on CentOS 7. 

Step 1: Update CentOS

It is always best to start with an updated operating system. If you have not done so, use the following command to update CentOS and reboot:

$ yum update -y
$ reboot

Step 2: Configure Yum Repo

Add the PostgreSQL repository in CentOS 7.

$ rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Visit PostgreSQL Repositories for additional package information on various operating systems.

Step 3: Install PostgreSQL Server and Client

Use the following command to install PostgreSQL:

$ yum install postgresql11-server postgresql11 -y

Step 4: Initialize Database

After installation is complete, we need to initialize the PostgreSQL database, which will create a data directory to store database and few configuration files:

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

Depending on the storage system used or allocated resources, the initialization may take some time. The default directory where PostgreSQL will store data is /var/lib/pgsql/11/data.

Step 5: Enable and Start PostgreSQL Service

Enable the service so the PostgreSQL starts automatically during server reboot:

$ systemctl enable postgresql-11.service
$ systemctl start postgresql-11.service

Step 6: Configure Firewall

If the firewall is configured on CentOS, we need to open ports so remote users can connect to PostgreSQL:

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

Step 7: Enable Remote Access

To allow PostgreSQL to accept remote connections, first, we need to change the listen to address to * in the configuration file:

$ nano /var/lib/pgsql/11/data/postgresql.conf
listen_address = '*'

Also, we need to let PostgreSQL know to accept remote connections:

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

Restart service after making configuration changes:

$ systemctl restart postgresql-11.service

Step 8: Set PostgreSQL Admin Password

Set admin user and password for PostgreSQL:

$ su - postgres
postgresql=$ psql -c "alter user postgres with password 'password'"
ALTER ROLE
postgresql=$

Step 9: Create A Demo User and Database

Login to PostgreSQL console to create a user and database for testing:

postgresql=$ createuser demo_usr
postgresql=$ createdb demo_db -O demo_usr
postgresql=$ grant all privileges on database demo_db to demo_usr

Login as a demo user to test:

$ psql -U demo_usr -h localhost -d demo_db

To modify the password for a database with a particular user:

$ psql -c "ALTER USER postgres WITH PASSWORD 'yourPassword'" -d template1

Step 10: pgAdmin Tool

pgAdmin is a management and development tool for PostgreSQL server. It provides a graphical user interface to administer and a development platform all through a user-friendly interface. 

Step 10.1: Installing pgAdmin

Install pgAdmin through repository already added for PostgreSQL:

$ yum install pgadmin4 -y

Package pgadmin4-web and web server httpd are also automatically installed as needed dependencies. 

Step 10.2: Enable pgAdmin Apache Configuration

A sample configuration file for pgAdmin is installed during installation. We need to rename or copy it as following:

$ cp /etc/httpd/conf.d/pgadmin4.conf.sample /etc/httpd/conf.d/pgadmin4.conf

Step 10.3: Configure VirtualHost

Edit the Apache configuration file to add a VirtualHost as appears below:

<VirtualHost *:80>
ServerName pgadmin.example.com
LoadModule wsgi_module modules/mod_wsgi.so
WSGIDaemonProcess pgadmin processes=1 threads=25
WSGIScriptAlias /pgadmin4 /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.wsgi

<Directory /usr/lib/python2.7/site-packages/pgadmin4-web>
          WSGIProcessGroup pgadmin
          WSGIApplicationGroup %{GLOBAL}
          <IfModule mod_authz_core.c>
             # Apache 2.4
             Require all granted
          </IfModule>
          <IfModule !mod_authz_core.c>
             # Apache 2.2
             Order Deny,Allow
             Deny from All
             Allow from 127.0.0.1
             Allow from ::1
          </IfModule>
</Directory>
</VirtualHost>

Step 10.4: Configure Data Directories

Create data directories to be used by pgAdmin and mention the directories in the configuration file:

$ mkdir -p /var/lib/pgadmin4/ /var/log/pgadmin4/
$ nano /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

Step 10.5: Create pgAdmin Configuration Database

Run the python script to create a database to store pgAdmin configurations:

$ python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py

When prompted, fill in the Email address and Password. This email address and password will be used as login credential to access the pgAdmin web interface:

Email address: This email address is being protected from spambots. You need JavaScript enabled to view it.
Password: <password>
Retype password: <password>
pgAdmin 4 - Application Initialisation
======================================

Step 10.6: Directories Permission

Set permissions for pgAdmin directories so Apache user can access:

$ chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4

Step 10.7: Configure SELinux

Ignore this step if SELinux is disabled. But if it is enabled in Enforcing mode, create the following policies  to allow the web server to access pgAdmin interface:

$ semanage fcontext -a -t httpd_sys_rw_content_t "/var/lib/pgadmin4(/.*)?"
$ semanage fcontext -a -t httpd_sys_rw_content_t "/var/log/pgadmin4(/.*)?"
$ restorecon -R /var/lib/pgadmin4/
$ restorecon -R /var/log/pgadmin4/

Enable and start httpd service:

$ systemctl enable httpd
$ systemctl start httpd

Step 10.8: Configure Firewall

Open web service ports so that pgAdmin interface can be accessed:

$ firewall-cmd --permanent --add-service=http
$ firewall-cmd --reload

The pgAdmin web interface can now be accessed from https://pgadmin.domain.com/pgadmin4. Use the credentials created in Step 10.5 to access the pgAdmin web interface.  

Latest HOW-TOs