Installing PostgreSQL9.2 on Redhat (RHEL5 & 6)

Installing PostgreSQL9.2 on Redhat (RHEL)

the same installation you can use for CentOS

1. Install PostgreSQL 9.2.1 Database Server on CentOS/Red Hat (RHEL)

1.1 Change root user or login as a root

su –

Example: su - root

1.2 Red Hat (RHEL)

Add exclude to /etc/yum/pluginconf.d/rhnplugin.conf file [main] section:

[main]

...

exclude=postgresql*

1.3 Install PostgreSQL 9.2 Repository

## Red Hat Enterprise Linux 6 - i386 - 32-bit ##

rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-redhat92-9.2-7.noarch.rpm

## Red Hat Enterprise Linux 6 - x86_64 - 64-bit ##

rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

 ## Red Hat Enterprise Linux 5 - i386 - 32-bit ##

rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-redhat92-9.2-7.noarch.rpm

 ## Red Hat Enterprise Linux 5 - x86_64 - 64-bit ##

rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

1.4 Install PostgreSQL 9.2 with YUM

yum install postgresql postgresql-server postgresql-contrib

2. Configure PostgreSQL 9.2

2.1 Set PostgreSQL Server to Listen Addresses and Set Port

Open /var/lib/pgsql/9.2/data/postgresql.conf file, and add/uncomment/modify following:

listen_addresses = '*'

port = 5432

If you want just localhost setup, then use following:

listen_addresses = 'localhost'

port = 5432

2.2 Set PostgreSQL Permissions

Modify PostgreSQL /var/lib/pgsql/9.2/data/pg_hba.conf (host-based authentication) file:

# Local networks

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

# Example

host       all            all            10.20.4.0/24       md5

# Example 2

host       test        testuser               127.0.0.1/32       md5

2.3 Start PostgreSQL Server and Autostart PostgreSQL on Boot

CentOS/Red Hat (RHEL)

## Start PostgreSQL 9.2 ##

service postgresql-9.2 start

## OR ##

/etc/init.d/postgresql-9.2 start

 ## Start PostgreSQL 9.2 on every boot ##

chkconfig --levels 235 postgresql-9.2 on

2.4 Create Test Database and Create New User

Change to postgres user

su - postgres

Create test database (as postgres user) type the following

createdb test

Login test database (as postgres user)

psql test

Create New “dbuser” Role with Superuser and Password

CREATE ROLE dbuser WITH SUPERUSER LOGIN PASSWORD '12345';

Test Connection from localhost (as Normal Linux User)

psql -h localhost -U dbuser test

3. Enable Remote Connections to PostgreSQL Server –> Open PostgreSQL Port (5432) on Iptables Firewall

3.1. Edit /etc/sysconfig/iptables file

vi /etc/sysconfig/iptables

3.2. Add following line before -A INPUT -j REJECT…

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

CentOS/Red Hat (RHEL)

service iptables restart

## OR ##

/etc/init.d/iptables restart

3.3 Test remote connection

psql -h dbserver_name_or_ip_address -U dbuser -W test

Example

Psql –h 192.168.1.1 –U dbuser –W test