In this tutorial, we'll learn how to install and configure pgAdmin 4 AlmaLinux server.
Introduction to pgAdmin
pgAdmin is the most popular and feature-rich open-source administration and development platform for PostgreSQL, the world's most advanced open-source database. pgAdmin allows database administrators, developers, and data analysts to manage PostgreSQL databases using an easy-to-use graphical interface. It can be run in desktop mode for single-user access or server mode, where it can be hosted centrally for multiple users. In this tutorial, we will focus on installing and configuring pgAdmin 4 in server mode on AlmaLinux 9.
pgAdmin in server mode allows you to access your PostgreSQL instances remotely via a web browser. It is ideal for teams or when you want a centralized management interface for multiple PostgreSQL databases.
Prerequisites
Before starting, make sure you have the following:
- An AlmaLinux 9 dedicated server or KVM VPS.
- PostgreSQL installed on the server (optional but recommended for connecting databases).
- A fully qualified domain name (FQDN) or a static IP address.
- Basic knowledge of the terminal and system administration.
Install and Configure pgAdmin 4 AlmaLinux Server
Step 1: Update the System
Before installing any software, it is essential to update your system to ensure all existing packages are up-to-date.
sudo dnf update -y
Once the update is complete, reboot the system if any kernel updates were installed.
Step 2: Install Required Dependencies
pgAdmin 4 requires several dependencies to run. You need to install them using the following command:
sudo dnf install epel-release
sudo dnf install python3-pip python3-devel gcc openssl-devel libffi-devel
EPEL (Extra Packages for Enterprise Linux) provides additional software packages, including dependencies for pgAdmin.
Step 3: Install PostgreSQL (Optional)
If you haven't installed PostgreSQL yet, you can install it using the following steps. If PostgreSQL is already installed, you can skip to Step 4.
Add the PostgreSQL repository:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
Install PostgreSQL:
sudo dnf install -y postgresql17-server postgresql17-contrib
Initialize the PostgreSQL database:
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
Start and enable PostgreSQL:
sudo systemctl enable --now postgresql-17
Step 4: Install pgAdmin 4
pgAdmin 4 is not included in the default AlmaLinux repositories. However, pgAdmin provides its own YUM repository. To install it, follow these steps:
Add the pgAdmin YUM repository:
sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
Install pgAdmin 4:
sudo dnf install -y pgadmin4-web
Step 5: Configure pgAdmin 4 in Server Mode
After installing pgAdmin 4, we need to configure it to run in server mode.
Run the setup script to configure pgAdmin 4:
sudo /usr/pgadmin4/bin/setup-web.sh
This script will ask for the initial email and password for the pgAdmin 4 web interface. Set these credentials carefully, as they will be used to log into the interface.
During the setup, the script will configure the Apache web server to host the pgAdmin web interface. Once completed, it will enable and start the Apache service:
sudo systemctl enable --now httpd
Step 6: Configure Firewall
If you are accessing pgAdmin 4 remotely, you need to allow HTTP and HTTPS traffic through the firewall.
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --permanent --add-service=https
sudo firewall-cmd --reload
Step 7: Create Apache Virtual Host Configuration
Open your Apache configuration file (or create one if it doesn’t exist). By default, Apache virtual host files are located in /etc/httpd/conf.d/.
You can create a new file for your domain, for example:
sudo vi /etc/httpd/conf.d/pgadmin.conf
Add the following configuration to ensure Apache listens on port 80 for your domain:
<VirtualHost *:80>
ServerName your-domain.com
ServerAlias www.your-domain.com
DocumentRoot /var/www/html
ErrorLog /var/log/httpd/error.log
CustomLog /var/log/httpd/access.log combined
</VirtualHost>
Make sure to replace your-domain.com and www.your-domain.com with your actual domain names. Also, if your DocumentRoot is different (where the web files for your domain reside), adjust that as well.
Restart Apache to apply the changes:
sudo systemctl restart httpd
Step 8: Install SSL
Certbot is not available in AlmaLinux’s default repositories, but you can install it by enabling the EPEL repository and then using the dnf package manager.
Enable the EPEL repository (if you haven’t done so already):
sudo dnf install epel-release -y
Install Certbot and the Apache plugin:
sudo dnf install certbot python3-certbot-apache -y
Obtain SSL Certificate Using Certbot.
Certbot automates the process of obtaining SSL certificates from Let’s Encrypt. To obtain a certificate for your domain, follow these steps:
Ensure that your Apache server is running:
sudo systemctl start httpd
Run Certbot with the Apache plugin to obtain the certificate:
sudo certbot --apache -d <your_domain.com>
Certbot will automatically configure your Apache server to use the newly obtained SSL certificate.
Step 9: Access pgAdmin 4
We've successfully installed pgAdmin 4 at this point. Navigate to your browser and access it using your domain name:
https://<domain name>.com/pgadmin4
Use login credantials that you have entered in step 4.
Step 10: Connecting PostgreSQL Databases to pgAdmin
Once logged in to pgAdmin 4, you can connect your PostgreSQL instances by adding a new server.
- On the pgAdmin 4 dashboard, right-click on "Servers" in the navigation panel and select Create > Server.
- In the General tab, provide a name for your server.
- In the Connection tab, enter the hostname or IP address of your PostgreSQL server, and provide the username and password for the PostgreSQL superuser (usually postgres).
Click Save to connect.
Step 11: Managing pgAdmin Users
Since pgAdmin is in server mode, multiple users can access the platform. To add new users:
- Log into the pgAdmin web interface.
- Navigate to the User Management option under the menu.
- Add new users by specifying their email, password, and roles (administrator or user).
Step 12: Managing pgAdmin Backups
It is essential to back up your pgAdmin 4 settings, especially if you have multiple server configurations. You can back up your configuration by copying the pgAdmin settings directory.
sudo cp -r /var/lib/pgadmin /backup/pgadmin
You should schedule regular backups to avoid data loss.
Conclusion
In this tutorial, you have learned how to install and configure pgAdmin 4 in server mode on AlmaLinux 9. pgAdmin provides an intuitive graphical interface for managing PostgreSQL databases, making it a valuable tool for both beginners and experienced database administrators. With server mode, you can securely manage your PostgreSQL databases remotely, offering a centralized platform for multiple users.
