Blog

03/06/2019

How to install and setup PostgreSQL on RHEL 8



How do I install PostgreSQL relational database management on RHEL 8 using the command line? How do I install and setup PostgreSQL on RHEL 8 server using application streams?

Introduction: PostgreSQL is a free and open source ORDBMS ( object-relational database management system). It is the world’s most advanced open source database. This page shows how to install PostgreSQL on RHEL 8 and configure other aspects of the database server.

How to install and setup PostgreSQL on RHEL 8

kbd { display: inline-block; margin: 0 .1em; padding: .1em .6em; font-family: Arial,”Helvetica Neue”,Helvetica,sans-serif; font-size: 11px; line-height: 1.4; color: #242729; text-shadow: 0 1px 0 #FFF; background-color: #e1e3e5; border: 1px solid #adb3b9; border-radius: 3px; box-shadow: 0 1px 0 rgba(12,13,14,0.2), 0 0 0 2px #FFF inset; white-space: nowrap;}
  1. Open a terminal Window
  2. Find version of PostgreSQL you want to install on RHEL 8:
    sudo yum module list | grep postgresql
  3. Install the default, PostgreSQL version 10 on RHEL 8:
    sudo yum install @postgresql
  4. Next initialize new PostgreSQL database cluster in RHEL 8:
    sudo postgresql-setup --initdb

Let us see all commands in details.

How to see a list of all available PostgreSQL application streams on RHEL 8

Run the following yum command
$ sudo yum module list | grep postgresql

How to install and use PostgreSQL on RHEL 8

In the previous step, we saw that RHEL 8 shipped with two Applications streams for PostgreSQL server. To install PostgreSQL 9.6, run:
$ sudo yum install @postgresql:9.6
The default is PostgreSQL 10, so running the following yum command installs the latest stable version:
$ sudo yum install @postgresql

Installing and use PostgreSQL on RHEL 8 using application streams (click to enlarge)

How to initialize new PostgreSQL database cluster

The first action you perform after PostgreSQL server installation is to run the following command:
$ sudo postgresql-setup --initdb

Creating a new PostgreSQL database cluster on RHEL 8

How to setup a password for postgres account

Run the following passwd command to setup a password:
$ sudo passwd postgres
Sample outputs:

Changing password for user postgres.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

How do I start/stop/restart the PostgreSQL server

You need to use the systemctl command:
sudo systemctl start postgresql ## <-- start the server ##
sudo systemctl stop postgresql ## <-- stop the server ##
sudo systemctl restart postgresql ## <-- resstart the server ##
sudo systemctl status postgresql ## <-- get status of the server ##

How to enable the PostgreSQL server at boot time on RHEL 8

Again use the systemctl command as follows:
$ sudo systemctl enable postgresql

Start and enable the PostgreSQL server

How do I log in using psql?

You need to use the psql command. It is a terminal-based front-end to PostgreSQL server. It enables you to type in queries interactively. The installation script created a user named postgres. That is the default account for default database and roles. Let us log in as postgres using the sudo command:
$ sudo -i -u postgres
Run it:
$ psql

Validating installation of PostgreSQL database server

At postgres=# prompt type q to quit from the command-line interface to PostgreSQL server. Did you notice you logged into PostgreSQL without any password? Let us fix this by creating HBA config:
$ sudo vi /var/lib/pgsql/data/pg_hba.conf
Find lines that read as follows:

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

# IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident

Replace ident with scram-sha-256:

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

# IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256

Restart the postgresql server:
$ sudo systemctl restart postgresql

How to create a new PostgreSQL user account

First create a Linux user account named tom:
$ sudo useradd tom
$ sudo passwd tom

Sample outputs:

Changing password for user tom.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

The postgres account is nothing but an administrative user for PostgreSQL server. So log in as postgres:
$ sudo -i -u postgres
Run the following createuser command to creates a new PostgreSQL role for tom Linux user:
$ createuser --interactive

Define a new PostgreSQL user account named tom

Create a new user account with password for new role:
$ createuser --interactive --pwprompt
Creating user with password on PostgreSQL

Finally create a new database named jerry for tom user by log in as postgres admin user:
$ sudo -i -u postgres
$ createdb -O tom jerry

Related: PostgreSQL add or create a user account and grant permission for database

How do I connect to jerry database with tom user?

Simply run the following commands:
$ sudo -i -u tom
$ psql -d jerry -U tom

Connecting to PostgreSQL server with the new user named tom for jerry database

How to create a new table

Log in:
$ psql -d jerry -U tom
Type the following SQL to create demo table:

CREATE TABLE demo(
 id serial PRIMARY KEY,
 email VARCHAR (100) UNIQUE NOT NULL,
 name  VARCHAR (50) UNIQUE NOT NULL
);

CREATE TABLE demo( id serial PRIMARY KEY, email VARCHAR (100) UNIQUE NOT NULL, name VARCHAR (50) UNIQUE NOT NULL );

See info about the demo table:
d
OR
dt

Let us add some data to our table, run the following SQL:

INSERT INTO demo (id, email, name) VALUES (1, 'webmaster@sxi.io', 'SXI ADMIN');
INSERT INTO demo (id, email, name) VALUES (2, 'foo@bar.com', 'Foo Bar');
INSERT INTO demo (id, email, name) VALUES (3, 'roja@nixcraft.com', 'Roja T');
INSERT INTO demo (id, email, name) VALUES (4, 'marlena@gmail.net.in', 'Marlena John');

INSERT INTO demo (id, email, name) VALUES (1, ‘webmaster@sxi.io’, ‘SXI ADMIN’); INSERT INTO demo (id, email, name) VALUES (2, ‘foo@bar.com’, ‘Foo Bar’); INSERT INTO demo (id, email, name) VALUES (3, ‘roja@nixcraft.com’, ‘Roja T’); INSERT INTO demo (id, email, name) VALUES (4, ‘marlena@gmail.net.in’, ‘Marlena John’);

View data:

SELECT * FROM demo;

SELECT * FROM demo;

Delete some data:

DELETE FROM demo WHERE id = 4;

DELETE FROM demo WHERE id = 4;

Update data:

UPDATE demo SET email = 'foo@gmail.com' WHERE id = 2;

UPDATE demo SET email = ‘foo@gmail.com’ WHERE id = 2;

How to add, query, delete and Update data in a table (click to enlarge)

Conclusion

Congratulations. You successfully set up PostgreSQL server on RHEL 8 server. You also learned how to create users, database and tables. For more info see the official PostgreSQL docs here.

(adsbygoogle = window.adsbygoogle || []).push({});

Posted by: SXI ADMIN

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

14/08/2019

How to KVM, QEMU start or stop virtual machine from command line (CLI)

KVM or Kernel Based Virtual Machine is a popular virtualization technology. It allows you to run virtual guest machines over a host machine. To start...
14/08/2019

How to Docker backup Saving and restoring your volumes

Running a Docker volume backup First, we spin up a temporary container, and we mount the backup folder and the target Docker volume to this container....
12/08/2019

How to Start and Enable Firewalld on CentOS 7

In this article, we discuss how to start and enable firewalld. It is highly recommended that you have a firewall protecting your server.Pre-Flight CheckThese...