How to Install and Use pgAdmin 4 on Rocky Linux 8

pgAdmin is a free and open-source graphical administration tool for PostgreSQL, one of the most advanced open-source databases. pgAdmin is a feature-rich PostgreSQL administration tool with a simple interface and easy to use. It supports PostgreSQL 9.6 and above, and it can be run on multiple operating systems, including Windows, macOS, and Linux.

Unlike other graphical database administration tools, the pgAdmin can be run on 3 different modes:

  • Desktop mode - can be installed as a user application on your laptop and computer. This method is recommended ways if you want to install pgAdmin on your laptop or computer.
  • Server mode - this type of deployment can be applied on the server. It's like phpMyAdmin on MySQL.
  • Container mode - can be run as a docker container.

In this guide, you will learn how to install pgAdmin on a Rocky Linux 8 server. You will learn how to set up pgAdmin with Apache web server, and learn the basic usage of pgAdmin for managing PostgreSQL database and user.

Prerequisites

  • An Rocky Linux server. Ensure all packages are updated to the latest version.
  • A root user or a user with root privileges. This user will be used for installing new packages and editing system configurations.
  • PostgreSQL database server - or you can install it using the following guide.

Installing PostgreSQL on Rocky Linux

If you have got PostgreSQL installed on your system you can skip this first step. Otherwise, you can use the following step for installing PostgreSQL on Rocky Linux. And if you want in flavor, t use the PostgreSQL from the official PostgreSQL repository (not from Rocky Linux repository), you may look for this guide.

For this step, you will be installing PostgreSQL from the official Rocky Linux repository and enable the authentication on PostgreSQL.

1. By default, the Rocky Linux repository provides multiple versions of PostgreSQL packages, the PostgreSQL 10, 12, and 13.

Execute the following command to check the PostgreSQL repository on Rocky Linux.

sudo dnf module list postgresql

You will see multiple versions of PostgreSQL modules as below.

Check PostgreSQL Repository Module

Enable the PostgreSQL 13 repository using the following command.

sudo dnf module enable postgresql:13

Type 'y' and press 'Enter' to enable the repository module.

Enable PostgreSQL repository module

2. Now execute the following command to install PostgreSQL server packages.

sudo dnf install postgresql postgresql-server

Type 'y' and press 'Enter' to continue the installation.

Installing PostgreSQL Server

3. If the PostgreSQL installation is complete, initialize the PostgreSQL data using the command below.

sudo postgresql-setup --initdb

Next, change the working directory to '/var/lib/pgsql/data' and edit the confgiration 'postgresql.conf' using nano.

cd /var/lib/pgsql/data/
nano postgresql.conf

Uncomment the option 'password_encryption' and change the value to 'scram-sha-256' as below.

password_encryption = scram-sha-256

Press the 'Ctrl+x', type 'y', then pres 'Enter' to save the configuration and exit.

Now edit the configiration 'pg_hba.conf' using nano.

nano pg_hba.conf

Change all local connection methods to 'scram-sha-256' as below.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Save the configuration by pressing the 'Ctrl+x', type 'y', the press 'Enter'.

4. Now execute the following command to enable the PostgreSQL service.

sudo systemctl enable postgresql
sudo systemctl is-enabled postgresql

Initial PostgreSQL Database Data and Enable PostgreSQL Service

After that, start the PostgreSQL service and check the service status by executing the command below.

sudo systemctl start postgresql
sudo systemctl status postgresql

And you will see similar output as below.

Check status PostgreSQL service

As can be seen, the PostgreSQL service is enabled, it will automatically run at the system startup. And it's currently 'active (running)' .

5. Next, log in to the PostgreSQL shell using the following command.

sudo -u postgres psql

Execute the following PostgreSQL query to create a new password for the default database user 'postgres'. Also, be sure to change the 'strongpostgrespassword' with your strong password.

ALTER USER postgres WITH PASSWORD 'strongpostgrespassword';

Now type 'exit' to log out from the PostgreSQL shell.

Create password for postgres database user

Installing pgAdmin4 on Rocky Linux

The pgAdmin provides a repository for its installation for different Linux, including Debian and RHEL based operating systems. For this step, you will be installing and configuring the pgAdmin4 on the Rocky Linux system.

1. First, execute the following command to add the repository and gpg key of the pgAdmin4.

rpm --import https://www.pgadmin.org/static/packages_pgadmin_org.pub
sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm

2. Now install the 'pgAdmin4-web' package using the dnf command below.

sudo dnf install pgadmin4-web

Type 'y' and press 'Enter' to confirm and add the GPG key of pgAdmin4.

Add pgAdmin4 repository and install pgadmin4-web

Now type 'y' and press 'Enter' again to continue the installation.

Install pgAdmin4 on Rocky Linux

The command will automatically install additional packages including the Apache/httpd.

3. If all installation is completed, execute the pgAdmin4 setup script below to create a new admin user and set up the Apache/httpd configuration for pgAdmin4.

/usr/pgadmin4/bin/setup-web.sh

Type your email address and password to create a new admin account for pgAdmin4.

Setup pgAdmin4 user and apache configuration

After that, type 'y' and press 'Enter' to generate and enable the Apache/httpd configuration for pgAdmin.

The pgAdmin4 configuration is complete, and it's available at the URL path 'http://server-ip/pgadmin4'.

Verify the pgAdmin Installation

To verify the pgAdmin4 installation, you will be accessing the pgAdmin URL path installation and log in to the pgAdmin with your user email and password.

1. open your web browser, type your server IP address with URL path '/pgadmin4' as below.

http://192.168.1.10/pgadmin4

And you will get the pgAdmin4 login page.

2. Type your login user and email, then click the 'Login' button.

pgAdmin4 login page

And you will get the pgAdmin4 dashboard as below.

pgAdmin4 dashboard

Connect to PostgreSQL Server with pgAdmin4

At this stage, you will be connecting the PostgreSQL through the pgAdmin4 application.

1. At the pgAdmin dashboard, click the icon 'Add New Server'.

Add server connection pgAdmin4

2. You will get a small window pop-up.

At the tab 'General', type a new 'Name' of your connection.

Set up connection name

For this guide, we will give a new connection name as 'local'.

3. Move to the tab 'Connection' and type your details PostgreSQL host, database, user, and password.

For this example, we will use the local PostgreSQL server with user 'postgres' to manage a default database 'postgres'.

Set up PostgreSQL server connection details

After that, click the 'Save' button to save a new connection.

4. If your connection is successful, you will see a similar page as below. Otherwise, you will see an error.

PostgreSQL connection successfully

As you can see at the bottom of the screenshot, the user 'postgres' is connected to the database 'postgres' with the application 'pgAdmin4', and the state of the connection is 'active'.

Basic Usage pgAdmin4: Creating New User

For this stage, you will be creating a new PostgreSQL using the pgAdmin4 tool.

1. Right click on the connection name menu "local => Create => Login/Group Role..".

Create new user pgAdmin4

2. At the tab 'General', type the new user that you want to create. For this example, you will be creating a new user 'bob'.

Configure Password for New user

3. Move to the tab 'Definition' and type the password for your user.

Setup Password pgAdmin4

4. Move to the tab 'Privileges' and turn on some privileges as you need.

Setup privileges for pgAdmin4

Now click the 'Save' button.

5. To verify your new user, click the menu 'Login/Group Roles' at left, choose your new user 'bob' that you just created.

Verify new user pgAdmin4

Click the tab 'Properties' on the page and you will get details of the user as on the top screenshot.

Basic Usage pgAdmin4: Creating New Database

After creating a PostgreSQL user through the pgAdmin4 dashboard, you will be creating a new database through the pgAdmin4 dashboard.

1. Right click on the connection name menu "local => Create => Database..".

Creating new database pgAdmin4

2. At the tab 'General', type your new database name and choose the user owner of it. For this example, you will be creating a new database 'bobdb', and make the user 'bob' as the owner.

Create new database pgAdmin4

Now click the 'Save' button.

3. To verify your new database, click the menu 'Databases' at the left, then click the database 'bobdb' that you just created.

Verify Database pgAdmin4

Click the tab 'Properties' on the page and you will get details of the database 'bobdb' as on the top screenshot.

Verify PostgreSQL User Connection with pgAdmin4

At this stage, you will add a new connection pgAdmin with the user 'bob' and database 'bobdb'

1. Back to the pgAdmin4 dashboard and click the icon 'Add New Server'.

Verify new connection pgAdmin4

2. At the tab 'General', type the new connection name as 'bob-connection' as below.

Add new connection pgAdmin4

3. Move to the tab 'Connection' and type details of the new connection. The host is '127.0.0.1' with the user 'bob', and will manage the database 'bobdb', and don't forget to type the password for the user 'bob'.

Detail connection pgAdmin4

Now click the 'Save' button.

4. If your database credentials are correct, you will see a similar page as below.

Connection successfully pgAdmin4

As you can see at the bottom of the screenshot, the user 'bob' is connected to the database 'bobdb' with the application 'pgAdmin4', and the state of the connection is 'active'.

Conclusion

Congratulations! Now you've successfully installed the pgAdmin4 administration tool for PostgreSQL on the Rocky Linux. Also, you've learned how to create a new PostgreSQL connection on the pgAdmin4. And the basic usage of pgAdmin4 for creating a new database and user. For the next stage, you can use add your PostgreSQL server connection and managing your database through the pgAdmin4 dashboard.

Share this page:

0 Comment(s)