Install PostgreSQL 15 on Rocky Linux 9 | Best Database

By Posted on

Install PostgreSQL 15 on Rocky Linux 9 | Best Database

This guide on the Orcacore website will teach you How To Install PostgreSQL 15 on Rocky Linux 9. PostgreSQL 15, the latest version of the hugely popular open-source database, is now available, featuring performance improvements and new features and capabilities to handle workloads in local and distributed deployments. Installing Install PostgreSQL 15 on Rocky Linux 9 unlocks a powerful database solution for your server.

Features that ship with version 15 of the open-source PostgreSQL database include improved sorting and compression, the MERGE command, and other workload management tools.

Steps To Install and Configure PostgreSQL 15 on Rocky Linux 9

To Install PostgreSQL 15 on Rocky Linux 9, you must log in to your server as a non-root user with sudo privileges. To do this, you can follow our guide on Initial Server Setup with Rocky Linux 9. Let’s begin with Install PostgreSQL 15 on Rocky Linux 9.

1. Add PostgreSQL 15 Repository on Rocky Linux 9

First, you can use the command below to add the latest PostgreSQL repository on your server:

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

Then, update your local package index with the following command:

sudo dnf update -y

2. Install PostgreSQL 15 on Rocky Linux 9

First, you need to disable the default module of PostgreSQL with the following command:

sudo dnf -qy module disable postgresql

Then, use the following command to install PostgreSQL 15 on your server:

sudo dnf install -y postgresql15-server

When your installation is completed, verify it by checking its version:

psql -V

In your output you will see:

**Output**
psql (PostgreSQL) 15.0

Initialize PostgreSQL 15 Database

At this point, you need to initialize the initdb database which is responsible for creating a new PostgreSQL cluster. A cluster is a group or collection of several databases that are managed by a cluster.

To do this, run the following command:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

When it is finished, you will get the following output:

**Output**
Initializing database ... OK

3. Manage PostgreSQL 15 Server

At this point, you can start and enable your PostgreSQL 15 on Rocky Linux 9. To do this, run the commands below:

# sudo systemctl enable postgresql-15
# sudo systemctl start postgresql-15

Verify your PostgreSQL 15 is active and running on your server with the command below:

sudo systemctl status postgresql-15
Install PostgreSQL 15 on Rocky Linux 9

4. Configure and Use PostgreSQL 15

PostgreSQL uses a concept called roles to handle authentication and authorization. During the PostgreSQL installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account.

If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

One way is to switch over to the Postgres account on your server by running the following command:

sudo -i -u postgres
**Output**
postgres@sam:~$

You can access the Postgres 15 shell on Rocky Linux 9 by running the following command:

postgres@sam:~$ psql

With this command, you will see that your shell prompt changes to:

To exit from the PostgreSQL 15 shell on Rocky Linux 9, run the following command:

postgres=# q

With this command, you will be back to the Postgres Linux command prompt.

postgres@sam:~$

You can type exit to return to the regular system user.

postgres@sam:~$ exit

Also, you can connect to your Postgres shell on Rocky Linux 9 in another way by typing the following command:

sudo -u postgres psql

In this way, you will directly connect to your PostgreSQL 15 shell.

**Output**
psql (15.0)
Type "help" for help.

postgres=#

Create a new PostgreSQL 15 Role

At this point, you can create a new Postgres role in two ways.

First, if you are logged in as a Postgres account, you can use the following command:

postgres@sam:~$ createuser --interactive

Second, you can use sudo for each command without switching from your normal account on Rocky Linux 9:

sudo -u postgres createuser --interactive

Both ways will be asked to enter your role name and whether the new role is a superuser or not.

**Output**
Enter name of role to add: orca
Shall the new role be a superuser? (y/n) y

Create a new PostgreSQL 15 Database

The Postgres authentication system for any role used to log in, the role should have a database with the same name, which it can access.

If you logged in as the Postgres account, run the following command to create the database with the name of the role you have created in the previous step:

postgres@sam:~$ createdb orca

Or you can use sudo to create the database on Rocky Linux 9:

sudo -u postgres createdb orca

Open the Postgres 15 Shell with the new Role

At this point, you need to create a Linux user with the same name as your Postgres role and database. To do this, run the following command:

sudo adduser orca

Now you can connect to the Postgres database with the following commands:

# sudo -i -u orca
# orca@sam:~$ psql

Or you can use the following command instead:

sudo -u orca psql
**Output**
psql (15.0)
Type "help" for help.

orca=#

When you have logged in to your PostgreSQL database on Rocky Linux 9, you can check your current connection information by running the following command:

orca=# conninfo

In your output you will see:

**Output**
You are connected to database "orca" as user "orca" via socket in "/var/run/postgres

For more information, you can visit the PostgreSQL Documentation page.

Conclusion

At this point, you have learned to Install and Configure PostgreSQL 15 on Rocky Linux 9. PostgreSQL 15 on Rocky Linux 9 is used for managing relational databases with advanced features, high performance, scalability, and strong data integrity. Install PostgreSQL 15 on Rocky Linux 9 is a relatively simple process and this article has guided you through it.

Hope you enjoy it. You may also like these articles:

How To Install Nginx on Rocky Linux 9

How To Install Apache on Rocky Linux 9

Install Python 3.13 on Rocky Linux 9

Alternative Solutions for Installing PostgreSQL 15 on Rocky Linux 9

While the method described above using the official PostgreSQL repository is a standard and reliable way to install PostgreSQL 15 on Rocky Linux 9, here are two alternative approaches:

1. Using Containerization (Docker)

Containerization, particularly with Docker, offers a highly portable and isolated environment for running applications, including databases. This approach eliminates potential conflicts with system libraries and dependencies, and simplifies deployment.

Explanation:

Docker allows you to run PostgreSQL 15 within a container. The container encapsulates all the necessary dependencies and configurations, ensuring consistent behavior across different environments. This is especially useful when you need to manage multiple PostgreSQL versions or when you want to isolate the database from the host system.

Steps:

  1. Install Docker: If Docker is not already installed, you need to install it.

    sudo dnf install docker -y
sudo systemctl start docker
sudo systemctl enable docker

  2. Pull the PostgreSQL 15 Docker Image: Obtain the official PostgreSQL 15 image from Docker Hub.

    sudo docker pull postgres:15

  3. Run the PostgreSQL 15 Container: Create and start a container using the pulled image. Important considerations here are setting the POSTGRES_PASSWORD environment variable for the initial user and database, and mapping a host directory to the container’s data directory for persistence.

    sudo docker run --name postgres15 -e POSTGRES_PASSWORD=your_secure_password -v /path/to/your/data:/var/lib/postgresql/data -p 5432:5432 -d postgres:15
    • --name postgres15: Assigns a name to the container.
    • -e POSTGRES_PASSWORD=your_secure_password: Sets the PostgreSQL superuser password. Replace your_secure_password with a strong password.
    • -v /path/to/your/data:/var/lib/postgresql/data: Mounts a host directory to the container’s data directory. This ensures that the database data persists even if the container is stopped or removed. Replace /path/to/your/data with a valid directory on your host system.
    • -p 5432:5432: Maps port 5432 on the host to port 5432 in the container. This allows you to connect to the database from the host system.
    • -d: Runs the container in detached mode (in the background).

  4. Connect to the PostgreSQL 15 Instance: You can now connect to the PostgreSQL 15 instance running inside the container using psql or any other PostgreSQL client.

    psql -h localhost -U postgres -p 5432 -W

    You will be prompted for the password you set in the POSTGRES_PASSWORD environment variable.

2. Using a Third-Party Management Tool (e.g., CockroachDB) Not Direct PostgreSQL 15 Install

While not a direct installation of PostgreSQL 15, this alternative leverages a distributed SQL database compatible with PostgreSQL. CockroachDB, while not PostgreSQL itself, offers a high degree of PostgreSQL compatibility and can be deployed on Rocky Linux 9. This option is particularly relevant if you need a database with built-in resilience and scalability.

Explanation:

CockroachDB is a distributed SQL database designed for resilience, scalability, and geo-distribution. It implements a large subset of the PostgreSQL wire protocol and supports many PostgreSQL features, making it relatively easy to migrate applications from PostgreSQL to CockroachDB. Installing CockroachDB provides a similar (though not identical) database environment that can be managed with some familiar PostgreSQL tools and techniques.

Steps:

  1. Download CockroachDB: Download the latest CockroachDB release for Linux from the official CockroachDB website.

    wget https://binaries.cockroachdb.com/cockroach-latest.linux-amd64.tgz

  2. Extract the Archive: Extract the downloaded archive.

    tar -xzf cockroach-latest.linux-amd64.tgz

  3. Move the Binary: Move the cockroach binary to a directory in your system’s PATH.

    sudo mv cockroach-latest.linux-amd64/cockroach /usr/local/bin/

  4. Start CockroachDB: Start a single-node CockroachDB cluster for testing and development. For production environments, a multi-node cluster is recommended.

    cockroach start-single-node --insecure --store=cockroach-data --listen-addr='localhost:26257' --http-addr='localhost:8080'
    • --insecure: Starts the cluster in insecure mode. Do not use this in production. Use certificates for secure communication in production environments.
    • --store=cockroach-data: Specifies the directory to store the database data.
    • --listen-addr='localhost:26257': Specifies the address to listen for client connections.
    • --http-addr='localhost:8080': Specifies the address for the Admin UI.

  5. Access the CockroachDB Console: Open a new terminal and access the CockroachDB SQL shell.

    cockroach sql --url "postgresql://root@localhost:26257?sslmode=disable"
    • --url "postgresql://root@localhost:26257?sslmode=disable": Specifies the connection URL. sslmode=disable is used because we started the cluster in insecure mode. In production, use sslmode=verify-full and provide the appropriate certificates.

    You can now execute SQL commands against the CockroachDB cluster, many of which are compatible with PostgreSQL syntax.

These alternative methods offer different advantages depending on your specific needs and priorities. Docker provides isolation and portability, while CockroachDB provides a distributed and resilient database solution with PostgreSQL compatibility. Remember to choose the method that best suits your requirements for Install PostgreSQL 15 on Rocky Linux 9 (or a PostgreSQL-compatible alternative).

Related posts: