Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8

Posted on 62 views

PostgreSQL is a relational database management system that is well-known for its dependability and resilience. You can count on reliability and issue fixes in your database lifecycle thanks to over 30 years of open source development from a global developer community. PostgreSQL is used by thousands of enterprises to support financial transactions, large amounts of website traffic, and e-commerce platforms, among other things. It’s also possible to add custom functions written in programming languages like Java, Python, C/C++, and others.

PostgreSQL just announced the release of PostgreSQL 14, a new version of the database. This new version has numerous speed enhancements to make it easier for administrators and developers to construct data-driven applications. Furthermore, it maintains PostgreSQL’s reputation by enhancing logical replication, query parallelism, high-write workloads, and connection concurrency, all of which result in improved performance. In this guide we will help you install PostgreSQL 14 on Rocky Linux 8 | AlmaLinux 8 | CentOS 8 Linux system.

Features of PostgreSQL 14

The following are the cool features shifted with PostgreSQL 14:

  • Parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming all increase performance.
  • B-tree index updates are handled more effectively, resulting in less index bloat.
  • Pipelined queries were supported using libpq, which increased throughput dramatically over high-latency connections.
  • Enhancements to security.
  • The jsonb and hstore types now provide subscripting operators.
  • Multiranges and JSON conveniences enable the representation of non-contiguous data ranges.
  • Data from stored procedures can now be returned using OUT parameters.

Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8

The steps below will guide you on how to install PostgreSQL 14 on PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8.

Step 1: System Update

Before you begin installing PostgreSQL 14 on Rocky Linux 8 | AlmaLinux 8 | CentOS 8, make the following updates to your system:

sudo dnf update

Reboot your system after the update:

sudo reboot

Step 2: Add PostgreSQL Repository on Rocky Linux 8|AlmaLinux 8|CentOS 8

PostgreSQL server 9.6, 10, 12 and 13 are included in the AppStream components.

$ dnf module list postgresql
Last metadata expiration check: 1 day, 2:59:37 ago on Tue 26 Oct 2021 08:31:04 AM EDT.
Rocky Linux 8 - AppStream
Name                                     Stream                               Profiles                                         Summary                                                         
postgresql                               9.6                                  client, server [d]                               PostgreSQL server and client module                             
postgresql                               10 [d]                               client, server [d]                               PostgreSQL server and client module                             
postgresql                               12                                   client, server [d]                               PostgreSQL server and client module                             
postgresql                               13                                   client, server [d]                               PostgreSQL server and client module                             

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

We’ll need to add the following official repositories to install PostgreSQL 14 on Rocky Linux 8 | AlmaLinux 8:

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

Installation output sample:

....
pgdg-redhat-repo-latest.noarch.rpm                                                                                                                             3.1 kB/s |  12 kB     00:03    
Dependencies resolved.
===============================================================================================================================================================================================
 Package                                            Architecture                             Version                                      Repository                                      Size
===============================================================================================================================================================================================
Installing:
 pgdg-redhat-repo                                   noarch                                   42.0-20                                      @commandline                                    12 k

Transaction Summary
===============================================================================================================================================================================================
Install  1 Package

Total size: 12 k
Installed size: 12 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                       1/1 
  Installing       : pgdg-redhat-repo-42.0-20.noarch                                                                                                                                       1/1 
  Verifying        : pgdg-redhat-repo-42.0-20.noarch                                                                                                                                       1/1 
Installed products updated.

Installed:
  pgdg-redhat-repo-42.0-20.noarch                                                                                                                                                              

Complete!

Step 3: Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8

Disable the default PostgreSQL module once the repository has been added:

sudo dnf -qy module disable postgresql

Then install PostgreSQL 14 Server and Client:

$ sudo dnf install -y postgresql14-server
Last metadata expiration check: 0:05:42 ago on Wed 27 Oct 2021 11:39:42 AM EDT.
Dependencies resolved.
===============================================================================================================================================================================================
 Package                                              Architecture                            Version                                            Repository                               Size
===============================================================================================================================================================================================
Installing:
 postgresql14-server                                  x86_64                                  14.0-1PGDG.rhel8                                   pgdg14                                  5.7 M
Installing dependencies:
 postgresql14                                         x86_64                                  14.0-1PGDG.rhel8                                   pgdg14                                  1.5 M
 postgresql14-libs                                    x86_64                                  14.0-1PGDG.rhel8                                   pgdg14                                  274 k

Transaction Summary
===============================================================================================================================================================================================
Install  3 Packages

Total download size: 7.5 M
Installed size: 31 M

Step 4: Initialize and Start PostgreSQL 14 Database Service

Before running the service, you must first initialize the database instance after installing PostgreSQL 14:

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

This will create the initial data as well as the main configuration file, which will be written to /var/lib/pgsql/14/data/postgresql.conf.

The PostgreSQL 14 database service is enabled and started as follows:

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

Verify that the PostgreSQL 14 service is up and running:

$ systemctl status postgresql-14
 postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-10-27 13:06:30 EDT; 1min 11s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 7547 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir $PGDATA (code=exited, status=0/SUCCESS)
 Main PID: 7552 (postmaster)
    Tasks: 8 (limit: 23547)
   Memory: 16.7M
   CGroup: /system.slice/postgresql-14.service
           ├─7552 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─7554 postgres: logger 
           ├─7556 postgres: checkpointer 
           ├─7557 postgres: background writer 
           ├─7558 postgres: walwriter 
           ├─7559 postgres: autovacuum launcher 
           ├─7560 postgres: stats collector 
           └─7561 postgres: logical replication launcher 

Oct 27 13:06:30 localhost.localdomain systemd[1]: Starting PostgreSQL 14 database server...
Oct 27 13:06:30 localhost.localdomain postmaster[7552]: 2021-10-27 13:06:30.638 EDT [7552] LOG:  redirecting log output to logging collector process
Oct 27 13:06:30 localhost.localdomain postmaster[7552]: 2021-10-27 13:06:30.638 EDT [7552] HINT:  Future log output will appear in directory "log".
Oct 27 13:06:30 localhost.localdomain systemd[1]: Started PostgreSQL 14 database server.

Step 5: Connect to PostgreSQL 14 Database Locally

There are two methods to connect to a PostgreSQL 14 instance.

Method 1

Using sudo to run the Postgresql command directly.

$ sudo -u postgres psql
psql (14.0)
Type "help" for help.

postgres=#

Method 2

To use this method, you must first switch to the Postgresql user created after installing PostgreSQL 14.

$ sudo -i -u postgres
[[email protected] ~]$ 

Connect to the instance while you’re here using psql command.

$ psql
psql (14.0)
Type "help" for help.

postgres=#

Secure Postgres User

Now that you’re logged in as the postgres user, execute the following command to set a strong password for the default postgres user:

$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE

Create Database in PostgreSQL

Connect to PostgreSQL to create a database. When PostgreSQL is installed, a default user named ‘postgres‘ is created. Make a connection with this user first. Create database called testdb.

# CREATE DATABASE testdb;
CREATE DATABASE

Create Tables in PostgreSQL

We’ll make a new table called Employees with the columns listed below:

  1. user_id – primary key
  2. username – unique and not null
  3. password – not null
  4. email – unique and not null
  5. created_on – not null
  6. last_login – null
# CREATE TABLE Employees (
postgres(# user_id serial PRIMARY KEY,
postgres(# username VARCHAR ( 50 ) UNIQUE NOT NULL,
postgres(# password VARCHAR ( 50 ) NOT NULL,
postgres(# email VARCHAR ( 255 ) UNIQUE NOT NULL,
postgres(# created_on TIMESTAMP NOT NULL,
postgres(#         last_login TIMESTAMP 
postgres(# );
CREATE TABLE

List PostgreSQL Tables:

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

Delete PostgreSQL Tables:

# DROP TABLE employees;
DROP TABLE

Step 6: Changing PostgreSQL Service Port

Check the default port on which PostgreSQL listens:

$ sudo netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/systemd           
tcp        0      0 192.168.122.1:53        0.0.0.0:*               LISTEN      1592/dnsmasq        
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1014/sshd           
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1015/cupsd          
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      7552/postmaster     
tcp6       0      0 :::111                  :::*                    LISTEN      1/systemd           
tcp6       0      0 :::22                   :::*                    LISTEN      1014/sshd           
tcp6       0      0 ::1:631                 :::*                    LISTEN      1015/cupsd          
tcp6       0      0 ::1:5432                :::*                    LISTEN      7552/postmaster

From the above output is running on port 5432.

By modifying the postgresql.conf file in the /var/lib/psql/14/data/ directory, we may change PostgreSQL’s default service port, which is 5432.

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

In the above file search for #port = 5432 and change to 5436:

# line 64 uncomment and change it to 5436
port = 5436                             # (change requires restart)

To make the changes take effect, restart PostgreSQL:

sudo systemctl restart postgresql-14

confirm if PostgreSQL is listening on port 5436:

$ sudo netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/systemd           
tcp        0      0 192.168.122.1:53        0.0.0.0:*               LISTEN      1592/dnsmasq        
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1014/sshd           
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1015/cupsd          
tcp        0      0 127.0.0.1:5436          0.0.0.0:*               LISTEN      11630/postmaster    
tcp6       0      0 :::111                  :::*                    LISTEN      1/systemd           
tcp6       0      0 :::22                   :::*                    LISTEN      1014/sshd           
tcp6       0      0 ::1:631                 :::*                    LISTEN      1015/cupsd          
tcp6       0      0 ::1:5436                :::*                    LISTEN      11630/postmaster

PostgreSQL is now listening on port 5436.

Step 7: Enable PostgreSQL Remote Access

You can update the configuration and set Listen address to your server IP address or “*” for all interfaces if you have applications that will connect to the server via the network.

$ sudo vim /var/lib/pgsql/14/data/postgresql.conf 
listen_addresses = '192.168.156.53'

PostgreSQL should also be configured to accept remote connections:

$ sudo vim /var/lib/pgsql/14/data/pg_hba.conf
# Accept from anywhere
#host all all 0.0.0.0/0 md5

# Accept from trusted subnet
host all all 192.168.156.0/24 md5

You must restart database service after performing the changes:

sudo systemctl restart postgresql-14

If you have an active firewalld service, allow port 5432/tcp:

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

Pass the IP address of your server to the psql command to test database remote connections:

$ psql -U  -h  -p 5432 

Step 8: Install PgAdmin 4 on Rocky Linux 8|AlmaLinux 8|CentOS 8

PgAdmin 4 tool enables you to administer PostgreSQL database server from a web interface. You can install it by following our guide in the link below:

Conclusion

Our article on how to install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8 is now complete. We hope you found this material useful; we will continue to provide you with new and valuable tutorials. Keep an eye out for updates.

coffee

Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.