How to install PostgreSQL on CentOS 7

Share on Social Media

Learn how to install PostgreSQL on CentOS 7 with our step-by-step guide. Set up and configure your database server efficiently with easy-to-follow instructions. #centlinux #linux #postgres

What is PostgreSQL?

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.

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 (PAID LINK) by Packt Publishing.

Read Also: Install PostgreSQL on Rocky Linux 9

Linux Server 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

Install PostgreSQL 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.

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

Disable old version PostgreSQL yum repositories.

# yum-config-manager --disable pgdg10 pgdg94 pgdg95 pgdg96

Build cache for yum repositories.

# 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 on CentOS 7 by using yum command.

# yum install -y postgresql11 postgresql11-server

Initialize PostgreSQL database instance as follows.

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

Enable and start PostgreSQL service.

# 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.
# systemctl start postgresql-11.service

Connect as postgres user and set admin password.

# 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.

Configure PostgreSQL Remote Access

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.

# 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.

# echo "host all all 192.168.116.0/24 md5" >> /var/lib/pgsql/11/data/pg_hba.conf

Restart PostgreSQL service to apply changes.

# systemctl restart postgresql-11.service

Allow PostgreSQL service in Linux firewall.

# firewall-cmd --permanent --add-service=postgresql
success
# firewall-cmd --reload
success

Our PostgreSQL service is configured for remote access.

Install pgAdmin4 on CentOS 7

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

pgAdmin4 is available in the same PostgreSQL 11 yum repository.

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

# yum install -y epel-release

Build yum cache for EPEL repository.

# yum makecache fast

Install pgAdmin4 using yum command.

# yum install -y pgadmin4

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

Enable and start Apache Service.

# systemctl enable httpd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.
# systemctl start httpd.service

Configure Linux Firewall

Allow HTTP service in Linux firewall.

# firewall-cmd --permanent --add-service=http
success
# firewall-cmd --reload
success

Configure Apache for pgAdmin4

pgAdmin4 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.

# 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.

# 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.

# chown -R apache:apache /var/log/pgadmin/
# chown -R apache:apache /var/lib/pgadmin/

Set SELinux file contexts.

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

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

# setsebool -P httpd_can_network_connect_db 1

Restart Apache service to apply changes.

# systemctl restart httpd.service

Initialize pgAdmin4 application as follows.

# 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
======================================

Access pgAdmin4 Web Interface

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

pgAdmin4 Login
pgAdmin4 Login

Login with Email and Password that we have defined in previous step.

pgAdmin4 Dashboard 1
pgAdmin4 Dashboard 1

Click on Add New Server to add our PostgreSQL database server to pgAdmin4 web interface.

pgAdmin4 - Create Server 1
pgAdmin4 – Create Server 1

Provide the necessary information as per above screenshot.

Click on Connection Tab and provide connection information as per following screenshot.

pgAdmin4 - Create Server 2
pgAdmin4 – Create Server 2

Click on Save.

pgAdmin4 Dashboard 2
pgAdmin4 Dashboard 2

Our PostgreSQL database has been added in pgAdmin4 SQL web interface.

Recommended Training for You: PostgreSQL Database Administration on Windows/Linux- Part 2

4006704 72fb 9show?id=oLRJ54lcVEg&offerid=1606991.4006704&bids=1606991

Final Thoughts

If you found this guide helpful and need further assistance with PostgreSQL installation or any other technical support, consider hiring a professional. Check out my Fiverr profile for expert services in database setup, configuration, and more. Let’s make your project a success!

16 thoughts on “How to install PostgreSQL on CentOS 7”

  1. Please help me,
    i have a problen when I try to install pgadmin 4 on Oracle Linux Server release 7.7
    I have epel/x86_64 repo allredy installed, but when i start yum -y install pgadmin4, the next error appears:

    …………..
    Error: Package: pgadmin4-python-flask-principal-0.4.0-14.rhel7.1.noarch (pgdg11)
    Requires: python-flask
    …………..
    and I don't know how to install it
    Thanks

  2. another error related to the first error I posted, please I need help

    ~]# restorecon -R /var/lib/pgadmin/
    restorecon: lstat(/var/lib/pgadmin) failed: No such file or directory

    ~]# restorecon -R /var/log/pgadmin/
    restorecon: lstat(/var/log/pgadmin) failed: No such file or directory

    ~]# 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: trainee@sqlserv.example.com
    Password:
    Retype password:
    Password must be at least 6 characters. Please try again.
    Password:
    Retype password:
    Traceback (most recent call last):
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/setup.py", line 413, in
    setup_db()
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/setup.py", line 347, in setup_db
    app = create_app()
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/__init__.py", line 400, in create_app
    driver.init_app(app)
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/utils/driver/__init__.py", line 40, in init_app
    DriverRegistry.load_drivers()
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/utils/driver/registry.py", line 88, in load_drivers
    module = import_module(module_name)
    File "/usr/lib64/python2.7/importlib/__init__.py", line 37, in import_module
    __import__(name)
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/utils/driver/psycopg2/__init__.py", line 28, in
    from .server_manager import ServerManager
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/pgadmin/utils/driver/psycopg2/server_manager.py", line 30, in
    from sshtunnel import SSHTunnelForwarder, BaseSSHTunnelForwarderError
    File "/usr/lib/python2.7/site-packages/pgadmin4-web/sshtunnel.py", line 25, in
    import paramiko
    File "/usr/lib/python2.7/site-packages/paramiko/__init__.py", line 30, in
    from paramiko.transport import SecurityOptions, Transport
    File "/usr/lib/python2.7/site-packages/paramiko/transport.py", line 55, in
    from paramiko.dsskey import DSSKey
    File "/usr/lib/python2.7/site-packages/paramiko/dsskey.py", line 27, in
    from cryptography.hazmat.primitives.asymmetric.utils import (
    ImportError: cannot import name decode_dss_signature
    ~]#

  3. Please Help I am having the following errors:

    ~]# chown -R apache:apache /var/log/pgadmin/
    chown: cannot access ‘/var/log/pgadmin/’: No such file or directory

    ~]# chown -R apache:apache /var/lib/pgadmin/
    chown: cannot access ‘/var/lib/pgadmin/’: No such file or directory

  4. Hi friend, I did everything as you did but, when I execute "[root@soft4you pgadmin4-web]# python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py" I received the following error:
    "python: can't open file '/usr/lib/python2.7/site-packages/pgadmin4-web/setup.py': [Errno 2] No such file or directory" and I can´t find a solution. Please help.

  5. I tried to locate pgadmin4 but it seems it's not here. I reapeat the process completly.
    [root@soft4you bin]# find / -name pgadmin4
    /usr/pgadmin4
    [root@soft4you pgadmin4]# ls -l
    total 4
    drwxr-xr-x 2 root root 4096 Jun 23 22:55 bin
    [root@soft4you pgadmin4]# cd bin
    [root@soft4you bin]# ls
    pgadmin4-web-setup.sh

    /run/pgadmin4
    (this folder is empty)
    /var/lib/pgadmin4
    /var/log/pgadmin4

  6. I double check the steps on the server configuration and everything was according to your guide, but still have the 500 Internal Server Error and cannot open the URL, I try with localhost/pgadmin4 or IP_Computer/pgadmin4
    any idea of why I'm having this issue

Leave a Comment