
PostgreSQL or Postgres is an open source relational database management system. It includes many advanced features which allows you to create complex web applications. It is a popular and has many advanced features like reliable transactions and concurrency without read locks. This tutorial explains how to install PostgreSQL database server on CentOS 8.
Before start installing ensure that you are login as a root or user with sudo privileges.
Install PostgreSQL on CentOS 8
Currently, in from the CentOS repositories, there are two versions 10.0 and 9.6 of PostgreSQL server are available for installation. Run the below command, to get list of available PostgreSQL module streams:
dnf module list postgresql
This will show output as below. It is showing two streams of postgresql module. Each stream includes two profiles server and client. The [d]
next to stream shows the default stream of PostgreSQL. To install default stream version 10.0, execute below command:
sudo dnf install @postgresql:10
To install a specific PostgreSQL server version 9.6, type:
sudo dnf install @postgresql:9.6
You should install contrib package which will gives several additional features for the PostgreSQL database:
sudo dnf install postgresql-contrib
On the completion of the installation, you should initialize the PostgreSQL database using below command:
sudo postgresql-setup initdb
Initializing database ... OK
If you would like to start PostgreSQL service on boot and then enable it:
sudo systemctl enable --now postgresql
Now verify the installation by connecting the to the PostgreSQL database server:
sudo -u postgres psql -c "SELECT version();"
It will display output as below:
PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
PostgreSQL Roles and Authentication Methods
PostgreSQL roles give the permission to access database. A role can represent a database user or a group of database users.
PostgreSQL provides multiple authentication methods. The most commonly-used methods are:
- Trust – This role can connect without a password and the conditions defined in the
pg_hba.conf
are met. - Password – Connect by providing a password. The passwords can be stored as
scram-sha-256
,md5
, andpassword
(clear-text). - Ident – Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
- Peer – Same as Ident, but it is supported on local connections only.
The configuration file named pg_hba.conf
holds the PostgreSQL client authentication. By default, for local connections, PostgreSQL is set to use the peer authentication method.
Once you install the PostgreSQL server it will automatically create a postgres user. This user is the superuser for the PostgreSQL instance. We can consider same as root user of MySql server.
You can login as a postgres
user, you should first switch to the user or you can use direct sudo
command. Then access the PostgreSQL prompt using the psql
utility:
sudo su - postgres
psql
To quite type q
.
Create PostgreSQL Database and Role
The superuser
and users with CREATEROLE
privileges can create new roles.
Perform the below steps to create role and database:
At first, connect to the PostgreSQL shell:
sudo -u postgres psql
Create new role sweta
using below command:
create role sweta;
To create new database type:
create database tecnstuff;
Now you should grant the permission on the database by executing below command:
grant all privileges on database tecnstuff to sweta;
Enable remote access to PostgreSQL server
When you are using PostgreSQL server in network you should enable remote access. By default, the PostgreSQL server listens only on the local interface 127.0.0.1
.
Open the configuration file, to enable the remote access to your PostgreSQL server.
sudo nano /var/lib/pgsql/data/postgresql.conf
Find CONNECTIONS AND AUTHENTICATION
section and add or edit below line:
listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service using below command:
sudo systemctl restart postgresql
You can test the changes using ss
utility:
ss -nlt | grep 5432
It will show output as given below:
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
At last, edit the pg_hba.conf
file to configure the server to accept remote connections.
# TYPE DATABASE USER ADDRESS METHOD
# The user sweta can access all databases from all locations using an md5 password
host all sweta 0.0.0.0/0 md5
# The user sweta can access only the janedb database from all locations using an md5 password
host janedb sweta 0.0.0.0/0 md5
# The user sweta can access all databases from a trusted location (192.168.92.125) without a password
host all sweta 192.168.92.125 trust
Conclusion
You have successfully learned how to install PostgreSQL versions: 9.6 and 10.0 on CentOS 8 system. You can get more details about PostgreSQL at PostgreSQL Documentation.
Leave a Reply