How to install PostgreSQL on CentOS 8

Share on Social Media

PostgreSQL is the most advanced RDBMS and pgAdmin is a web UI for Postgres. Here, you will learn how to install PostgreSQL on CentOS 8 alongwith pgAdmin. #centlinux #linux #postgres

What is PostgreSQL? :

PostgreSQL (also known as Postgres) is a free and open source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL was originally named POSTGRES, referring to its origins as a successor to the Ingres database. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

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

PostgreSQL is a free software and distributed under PostgreSQL License (free and open-source, permissive).

What is pgAdmin? :

pgAdmin is free and open source graphical user interface (GUI) administration tool for Postgres database servers.

pgAdmin is available in desktop and web interfaces. pgAdmin is the feature rich and most popular among the other administration tools for PostgreSQL.

Just like PostgreSQL, pgAdmin is also a free software. It is also distributed under PostgreSQL license.

Environment Specification:

We are using a minimal CentOS 8 virtual machine with following specifications.

  • CPU – 3.4 Ghz (2 cores)
  • Memory – 2 GB
  • Storage – 20 GB
  • Operating System – CentOS 8.2
  • Hostname – postgres-01.centlinux.com
  • IP Address – 192.168.116.230 /24

Update Linux Server Packages:

Use a ssh client and login to postgres-01.centlinux.com as root user.

Update Linux server packages by using dnf command.

# dnf update -y
Last metadata expiration check: 0:00:58 ago on Sun 22 Nov 2020 08:26:21 PM PKT.
Dependencies resolved.
Nothing to do.
Complete!

Our Linux operating system is already up-to-date.

Check the Linux operating system and Kernel version.

# uname -r
4.18.0-193.28.1.el8_2.x86_64

# cat /etc/redhat-release
CentOS Linux release 8.2.2004 (Core)

Install Postgres Yum Repository:

Although Postgres database server is available in Linux yum repositories, but to install latest version, we have to add PostgreSQL official yum repository in our Linux server.

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

Disable the PostgreSQL module in standard yum repository by executing following command.

# dnf -qy module disable postgresql

Install PostgreSQL on CentOS 8:

After setting Postgres Official Yum Repository, you can now easily install PostgreSQL on CentOS 8 server by using dnf command.

# dnf install -y postgresql13-server
Install PostgreSQL on CentOS 8

You need to execute following command once to initialize the Postgres database.

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

Enable and start Postgres database service.

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

Check the status of Postgres database service.

# systemctl status postgresql-13.service
Check the status of Postgres database service

Check the version of installed PostgreSQL server.

# psql -V
psql (PostgreSQL) 13.1

Switch to postgres user and connect to psql shell to set admin user password.

# su - postgres
$ psql
psql (13.1)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD '123';
ALTER ROLE
postgres=# q
$ exit
logout

Configure Postgres Database Service for Network Access:

PostgreSQL default service port is 5432/tcp. Execute the following command to verify that PostgreSQL service is listening on this port.

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

As you may notice that the PostgreSQL service is initially runs on localhost interface only. However, to make our Postgres database available for network clients, you have to configure PostgreSQL to run on all (or on some specific) interfaces.

For this purpose, you have to edit PostgreSQL configuration file in vim text editor.

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

Search for following directive therein.

# listen_addresses = 'localhost'

And replace it with the following directive.

listen_addresses = '*'

Your Postgres database service is now configured to listen on all network interfaces.

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

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

Restart Postgres database service to apply changes.

# systemctl restart postgresql-13.service

Again check the network services by using ss command.

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

Postgres Service is now running on all network interfaces.

Configure Linux Firewall for Postgres Database Service:

In CentOS 8, we already have a firewall service for PostgreSQL, therefore we are allowing it by using firewall-cmd command. However, you can create a custom firewall service of your own, if your PostgreSQL server is configured to listen on a non-default port.

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

Postgres database server has been installed on Linux server.

Install pgAdmin Yum Repository:

pgAdmin is a popular web interface for database administration of PostgreSQL databases.

Although pgAdmin is also provided within PostgreSQL official yum repositories. But it doesn’t work on our CentOS 8 server.

Therefore, we are installing the latest stable version of pgAdmin from pgAdmin official yum repository.

For this purpose, first you need to remove the PostgreSQL yum repositories from your Linux server.

# dnf remove -y pgdg-redhat-repo
 remove the PostgreSQL yum repositories

Now, add the pgAdmin official yum repository in your Linux operating system.

# dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm
dd the pgAdmin official yum repository

You are also required to install EPEL (Extra Packages for Enterprise Linux) yum repository, because pgAdmin requires some software packages that are not available in standard yum repositories.

Use dnf command and install EPEL yum repository.

# dnf install -y epel-release

Build cache for newly installed yum repositories.

# dnf makecache
CentOS-8 - AppStream                            2.1 kB/s | 4.3 kB     00:02
CentOS-8 - Base                                 3.7 kB/s | 3.9 kB     00:01
CentOS-8 - Extras                               768  B/s | 1.5 kB     00:02
Extra Packages for Enterprise Linux Modular 8 -  24 kB/s |  98 kB     00:04
Extra Packages for Enterprise Linux 8 - x86_64   92 kB/s | 8.3 MB     01:32
pgadmin4                                        1.1 kB/s | 2.9 kB     00:02
Metadata cache created.

Install pgAdmin on CentOS 8:

All repositories has been added. You can now install pgAdmin by using dnf command.

# dnf install -y pgadmin4
Install pgAdmin on CentOS 8

To configure SELinux policies, the pgAdmin setup requires semanage command, which is provided in policycoreutils-python-utils packages. Therefore, you should install it before executing pgAdmin setup script.

# dnf install -y policycoreutils-python-utils
install semanage command

The pgAdmin software comes with a well written configuration script to configure pgAdmin web service. Execute it to create a admin user, configure SELinux policies and Apache web server to deploy pgAdmin web service.

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

The above script installs a configuration file (/etc/httpd/conf.d/pgadmin4.conf) to deploy pgAdmin on Apache web server.

No customization is required in this file. You are only required to enable and start httpd service.

# systemctl enable --now httpd.service
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service â /usr/lib/systemd/system/httpd.service.

Configure Linux firewall to allow incoming traffic to Apache web server.

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

Open URL http://postgres-01.centlinux.com/pgadmin4/ in a web browser.

pgAdmin4 Login Page

Login to pgAdmin as admin user that we have created by setup-web.sh script.

pgAdmin4 Dashboard

After successful login, you may reach at the pgAdmin dashboard.

To add your PostgreSQL database server in pgAdmin inventory, click on “Add New Server”.

pgAdmin4 Add New Server - General

Provide the Server name and click on “Connection” tab.

pgAdmin4 Add New Server - Connection

Provide the database connection information in this dialog box as we did in the above screenshots.

Click on “Save”.

pgAdmin4 Add New Server - Save

Our Postgres database server has been added in pgAdmin. You can see a tree of you database server in the left side panel.

Conclusion – Install PostgreSQL on CentOS 8:

In this installation guide, you have learned how to install PostgreSQL on CentOS 8 along with pgAdmin. For learning more about Postgres databases, we recommend that you should read Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13 (PAID LINK) by Packt Publishing