How to Install and Configure PostgreSQL 11 on CentOS /RHEL/ Ubuntu

Install PostgreSQL 11 on CentOS /RHEL/ Ubuntu.

PostgreSQL Server is a free and open source RDBMS with emphasizes on extensibility and technical compliance. It is efficient in handling a variety of workloads, from a single computer to Data warehouses supporting multiple users. PostgreSQL has proven to be highly scalable both in the volume of data it can manage and the number of concurrent users it can support. These points will be helpful for you to Install PostgreSQL 11 on CentOS /RHEL/ Ubuntu.

Salient enhancements in PostgreSQL 11

• Increased Robustness & Performance for Partitioning
• Embedded Transactions Supported in Stored Procedures
• Enhanced Capabilities for Query Parallelism
• Just-in-Time Compilation for Expressions
• General User Experience Improvements

Read How to Install PostgreSQL 11 on CentOS /RHEL/ Ubuntu?

Install PostgreSQL and PostgreSQL-server packages

[root@egrovechn]# yum install postgresql postgresql-server postgresql-contrib

Configure PostgreSQL Database Server

Initialize the cluster first with initdb command:

[root@egrovechn]# service postgresql initdb

(or)

[root@egrovechn]# /etc/init.d/postgresql initdb

Edit /var/lib/pgsql/data/postgresql.conf file:

[root@egrovechn]# vi /var/lib/pgsql/data/postgresql.conf

Set PostgreSQL server to listen all addresses and Change PostgreSQL port (default is 5432). Add/Uncomment/Edit following lines:

listen_addresses = ‘*’

port = 5432

Edit /var/lib/pgsql/data/pg_hba.conf file:

[root@egrovechn]# vi /var/lib/pgsql/data/pg_hba.conf

Add (example) your local network with md5 passwords:

# Local networks

host       all           all           xx.xx.xx.xx/xx   md5

# Example

host       all           all           10.20.4.0/24       md5

# All Network

host       all           all           0.0.0.0/0 trust

Start/Restart PostgreSQL Server:

[root@egrovechn]# service postgresql start

(or)

[root@egrovechn]# /etc/init.d/postgresql start

Change to postgres user:

[root@egrovechn]# su postgres

Create test database (as postgres user):

createdb test

Login test database (as postgres user):

psql test

Create new “pguser” Role with Superuser and Password:

CREATE ROLE pguser WITH SUPERUSER LOGIN PASSWORD ‘password’;

Open PostgreSQL Port (5432) on Iptables Firewall (as root user again)

Edit /etc/sysconfig/iptables file:

[root@egrovechn]# vi /etc/sysconfig/iptables

Add following line before COMMIT:

-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 5432 -j ACCEPT

-A INPUT -p tcp -m tcp –sport 1024:65535 –dport 5432 -j ACCEPT

-A OUTPUT -p tcp -m tcp –sport 1024:65535 –dport 5432 -j ACCEPT

Restart Iptables Firewall:

[root@egrovechn]# service iptables restart

(or)

[root@egrovechn]# /etc/init.d/iptables restart

Test remote connection:

[root@egrovechn]# psql -h dbserver -U testuser test

Postgresql Installation In UBUNTU 18.04

Installation Command

sudo apt update
sudo apt install postgresql postgresql-contrib

Switching Over to the postgres Account

sudo -i -u postgres
psql

Change password using below command

\password

exit command

\q

Login Using Password:

Below path change the database administartive login type changed from peer to md5
vi /etc/postgresql/10/main/pg_hba.conf

local all postgres peer

this line should be changed to below one

local all postgres md5

Service Restart

sudo systemctl restart postgresql

Verify Login using password
sudo -i -u postgres
psql

Postgresql Installation In CENTOS 7

Installation

sudo yum install postgresql-server postgresql-contrib

Create a new PostgreSQL database cluster:

sudo postgresql-setup initdb

Switching Over to the postgres Account
sudo -i -u postgres
psql

Change password using below command

\password

exit command

\q

Open the HBA configuration

sudo vi /var/lib/pgsql/data/pg_hba.conf

host all all 127.0.0.1/32 ident
host all all ::1/128 ident

Replace ident with md5

Now start and enable PostgreSQL:

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl restart postgresql

Verify Login using password

sudo -i -u postgres
psql

eGrove Systems Corporation is the leading web application development company in NJ. Our Professionals are experts in Joomla development, Drupal development, Magento development, WordPress development, HTML5 development, Android development and Python development etc.