Install PostgreSQL and pgAdmin on CentOS 7

PostgreSQL (or Postgres) is a free and open-source, relational database management system (RDBMS) emphazing on extensibility and technical standards compliance. PostgreSQL is developed by the PostgreSQL Global Development Group. It is the default database for MacOS server but also available for other platforms.

pgAdmin is the most popular open source and feature enrich web interface for administration of PostgreSQL database servers. Currently pgAdmin 4 has been released and available via PostgreSQL yum repository.

In this article, we are installing PostgreSQL 11 and pgAdmin 4 on CentOS 7 server. The article is broken down into three sections, first we will install PostgreSQL, then we will configure PostgreSQL for remote access and finally we will install pgAdmin web interface.

This article is for setting up a PostgreSQL 11 database server with pgAdmin 4 web interface. However, if you want to learn PostgreSQL 11 in detail then you should read Mastering PostgreSQL 11: Expert techniques to build scalable, reliable, and fault-tolerant database applications, 2nd Edition by Packt Publishing.

This Article Provides:

Environment Specification:

We have configured a CentOS 7 virtual machine with following specifications.

CPU - 3.4 Ghz (2 cores)
Memory - 2 GB
Storage - 20 GB
Operating System - CentOS 7.6
Hostname - postgresql-01.example.com
IP Address - 192.168.116.184/24

Installing PostgreSQL 11 on CentOS 7:

Connect with postgresql-01.example.com using ssh as root user.

In CentOS 7.6, PostgreSQL 9.2 is available in standard yum repositories.

But we are required to install latest version of PostgreSQL i.e. 11. Therefore, we have to add PostgreSQL yum repository in our CentOS 7 server and then we will be able to install PostgreSQL 11.

Yum repositories for various Linux distros can be obtained from PostgreSQL official download page.

Install PostgreSQL yum repository using following command.

[root@postgresql-01 ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror
pgdg-redhat-repo-latest.noarch.rpm                       | 5.6 kB     00:00
Examining /var/tmp/yum-root-zy9vpg/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-4.noarch
Marking /var/tmp/yum-root-zy9vpg/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-4 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

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

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

Total size: 6.8 k
Installed size: 6.8 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pgdg-redhat-repo-42.0-4.noarch                               1/1
  Verifying  : pgdg-redhat-repo-42.0-4.noarch                               1/1

Installed:
  pgdg-redhat-repo.noarch 0:42.0-4

Complete!

Disable old version PostgreSQL yum repositories.

[root@postgresql-01 ~]# yum-config-manager --disable pgdg10 pgdg94 pgdg95 pgdg96

Build cache for yum repositories.

[root@postgresql-01 ~]# yum makecache fast
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp3.isra.edu.pk
 * extras: ftp3.isra.edu.pk
 * updates: ftp3.isra.edu.pk
base                                                     | 3.6 kB     00:00
extras                                                   | 3.4 kB     00:00
pgdg11                                                   | 3.6 kB     00:00
updates                                                  | 3.4 kB     00:00
Metadata Cache Created

Install PostgreSQL 11 client and server packages using yum command.

[root@postgresql-01 ~]# yum install -y postgresql11 postgresql11-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp3.isra.edu.pk
 * extras: ftp3.isra.edu.pk
 * updates: ftp3.isra.edu.pk
Resolving Dependencies
--> Running transaction check
---> Package postgresql11.x86_64 0:11.4-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql11-libs(x86-64) = 11.4-1PGDG.rhel7 for package: postgresql11-11.4-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql11-11.4-1PGDG.rhel7.x86_64
---> Package postgresql11-server.x86_64 0:11.4-1PGDG.rhel7 will be installed
--> Running transaction check
---> Package postgresql11-libs.x86_64 0:11.4-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                  Arch        Version                 Repository   Size
================================================================================
Installing:
 postgresql11             x86_64      11.4-1PGDG.rhel7        pgdg11      1.6 M
 postgresql11-server      x86_64      11.4-1PGDG.rhel7        pgdg11      4.7 M
Installing for dependencies:
 postgresql11-libs        x86_64      11.4-1PGDG.rhel7        pgdg11      361 k

Transaction Summary
================================================================================
Install  2 Packages (+1 Dependent package)

Total download size: 6.7 M
Installed size: 29 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg11/packages/postgresql11-libs-11.4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Public key for postgresql11-libs-11.4-1PGDG.rhel7.x86_64.rpm is not installed
(1/3): postgresql11-libs-11.4-1PGDG.rhel7.x86_64.rpm       | 361 kB   00:04
(2/3): postgresql11-11.4-1PGDG.rhel7.x86_64.rpm            | 1.6 MB   00:07
(3/3): postgresql11-server-11.4-1PGDG.rhel7.x86_64.rpm     | 4.7 MB   00:15
--------------------------------------------------------------------------------
Total                                              357 kB/s | 6.7 MB  00:19
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-4.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql11-libs-11.4-1PGDG.rhel7.x86_64                    1/3
  Installing : postgresql11-11.4-1PGDG.rhel7.x86_64                         2/3
  Installing : postgresql11-server-11.4-1PGDG.rhel7.x86_64                  3/3
  Verifying  : postgresql11-11.4-1PGDG.rhel7.x86_64                         1/3
  Verifying  : postgresql11-server-11.4-1PGDG.rhel7.x86_64                  2/3
  Verifying  : postgresql11-libs-11.4-1PGDG.rhel7.x86_64                    3/3

Installed:
  postgresql11.x86_64 0:11.4-1PGDG.rhel7
  postgresql11-server.x86_64 0:11.4-1PGDG.rhel7

Dependency Installed:
  postgresql11-libs.x86_64 0:11.4-1PGDG.rhel7

Complete!

Initialize PostgreSQL database instance as follows.

[root@postgresql-01 ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK

Enable and start PostgreSQL service.

[root@postgresql-01 ~]# systemctl enable postgresql-11.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.
[root@postgresql-01 ~]# systemctl start postgresql-11.service

Connect as postgres user and set admin password.

[root@postgresql-01 ~]# su - postgres
-bash-4.2$ psql
psql (11.4)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD '123';
ALTER ROLE
postgres=# \q
-bash-4.2$ exit
logout

PostgreSQL 11 server has been installed on our CentOS 7 server.

Configuring PostgreSQL Remote Access on CentOS 7:

By default, PostgreSQL service runs locally on port 5432/tcp. However, if required we can configure it for remote access from other computers in our network.

Edit PostgreSQL configuration file.

[root@postgresql-01 ~]# vi /var/lib/pgsql/11/data/postgresql.conf

Find and set following directive in this file.

listen_addresses = '*'

Allow network clients to access PostgreSQL service in pg_hba.conf file.

[root@postgresql-01 ~]# echo "host all all 192.168.116.0/24 md5" >> /var/lib/pgsql/11/data/pg_hba.conf

Restart PostgreSQL service to apply changes.

[root@postgresql-01 ~]# systemctl restart postgresql-11.service

Allow PostgreSQL service in Linux firewall.

[root@postgresql-01 ~]# firewall-cmd --permanent --add-service=postgresql
success
[root@postgresql-01 ~]# firewall-cmd --reload
success

Our PostgreSQL service is configured for remote access.

Installing pgAdmin 4 Web Interface on CentOS 7:

To ease the process of Database Administration of PostgreSQL, we are installing a popular SQL web interface i.e. pgAdmin 4 on our CentOS 7 server.

pgAdmin 4 is available in the same PostgreSQL 11 yum repository.

But first we are installing EPEL (Extra Packages for Enterprise Linux) yum repository, because pgAdmin 4 requires some packages that are available in EPEL yum repository.

[root@postgresql-01 ~]# yum install -y epel-release
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp3.isra.edu.pk
 * extras: ftp3.isra.edu.pk
 * updates: ftp3.isra.edu.pk
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-11 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                Arch             Version         Repository        Size
================================================================================
Installing:
 epel-release           noarch           7-11            extras            15 k

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

Total download size: 15 k
Installed size: 24 k
Downloading packages:
epel-release-7-11.noarch.rpm                               |  15 kB   00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : epel-release-7-11.noarch                                     1/1
  Verifying  : epel-release-7-11.noarch                                     1/1

Installed:
  epel-release.noarch 0:7-11

Complete!

Build yum cache for EPEL repository.

[root@postgresql-01 ~]# yum makecache fast
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/x86_64/metalink                                     | 7.5 kB     00:00
 * base: ftp3.isra.edu.pk
 * epel: kartolo.sby.datautama.net.id
 * extras: ftp3.isra.edu.pk
 * updates: ftp3.isra.edu.pk
base                                                     | 3.6 kB     00:00
epel                                                     | 5.3 kB     00:00
extras                                                   | 3.4 kB     00:00
pgdg11                                                   | 3.6 kB     00:00
updates                                                  | 3.4 kB     00:00
(1/3): epel/x86_64/group_gz                                |  88 kB   00:02
(2/3): epel/x86_64/updateinfo                              | 989 kB   00:05
(3/3): epel/x86_64/primary_db                              | 6.7 MB   00:20
Metadata Cache Created

Install pgAdmin 4 using yum command.

[root@postgresql-01 ~]# yum install -y pgadmin4
...
Installed:
  pgadmin4.x86_64 0:4.10-2.rhel7

Dependency Installed:
  apr.x86_64 0:1.4.8-3.el7_4.1
  apr-util.x86_64 0:1.5.2-6.el7
  httpd.x86_64 0:2.4.6-89.el7.centos
  httpd-tools.x86_64 0:2.4.6-89.el7.centos
  libtomcrypt.x86_64 0:1.17-26.el7
  libtommath.x86_64 0:0.42.0-6.el7
  mailcap.noarch 0:2.1.41-2.el7
  mod_wsgi.x86_64 0:3.4-18.el7
  pgadmin4-docs.noarch 0:4.10-2.rhel7
  pgadmin4-python-Flask-Mail.noarch 0:0.9.1-4.rhel7.1
  pgadmin4-python-alembic.noarch 0:0.9.7-3.rhel7.1
  pgadmin4-python-babel.noarch 0:2.3.4-2.rhel7.1
  pgadmin4-python-backports.csv.noarch 1:1.0.5-3.rhel7.1
  pgadmin4-python-beautifulsoup4.noarch 0:4.5.1-3.rhel7.1
  pgadmin4-python-blinker.noarch 0:1.4-3.rhel7.1
  pgadmin4-python-dateutil.noarch 1:2.7.2-1.rhel7.1
  pgadmin4-python-flask.noarch 1:1.0.2-1.rhel7
  pgadmin4-python-flask-babel.noarch 0:0.11.1-4.rhel7.1
  pgadmin4-python-flask-babelex.noarch 0:0.9.3-1.rhel7.1
  pgadmin4-python-flask-gravatar.noarch 0:0.5.0-1.rhel7.1
  pgadmin4-python-flask-htmlmin.noarch 0:1.5.0-1.rhel7
  pgadmin4-python-flask-login.noarch 0:0.4.1-1.rhel7
  pgadmin4-python-flask-migrate.noarch 0:2.4.0-1.rhel7
  pgadmin4-python-flask-paranoid.noarch 0:0.2-1.rhel7.1
  pgadmin4-python-flask-principal.noarch 0:0.4.0-14.rhel7.1
  pgadmin4-python-flask-security.noarch 0:3.0.0-1.rhel7.1
  pgadmin4-python-flask-sqlalchemy.noarch 0:2.3.2-1.rhel7
  pgadmin4-python-flask-wtf.noarch 0:0.14.2-1.rhel7
  pgadmin4-python-htmlmin.noarch 0:0.1.12-1.rhel7.1
  pgadmin4-python-itsdangerous.noarch 0:0.24-10.rhel7.1
  pgadmin4-python-jinja2.noarch 0:2.8-9.rhel7.1
  pgadmin4-python-markupsafe.x86_64 0:0.23-13.rhel7.1
  pgadmin4-python-passlib.noarch 0:1.7.1-1.rhel7.1
  pgadmin4-python-psutil.x86_64 0:5.5.1-1.rhel7
  pgadmin4-python-simplejson.x86_64 0:3.16.0-1.rhel7
  pgadmin4-python-six.noarch 0:1.12.0-3.rhel7
  pgadmin4-python-sqlalchemy.x86_64 0:1.2.18-1.rhel7
  pgadmin4-python-sqlparse.noarch 0:0.2.4-1.rhel7.1
  pgadmin4-python-sshtunnel.noarch 0:0.1.4-1.rhel7
  pgadmin4-python-werkzeug.noarch 0:0.15.4-1.rhel7
  pgadmin4-python-wtforms.noarch 0:2.2.1-1.rhel7
  pgadmin4-pytz.noarch 0:2018.9-1.rhel7
  pgadmin4-web.noarch 0:4.10-2.rhel7
  pyOpenSSL.x86_64 0:0.13.1-4.el7
  python-babel.noarch 0:0.9.6-8.el7
  python-backports.x86_64 0:1.0-8.el7
  python-backports-ssl_match_hostname.noarch 0:3.5.0.1-1.el7
  python-beaker.noarch 0:1.5.4-10.el7
  python-blinker.noarch 0:1.3-2.el7
  python-cffi.x86_64 0:1.6.0-5.el7
  python-dateutil.noarch 0:1.5-7.el7
  python-editor.noarch 0:0.4-4.el7
  python-enum34.noarch 0:1.0.4-1.el7
  python-flask.noarch 1:0.10.1-4.el7
  python-idna.noarch 0:2.4-1.el7
  python-ipaddress.noarch 0:1.0.16-2.el7
  python-itsdangerous.noarch 0:0.23-2.el7
  python-jinja2.noarch 0:2.7.2-3.el7_6
  python-lxml.x86_64 0:3.2.1-4.el7
  python-mako.noarch 0:0.8.1-2.el7
  python-markupsafe.x86_64 0:0.11-10.el7
  python-paramiko.noarch 0:2.1.1-9.el7
  python-paste.noarch 0:1.7.5.1-9.20111221hg1498.el7
  python-ply.noarch 0:3.4-11.el7
  python-pycparser.noarch 0:2.14-1.el7
  python-setuptools.noarch 0:0.9.8-7.el7
  python-six.noarch 0:1.9.0-2.el7
  python-speaklater.noarch 0:1.3-1.el7
  python-tempita.noarch 0:0.5.1-6.el7
  python-werkzeug.noarch 0:0.9.1-2.el7
  python2-click.noarch 0:6.7-8.el7
  python2-crypto.x86_64 0:2.6.1-16.el7
  python2-cryptography.x86_64 0:1.7.2-2.el7
  python2-psycopg2.x86_64 0:2.8.3-1.rhel7
  python2-pyasn1.noarch 0:0.1.9-7.el7
  pytz.noarch 0:2016.10-2.el7

Complete!

pgAdmin 4 is a python based web application, therefore it requires a web server with python language support for deployment. Luckily, pgAdmin 4 automatically installs Apache HTTP server and Python language support during installation.

Enable and start Apache Service.

[root@postgresql-01 ~]# systemctl enable httpd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.
[root@postgresql-01 ~]# systemctl start httpd.service

Allow HTTP service in Linux firewall.

[root@postgresql-01 ~]# firewall-cmd --permanent --add-service=http
success
[root@postgresql-01 ~]# firewall-cmd --reload
success

pgAdmin 4 also installed a configuration file in Apache configuration directory. Therefore, to enable pgAdmin web application, we have to rename this file to make is readable by the httpd.service.

[root@postgresql-01 ~]# mv /etc/httpd/conf.d/pgadmin4.conf.sample /etc/httpd/conf.d/pgadmin4.conf

Edit /etc/httpd/conf.d/pgadmin4.conf file and the file contents after editing should be look like as follows. (The lines in yellow has been added here).

<VirtualHost *:80>
ServerName 192.168.116.184
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>

Define path variables in following file.

[root@postgresql-01 ~]# vi /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py

Add following settings therein.

SQLITE_PATH = '/var/lib/pgadmin/pgadmin.db'
SESSION_DB_PATH = '/var/lib/pgadmin/sessions'
STORAGE_DIR = '/var/lib/pgadmin/storage'
LOG_FILE = '/var/log/pgadmin/pgadmin.log'

Adjust file permissions.

[root@postgresql-01 ~]# chown -R apache:apache /var/log/pgadmin/
[root@postgresql-01 ~]# chown -R apache:apache /var/lib/pgadmin/

Set SELinux file contexts.

[root@postgresql-01 ~]# semanage fcontext -a -t httpd_sys_rw_content_t "/var/lib/pgadmin(/.*)?"
[root@postgresql-01 ~]# restorecon -R /var/lib/pgadmin/
[root@postgresql-01 ~]# semanage fcontext -a -t httpd_sys_rw_content_t "/var/log/pgadmin(/.*)?"
[root@postgresql-01 ~]# restorecon -R /var/log/pgadmin/

Set SELinux boolean, so that Apache can access PostgreSQL service port.

[root@postgresql-01 ~]# setsebool -P httpd_can_network_connect_db 1

Restart Apache service to apply changes.

[root@postgresql-01 ~]# systemctl restart httpd.service

Initialize pgAdmin 4 application as follows.

[root@postgresql-01 ~]# python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: ahmer@postgresql-01.example.com
Password:
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Browse URL http://postgresql-01.example.com/pgadmin4/ in client's browser.