Install and Configure PostgreSQL on RHEL 9 or CentOS 9

Install and configure PostgreSQL on RHEL 9

Learn how to install and configure PostgreSQL on RHEL 9 or CentOS 9 with our comprehensive step-by-step guide. Ensure your database is set up correctly for optimal performance with this easy-to-follow tutorial.

Table of Contents

Introduction

PostgreSQL is a powerful, open-source relational database system that has earned a strong reputation for reliability, feature robustness, and performance. Whether you’re setting up a development environment or preparing a production server, understanding how to install and configure PostgreSQL on RHEL 9 or CentOS 9 is essential. This guide will walk you through the entire process step-by-step.

Prerequisites

Before diving into the installation, ensure your system meets the following prerequisites:

  • A running instance of RHEL 9 or CentOS 9.
  • Root or sudo user privileges.
  • Internet access for downloading packages.

Install and Configure PostgreSQL on RHEL 9: Step-by-Step Instructions

Relational databases are a cornerstone of many applications, and PostgreSQL stands out due to its advanced features and compliance with SQL standards. In this guide, we will cover the installation and configuration of PostgreSQL on two popular Linux distributions: RHEL 9 and CentOS 9. To install PostgreSQL, follow these steps:

Installing and configuring PostgreSQL on RHEL 9

Photo by admingeek from Infotechys

Step 1: Update the System

First, update your system packages to the latest versions:

				
					sudo dnf update -y
				
			

Step 2: Add PostgreSQL Repository

Next, add the PostgreSQL repository to your system. This ensures you get the latest version of PostgreSQL.

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

Step 3: Disable the Built-in PostgreSQL Module

RHEL 9 and CentOS 9 come with a default PostgreSQL module that might not be the latest version. Disable it before proceeding.

				
					sudo dnf -qy module disable postgresql
				
			
				
					Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
				
			

Step 4: Install PostgreSQL

As of the date of this publication, the latest version of Postgres is version 16. We will proceed with this version of Postgres. However, if you want to install a previous version, you can replace the command below with the version number. Now, install PostgreSQL using the following command:

				
					sudo dnf install -y postgresql16-server
				
			

Step 5: Initialize the Database

Initialize the PostgreSQL database to set it up for first-time use:

				
					sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
				
			
				
					Initializing database ... OK
				
			

Install and Configure PostgreSQL on RHEL 9: Initial Configuration

With PostgreSQL installed, some initial configuration steps are necessary.

Step 1: Configure PostgreSQL to Start on Boot

Enable PostgreSQL to start automatically when the system boots up:

				
					sudo systemctl enable postgresql-16
				
			
				
					Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-16.service → /usr/lib/systemd/system/postgresql-16.service.
				
			

Step 2: Start PostgreSQL Service

Start the PostgreSQL service with the following command:

				
					sudo systemctl start postgresql-16
				
			

Verify the postgresql service is running:

				
					sudo systemctl status postgresql-16
				
			
				
					● postgresql-16.service - PostgreSQL 16 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; enabled; preset: disabled)
     Active: active (running) since Wed 2024-07-17 22:56:36 EDT; 5s ago
       Docs: https://www.postgresql.org/docs/16/static/
    Process: 7999 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 8004 (postgres)
      Tasks: 7 (limit: 23155)
     Memory: 17.3M
        CPU: 68ms
     CGroup: /system.slice/postgresql-16.service
             ├─8004 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
             ├─8005 "postgres: logger "
             ├─8006 "postgres: checkpointer "
             ├─8007 "postgres: background writer "
             ├─8009 "postgres: walwriter "
             ├─8010 "postgres: autovacuum launcher "
             └─8011 "postgres: logical replication launcher "
...omitted for brevity...
				
			

Step 3: Set Up Firewall Rules

Configure your server’s firewall to allow PostgreSQL traffic:

				
					sudo firewall-cmd --add-service=postgresql --permanent
				
			
				
					sudo firewall-cmd --reload
				
			

Creating a Database and User

PostgreSQL uses roles to handle authentication and authorization. Here’s how to create a new role and database.

Step 1: Switch to the PostgreSQL User

Switch to the PostgreSQL user to access the PostgreSQL prompt:

				
					sudo -i -u postgres
				
			

Step 2: Create a New Database

Create a new database by running:

				
					sudo -i -u postgres
				
			

Step 3: Create a New User

Create a new user and set a password:

				
					psql -c "CREATE USER myuser WITH PASSWORD 'mypassword';"
				
			

Step 4: Grant Privileges

Grant all privileges on the new database to the new user:

				
					psql -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;"
				
			

Table of Commands

CommandDescription
sudo dnf update -yUpdate system packages
sudo dnf install -y pgdg-redhat-repoAdd PostgreSQL repository
sudo dnf -qy module disable postgresqlDisable built-in PostgreSQL module
sudo dnf install -y postgresql16-serverInstall PostgreSQL
sudo /usr/pgsql-16/bin/postgresql-16-setup initdbInitialize the database
sudo systemctl enable postgresql-16Enable PostgreSQL to start on boot
sudo systemctl start postgresql-16Start PostgreSQL service
sudo firewall-cmd --add-service=postgresql --permanentAllow PostgreSQL through firewall
sudo firewall-cmd --reloadReload firewall
sudo -i -u postgresSwitch to PostgreSQL user
createdb mydbCreate a new database
psql -c "CREATE USER myuser WITH PASSWORD 'mypassword';"Create a new user
psql -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;"Grant privileges to user

Configuring Remote Access

By default, PostgreSQL only allows connections from the local machine. To enable remote access, modify the configuration files.

Step 1: Edit pg_hba.conf

Open the pg_hba.conf file:

				
					sudo vim /var/lib/pgsql/16/data/pg_hba.conf
				
			

Add the following line to allow remote access:

				
					host    all             all             0.0.0.0/0               md5
				
			

Step 2: Edit postgresql.conf

Open the postgresql.conf file:

				
					sudo vim /var/lib/pgsql/16/data/postgresql.conf
				
			

Uncomment and set the listen_addresses line:

				
					listen_addresses = '*'
				
			

Step 3: Restart PostgreSQL Service

Restart PostgreSQL to apply the changes:

				
					sudo systemctl restart postgresql-16
				
			

Starting and Enabling PostgreSQL Service

To ensure PostgreSQL runs smoothly, make sure the service is both started and enabled.

				
					sudo systemctl enable --now postgresql-16
				
			

Verifying the Installation

Finally, verify that PostgreSQL is running correctly.

				
					sudo systemctl status postgresql-16
				
			

Test Database Connection

Connect to the PostgreSQL database using the psql command-line tool:

				
					psql -U myuser -d mydb -h localhost -W
				
			

You will be prompted to enter the password. Once connected, you should see the PostgreSQL prompt.

Conclusion

Installing and configuring PostgreSQL on RHEL 9 or CentOS 9 is straightforward if you follow these steps. By ensuring your system is up-to-date, properly configuring PostgreSQL, and setting up firewall rules, you can create a robust database environment. Whether for development or production, PostgreSQL provides the reliability and performance needed for modern applications.

Remember, good database management practices and regular maintenance are crucial for optimal performance.

For more detailed guides on PostgreSQL and other database systems, stay tuned to our blog. If you have any questions or run into issues, feel free to leave a comment below.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *