Install PostgreSQL V9.x on Centos

1. Configure your YUM repository Since there is no v9.x in yum repository at the time of writing, you could not use yum to intall postgres without reconfiguration of yum repository. In order to do so: Edit /etc/yum.repos.d/CentOS-Base.repo, add the following lines to both [base] and [updates] sections exclude=postgresql* Then you need download and install PGDG RPM file for for your version of the CentOS from here. Here is the link for 64 Bit version of CentOS.

wget http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm
rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

2. Install postgreSQL After these modifications you could use yum to install postgreSQl versino 9.2. I installed both postgresql-server and postgresql-devel

yum install postgresql-server postgresql-devel

3. Post-installation configuration 3.1. Initialize The first command (only needed once) is to initialize the database in PGDATA:

/sbin/service postgres-9.2 initdb

3.2. Startup

/etc/init.d/postgresql-9.2 start

If you want PostgreSQL to start automatically when CentOS starts:

/sbin/chkconfig postgresql-9.2 on

3.3.Set PostgreSQL 9 Environment The deault home directory for the user postgres is at /var/lib/pgsql The bash_profile for the user postgres will look like this:

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.2/data
export PGDATA

This contains a path for the data directory, but no path for the executable/binary directory. To ammend this, add the path as below:

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.2/data
export PGDATA
PATH=$PATH:$HOME/bin:/usr/pgsql-9.2/bin
export PATH

Placing the binary directory in the path for postgres will allow you to invoke pg_ctl and other commands from the shell. 3.4. Add User The superuser postgres has no password set by default. To set the password, switch to postgres user:

# Linux environment
sudo su -
passwd postgres
# follow the screen to supply the new password for postgres

Connect to postgres database as user postgres.

psql postgres postgres
postgres=#
postgres=# create user myuser with password 'secret';

if you forget to assign a password to new use, you could do it by going to the database environment:

#postgresql environment
alter user user_name with password='new_password'

3.5. Create a database and give ownership to the new user:

postgres=# create database mytestdb owner=myuser;

Connect to the database as new user:

postgres=# \c mytestdb myuser
Password for user myuser:
You are now connected to database "mytestdb" as user "myuser".

3.6. Other configurations Modifications to the configure file to allow postgresql accept local/remote access Step # 1: Enable client authentication Edit the PostgreSQL configuration file /var/lib/pgsql/9.2/data/pg_hba.conf:

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 10.14.0.0/0 password
# IPv6 local connections:
host all all ::1/128 password

# Save and close the file. In order for the change to take effect, reload the pg_hba.conf file.

su - postgres
pg_ctl reload

Step # 2: Enable networking for PostgreSQL: Allow TCP/IP socket If you are using PostgreSQL version 8.x or newer use the following instructions: Open PostgreSQL configuration file /var/lib/pgsql/9.2/data/postgresql.conf and change

listen_addresses='localhost'

to

listen_addresses='*'

Step # 3 Restart PostgreSQL Server

/etc/init.d/postgresql-9.2 restart

Step # 4: Modify Iptables firewall rules Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables): put this line in the iptables,

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

But it must before this line,

-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

Restart firewall:

/etc/init.d/iptables restart

All set.