How to install PostgreSQL on CentOS 8

Learn how to install PostgreSQL on CentOS 8 with this step-by-step guide. Follow our comprehensive tutorial to set up and configure Postgres on your Linux system quickly and easily. #centlinux #linux #postgres

What is PostgreSQL Server?

PostgreSQL Server, often referred to as Postgres, is a highly versatile and feature-rich database management system. It has been actively developed and maintained by a global community of developers for over two decades. Some key features of PostgreSQL include:

1. ACID Compliance: Ensures that transactions are processed reliably, maintaining data integrity even in the event of system failures.
2. Advanced SQL Support: Provides comprehensive support for SQL standards, including subqueries, triggers, views, foreign keys, and stored procedures.
3. Extensibility: Allows users to define their own data types, operators, index types, and functions. This flexibility makes it adaptable to a wide range of applications.
4. High Performance: Includes powerful optimization features such as indexing, parallel query execution, and sophisticated query planning and execution strategies.
5. Concurrency: Uses Multi-Version Concurrency Control (MVCC) to handle multiple transactions simultaneously without locking issues, ensuring smooth and efficient performance.
6. Data Integrity and Security: Offers robust mechanisms for ensuring data accuracy and protecting data from unauthorized access, including encryption and access controls.
7. Replication and High Availability: Supports various replication methods (e.g., streaming replication, logical replication) to ensure data availability and reliability in distributed systems.
8. Community and Ecosystem: Backed by a vibrant community, PostgreSQL benefits from regular updates, extensive documentation, and a wide array of third-party tools and extensions.
9. Cross-Platform Support: Runs on various operating systems, including Linux, Windows, and macOS, providing flexibility in deployment environments.

PostgreSQL is used by a wide range of organizations, from startups to large enterprises, due to its reliability, flexibility, and strong community support. It is an excellent choice for applications requiring complex queries, high transaction throughput, and strong data integrity guarantees.

Recommended Online Training: PostgreSQL Database Administration on Windows/Linux- Part 2

What is pgAdmin?

pgAdmin is a comprehensive and feature-rich open-source management tool for PostgreSQL, a powerful relational database management system. Designed to simplify database management and operations, pgAdmin provides a graphical user interface (GUI) that allows users to interact with PostgreSQL databases more intuitively. Key features of pgAdmin include:

1. User-Friendly Interface: Provides a graphical interface for database management tasks, making it easier for users to navigate and interact with their databases.
2. Database Management: Allows users to create, modify, and delete databases, tables, indexes, and other database objects. It also supports database backups and restores.
3. SQL Query Tool: Includes a powerful SQL editor with syntax highlighting, auto-completion, and error detection. Users can write and execute SQL queries, view query results, and analyze performance.
4. Data Visualization: Offers tools to visualize data through charts, graphs, and other visual representations, helping users to understand and interpret their data better.
5. User and Permission Management: Facilitates the management of users, roles, and permissions, ensuring secure and controlled access to database resources.
6. Monitoring and Performance: Provides monitoring tools to track database performance, view logs, and analyze query execution plans, helping administrators optimize and troubleshoot their databases.
7. Cross-Platform Compatibility: Runs on various operating systems, including Windows, macOS, and Linux, providing flexibility in deployment and usage.
8. Extensibility: Supports extensions and plugins, allowing users to enhance the functionality of pgAdmin to suit their specific needs.
9. Multi-Database Support: Enables management of multiple PostgreSQL database instances from a single interface, making it convenient for administrators handling complex database environments.

pgAdmin is widely used by database administrators, developers, and analysts for managing PostgreSQL databases efficiently. Its rich set of features and user-friendly design make it an essential tool for anyone working with PostgreSQL.

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

Read Also: How to install PostgreSQL on Rocky Linux 9

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)

Configure 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 Server 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

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.

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

Final Thoughts

In conclusion, installing PostgreSQL on CentOS 8 can be straightforward if you follow the right steps. PostgreSQL is a robust, feature-rich database management system, and setting it up correctly ensures you can leverage its full potential for your applications.

If you need further assistance with installing PostgreSQL on Linux or any other database-related tasks, I’m here to help. Check out my Fiverr service for professional and reliable support: Linux Server Admin. I offer step-by-step guidance and expert advice to ensure your PostgreSQL installation and configuration are done right. Let’s get your database up and running smoothly!