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 (Red Hat Enterprise Linux) 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

  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

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

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
);

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@cyberciti.biz', 'Vivek Gite');
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;

Delete some data:

DELETE FROM demo WHERE id = 4;

Update data:

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.


🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 2 comments so far... add one


CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
2 comments… add one
  • LAL PRASAD Aug 25, 2020 @ 17:45

    The below steps to be followed in case of issue to connect DB from PGAdmin.
    1) Allow TC/IP connection: config file: pg_hba.conf
    $ sudo vi /var/lib/pgsql/data/pg_hba.conf

    105186551860cd00f64f9c0_000007

    2) Allow port 5432 and listener_addresses all: Config file: postgresql.conf : uncomment the below lines:
    $ sudo vi /var/lib/pgsql/data/postgresql.conf

    105186551860cd00f64f9c0_000008

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum