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
- Password – Connect by providing a password. The passwords can be stored as
- 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
sudo su - postgres
To quite type
Create PostgreSQL Database and Role
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
Open the configuration file, to enable the remote access to your PostgreSQL server.
sudo nano /var/lib/pgsql/data/postgresql.conf
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 -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
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.