PostgreSQL

How to Install and configure PostgreSQL on Centos/RHEL

Post Views: 16,813
Category: Software Tags:

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, WordPress development, HTML5 development, Android development and Python development etc.

  1. Reading this article was an experience. I enjoyed all the information you provided and appreciated the work you did in getting it written. You really did a lot of research.

Comments are closed.