Install PostgreSQL and pgAdmin in AlmaLinux / Rocky 8

PostgreSQL is a popular worldwide used open-source database. It is available for Linux including other common operating systems such as macOS, Windows 10/7, and BSD. Here we will install and configure the latest PostgreSQL version on AlmaLinux or Rocky Linux 8.

PostgreSQL implements the 2008 SQL standard very comprehensively. In addition to common data types, the database can also natively handle XML and version 9.2 with data in JSON format. Postgres is based on the typical client-server model: The central server component called ” postmaster ” manages all database files and all connections that are established for communication (input and output) with the database server.

Just like MYSQL, the user only needs a client program to connect and start creating a database via the command line or the integrated terminal. Whereas to handle MariaDB or MySQL graphically we have a simple web application phpMyAdmin and in the same way, we have a graphical user interface for PostgreSQL as well – such as pgAdmin or phpPgAdmin that can be optionally installed and used.

Here in this guide, we let you know how to install the PostgreSQL database server and pgAdmin web GUI interface on AlmaLinux 8 or Rocky Linux.

 

How to install PostgreSQL on AlmaLinux 8 and Rocky

While writing this tutorial the latest version of the PostgreSQL was 13 and development 14. However, you can use this tutorial no matter what is the current stable version, you will always get the latest one. Also, you can use the following steps for CentOS and RHEL 8 systems as well.

1. Add PostgreSQL Yum Repository

The default version of the PostgreSQL available to install in AlmaLinux or Rocky repo is version 10. Therefore to download the latest one we have to add its official repository on our RPM-based Linux systems.

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

 

2. Disable Default module

As the version is available to install from the default system’s PostgreSQL module is 10, hence we need to disable it so that we can get the latest available version.

sudo dnf -qy module disable postgresql

 

2. Run system update

To let the system know about the newly added PostgreSQL yum repository, run the system update command which will also add the required GPG keys.

sudo dnf update -y

 

3. Install Postgresql client & server on AlmaLinux or Rocky

Using the below-given command we can install the latest available version of the PostgreSQL database, however, if you want some old or the current one while you reading this article then replace the version number given in the below command:

sudo dnf install postgresql13 postgresql13-server

Install Postgresql client server on AlmaLinux or Rocky

 

4. Initialize the database

Once the installation is completed, let’s use initdb that will create a new PostgreSQL database cluster refers to a collection of databases managed by a single server instance. Initialize database will create directories, and generate tables to give a proper structure where your data going to be live.

sudo /usr/pgsql-*/bin/postgresql-*-setup initdb

Initialize database

Note: If you have multiple versions installed and want to initialize some particular of them then replace the * in the above command with the version number, for example for version 11, the above command will be:

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

 

5. Enable and Start PostgreSQL Service

Now, let’s enable and start the database service so that it can start automatically with system boot.

sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13

To confirm everything is working fine, check the status of its service-

sudo systemctl status postgresql-13

 

6. Secure PostgreSQL default Database

To make sure our PostgreSQL is secured with a strong password,  set a password for its system user and then default database admin user account using below given commands-

Change user password

passwd postgres

Login using Postgres system account-

su - postgres

Now, change the Admin database password-

psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"

Note: Replace your-password in the above with a secure password that you want to set for the admin database user.

 

7. Postgres server Authentication method – just for knowledge

Postgres offers multiple authentication methods that we use can use for its server to connect the client to access databases such as Trust, Password, GSSAPI Authentication, and more… You can know about them at official docs. However, by default, it will be using Password authentication which further offers several methods to securely authenticate database servers such as scram-sha-256, md5, and password.  By default, it will use the securest one- scram-sha-256, which you can set to another if you want.

In case you want to change then just edit pg_hba.conf and replace scram-sha-256 with other methods such as md5.

nano /var/lib/pgsql/13/data/pg_hba.conf

Well, you do not need to edit the file unless you know what are you doing, thus if you are not sure about it and then go with the default settings.

 

8. Example to create a database

Let’s say you want to create a database on PostgreSQL to use with some application, then here are the commands to do that-

Login with Postgre system user-

su - postgres
psql

Create a new user. Here we are creating a demo user protected by a password. Change them.

CREATE USER demo WITH PASSWORD 'password';

Create new PostgreSQL database-

CREATE DATABASE h2sdb;

Now, let’s Grant all rights or privileges on created database to the user:

GRANT ALL ON DATABASE h2sdb TO demo;

However, the owner of the Database still will be the default Admin user of PostgreSQL to change that you can use the command-

Syntax:

ALTER DATABASE database_name OWNER TO user-name;

Example:

ALTER DATABASE h2sdb OWNER TO demo;

 

To check and confirm whether the database has been created or not simply type

\l

You will get the following kind of output

CHnage Database Owner in PostgreSQL min

Now, your Database server is ready let’s install the graphical user interface to manage it conveniently.

 

How to install pgAdmin4 on AlmaLinux or Rocky

1. Add repo of pgAdmin

Disable PostgreSQL common repo to get the latest packages of  PGAdmin 4:

sudo dnf install yum-utils
sudo yum-config-manager --disable pgdg-common

now run command to include repo:

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

 

2. Run system update and install pgAdmin4

sudo dnf update -y
sudo dnf install pgadmin4

 

3. Start Webserver on AlmaLinux or Rocky

sudo systemctl enable --now httpd

Check the status-

sudo systemctl status httpd

 

4. Configure the pgAdmin web service

There is a script that comes along with the pgAdmin4 packages, we just need to initiate that to configure pgAdmin web. This will ask you several things such as email, the password you want to set for it along configuring SELinux, and creating storage and log directories.

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

 

5. Login pgAdmin4 Web interface

Open your browser and point to your server-IP-address with folder name pgadmin4.
Example:  http://server-ip/pgadmin4

Login PGADmin Web interface

 

6. Add PostgreSQL server in pgAdmin

You will see there is no database server connected to pgAdmin by default, thus, let’s connect the one we have created in this tutorial or any other remote one you have.

Step 1: Click on Add New Server icon

Step 2: Give some name to your Server so that it can easily be identified later.

Add New PostgreSQL Server in pgADMIN

Step 3: Select the Connection Tab.

Step 4: For host, type- localhost and then type the database username and password you want to connect.  However, we can use the default one that is postgres that Admin password you have set here in Step 6 of this tutorial.

Step 5: Click on the Save button.

Add the database server username and hostname

Optional Step

If you have installed PostgreSQL on some remote server then you also need to set SSH Tunnel. Click on its TAB and add the IP address of the server along with the username and password to open a tunnel.

 

7.  pgAdmin4 Dashboard- GUI Interface to manage Database

Now, we have the Graphical user interface to manage all our PostgreSQL Databases and users without issuing any commands. You can also monitor the Database load right from the pgAdmin Dashboard

GUI Interface to manage Database

 

Closing thoughts:

We have tried to make the tutorial as simple as possible, still, if you are facing any problem while setting up PostgreSQL and pgAdmin on AlmaLinux or Rocky Linux 8; then let us know, the comment section is all yours.

 

 

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.