Technology

How to Install and configure PostgreSQL on Centos/RHEL

PostgreSQL

Install PostgreSQL and its-server packages

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

Configure Database Server

Initialize the cluster first with initdb command:

 

[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 the server to listen all addresses and Change the 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 its 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 the 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

eGrove Systems corporation is the leading web application development company in New Hampshire. Our Professionals are experts in Joomla development, Drupal development, Magento development, Worpdress development, HTML5 development, Android development and Python development etc.