How to Install Postgresql server in Centos 8

Linux

Install postgresql 12 on Centos8/RHEL8

Postgresql is an object-relational database management system based on postgres 4.2. Postgresql 12 is available for Production use by Developers and Database Administrators.

 Step 1: download postgresql from official postgresql repository

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

It can be added to your system by running the command below

[root@centos ~]# yum localinstall pgdg-redhat-repo-latest.noarch.rpm

Step 2: Install postgresql 12

[root@centos ~]# yum --disablerepo=AppStream install postgresql12-server postgresql12

Also Read -> Install PostgreSQL 11 on centos 7

Step 3: Initialize database

After installation, database initialization is required before service can be started.

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

sample output:

Initializing database ... OK

Step 4: start postgresql service

Now we can start,enable and check service status by using below commands.

[root@centos ~]# systemctl start postgresql-12
[root@centos ~]# systemctl enable postgresql-12
[root@centos ~]# systemctl status postgresql-12

We hope you are following "How to Install Postgresql server in Centos 8" step by step carefully. The remaining steps will help you to finish the upgrade process..

Step 5: configure database server

once it is installed then enable PostgreSQL server to listen on all available networks.By default, the PostgreSQL server listens only on the local interface.

[root@centos ~]# vi /var/lib/pgsql/12/data/postgresql.conf

change the line listen_addresses as below

listen_addresses = '*'

Also set PostgreSQL to accept remote connections.

[root@centos ~]# vi /var/lib/pgsql/12/data/pg_hba.conf

add below line at the end of the file

host all all 0.0.0.0/0 md5

note: For security you can allow from secific ip or network instead of all networks (ex: host all all 192.168.0.1/24 md5).

Also Read -> How to Install Postgresql on Ubuntu 20 04

Step 5: Restart postgresql service

[root@centos ~]# systemctl restart postgresql-12
[root@centos ~]# systemctl status postgresql-12

Step 6: Allow from firewall

If you have a running firewall service and remote clients should connect to your database server, so you can allow PostgreSQL service in firewall as below

[root@centos ~]# firewall-cmd --add-service=postgresql --permanent
[root@centos ~]# firewall-cmd --reload

Step 7: connect postgres database

After installing PostgreSQL database on your centos 8 system the installer will automatically create default user called as postgres.default password for this user is empty.To access the PostgreSQL database we need to execute command 'su' as root user to switch to system postgres user. Then type psql to login to the database.

sample output

[root@centos ~]# su - postgres
[postgres@centos ~]$ psql
psql (12.1)
Type "help" for help.
postgres=#

change postgres password as below

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
[postgres@centos ~]$ exit
logout
[root@centos ~]#

Also Read -> Install PostgreSQL in XAMPP on Windows and integrate phpPgAdmin

Step 8: create database 

First, we will connect to the PostgreSQL shell as mentioned in above step 7

Then create database and crate role and grant privileges to user on that database as below.

postgres=# create database testdb;
CREATE DATABASE
postgres=# create role test;
CREATE ROLE
postgres=# grant all privileges on database testdb to test;
GRANT
postgres=# ALTER ROLE "test" WITH LOGIN;
ALTER ROLE
postgres=# \q
[postgres@centos ~]$ exit
logout
[root@centos ~]#

That's it. Now we have successfully installed PostgreSQL 12 on CentOS 8/RHEL 8 system.