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
- Open a terminal Window
- Find version of PostgreSQL you want to install on RHEL 8:
sudo yum module list | grep postgresql - Install the default, PostgreSQL version 10 on RHEL 8:
sudo yum install @postgresql - 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
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
$ 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
$ createuser --interactive --pwprompt
Creating user with password on PostgreSQL
$ 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;
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
- Share on Twitter • Facebook • 2 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
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
85397187060099f9bbbd3c_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
85397187060099f9bbbd3c_000008
Thanks for additional input.