How to Install and configure PostgreSQL on Centos/RHEL

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

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.

View the original article here