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
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. 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
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
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 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
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
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
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
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.
Login to pgAdmin as admin user that we have created by setup-web.sh script.
After successful login, you may reach at the pgAdmin dashboard.
To add your PostgreSQL database server in pgAdmin inventory, click on “Add New Server”.
Provide the Server name and click on “Connection” tab.
Provide the database connection information in this dialog box as we did in the above screenshots.
Click on “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