Install postgresql on Rocky Linux
PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.while writing this article postgresql 13 is the latest version.PostgreSQL 13 contains many new features and enhancements.few features are listed below.
- Space savings & performance gains from de-duplication of B-tree index entries
- Improved performance for queries that use aggregates or partitioned tables
- Better query planning when using extended statistics
- Parallelized vacuuming of indexes
- Incremental sorting
In this Article, we will show you how to install postgresql on Rocky Linux 8.
Also Read -> How to Install and Configure Clamav in Linux
Step 1: Add poststgresql repo
you need to update your rocky linux 8 system before you start the installation of PostgreSQL using dnf command.
sudo dnf updateThe default version of PostgreSQL on Appstream repositories is PostgreSQL 10.
if you want to install latest version of postgresql 13 and it is available on the official PostgreSQL repository, so you need to add PostgreSQL repository to your Rocky Linux system.
sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpmStep 2: Install postgresql
once it is installed,you need to be disable built-in PostgreSQL module repo.
sudo dnf -qy module disable postgresqlNow install latest version of postresql.
sudo dnf install postgresql13-serveroutput:
Last metadata expiration check: 0:01:12 ago on Mon 27 Sep 2021 03:49:27 PM IST.
Dependencies resolved.
========================================================================================================================================================================
Package Architecture Version Repository Size
========================================================================================================================================================================
Installing:
postgresql13-server x86_64 13.4-1PGDG.rhel8 pgdg13 5.5 M
Installing dependencies:
postgresql13 x86_64 13.4-1PGDG.rhel8 pgdg13 1.5 M
postgresql13-libs x86_64 13.4-1PGDG.rhel8 pgdg13 414 k
Transaction Summary
========================================================================================================================================================================
Install 3 Packages
Total download size: 7.4 M
Installed size: 31 M
Is this ok [y/N]: y
Step 3: Initialize db and start service
once postgresql is installed,you must initialize the PostgreSQL configuration and then start and enable the PostgreSQL service.
sudo /usr/pgsql-13/bin/postgresql-13-setup initdboutput:
Initializing database ... OKnow you can start and enable the service to start on boot time and also check the status of service by the following commands.
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13
sudo systemctl status postgresql-13output:
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2021-09-27 16:04:06 IST; 13s ago
Docs: https://www.postgresql.org/docs/13/static/
Main PID: 29826 (postmaster)
Tasks: 8 (limit: 7596)
To verify the installed version, you can use 'psql -V' command.
$ psql -V
psql (PostgreSQL) 13.4Also Read -> Install PostgreSQL in XAMPP on Windows and integrate phpPgAdmin
Step 4: setup password for postgres
while postgresql installation, it will create a new system user and database user name as postgres.so you have to secure the postgres user with a password for security purpose for both system and user and database user.
sudo passwd postgresset the new password for the system user postgres.
Now login into the postgresql shell, and change the paaword for postgres user.
First,log in as a system user postgres as below.
su - postgresNow login to the shell using the psql
psqlthen run below query to change password for db postgres user.
ALTER USER postgres WITH PASSWORD 'strong_password';Now type \q to quit from postgres shell and type exit to logout from postgres user shell.
Step 5: create database
Log in to the PostgreSQL shell with the following command.
sudo su - postgresand then type psql
output:
[root@RockyLinux ~]# sudo su - postgres
Last login: Mon Sep 27 19:25:41 IST 2021 on pts/1
[postgres@RockyLinux ~]$ psql
psql (13.4)
Type "help" for help.
postgres=#now create user with privileges
postgres=# CREATE USER testuser WITH CREATEDB CREATEROLE PASSWORD 'strong_password';and then create database
postgres=# CREATE DATABASE testdb OWNER testuser;and verify the new user with \du and list the databases with \l
output:
postgres=# CREATE USER testuser WITH CREATEDB CREATEROLE PASSWORD 'strong_password';
CREATE ROLE
postgres=# CREATE DATABASE testdb OWNER testuser;
CREATE DATABASE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testuser | Create role, Create DB | {}
postgres=# \l
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)That's it. Now you have successfully installed postgresql 13 and also created the database on Rocky linux 8 system.
Also Read -> How to Install Docker and Docker Compose on Rocky Linux
