CentOS

How to Install pgAdmin4 on CentOS 8

Introduction

pgAdmin is one of the most popular tools for managing PostgreSQL database. It was a feature-rich PostgreSQL administration tool. A complete rewrite from the previous pgAdmin version, built with Python and JavaScript.

The pgAdmin4 designed for multiple PostgreSQL versions supports for PostgreSQL 9.2 and above. Multiplatform that can run on Linux, Mac, and Windows. Also, it provides multiple deployment models, you can deploy as a single Desktop application, or deploy as a web-based application.

What we will do?

For this tutorial, you will learn how to install and configure the pgAdmin4 on the CentOS 8 Server. This guide will cover some topics about the pgAdmin4 operation, such as connecting to the PostgreSQL server, create a new database, and create a new user.

Prerequisites

To complete this guide, ensure you’ve got the CentOS 8 server with the PostgreSQL installed on your environment. Also, you must access your server and have the root privileges.

You can use the PostgreSQL v10 that can be installed through the CentOS AppStream repository, or using the PostgreSQL 12 which is available on the official Repository.

To use the PostgreSQL v10, you must complete the following guide.

How to Install PostgreSQL on CentOS 8

To use the PostgreSQL v12, you must complete the following guide.

How to Install PostgreSQL 12 on CentOS 8

Before going any further, log in to your Ubuntu server and type the sudo command to get the root privileges on your system.

sudo su

Step 1 – Add EPEL and PostgreSQL repository for CentOS 8

First, you must add the EPEL and PostgreSQL repository to your CentOS 8 system.

The EPEL repository is must be installed because the PostgreSQL needs some packages which only can be installed from the EPEL repository.

Install the EPEL and PostgreSQL repository for CentOS 8 system using the dnf command below.

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

After that, you must disable the default PostgreSQL repository provides by the CentOS AppStream because of the pgAdmin4 only available on the official PostgreSQL repository.

dnf -qy module disable postgresql

Now check all available repository on the CentOS system.

dnf repolist

And below is the result you will get.

Add EPEL and PostgreSQL Repository

As a result, you’ve added the EPEL and PostgreSQL repository to the CentOS 8 system, and you’re ready to install the pgAdmin.

Step 2 – Install the pgAdmin4 to CentOS 8

Before going any further, check the pgAdmin4 package using the command below.

dnf info pgadmin4

Below is the result you will get.

Show pgAdmin4 Packages on CentOS 8

As can be seen, the PostgreSQL management tool pgadmin4 v4.21 is available on the ‘pgdg-common’ repository.

Install the pgAdmin4 package for CentOS 8 using the following command.

dnf install pgadmin4 -y

The command above will install some additional python packages and Apache HTTPd webserver.

Once all installation is finished, go to the ‘/etc/httpd/conf.d’ directory and copy the smaple configuration ‘pgadmin4.conf.sample’ to ‘pgadmin4.conf’.

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

Next, test the httpd configuration and make sure there no error.

apachectl configtest

Now start and enable the httpd service, then check the httpd service.

systemctl enable --now httpd
systemctl status httpd

Below is the result you will get.

Install pgAdmin4 on CentOS 8

As a result, the httpd service is up and running with pgAdmin4 configuration enabled on it. the pgAdmin4 is accessible through the sub-folder ‘/pgadmin4’ on the URL.

Step 3 – Configure pgAdmin4

After installing the pgAdmin4 packages, you need to create the data directory and log directory for storing pgAdmin files.

Create the pgAdmin4 data directory on ‘/var/lib/pgadmin4’ and the log directory on ‘/var/log/pgadmin4’ uisg the mkdir command below.

mkdir -p /var/log/pgadmin4
mkdir -p /var/lib/pgadmin4

After that, edit the pgAdmin configuration ‘/usr/lib/python3.6/site-packages/pgadmin4-web/config_distro.py’ using vim editor.

vim /usr/lib/python3.6/site-packages/pgadmin4-web/config_distro.py

Paste the following configuration to the bottom of the line.

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'

Save and close.

Next, create the user for the pgAdmin4 using the following command.

python3 /usr/lib/python3.6/site-packages/pgadmin4-web/setup.py

And you will be asked for the email address and the password that will be used to log in to the pgAdmin4.

Configure pgAdmin4 on CentOS 8

Type your email address and password, and you’ve created the user for pgAdmin4 authentication.

Next, you must fix the ownership of pgAdmin4 data and log directory. Change the ownership of both pgAdmin4 data and log directories to the user ‘apache’.

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

And as a result, you’ve successfully installed the pgAdmin4 to the CentOS 8 system.

Step 4 – Set up SELinux and Firewalld

For this tutorial, we will run the pgAdmin4 with the SELinux and firewalld enabled.

First, fix the SELinux context for pgAdmin data and log directories using the following command.

chcon -t httpd_sys_rw_content_t /var/log/pgadmin4 -R
chcon -t httpd_sys_rw_content_t /var/lib/pgadmin4 -R

After that, allow the httpd service to connect to the network.

setsebool -P httpd_can_network_connect 1

Next, add the HTTP and HTTPS services to the firewalld rules using the command below.

Add HTTP and HTTPS Services to Firewall

firewall-cmd --add-service={http,https} --permanent

Now reload the firewalld and restart the httpd service.

firewall-cmd --reload
systemctl restart httpd

Setup SELinux and Firewalld pgAdmin4 on CentOS 8

As a result, you’ve successfully set up the SELinux and firewalld for pgAdmin4. In effect, you’re ready to connect to your PostgreSQL server through the pgAdmin4.

Step 5 – Connect to PostgreSQL Server using pgAdmin4

Open your web browser and type the pgAdmin4 server IP address following by the default path ‘/pgadmin4’ on the address bar.

http://server-ip/pgadmin4/

Now you will get the pgAdmin4 login page.

pgAdmin4 Login Page on CentOS 8

Type the email address and password that you just created during the installation, then click the ‘Login‘ button.

And as a result, you will get the pgAdmin dashboard.

Common Bug on pgAdmin4 Virtualhost configuration
After you’ve logged in to the pgAdmin4, you will get the default Apache index.html. It’s fine, you can just add the path url ‘/pgadmin4’ again on the address bar and you will get the pgAdmin4 Dashboard.

Next, connect to your PostgreSQL server using your user and password, click the ‘Add Server‘ button on the dashboard and you will be prompted the new window for configuring details about your PostgreSQL server.

Connect to PostgreSQL Server using pgAdmin4

On the ‘General‘ tab, type the name of your connection.

Name: postgresql-server1

Move to the ‘Connection‘ tab and type details about your PostgreSQL server.

Host name/address: 127.0.0.1
Port: 5432
Maintenance database: your-database-name
Username : yoour-username
Password: your-password

Now click the ‘Save‘ button and you be automatically connected to your PostgreSQL server as below.

Install pgAdmin4 on CentOS 8

As a result, you’ve successfully connected to the PostgreSQL server through the pgAdmin4 dashboard.

Step 6 – Create a New Database using pgAdmin4

To create a new database using the pgAdmin4, make sure that you’re connected to the PostgreSQL server.

Now look at the left menu inside your PostgreSQL connecttion, click the ‘Databases‘ section, then click ‘Object‘ > ‘Create‘ > ‘Database‘.

Create Database using pgAdmin4

On the ‘General‘ tab, type your database name and change the owner with your own, or you can use the default user ‘postgres’ as the owner.

Database: database-name
Owner: username/default-postgres

Now click the ‘Save‘ button.

Create Database using pgAdmin4

As a result, you’ve created a new PostgreSQL database using the pgAdmin4.

Step 7 – Create a New User/Role using pgAdmin4

After creating a new database, you will create a new user/Role using the pgAdmin4.

On the left side of your PostgreSQL connection, clikc the ‘Login/Group Roles‘ section, then click ‘Object‘ > ‘Create‘ > ‘Login/Group Role…‘.

Create New User/Role using pgAdmin4

On the ‘General‘ tab, type your new username.

Name: username

Move to the ‘Definition‘ tab and type your password.

Password: your-password

Next, move again to the ‘Privileges‘ tab and enable the following privileges.

Can login? Yes
Create database? Yes

Now click the ‘Save‘ button.

Create New User/Role using pgAdmin4

As a result, you’ve created a new PostgreSQL user/Role using pgAdmin4.

Step 8 – Create a New Table

To create a new table, click the drop-down icon on your database name, click ‘Schemas‘, then click ‘Tables‘. Now click the ‘Object‘ menu on top, then click ‘Create‘ and ‘Table‘.

Create Table pgAdmin4

On the ‘General‘ tab, type your table name, choose the owner, and default tablespace.

Name: table-name
Owner: username
Scheme: Public
Tablespace: pg_default

Now move to the ‘Columns’ section, click the + button to create new columns as below.

Create Column Table

At this stage, you can click the ‘Save‘ button.

As a result, you’ve created a new table on your PostgreSQL database through the pgAdmin4 dashboard.

Step 9 – Grant Privileges of Database to User

After creating the PostgreSQL tables, you will grant privileges of your database to another user.

Right-click on your database name and click the ‘Grant Wizard‘.

Grant Privileges of Database

Now make a mark for all objects on the database and click ‘Next‘.

Gran All Tablest Privileges of Database

Add your user by clicking the ‘+’ button and select the privilege as you want, then click ‘Next‘.

Select user for Grant Database Tables

Now click the ‘Finish‘ button.

Select user for Grant Database Tables

As a result, you’ve successfully granted your user privileges to access the database.

Step 10 – Insert Data using the pgAdmin4 Query Tool

The pgAdmin4 provides a powerful ‘Query Tool’ that allows you to execute arbitrary SQL query command and see and review the result of your queries.

To insert data to your table, click on your table name, and choose the ‘Tools‘ menu on top, click the ‘Query Tool‘.

Open the Query Tool pgAdmin4

And you will get the new tab of Query Tool on your dashboard.

Insert your PostgreSQL query on the box and click the ‘Play‘ button as below.

Insert Data using Query Tool pgAdmin4

Now you will get the successful response of your SQL queries at the bottom ‘Messages‘ tab.

Query returned successfully in 304 msec.

As a result, you’ve successfully insert new data to the PostgreSQL database using the pgAdmin4 Query Tool.

And finally, you’ve successfully installed the pgAdmin4 on the CentOS 8 server. Also, you’ve learned the basic usage of the pgAdmin4 tool for managing the PostgreSQL server.

Write A Comment