Atlantic.Net Blog

How to Install and Use PostgreSQL on Oracle Linux 8

Hitesh Jethva
by Atlantic.Net (511 posts) under Dedicated Server Hosting, Tutorials
0 Comments

PostgreSQL is a free, open-source, secure, and powerful relational database management system. It is specially designed for high-performance and mission-critical applications. PostgreSQL supports both SQL and JSON querying and is mainly used for many web applications as well as mobile and analytics applications. It is compatible with various platforms using all major languages and middleware. At the time of writing this article, PostgreSQL 14 is the latest version. This version comes with significant improvements to the indexing and lookup system that benefit large databases.

In this post, we will show you how to install and secure PostgreSQL on Oracle Linux 8.

Prerequisites

  • A server running Oracle Linux 8 on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Oracle Linux 8 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged in to your server, run the following command to update your base system with the latest available packages.

dnf update -y

Step 2 – Add PostgreSQL 14 Repository

By default, the latest version of PostgreSQL is not included in the Oracle Linux default repository. You can check the default available version in the AppStream repository using the following command:

dnf module list postgresql

You should see the list of available versions in the following output:

Oracle Linux 8 Application Stream (x86_64)
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

In order to install the latest PostgreSQL version, you will need to install the PostgreSQL repo on your system.

You can install it 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 repo is created, you can proceed to the next step.

Step 3 – Install PostgreSQL 14 on OracleLinux 8

Now, update your repository using the following command:

dnf update -y

Next, disable the default PostgreSQL repo using the following command:

dnf -qy module disable postgresql

Next, install the latest version of PostgreSQL by running the following command:

dnf install postgresql14 postgresql14-server

Once PostgreSQL is installed, you will get the following output:

Last metadata expiration check: 0:00:11 ago on Sunday 29 May 2022 12:33:52 PM EDT.
Dependencies resolved.
==============================================================================================================================================
 Package                               Architecture             Version                             Repository                           Size
==============================================================================================================================================
Installing:
 postgresql14                          x86_64                   14.3-1PGDG.rhel8                    pgdg14                              1.5 M
 postgresql14-server                   x86_64                   14.3-1PGDG.rhel8                    pgdg14                              5.7 M
Installing dependencies:
 lz4                                   x86_64                   1.8.3-3.el8_4                       ol8_baseos_latest                   103 k
 postgresql14-libs                     x86_64                   14.3-1PGDG.rhel8                    pgdg14                              276 k

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

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

Next, initialize the PostgreSQL database with the following command:

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

Sample output:

Initializing database ... OK

Next, start the PostgreSQL service and enable it to start at system reboot with the following command:

systemctl start postgresql-14
systemctl enable postgresql-14

You can check the status of PostgreSQL with the following command:

systemctl status postgresql-14

You should 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 Sun 2022-05-29 12:35:13 EDT; 8s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 2084 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 2090 (postmaster)
    Tasks: 8 (limit: 23694)
   Memory: 16.7M
   CGroup: /system.slice/postgresql-14.service
           ├─2090 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─2091 postgres: logger 
           ├─2093 postgres: checkpointer 
           ├─2094 postgres: background writer 
           ├─2095 postgres: walwriter 
           ├─2096 postgres: autovacuum launcher 
           ├─2097 postgres: stats collector 
           └─2098 postgres: logical replication launcher 

May 29 12:35:13 oraclelinux8 systemd[1]: Starting PostgreSQL 14 database server...
May 29 12:35:13 oraclelinux8 postmaster[2090]: 2022-05-29 12:35:13.577 EDT [2090] LOG:  redirecting log output to logging collector process
May 29 12:35:13 oraclelinux8 postmaster[2090]: 2022-05-29 12:35:13.577 EDT [2090] HINT:  Future log output will appear in directory "log".
May 29 12:35:13 oraclelinux8 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=2090,fd=7))
LISTEN 0      128            [::1]:5432         [::]:*    users:(("postmaster",pid=2090,fd=6))

Also Read

How to Secure PostgreSQL Server in Linux

Step 4 – Set a Password Postgres User

By default, the password of the Postgres user is not set, so it is recommended to set a password for security reasons.

To set a password, log in to PostgreSQL with the following command:

su - postgres

Next, set a secure password with the following command:

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

Next, exit from the PostgreSQL shell using the following command:

exit

Step 5 – Change PostgreSQL Authentication Method

By default, PostgreSQL is configured to use the peer method to connect to PostgreSQL locally, but this method is not recommended for the production environment. It is recommended to change the authentication method from peer to scram-sha-256.

You can change it by editing the PostgreSQL main configuration file:

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

Find the following line:

local   all             all                                     peer

And replace it with the following line:

local   all             all                                     scram-sha-256

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

systemctl restart postgresql-14

Step 6 – Create a Database and User in PostgreSQL

First, log in to the PostgreSQL shell with the following command:

sudo -u postgres psql

You will get the following output:

psql (14.3)
Type "help" for help.

postgres=# 

Next, create a new PostgreSQL user named testuser using the following command:

CREATE USER testuser WITH CREATEDB CREATEROLE PASSWORD 'passoword';

To verify the PostgreSQL users, run:

\du

You will get the following output:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser     | Create role, Create DB                                     | {}

To create a new PostgreSQL database named testdb, run:

CREATE DATABASE testdb OWNER testuser;

To verify the PostgreSQL databases, run:

\l

You will get 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
 testdb   | testuser    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# 

Also Read

How to Backup and Restore a Database in PostgreSQL

Conclusion

In this post, we explained how to install PostgreSQL on Oracle Linux 8. You can now start working with PostgreSQL to get familiar with all its features. For security reasons, it is always recommended to install the latest version of PostgreSQL in the production environment. Give it a try on your dedicated server from Atlantic.Net!

Get A Free To Use Cloud VPS

Free Tier Includes:
G3.2GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

How to Set Up Kubernetes Cluster Using Minikube on Arch Linux
How to Install Jenkins on Arch Linux
Which Compliance Standards Require an IPS?
Install Netdata Monitoring Tool on Arch Linux
How to Install Vue.js on Arch Linux

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2008 Lookout Dr,

Garland, Texas 75044

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources