PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
List the PostgreSQL Module
List the PostgreSQL module by using the following command:
dnf module list postgresql
Output:
[root@vps ~]# dnf module list postgresql
Last metadata expiration check: 0:02:54 ago on Fri 19 Feb 2021 01:38:26 PM EST.
AlmaLinux 8.3 - AppStream
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
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
Enable the PostgreSQL Module
Enable the PostgreSQL module of version 12 by using the following command:
dnf module enable postgresql:12
Output:
[root@vps ~]# dnf module enable postgresql:12
Last metadata expiration check: 0:03:07 ago on Fri 19 Feb 2021 01:38:26 PM EST.
Dependencies resolved.
================================================================================
Package Architecture Version Repository Size
================================================================================
Enabling module streams:
postgresql 12
Transaction Summary
================================================================================
Is this ok [y/N]: y
Complete!
Install the PostgreSQL Server
After the version 12 module has been enabled, install the postgresql-server
by using the following command:
dnf install postgresql-server
Output:
[root@vps ~]# dnf install postgresql-server
Last metadata expiration check: 0:03:23 ago on Fri 19 Feb 2021 01:38:26 PM EST.
Dependencies resolved.
================================================================================
Package Arch Version Repo Size
================================================================================
Installing:
postgresql-server x86_64 12.5-1.module_el8.3.0+2046+664538f4 appstream 5.6 M
Installing dependencies:
libicu x86_64 60.3-2.el8_1 baseos 8.8 M
libpq x86_64 12.5-1.el8_3 appstream 194 k
postgresql x86_64 12.5-1.module_el8.3.0+2046+664538f4 appstream 1.5 M
Creating a New PostgreSQL Database Cluster
Initialize a database storage area on disk by using the following command:
postgresql-setup --initdb
Output:
[root@vps ~]# postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Start the PostgreSQL service:
systemctl start postgresql
Enable the PostgreSQL service:
systemctl enable postgresql
Output:
[root@vps ~]# systemctl enable postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
Verify the PostgreSQL service:
systemctl status postgresql
Output:
[root@vps ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor >
Active: active (running) since Fri 2021-02-19 13:44:05 EST; 58min ago
Main PID: 57294 (postmaster)
Tasks: 8 (limit: 23721)
Memory: 45.7M
CGroup: /system.slice/postgresql.service
├─57294 /usr/bin/postmaster -D /var/lib/pgsql/data
├─57295 postgres: logger
PostgreSQL Roles
We’ll switch to postgres account for this.
sudo -i -u postgres
you can access a postgresql prompt using the psql utility.
psql
Output:
[postgres@vps ~]$ psql
psql (12.5)
Type "help" for help.
postgres=#
To exit out of the postgresql shell type.
\q
Change to your original account by using the following command:
exit
To create postgresql Role:
createuser --interactive
Output:
[postgres@vps ~]$ createuser --interactive
Enter name of role to add: jones
Shall the new role be a superuser? (y/n) y
PostgreSQL Database
Create a Database by using the following command:
createdb db_name
Enter the Database name something like:
[postgres@vps ~]$ createdb jones
Opening a Postgres Prompt with the New Role
Add new user by using the following command:
sudo adduser jones
To switch over and connect to the database:
sudo -i -u jones
psql
Output:
[jones@vps ~]$ psql
psql (12.5)
Type "help" for help.
Once you logged in as jones and check your current connection information.
\conninfo
Output:
jones=# \conninfo
You are connected to database "jones" as user "jones" via socket in "/var/run/postgresql" at port "5432".