How to install PostgreSQL on Centos 7

What is PostgreSQL

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

Install PostgreSQL on Centos 7Initializing Database

To install the PostgreSQL server along with the PostgreSQL contrib package which provides several additional features for the PostgreSQL database simply type:

[root@PostgreSQL ~]# yum install postgresql-server postgresql-contrib -y

Initializing Database

Start the postgreSQL by help of below commands:

[root@PostgreSQL ~]# postgresql-setup initdb

Starting PostgreSQL

[root@PostgreSQL ~]# systemctl start postgresql

[root@PostgreSQL ~]# systemctl enable postgresql

Verifying PostgreSQL Installation

[root@PostgreSQL ~]# sudo -u postgres psql -c "SELECT version();"

Install PostgreSQL from the PostgreSQL repositories 

For installation latest repository you can install it from PostgreSQL sites. Here you can select the version of the PostgreSQL and Server where you want to install it. 

# Install the repository RPM:

[root@PostgreSQL ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:

[root@PostgreSQL ~]# yum install postgresql12-server -y

# Optionally initialize the database and enable automatic start:

[root@PostgreSQL ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb

[root@PostgreSQL ~]# systemctl enable postgresql-12

[root@PostgreSQL ~]# systemctl start postgresql-12.service

After this postgresql could not be run and show you error. It will show you given hint " Is another postmaster already running on port 5432? If not, wait a...and retry" that's mean 5432 port already using somewhere so you can kill it.

Check the port by help of given command:

[root@PostgreSQL ~]# netstat -tulpn

If 5436 port is already using then you can kill them. In below screen you can check the step. 

Verifying PostgreSQL Installation

[root@PostgreSQL ~]# sudo -u postgres /usr/pgsql-12/bin/psql -c "SELECT version();"

PostgreSQL Roles and Authentication Methods

Database access permissions within PostgreSQL are handled with the concept of roles. A role can represent a database user or a group of database users.

PostgreSQL supports multiple authentication methods. The most commonly-used methods are:

• Trust - With this method, the role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
• Password - A role can connect by providing a password. The passwords can be stored as scram-sha-256 md5 and password (clear-text).
• Ident - This method is 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 only supported on local connections.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. By default, for local connections, PostgreSQL is set to use the peer authentication method.

The postgres user is created automatically when you install PostgreSQL. This user is the superuser for the PostgreSQL instance and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as the postgres user first you need to switch to the user postgres and then access PostgreSQL prompt using the psql utility:

[root@PostgreSQL ~]# sudo su - postgres

-bash-4.2$ psql

postgres=#

or 

[root@PostgreSQL ~]# sudo -u postgres psql

Change the password for the default PostgreSQL user.

[root@PostgreSQL ~]# sudo passwd postgres

Switch to the postgres user. 

su - postgres 

Create a new user by typing:

create user sonar

Switch to the PostgreSQL shell.

psql

Set a password for the newly created user for SonarQube database.

ALTER USER sonar WITH ENCRYPTED password 'StrongPassword';

Create a new database for PostgreSQL database by running:

CREATE DATABASE sonar OWNER sonar;

Exit from the psql shell: 

 

 

[root@PostgreSQL logs]# vi /var/lib/pgsql/12/data/pg_hba.conf

 

Find the following lines and change peer to trust and idnet to md5 

          

 

 

Once updated, the configuration should look like the one shown below.

Creating PostgreSQL Role and Database

Only superusers and roles with CREATEROLE privilege can create new roles.

In the following example, we will create a new role named sonar a database named sonarqube and grant privileges on the database.

[root@PostgreSQL ~]# sudo -u postgres psql

postgres=# create role sonar;

postgres=# create database sonarqube;

postgres=# grant all privileges on database sonarqube to sonar;

postgres=# ALTER USER sonar WITH ENCRYPTED password '*******';

Enable remote access to PostgreSQL server

By default, the PostgreSQL server listens only on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf (/var/lib/pgsql/data/postgresql.conf) and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section.

[root@PostgreSQL ~]# systemctl restart postgresql-12