PostgreSQL is an open-source, relational database system that uses SQL to store and scale complicated workloads. PostgreSQL is used as the primary data store or data warehouse for many web, mobile, geospatial, and analytics applications due to its proven architecture, reliability, data integrity, robust feature set, and extensibility. It is a highly stable database backed by more than 30 years of active development on the core platform and the dedication of the open-source community behind the software to consistently deliver performant and innovative solutions.

PostgreSQL is highly extensive with options that you can use to define your data types, build out custom functions, and write code from different programming languages without recompiling your database. It is a high fault-tolerant database with high data integrity that helps developers build applications and manage data whether big or small.

PostgreSQL 14 has improvements like;

  • Built-in Range and Multirange Types with an option to create your range types.
  • B-tree bottom-up index deletion.
  • Logical replication stream in-progress transactions.
  • Window functions can use incremental sorting.
  • Foreign data wrapper query parallelism.
  • The compression method for TOAST tables is now selectable and adds support for lz4 compression.
  • Pipeline mode allows applications to send a query without having to read the result of the previously sent query.

This guide will show you how to install Install PostgreSQL 14 on CentOS 9|AlmaLinux 9|RHEL 9.

Install PostgreSQL 14 on CentOS 9|AlmaLinux 9|RHEL 9

Add PostgreSQL 14 repository to your system.

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

Update your system packages.

sudo dnf update -y

Install PostgreSQL with the following command.

sudo dnf install -y postgresql14-server

Initialize, start and enable the PostgreSQL server.

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

Check the status of the server.

$ systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; ve>
     Active: active (running) since Tue 2022-07-12 18:54:59 EAT; 9s ago
       Docs: https://www.postgresql.org/docs/14/static/
    Process: 33479 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${>
   Main PID: 33484 (postmaster)
      Tasks: 8 (limit: 48809)
     Memory: 16.6M
        CPU: 36ms
     CGroup: /system.slice/postgresql-14.service
             ├─33484 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
             ├─33485 "postgres: logger "
             ├─33487 "postgres: checkpointer "
             ├─33488 "postgres: background writer "
             ├─33489 "postgres: walwriter "
             ├─33490 "postgres: autovacuum launcher "
             ├─33491 "postgres: stats collector "
             └─33492 "postgres: logical replication launcher "

To directly access the database control, use the following command.

sudo -i -u postgres psql

To confirm the current connection and user information, enter the following command.

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Then you can check the version with the following code.

postgres=# SELECT version();
                                                 version                        
                          
--------------------------------------------------------------------------------
--------------------------
 PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.1 20220127 (
Red Hat 11.2.1-9), 64-bit
(1 row)

To exit the shell, use the following command.

postgres=# \q

Change Default port

By default, PostgreSQL listens from the local host connection. To allow remote connection, edit the configuration file.

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

Then edit the addresses as shown below.

# Set to listen on all network interfaces
listen_addresses = '*'

# Set to listen on specific IP address
listen_addresses = '192.168.10.12'

Save and exit the file.

Add a client authentication entry to the pg_hba.conf file.

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

Add the following entry to the file to accept a remote connection.

host      all      all      0.0.0.0/0       md5

To allow IP from a local network, you could use the following.

host      all      all      192.168.200.0/24      md5

Restart the service for the changes to take effect.

sudo systemctl restart postgresql-14

Create Users

Create a new user

sudo -i -u postgres createuser technixleo

Create Password for the User

sudo -i -u postgres psql

Then edit the password for the user.

ALTER USER technixleo PASSWORD 'myPassword';
ALTER ROLE

Quit the shell

\q

Create Database

Login to the shell.

sudo -i -u postgres

To create a database, use the following command.

[[email protected] ~]$ createdb newdb

To access the database, use the following command.

$ psql newdb

It opens as shown below.

newdb=# 

Create Table

To create a table, access the PostgreSQL shell with the database, then use the following command. I am creating a table named staff with the following fields using the CREATE statement.

CREATE TABLE staff (
        ID                       int,
        First_Name          varchar(50),
        Last_Name          varchar(50),
        Department         varchar(50)
);

To Populate your table with data, use the INSERT statement.

INSERT INTO staff 
VALUES  ( 2019, 'Mark', 'Carlton', 'Accounts'),
             ( 2341, 'Jane', 'Doe', 'IT'),
             ( 2134, 'John', 'Doe', 'HR'),
             ( 3123, 'Sia', 'Nungari', 'Reception'),
             ( 6231, 'Karen', 'Karfield', 'Reception'),
             ( 6543, 'Amos', 'Wart', 'Accounts');

To Query a table, use the SELECT statement.

newdb=# SELECT * FROM staff;

  id  | first_name | last_name | department 
------+------------+-----------+------------
 2019 | Mark       | Carlton   | Accounts
 2341 | Jane       | Doe       | IT
 2134 | John       | Doe       | HR
 3123 | Sia        | Nungari   | Reception
 6231 | Karen      | Karfield  | Reception
 6543 | Amos       | Wart      | Accounts
(6 rows)

You can remove a row from a table using the DELETE command.

newdb=# DELETE FROM staff WHERE Department = 'Reception';
DELETE 2

Then check the table, the records from the Reception department are removed.

newdb=# SELECT * FROM staff;
  id  | first_name | last_name | department 
------+------------+-----------+------------
 2019 | Mark       | Carlton   | Accounts
 2341 | Jane       | Doe       | IT
 2134 | John       | Doe       | HR
 6543 | Amos       | Wart      | Accounts
(4 rows)

To drop a table, simply use the DROP statement.

newdb=# DROP TABLE staff;
DROP TABLE

Conclusion

From this guide, we have installed PostgreSQL 14 on CentOS 9|AlmaLinux 9|RHEL 9. We have also seen how to allow connections from all remote instances or specify remote network ranges. PostgreSQL is an object-relational database management system that supports both SQL (relational) and JSON (non-relational) querying.

More articles to check out on RHEL 9 based Linux systems:

LEAVE A REPLY

Please enter your comment!
Please enter your name here