How to Install PostgreSQL 14 on Rocky Linux

PostgreSQL is a free, open-source, object-relational database system designed to scale the most complicated data workloads. Generally, it is used as the primary data store for many web, mobile, geospatial, and analytics applications. PostgreSQL can store structured and unstructured data in a single product. PostgreSQL 14 comes with a variety of features that help users to deploy their data-backed applications.

In this post, we will show you how to install PostgreSQL 14 in Rocky Linux 8.

Step 1 – Add PostgreSQL 14 Repository

By default, the latest version of PostgreSQL is not included in the Rocky Linux 8 default repo. You can confirm this by running the following command:

dnf update -y
dnf module list postgresql

You should see the all available PostgreSQL versions in the following output:

Last metadata expiration check: 0:19:52 ago on Wednesday 16 February 2022 04:26:10 PM UTC.
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                 

Now, install the PostgreSQL 14 repository using the following command:

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

Once the PostgreSQL 14 repository is added, you can move to the next step.

Also Read

How To Secure PostgreSQL Server

Step 2 – Install PostgreSQL 14 on Rocky Linux 8

First, you will need to disable the default PostgreSQL repository:

dnf -qy module disable postgresql

Next, run the following command to install the PostgreSQL 14:

dnf install postgresql14 postgresql14-server -y

Once the installation is finished, initialized the PostgreSQL database with the following command:

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

Next, start and enable the PostgreSQL service using the following command:

systemctl start postgresql-14
systemctl enable postgresql-14

To check the status of PostgreSQL, run the following command:

systemctl status postgresql-14

You will get the following output:

● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-02-16 16:47:32 UTC; 10s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 8423 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 8428 (postmaster)
    Tasks: 8 (limit: 11412)
   Memory: 17.4M
   CGroup: /system.slice/postgresql-14.service
           ├─8428 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─8430 postgres: logger 
           ├─8432 postgres: checkpointer 
           ├─8433 postgres: background writer 
           ├─8434 postgres: walwriter 
           ├─8435 postgres: autovacuum launcher 
           ├─8436 postgres: stats collector 
           └─8437 postgres: logical replication launcher 

Feb 16 16:47:32 linux systemd[1]: Starting PostgreSQL 14 database server...
Feb 16 16:47:32 linux postmaster[8428]: 2022-02-16 16:47:32.115 UTC [8428] LOG:  redirecting log output to logging collector process
Feb 16 16:47:32 linux postmaster[8428]: 2022-02-16 16:47:32.115 UTC [8428] HINT:  Future log output will appear in directory "log".
Feb 16 16:47:32 linux systemd[1]: Started PostgreSQL 14 database server.

By default, PostgreSQL listens on port 5432. You can check it with the following command:

ss -antpl | grep 5432

You will get the following output:

LISTEN 0      128        127.0.0.1:5432       0.0.0.0:*    users:(("postmaster",pid=8428,fd=7))                                                                                           
LISTEN 0      128            [::1]:5432          [::]:*    users:(("postmaster",pid=8428,fd=6))                                                                                           

Step 3 – Connect to PostgreSQL

There are two ways to connect to the local PostgreSQL instance:

Use the following command to connect to the local PostgreSQL instance directly:

sudo -u postgres psql

You will get the PostgreSQL shell in the following output:

psql (14.2)
Type "help" for help.

postgres=# 

You can also use the following method to connect to the local PostgreSQL instance:

First, switch the user to the PostgreSQL user:

su - postgres

Next, run the psql command to connect to the PostgreSQL shell:

psql

You will get the following output:

psql (14.2)
Type "help" for help.

postgres=# 

Now, run the exit command two times to exit from the PostgreSQL.

Also Read

How to Install and Use pgAdmin on Ubuntu 18.04

Step 4 – Working with PostgreSQL

By default, the Postgres user is configured without any password. For security reasons, it is recommended to set a strong password for the Postgres user.

Run the following command to set a password for the Postgres user:

psql -c "alter user postgres with password 'password'"

To create a database named wpdb, run the following command:

psql
CREATE DATABASE wpdb;

To list all PostgreSQL databases, run the following command:

\l

You will get a list of all PostgreSQL databases in the following output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 wpdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

Step 5 – Configure PostgreSQL for Remote Access

By default, PostgreSQL is configured to listen on localhost, so only local users can connect to the PostgreSQL database. If your application is hosted on a different server then you will need to configure PostgreSQL for remote access.

You can configure it by editing the following file:

nano /var/lib/pgsql/14/data/postgresql.conf

Change the following line with your server IP address:

listen_addresses = 'your-server-ip'

Save and close the file, then edit another file:

nano /var/lib/pgsql/14/data/pg_hba.conf

Add the following line:

# Accept from anywhere
host all all 0.0.0.0/0 md5

Save and close the file, then restart the PostgreSQL service to apply the changes:

systemctl restart postgresql-14

You can now connect to the PostgreSQL server from the remote machine using the following command:

psql -U username -h your-server-ip -p 5432 dbname

Conclusion

In this post, we learned how to install PostgreSQL 14 on Rocky Linux 8. We also learned how to connect and interact with PostgreSQL. Try it on dedicated hosting from Atlantic.Net!