Latest

Monday, 26 September 2022

Setup PostgreSQL Database Server on Rocky Linux 9

In this configuration guide, you will learn how to setup PostgreSQL 14 database server on Rocky Linux 9 along with pgAdmin 4, a web based user interface.

 

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. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.

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 Windows, Linux, FreeBSD, and OpenBSD. (Source: Wikipedia)

Read Also:

How to install PostgreSQL and pgAdmin on CentOS/RHEL 8
How to install PostgreSQL and pgAdmin on CentOS 7

 

Environment Specification:

We are using a minimal Rocky Linux 9 virtual machine with following specifications.

  • CPU - 3.4 Ghz (2 cores)
  • Memory - 2 GB
  • Storage - 20 GB
  • Operating System - Rocky Linux release 9.0 (Blue Onyx)
  • Hostname – postgres-01.centlinux.com
  • IP Address - 192.168.116.131/24

We are using a Lenovo - 2022 - IdeaPad 3i - Essential Laptop Computer - Intel Core i5 12th Gen - 15.6" FHD Display - 8GB Memory - 512GB Storage. You can also use the following link to purchase the same at very reasonable price from Amazon.com.

Lenovo Idealpad 3i

 

Updating your Rocky Linux Server:

It is a best practice to update your Linux operating system before performing any server configurations.

By using a SSH Client, connect with postgres-01.centlinux.com as root user.

Execute dnf command to refresh the yum cache of your Linux server.

# dnf makecache
Rocky Linux 9 - BaseOS                          1.2 kB/s | 3.6 kB     00:02
Rocky Linux 9 - AppStream                       1.5 kB/s | 3.6 kB     00:02
Rocky Linux 9 - Extras                          1.1 kB/s | 2.9 kB     00:02
Metadata cache created.

Now you can update your Rocky Linux server with the help of following dnf command.

# dnf update -y

The above command may update your Linux Kernel. If it does than reboot your Linux machine once to load the Latest Linux Kernel.

# reboot

After reboot, login as root user and check Linux Kernel and operating system versions.

# uname -r
5.14.0-70.26.1.el9_0.x86_64

# cat /etc/rocky-release
Rocky Linux release 9.0 (Blue Onyx)

 

Installing PostgreSQL Database Software:

In Rocky Linux 9, PostgreSQL 13 is available in standard yum repositories that you can easily install by throwing a dnf command.

You can verify the version of available PostgreSQL server as follows.

# dnf info postgresql-server
Last metadata expiration check: 0:49:27 ago on Fri 23 Sep 2022 09:48:01 AM CDT.
Available Packages
Name         : postgresql-server
Version      : 13.7
Release      : 1.el9_0
Architecture : x86_64
Size         : 5.7 M
Source       : postgresql-13.7-1.el9_0.src.rpm
Repository   : appstream
Summary      : The programs needed to create and run a PostgreSQL server
URL          : http://www.postgresql.org/
License      : PostgreSQL
Description  : PostgreSQL is an advanced Object-Relational database management
             : system (DBMS). The postgresql-server package contains the
             : programs needed to create and run a PostgreSQL server, which will
             : in turn allow you to create and maintain PostgreSQL databases.

But, in this configuration guide, we are installing the PostgreSQL 14 on Rocky Linux 9.

PostgreSQL 14 is the latest PostgreSQL version at the time of this writing and is not available in standard yum repositories.

To setup Postgres 14 database, you need to install their official yum repository as follows.

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

Now, you can install Postgres 14 by using dnf command.

# dnf install -y postgresql14-server

After successful installation, you need to initialize your PostgreSQL database instance.

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

Enable and start PostgreSQL database service.

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

Check the status of your database service.

# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; ve>
     Active: active (running) since Fri 2022-09-23 10:43:23 CDT; 20s ago
       Docs: https://www.postgresql.org/docs/14/static/
    Process: 5148 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${P>
   Main PID: 5153 (postmaster)
      Tasks: 8 (limit: 7495)
     Memory: 16.6M
        CPU: 64ms
     CGroup: /system.slice/postgresql-14.service
             ├─5153 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
             ├─5154 "postgres: logger "
             ├─5156 "postgres: checkpointer "
             ├─5157 "postgres: background writer "
             ├─5158 "postgres: walwriter "
             ├─5159 "postgres: autovacuum launcher "
             ├─5160 "postgres: stats collector "
             └─5161 "postgres: logical replication launcher "

Sep 23 10:43:23 postgres-01.centlinux.com systemd[1]: Starting PostgreSQL 14>
Sep 23 10:43:23 postgres-01.centlinux.com postmaster[5153]: 2022-09-23 10:43>
Sep 23 10:43:23 postgres-01.centlinux.com postmaster[5153]: 2022-09-23 10:43>
Sep 23 10:43:23 postgres-01.centlinux.com systemd[1]: Started PostgreSQL 14 >

Verify the version of installed database software by using psql command.

# psql -V
psql (PostgreSQL) 14.5

 

Set Database Admin User Password:

Switch to postgres user and set DBA password as follows.

# su - postgres

$ psql
psql (14.5)
Type "help" for help.

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

$ exit
logout

 

Configure Network Access to PostgreSQL Server:

By default the Postgres service listens on the localhost network interface.

You can verify it by executing ss command at Linux bash prompt.

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

To enable network access to your database server, you need to perform following configurations.

Open postgresql.conf file in vim text editor.

# vi /var/lib/pgsql/14/data/postgresql.conf

Search for following directive therein.

# listen_addresses = 'localhost'

And replace it with the following directive.

listen_addresses = '*'

Allow network clients to access database service in pg_hba.conf file.

# echo "host all all 192.168.116.0/24 md5" >> /var/lib/pgsql/14/data/pg_hba.conf

Restart database service to apply changes.

# systemctl restart postgresql-14.service

Now, check the status of service again by executing ss command.

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

You can see that, now the database service is listening on all network interfaces.

One last thing is to allow the service port in Linux firewall.

There is a preconfigured firewall service, postgresql, that you can use to allow PostgreSQL default port i.e 5432/tcp in Linux firewall.

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

 

Installing pgAdmin 4 Web UI:

Although, you have installed PostgreSQL software on your Linux operating system. But it doesn't provides a GUI based database management environment and you have to perform DBA tasks straight from the psql command prompt.

There are many third party web user interfaces are available for Postgres, but pgAdmin 4 is the most popular among them due to it's rich database management features.

You can install pgAdmin 4 as follows or you can obtain an alternate download link from their official download page, if you are installing on some other Linux distribution.

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

pgAdmin 4 also requires some software packages from EPEL (Extra Packages for Enterprise Linux) yum repository. Therefore, you should install it first before moving forward with server configurations.

# dnf install -y epel-release

Build your yum cache for the newly installed repositories.

# dnf makecache
Extra Packages for Enterprise Linux 9 - x86_64  139 kB/s |  10 MB     01:16
pgadmin4                                        283  B/s | 833  B     00:02
PostgreSQL common RPMs for RHEL / Rocky 9 - x86  61  B/s | 195  B     00:03
PostgreSQL 14 for RHEL / Rocky 9 - x86_64       102  B/s | 195  B     00:01
PostgreSQL 13 for RHEL / Rocky 9 - x86_64        77  B/s | 195  B     00:02
PostgreSQL 12 for RHEL / Rocky 9 - x86_64       102  B/s | 195  B     00:01
PostgreSQL 11 for RHEL / Rocky 9 - x86_64        76  B/s | 195  B     00:02
PostgreSQL 10 for RHEL / Rocky 9 - x86_64        96  B/s | 195  B     00:02
Rocky Linux 9 - BaseOS                          1.9 kB/s | 3.6 kB     00:01
Rocky Linux 9 - AppStream                       1.8 kB/s | 3.6 kB     00:02
Rocky Linux 9 - Extras                          1.1 kB/s | 2.9 kB     00:02
Metadata cache created.

Now, you can install pgAdmin 4 software by using dnf command.

# dnf install -y pgadmin4

pgAdmin 4 configuration script requires semanage command to configure SELinux context for the software files. Therefore, you need to install policycoreutils-python-utils package before installing the Postgres web user interface.

# dnf install -y policycoreutils-python-utils

Execute following script to setup pgAdmin 4 web user interface.

# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based 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

Allow the HTTP server in Linux firewall by using firewall-cmd command.

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

Open URL http://192.168.116.131/pgadmin4/ in a web browser.

Login as user that you have created during pgAdmin setup.

After successful login, you will redirected to the pgAdmin dashboard.

Currently, there are no PostgreSQL servers in the list. Click on "Add New Server" to add your database server.

At the "General" Tab, provide a name for your database server.

At the "Connection" Tab, provide the database user and the password to create a connection.

Click on "Save".

Your PostgreSQL database server has been added in pgAdmin 4 web user interface.

To develop your skills in PostgreSQL database administration, we recommend that you should attend an online training like SQL PostgreSQL at Udemy or read PostgreSQL 14 Administration Cookbook by Simon Riggs & Gianni Ciolli.


Conclusion:

In this configuration guide, you have learn how to setup a PostgreSQL database server on Rocky Linux 9 along with pgAdmin 4 web user interface.

If you find this article useful? Don't you mind support us by Buy Me A Coffee


No comments:

Post a Comment

© 2022 CentLinux. All Rights Reserved.