Install PostgreSQL 9.3 On CentOS 6.5/6.4

By
Roy s

PostgreSQL, often simply Postgres, is an open source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. It is released under the PostgreSQL License, a free/open source software license, similar to the MIT License. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of volunteers employed and supervised by companies such as Red Hat and EnterpriseDB.

It implements the majority of the SQL:2011 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible updateable views, data types, operators, index methods, functions, aggregates, procedural languages,and has a large number of extensions written by third parties. PostgreSQL is available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X.

Installation

Add  Last postgresql repo:

yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm

Now start install:

 yum install postgresql93-server postgresql93-contrib

Initialize the database:

service postgresql-9.3 initdb

Make it bootable on start:

chkconfig postgresql-9.3 on

Start the server, check if its running and listening:

# /etc/init.d/postgresql-9.3   start
# netstat  -an  |  grep   543
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN
tcp        0      0 ::1:5432                    :::*                        LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     26400  /tmp/.s.PGSQL.5432

Allow remote access from all:

vi /var/lib/pgsql/9.3/data/postgresql.conf

and add:

listen_addresses = '*'

Now,

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

and add:

host all all  0.0.0.0/0 md5

Restart Posgresql to reload the new config:

/etc/init.d/postgresql restart

Allow   Postgresql in The  firewall

vi /etc/sysconfig/iptables

Add line:

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

and now restart iptables

service iptables restart

 Create user and database in Postgresql

 su  - postgresql

Create user user1.

$ createuser user1

Create database:

$ createdb db1

Set password and Grant access to the database db1 for user1:

$ psql
psql (9.3.2)
Type "help" for help.

postgres=# alter user user1 with encrypted password 'user1';
ALTER ROLE

postgres=# grant all privileges on database db1 to user1;
GRANT
postgres=#

Enjoy!