How To Install PostgreSQL 14 on Rocky Linux 9

Databases are key in any production environment as they are used to store and organize data. A database is defined as a collection of structured data. They are majorly classified into two categories:

• Relational databases (RDBMS or SQL databases): They store data in tables and rows, the tables are then linked using keys. The popular relational databases are MySQL, MariaDB, PostgreSQL, SQLite e.t.c
• Non-relational databases(NoSQL databases): They store data using a storage model optimized for specific requirements. The most popular NoSQL databases are MongoDB, Couchbase, Apache Cassandra, Redis, Apache HBase e.t.c

PostgreSQL/Postgres is a free and open-source, object-oriented relational database management system for both small and large projects. It implements the Structured Query Language and also supports non-relational (JSON) queries. This database system has been in active development for over 30 years, resulting in high resilience and performance with notable levels of integrity. PostgreSQL finds use in several areas such as primary storage for web data, and mobile and analytic applications.

The latest release version, PostgreSQL 14 has innumerable performance improvements. These improvements continue to hold the reputation of PostgreSQL in connection concurrency, logical replication, high-write workloads, and query parallelism.

The cool features tied to this release are:

• Performance improvements through parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming.
• JSON conveniences and multi ranges improvements enable the representation of non-contiguous data ranges.
• OUT parameters can now be used to return data from stored procedures.
• Subscription operators were added to the JSON and hstore types.
• Supported pipelined queries through libpq, which significantly increases throughput over high-latency connections.
• Security enhancements
• Updates to B-tree indexes are more efficiently managed, resulting in less index bloat.

This guide covers all the steps on how to install and use PostgreSQL 14 in Rocky Linux 9.

1. Install PostgreSQL 14 on Rocky Linux 9

The default Rocky Linux 9 repositories have PostgreSQL 13 as the default and latest version. This can be verified by running the command below:

$ dnf provides postgresql-server
Last metadata expiration check: 0:01:10 ago on Tue 19 Jul 2022 03:58:17 PM CEST
postgresql-server-13.7-1.el9_0.x86_64 : The programs needed to create and run a PostgreSQL server
Repo        : appstream
Matched from:
Provide    : postgresql-server = 13.7-1.el9_0

However, in this guide, we are interested in PostgreSQL 14 which is not available in the default repositories. To be able to install it, we need to add an extra repository to the system.

To add the PostgreSQL 14 repository on Rocky Linux 9, use the command:

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

Disable the built-in PostgreSQL module;

sudo dnf -qy module disable postgresql

Once the repository has been added, PostgreSQL 14 can be installed on Rocky Linux 9 by executing the command:

sudo dnf install postgresql14-server

Dependency Tree

.....
Transaction Summary
=============================================================================================================
Install  4 Packages

Total download size: 7.5 M
Installed size: 32 M
Is this ok [y/N]: y

Initialize PostgreSQL:

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

Now start and enable PostgreSQL 14 on Rocky Linux 9

sudo systemctl start postgresql-14 && sudo systemctl enable postgresql-14

Verify if the service is running:

$ systemctl status postgresql-14.service
● postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
     Active: active (running) since Tue 2022-07-19 16:03:19 CEST; 32s ago
       Docs: https://www.postgresql.org/docs/14/static/
   Main PID: 31866 (postmaster)
      Tasks: 8 (limit: 23441)
     Memory: 16.5M
        CPU: 37ms
     CGroup: /system.slice/postgresql-14.service
             ├─31866 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
             ├─31867 "postgres: logger "
             ├─31869 "postgres: checkpointer "
             ├─31870 "postgres: background writer "
             ├─31871 "postgres: walwriter "
             ├─31872 "postgres: autovacuum launcher "
             ├─31873 "postgres: stats collector "
             └─31874 "postgres: logical replication launcher "

2. Using PostgreSQL Roles and Databases

Once installed, you are set to use PostgreSQL 14. But there are a few concepts available here:

• roles: this is used to handle client authentication and authorization. These are more similar to the Unix-style accounts but since PostgreSQL doesn’t distinguish between users and groups, it prefers to call them roles.
• ident authentication: This associates the Postgres roles with a matching Unix/Linux system account. In case a role exists within PostgreSQL, the Linux username with a similar identity is able to sign in as that role.

When PostgreSQL is installed, a user account with the name postgres is crated and tied as the default PostgreSQL role. To login to PostgreSQL, you can use two methods:

• Option 1

Connecting to the instance directly with the sudo command below.

$ sudo -u postgres psql
could not change directory to "/home/rocky9": Permission denied
psql (14.4)
Type "help" for help.

postgres=# 

Exit the shell using the command:

postgres=# \q

• Option 2

Switching to the Postgres user then access the Postgres prompt

sudo -i -u postgres

Access the PostgreSQL prompt:

[postgres@neveropen ~]$ psql
psql (14.4)
Type "help" for help.

postgres=# 

Here, to exit the shell, use the command:

postgres=# \q

Now exit the postgres account:

[postgres@neveropen ~]$ exit

Creating a New Role in PostgreSQL 14

To create a new role in PostgreSQL 14, connect to the postgres role first:

sudo -u postgres psql

Create the user using the command:

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';

Verify the creation:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

Creating a New Database in PostgreSQL 14

The next this is to create a new database for the created user above. Still, in the PostgreSQL shell, create a database with the same name as the user since the role will attempt to connect to a database.

postgres=# CREATE DATABASE admin;
CREATE DATABASE
postgres=# \q

Opening a Postgres Prompt with the New Role

To access the created role, begin by adding the role as a Linux user

sudo adduser admin

Verify if you can connect to the created user:

$ sudo -u admin psql
could not change directory to "/home/rocky9": Permission denied
psql (14.4)
Type "help" for help.

admin=# \conninfo
You are connected to database "admin" as user "admin" via socket in "/var/run/postgresql" at port "5432".

Creating Tables in PostgreSQL 14

Once connected the PostgreSQL using the new user and database, you can create a table using the command with the syntax below:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

For example:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Verify the creation:

admin# \dt
         List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | admin
(1 row)

To view detailed information use:

admin# \d
                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | admin
 public | playground_equip_id_seq | sequence | admin
(2 rows)

Adding, Querying, and Deleting Data in a Table

With the table created, you can add the preferred data to it. for example:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Retrieve the added data:

admin=#  SELECT * FROM playground;
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

You can delete entries in the table. For example:

DELETE FROM playground WHERE type = 'slide';
SELECT * FROM playground;

Sample Output:

equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Updating Data in a Table

To update data in an existing table we can proceed as shown.

UPDATE playground SET color = 'red' WHERE type = 'swing';

You will have the color updated in “swing” to red

admin=# SELECT * FROM playground;
 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)

Exit the shell:

admin=# \q

3. Configure PostgreSQL for Remote Access (optional)

The PostgreSQL 14 can be configured for remote access. But before that, you need to familiarize yourself with a few concepts. As of now, you know the default authentication in PostgreSQL is the ident authentication. The other authentication types are:

• Password: here a role connects using a password.
• Trust: allows a role to connect as long as conditions are defined in the pg_hba.conf file are satisfied.
• Peer: similar to ident authentication but it is only supported on local connections.

For remote access, we need to make a few configurations to the pg_hba.conf file. Allow the PostgreSQL 14 instance to be accessed from everywhere;

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

In the opened file, make the below changes:

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0                md5

# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                md5

Then allow listening on * by editing the postgresql.conf file below.

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

Uncomment and edit the line:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------------------------------
.......
listen_addresses='*'

Restart the service for changes to apply:

sudo systemctl restart postgresql-14.service

4. Connect to PostgreSQL 14 Instance Remotely.

By default, PostgreSQL listens on port 5432. Verify this with the command:

$ ss -tunelp | grep 5432
tcp   LISTEN 0      244          0.0.0.0:5432       0.0.0.0:*    uid:26 ino:59544 sk:9 cgroup:/system.slice/postgresql-14.service <->        
tcp   LISTEN 0      244             [::]:5432          [::]:*    uid:26 ino:59545 sk:c cgroup:/system.slice/postgresql-14.service v6only:1 <->

To be able to access it, we need to allow this port through the firewall:

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

To access PostgreSQL from a remote host, use the command with the syntax:

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

For example:

psql 'postgres://admin:[email protected]:5432/postgres?sslmode=disable'

Sample Output:

Recommended books:

• Best Books To Learn PostgreSQL Databases

Conclusion

We have triumphantly walked through a detailed demonstration of how to install PostgreSQL 14 on Rocky Linux 9. We have also learned a few basic operations on PostgreSQL. I hope this was informative.

See more on this page:

• Best Books To Learn PostgreSQL Database
• Install PostgreSQL 14 on Ubuntu 22.04 (Jammy Jellyfish)
• How To Install PostgreSQL 14 on Debian
• How To Manage PostgreSQL Database with Ansible