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.
