Install PostgreSQL 14 on Rocky Linux 8 | Best Setup

In this comprehensive guide brought to you by Orcacore, we will walk you through the process of How To Install PostgreSQL 14 on Rocky Linux 8. PostgreSQL is a powerful, enterprise-grade, open-source database management system (DBMS). Its support for both SQL and JSON enables relational and non-relational queries, offering exceptional extensibility and adherence to SQL standards. What sets PostgreSQL apart is its inclusion of advanced data types and performance optimization features, typically found only in expensive commercial databases like Oracle and SQL Server. It’s also widely recognized and referred to as Postgres.

PostgreSQL 14 introduces significant enhancements, including streamlined authentication processes. Notably, it simplifies the assignment of write-only and read-only privileges to users for tables, views, and schemas through the introduction of two predefined roles: pg_write_all_data and pg_read_all_data. The pg_write_all_data role simplifies the creation of superuser-like permissions.

Before proceeding, ensure you are logged into your Rocky Linux 8 server as a non-root user with sudo privileges. If you haven’t already configured this, refer to our guide on Initial Server Setup with Rocky Linux 8.

1. Import PostgreSQL 14 Repository on Rocky Linux 8

First, update your local package index to ensure you have the latest information about available packages:

sudo dnf update -y

Next, install the PostgreSQL RPM repository using the following command:

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

Disable the built-in PostgreSQL module to prevent conflicts with the version you’re about to install:

sudo dnf -qy module disable postgresql

2. Installing PostgreSQL 14 on Rocky Linux 8

Now, you can install PostgreSQL 14 on Rocky Linux 8 with this command:

sudo dnf install -y postgresql14-server

After the installation completes, initialize the database and enable automatic startup of the PostgreSQL service:

# sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
# sudo systemctl enable postgresql-14
# sudo systemctl start postgresql-14

Verify that PostgreSQL 14 is active and running correctly:

sudo systemctl status postgresql-14

Check PostgreSQL 14 is Running and Active

3. Switch To PostgreSQL 14 Account

In PostgreSQL, only superusers or roles with the CREATEROLE privilege can create new roles. During the installation process, a user account associated with the default Postgres role is automatically created.

To access the PostgreSQL 14 shell, execute these commands:

sudo -i -u postgres

Output
[postgres@olivia ~]$

Then, type:

[postgres@olivia ~]$ psql

This indicates a successful connection to the database.

To exit the Postgres database shell, type:

exit

Alternatively, you can access the PostgreSQL shell directly:

sudo -u postgres psql

Output
psql (14.5)
Type "help" for help.

postgres=#

4. Create User and Database with PostgreSQL 14

Now, let’s create a user and a database. As mentioned earlier, only superusers and roles with the "createrole" privilege can create new roles.

To create a new user, use the following command:

sudo su - postgres -c "createuser <name>"

For example:

sudo su - postgres -c "createuser orcauser"

Create a PostgreSQL database for the newly created user:

sudo su - postgres -c "createdb <namedb>"

For example:

sudo su - postgres -c "createdb orcadb"

Grant the necessary privileges to the database and user. First, access the PostgreSQL shell:

sudo -u postgres psql

Then, grant all privileges to the database for the specified user:

postgres=# GRANT ALL PRIVILEGES ON DATABASE orcadb TO orcauser;

Output
GRANT

Check your current connection information:

postgres=# conninfo

Exit the PostgreSQL shell:

exit

For more detailed information, refer to the PostgreSQL Documentation Page.

Conclusion

You have now successfully learned How To Install PostgreSQL 14 on Rocky Linux 8 and how to create a user and database using your PostgreSQL account. Installing PostgreSQL 14 on Rocky Linux 8 allows you to leverage a robust database for your applications.

We hope you found this guide helpful. Please follow us on Facebook and Twitter.

You might also be interested in these related articles:

Install Django on Rocky Linux 9

Configure Apache Maven on Rocky Linux 8

Install GlassFish on Rocky Linux 9

Install Python 3.11 on Rocky Linux 9

Alternative Solutions for Installing PostgreSQL 14 on Rocky Linux 8

While the above method provides a straightforward approach to installing PostgreSQL 14 on Rocky Linux 8, here are two alternative methods: using Docker and using Ansible.

1. Installing PostgreSQL 14 using Docker

Docker provides a containerized environment, allowing you to run PostgreSQL 14 without directly installing it on your host system. This approach offers isolation, portability, and ease of management.

Explanation:

Docker containers encapsulate all dependencies required to run an application, including the PostgreSQL database. This eliminates dependency conflicts and ensures consistent behavior across different environments. Docker Hub provides pre-built PostgreSQL images, simplifying the setup process.

Steps:

Install Docker: If you haven’t already, install Docker on your Rocky Linux 8 system. Refer to the official Docker documentation for installation instructions.
```
sudo dnf install -y docker
sudo systemctl start docker
sudo systemctl enable docker
```
Pull the PostgreSQL 14 Docker Image: Obtain the official PostgreSQL 14 image from Docker Hub.
```
sudo docker pull postgres:14
```
Run the PostgreSQL 14 Container: Create and start a Docker container based on the pulled image. Configure environment variables for the PostgreSQL superuser password.
```
sudo docker run --name postgres14 -e POSTGRES_PASSWORD=your_strong_password -p 5432:5432 -d postgres:14
```
- --name postgres14: Assigns the name "postgres14" to the container.
- -e POSTGRES_PASSWORD=your_strong_password: Sets the PostgreSQL superuser password. Replace your_strong_password with a secure password.
- -p 5432:5432: Maps port 5432 on the host to port 5432 within the container.
- -d: Runs the container in detached mode (background).
- postgres:14: Specifies the image to use.
Connect to the PostgreSQL 14 Instance: Use a PostgreSQL client (e.g., psql) to connect to the database running inside the Docker container.
```
psql -h localhost -U postgres -p 5432
```
You will be prompted for the password you set in the POSTGRES_PASSWORD environment variable.
Optional: Persisting Data: By default, the data inside a Docker container is ephemeral. To persist data between container restarts, you can use Docker volumes. Create a volume and mount it to the container’s data directory.
```
sudo docker volume create pgdata
sudo docker run --name postgres14 -e POSTGRES_PASSWORD=your_strong_password -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres:14
```
This will store the database data in the pgdata volume, ensuring it survives container restarts.

Using Docker simplifies How To Install PostgreSQL 14 on Rocky Linux 8 and manages the database environment.

2. Installing PostgreSQL 14 using Ansible

Ansible is an automation tool that allows you to define and execute tasks across multiple servers. You can use Ansible to automate the installation and configuration of PostgreSQL 14 on Rocky Linux 8.

Explanation:

Ansible uses playbooks, which are YAML files that define the desired state of your systems. You can create a playbook to install PostgreSQL, configure its settings, and ensure the service is running. This approach provides idempotency, meaning that running the playbook multiple times will always result in the same desired state.

Steps:

Install Ansible: Install Ansible on your control machine (the machine from which you’ll run the playbook).
```
sudo dnf install -y ansible
```
Create an Inventory File: Define the target server(s) in an Ansible inventory file (e.g., inventory.ini).
```
[databases]
dbserver ansible_host=your_server_ip ansible_user=your_user ansible_ssh_private_key_file=~/.ssh/id_rsa
```
- dbserver: The name of the server group.
- ansible_host: The IP address or hostname of the target server.
- ansible_user: The username to use for SSH connections.
- ansible_ssh_private_key_file: The path to the SSH private key for authentication. Replace with your actual server details.

Create an Ansible Playbook: Create a YAML file (e.g., install_postgresql.yml) containing the Ansible tasks.

---
- hosts: databases
  become: true
  tasks:
    - name: Update package cache
      dnf:
        update_cache: yes

    - name: Install PostgreSQL 14 repository
      dnf:
        name: https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
        state: present

    - name: Disable built-in PostgreSQL module
      command: dnf -qy module disable postgresql
      ignore_errors: yes  # Module might not be enabled

    - name: Install PostgreSQL 14 server
      dnf:
        name: postgresql14-server
        state: present

    - name: Initialize the database
      command: /usr/pgsql-14/bin/postgresql-14-setup initdb
      args:
        creates: /var/lib/pgsql/14/data/PG_VERSION # Only run if PG_VERSION doesn't exist

    - name: Enable and start PostgreSQL 14 service
      systemd:
        name: postgresql-14
        enabled: yes
        state: started

This playbook performs the following tasks:

Updates the package cache.
Installs the PostgreSQL 14 repository.
Disables the built-in PostgreSQL module.
Installs the PostgreSQL 14 server package.
Initializes the database (only if it hasn’t been initialized before).
Enables and starts the PostgreSQL 14 service.

Run the Ansible Playbook: Execute the playbook using the ansible-playbook command.
```
ansible-playbook -i inventory.ini install_postgresql.yml
```

Ansible automates How To Install PostgreSQL 14 on Rocky Linux 8, ensuring consistency and repeatability across multiple servers.