Install PostgreSQL with pgAdmin on CentOS / RHEL 8 - CentLinux

Latest

Tuesday, 24 November 2020

Install PostgreSQL with pgAdmin on CentOS / RHEL 8

Install PostgreSQL with pgAdmin on CentOS 8

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.

 

Table of Contents:

 

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. (Source: Wikipedia)

PostgreSQL is a free software and distributed under PostgreSQL License (free and open-source, permissive).

 

What is pgAdmin? :

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

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

Just like PostgreSQL, pgAdmin is also a free software. It is also distributed under PostgreSQL license.

 

Environment Specification:

We are using a minimal CentOS 8 virtual machine with following specifications.

  • CPU - 3.4 Ghz (2 cores)
  • Memory - 2 GB
  • Storage - 20 GB
  • Operating System - CentOS 8.2
  • Hostname – postgres-01.centlinux.com
  • IP Address - 192.168.116.230 /24

 

Update Linux Server Packages:

Use a ssh client and login to postgres-01.centlinux.com as root user.

Update Linux server packages by using dnf command.

# dnf update -y
Last metadata expiration check: 0:00:58 ago on Sun 22 Nov 2020 08:26:21 PM PKT.
Dependencies resolved.
Nothing to do.
Complete!

Our Linux operating system is already up-to-date.

Check the Linux operating system and Kernel version.

# uname -r
4.18.0-193.28.1.el8_2.x86_64

# cat /etc/redhat-release
CentOS Linux release 8.2.2004 (Core)

 

Installing Postgres Official Yum Repository:

Although Postgres database server is available in Linux yum repositories, but to install latest version, we have to add PostgreSQL official yum repository in our Linux server.

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

01-dnf-install-postgres-repo

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

# dnf -qy module disable postgresql

 

Installing Postgres Database Server on CentOS 8:

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

# dnf install -y postgresql13-server

02-dnf-install-postgresql-server

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

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

Enable and start Postgres database service.

# systemctl enable --now postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service â /usr/lib/systemd/system/postgresql-13.service.

Check the status of Postgres database service.

# systemctl status postgresql-13.service

03-systemctl-status-postgresql-service

Check the version of installed PostgreSQL server.

# psql -V
psql (PostgreSQL) 13.1

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

# su - postgres
$ psql
psql (13.1)
Type "help" for help.

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

 

Configure Postgres Database Service for Network Access:

PostgreSQL default service port is 5432/tcp. Execute the following command to verify that PostgreSQL service is listening on this port.

# ss -tulpn | grep 5432
tcp     LISTEN   0        128            127.0.0.1:5432          0.0.0.0:*       users:(("postmaster",pid=1603,fd=7))
tcp     LISTEN   0        128                [::1]:5432             [::]:*       users:(("postmaster",pid=1603,fd=6))

As you may notice that the PostgreSQL service is initially runs on localhost interface only. However, to make our Postgres database available for network clients, you have to configure PostgreSQL to run on all (or on some specific) interfaces.

For this purpose, 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.116.0/24 md5" >> /var/lib/pgsql/13/data/pg_hba.conf

Restart Postgres database service to apply changes.

# systemctl restart postgresql-13.service

Again check the network services by using ss command.

# ss -tulpn | grep 5432
tcp     LISTEN   0        128              0.0.0.0:5432          0.0.0.0:*       users:(("postmaster",pid=1781,fd=6))
tcp     LISTEN   0        128                 [::]:5432             [::]:*       users:(("postmaster",pid=1781,fd=7))

Postgres Service is now running on all network interfaces.

 

Configure Linux Firewall for Postgres Database Service:

In CentOS 8, we already have a firewall service for PostgreSQL, therefore we are allowing it by using firewall-cmd command. However, you can create a custom firewall service of your own, if your PostgreSQL server is configured to listen on a non-default port.

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

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 are installing the latest stable version of pgAdmin from pgAdmin official yum repository.

For this purpose, first you need to remove the PostgreSQL yum repositories from your Linux server.

# dnf remove -y pgdg-redhat-repo

04-dnf-remove-postgres-repo

Now, add the pgAdmin official yum repository in your Linux operating system.

# dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm

05-dnf-install-pgadmin4-repo

You are also required to install EPEL (Extra Packages for Enterprise Linux) yum repository, because 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.

# dnf makecache
CentOS-8 - AppStream                            2.1 kB/s | 4.3 kB     00:02
CentOS-8 - Base                                 3.7 kB/s | 3.9 kB     00:01
CentOS-8 - Extras                               768  B/s | 1.5 kB     00:02
Extra Packages for Enterprise Linux Modular 8 -  24 kB/s |  98 kB     00:04
Extra Packages for Enterprise Linux 8 - x86_64   92 kB/s | 8.3 MB     01:32
pgadmin4                                        1.1 kB/s | 2.9 kB     00:02
Metadata cache created.

 

Installing pgAdmin Web UI on CentOS 8:

All repositories has been added. You can now install pgAdmin by using dnf command.

# dnf install -y pgadmin4

06-dnf-install-pgadmin4

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.

# dnf install -y policycoreutils-python-utils

07-dnf-install-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, configure SELinux policies and Apache web server to deploy pgAdmin web service.

# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat 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: ahmer@postgres-01.centlinux.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

The above script installs a configuration file (/etc/httpd/conf.d/pgadmin4.conf) to deploy pgAdmin on Apache web server.

No customization is required in this file. You are only required to enable and start httpd service.

# systemctl enable --now httpd.service
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service â /usr/lib/systemd/system/httpd.service.

Configure Linux firewall to allow incoming traffic to Apache web server.

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

Open URL http://postgres-01.centlinux.com/pgadmin4/ in a web browser.

01-install-postgresql-pgadmin-on-centos-8-login

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

02-install-postgresql-pgadmin-on-centos-8-dashboard

After successful login, you may reach at the pgAdmin dashboard.

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

03-install-postgresql-pgadmin-on-centos-8-add-new-server-general

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

04-install-postgresql-pgadmin-on-centos-8-add-new-server-connection

Provide the database connection information in this dialog box as we did in the above screenshots.

Click on "Save".

05-install-postgresql-pgadmin-on-centos-8-dashboard

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 installation guide, you have learned how to install PostgreSQL with pgAdmin on CentOS 8 server. For learning more about Postgres databases, we recommend that you should read Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13 by Packt Publishing

No comments:

Post a Comment