Qadry
unixcop Admin

How to Install PostgreSQL with pgAdmin on CentOS 8

Introduction

PostgreSQL is the most advanced RDBMS and pgAdmin is a web UI for Postgres. Here, you will see how to install PostgreSQL with pgAdmin on CentOS / RHEL 8.

What is PostgreSQL?

PostgreSQL (also known as Postgres) is a free and open source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.PostgreSQL was originally named POSTGRES, referring to its origins as a successor to the Ingres database. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.It is the default database for macOS Server, and is also available for Linux, FreeBSD, OpenBSD, and Windows.

What is pgAdmin?

pgAdmin is free and open source graphical user interface (GUI) administration tool for Postgres database servers.

In addition to, pgAdmin is available in desktop and web interfaces. pgAdmin is the feature rich and most popular among the other administration tools for PostgreSQL.

Update Linux Server Packages

So Update Linux server packages by using dnf command.

# dnf update -y

Installing Postgres Official

To install latest version, we have to add PostgreSQL official yum repository in our Linux server.

[root@unixcop ~]# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 21:31:22 ago on Sun Aug  8 10:39:31 2021.
pgdg-redhat-repo-latest.noarch.rpm               18 kB/s |  12 kB     00:00    
Dependencies resolved.
================================================================================
 Package                 Architecture  Version        Repository           Size
================================================================================
Installing:
 pgdg-redhat-repo        noarch        42.0-19        @commandline         12 k

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

Total size: 12 k
Installed size: 12 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : pgdg-redhat-repo-42.0-19.noarch                        1/1 
  Verifying        : pgdg-redhat-repo-42.0-19.noarch                        1/1 

Installed:
  pgdg-redhat-repo-42.0-19.noarch                                               

Complete!
[root@unixcop ~]# 

Disable the PostgreSQL module in standard yum repository by executing following command.

# dnf -qy module disable postgresql

Installing Postgres Database Server on CentOS 8

So Install Postgres database server 13 (latest at the time of this writing) on your Linux server by using dnf command.

[root@unixcop ~]# dnf install -y postgresql13-server
Last metadata expiration check: 0:01:38 ago on Mon Aug  9 10:31:26 2021.
Dependencies resolved.
======================================================================================================================================================
 Package                                    Architecture                  Version                                 Repository                     Size
======================================================================================================================================================
Installing:
 postgresql13-server                        x86_64                        13.3-2PGDG.rhel8                        pgdg13                        5.5 M
Installing dependencies:
 libicu                                     x86_64                        60.3-2.el8_1                            baseos                        8.8 M
 postgresql13                               x86_64                        13.3-2PGDG.rhel8                        pgdg13                        1.5 M
 postgresql13-libs                          x86_64                        13.3-2PGDG.rhel8                        pgdg13                        413 k

Transaction Summary
======================================================================================================================================================
Install  4 Packages

Total download size: 16 M
Installed size: 63 M
Downloading Packages:
(1/4): postgresql13-libs-13.3-2PGDG.rhel8.x86_64.rpm                                                                   69 kB/s | 413 kB     00:06    
(2/4): postgresql13-13.3-2PGDG.rhel8.x86_64.rpm                                                                        92 kB/s | 1.5 MB     00:16    
(3/4): postgresql13-server-13.3-2PGDG.rhel8.x86_64.rpm                                                                158 kB/s | 5.5 MB     00:35    
(4/4): libicu-60.3-2.el8_1.x86_64.rpm                                                                                 190 kB/s | 8.8 MB     00:47    
------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                 345 kB/s |  16 MB     00:48     
warning: /var/cache/dnf/pgdg13-e81daebfc8b779ec/packages/postgresql13-13.3-2PGDG.rhel8.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
PostgreSQL 13 for RHEL/CentOS 8 - x86_64                                                                              1.6 MB/s | 1.7 kB     00:00    
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                              1/1 
  Installing       : postgresql13-libs-13.3-2PGDG.rhel8.x86_64                                                                                    1/4 
  Running scriptlet: postgresql13-libs-13.3-2PGDG.rhel8.x86_64                                                                                    1/4 
  Installing       : libicu-60.3-2.el8_1.x86_64                                                                                                   2/4 
  Running scriptlet: libicu-60.3-2.el8_1.x86_64                                                                                                   2/4 
  Installing       : postgresql13-13.3-2PGDG.rhel8.x86_64                                                                                         3/4 
  Running scriptlet: postgresql13-13.3-2PGDG.rhel8.x86_64                                                                                         3/4 
  Running scriptlet: postgresql13-server-13.3-2PGDG.rhel8.x86_64                                                                                  4/4 
  Installing       : postgresql13-server-13.3-2PGDG.rhel8.x86_64                                                                                  4/4 
  Running scriptlet: postgresql13-server-13.3-2PGDG.rhel8.x86_64                                                                                  4/4 
  Verifying        : libicu-60.3-2.el8_1.x86_64                                                                                                   1/4 
  Verifying        : postgresql13-13.3-2PGDG.rhel8.x86_64                                                                                         2/4 
  Verifying        : postgresql13-libs-13.3-2PGDG.rhel8.x86_64                                                                                    3/4 
  Verifying        : postgresql13-server-13.3-2PGDG.rhel8.x86_64                                                                                  4/4 

Installed:
  libicu-60.3-2.el8_1.x86_64                          postgresql13-13.3-2PGDG.rhel8.x86_64         postgresql13-libs-13.3-2PGDG.rhel8.x86_64        
  postgresql13-server-13.3-2PGDG.rhel8.x86_64        

Complete!
[root@unixcop ~]# 

You need to execute following command once to initialize the Postgres database.

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

[root@unixcop ~]# 

Also Enable, start and check status Postgres database with these commands.

[root@unixcop ~]# systemctl start postgresql-13
[root@unixcop ~]# systemctl enable postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.
[root@unixcop ~]# systemctl status postgresql-13
postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-08-09 08:21:20 EDT; 21s ago
     Docs: https://www.postgresql.org/docs/13/static/
 Main PID: 2178 (postmaster)
    Tasks: 8 (limit: 11426)
   Memory: 16.8M
   CGroup: /system.slice/postgresql-13.service
           ├─2178 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─2179 postgres: logger 
           ├─2181 postgres: checkpointer 
           ├─2182 postgres: background writer 
           ├─2183 postgres: walwriter 
           ├─2184 postgres: autovacuum launcher 
           ├─2185 postgres: stats collector 
           └─2186 postgres: logical replication launcher 

Aug 09 08:21:20 unixcop systemd[1]: Starting PostgreSQL 13 database server...
Aug 09 08:21:20 unixcop postmaster[2178]: 2021-08-09 08:21:20.237 EDT [2178] LOG:  redirecting log output to logging collector process
Aug 09 08:21:20 unixcop postmaster[2178]: 2021-08-09 08:21:20.237 EDT [2178] HINT:  Future log output will appear in directory "log".
Aug 09 08:21:20 unixcop systemd[1]: Started PostgreSQL 13 database server.
[root@unixcop ~]# systemctl is-enabled postgresql-13
enabled
[root@unixcop ~]# 

Also Check the version of installed PostgreSQL .

[root@unixcop ~]# psql -V
psql (PostgreSQL) 13.3
[root@unixcop ~]# 

Switch to postgres user and connect to psql shell to set admin user password.

[root@unixcop ~]# su - postgres
[postgres@unixcop ~]$ psql
psql (13.3)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'unixcop';
ALTER ROLE
postgres=# \q
[postgres@unixcop ~]$ exit
logout
[root@unixcop ~]# 

Configure Network Access for Postgres Database Service

You may notice that the PostgreSQL service is initially runs on localhost interface only.

So you have to edit PostgreSQL configuration file in vim text editor.

# vi /var/lib/pgsql/13/data/postgresql.conf

Search for following directive therein.

# listen_addresses = 'localhost'

And replace it with the following directive.

listen_addresses = '*'

Your Postgres database service is now configured to listen on all network interfaces.

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

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

Restart Postgres database service to apply changes.

# systemctl restart postgresql-13.service

Postgres Service is now running on all network interfaces.

Configure Linux Firewall for Postgres Database

We will allow Postgre service by using firewall-cmd command.

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

So, Postgres database server has been installed on Linux server.

Installing pgAdmin Yum Repository on CentOS 8

pgAdmin is a popular web interface for database administration of PostgreSQL databases.

Although pgAdmin is also provided within PostgreSQL official yum repositories. But it doesn’t work on our CentOS 8 server.

Therefore, we will install the latest stable version of pgAdmin from pgAdmin official yum repository.

Because we will add this yum repo, first you need to remove the PostgreSQL yum repositories from your Linux server.

# dnf remove -y pgdg-redhat-repo

Add the pgAdmin official yum repository in your Linux operating system with command:

[root@unixcop ~]#  dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm
Last metadata expiration check: 1 day, 0:04:44 ago on Sun Aug  8 10:39:31 2021.
pgadmin4-redhat-repo-1-1.noarch.rpm                                                                                   5.6 kB/s | 6.6 kB     00:01    
Dependencies resolved.
======================================================================================================================================================
 Package                                      Architecture                   Version                       Repository                            Size
======================================================================================================================================================
Installing:
 pgadmin4-redhat-repo                         noarch                         1-1                           @commandline                         6.6 k

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

Total size: 6.6 k
Installed size: 4.0 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                              1/1 
  Installing       : pgadmin4-redhat-repo-1-1.noarch                                                                                              1/1 
  Verifying        : pgadmin4-redhat-repo-1-1.noarch                                                                                              1/1 

Installed:
  pgadmin4-redhat-repo-1-1.noarch                                                                                                                     

Complete!
[root@unixcop ~]# 

Also pgAdmin requires some software packages that are not available in standard yum repositories.

Use dnf command and install EPEL yum repository.

# dnf install -y epel-release

Build cache for newly installed yum repositories with command:

[root@unixcop ~]# dnf makecache
CentOS Linux 8 - AppStream                                                                                            2.6 kB/s | 4.3 kB     00:01    
CentOS Linux 8 - BaseOS                                                                                               5.4 kB/s | 3.9 kB     00:00    
CentOS Linux 8 - Extras                                                                                               2.8 kB/s | 1.5 kB     00:00    
Extra Packages for Enterprise Linux Modular 8 - x86_64                                                                 21 kB/s |  36 kB     00:01    
Extra Packages for Enterprise Linux 8 - x86_64                                                                         22 kB/s |  33 kB     00:01    
pgadmin4                                                                                                              704  B/s | 833  B     00:01    
pgadmin4                                                                                                              3.8 MB/s | 3.8 kB     00:00    
Importing GPG key 0x210976F2:
 Userid     : "Package Manager (Package Signing Key) <packages@pgadmin.org>"
 Fingerprint: E869 7E2E EF76 C02D 3A63 3277 8881 B2A8 2109 76F2
 From       : /etc/pki/rpm-gpg/PGADMIN_PKG_KEY
Is this ok [y/N]: y
pgadmin4                                                                                                              117 kB/s | 441 kB     00:03    
Metadata cache created.

Installing pgAdmin Web UI

You can now install pgAdmin by using :

# dnf install -y pgadmin4

IMPORTANT NOTE:

To configure SELinux policies, the pgAdmin setup script requires semanage command, which is provided in policycoreutils-python-utils packages. Therefore, you should install it before executing pgAdmin setup script.

Also you can visit this link to fix this problem correctly How to Fix ‘semanage command’ Not Found Error in CentOS

# dnf install -y policycoreutils-python-utils

The pgAdmin software comes with a well written configuration script to configure pgAdmin web service. Execute it to create a admin user and Apache web server to deploy pgAdmin web service.

[root@unixcop ~]# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.

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

Email address: qadry@postgres-01.unixcop.com
Password: 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Creating storage and log directories...
Configuring SELinux...

The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
[root@unixcop ~]# 

Start, enable and check status of httpd service.

[root@unixcop ~]# systemctl start httpd
[root@unixcop ~]# systemctl enable httpd
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
[root@unixcop ~]# systemctl status httpd
 httpd.service - The Apache HTTP Server
   Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-08-09 08:48:49 EDT; 16s ago
     Docs: man:httpd.service(8)
 Main PID: 3881 (httpd)
   Status: "Running, listening on: port 80"
    Tasks: 241 (limit: 11426)
   Memory: 44.2M
   CGroup: /system.slice/httpd.service
           ├─3881 /usr/sbin/httpd -DFOREGROUND
           ├─3882 /usr/sbin/httpd -DFOREGROUND
           ├─3883 /usr/sbin/httpd -DFOREGROUND
           ├─3884 /usr/sbin/httpd -DFOREGROUND
           ├─3885 /usr/sbin/httpd -DFOREGROUND
           └─3886 /usr/sbin/httpd -DFOREGROUND

Aug 09 08:48:47 unixcop systemd[1]: Starting The Apache HTTP Server...
Aug 09 08:48:47 unixcop httpd[3881]: [Mon Aug 09 08:48:47.749981 2021] [so:warn] [pid 3881:tid 140142522952000] AH01574: module wsgi_module is alread>
Aug 09 08:48:49 unixcop httpd[3881]: AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using fe80::20c:29ff:feca>
Aug 09 08:48:49 unixcop systemd[1]: Started The Apache HTTP Server.
Aug 09 08:48:52 unixcop httpd[3881]: Server configured, listening on: port 80
lines 1-21/21 (END)

Configure Linux firewall to allow incoming traffic to Apache web server

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

Open URL http://your_ip_address/pgadmin4/ in a web browser.

Login to pgAdmin as admin user that we have created by setup-web.sh script.

To add your PostgreSQL database server in pgAdmin inventory, click on “Add New Server”.

Provide the Server name and click on “Connection” tab.

Provide the database connection information in this dialog box that you created.

Then Click on “Save”.

Our Postgres database server has been added in pgAdmin. You can see a tree of you database server in the left side panel.

Conclusion

In this tutorial, you have learned how to install PostgreSQL with pgAdmin on CentOS 8 server.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

x