Install PostgreSQL on CentOS 6.x

While working on my last assignments I had to install/re-install PostgreSQL multiple times and initially didn’t find a consolidated tutorial as to how to do it step-by-step on CentOS 6.x.

So here is a step-by-step guide on “How to install PostgreSQL on CentOS 6.x”

Install PostgreSQL

You first need to add the PostgreSQL repository to yum and update it.

CentOS 6.x 32bit:

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-centos94-9.4-1.noarch.rpm

CentOS 6.x 64bit:

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Finally update the yum package.

yum update

Install & Initialize PostgreSQL:

yum install postgresql94-server postgresql94-contrib
service postgresql-9.4 initdb

Start PostgreSQL service and make it to start automatically on every reboot.

service postgresql-9.4 start
chkconfig postgresql-9.4 on

Adjust iptables for external access of PostGreSQL.

vi /etc/sysconfig/iptables

Add the following line:

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

Save and exit the file. Restart iptables service.

service iptables restart

Configure PostgreSQL-MD5 Authentication:

MD5 authentication requires the client to supply an MD5-encrypted password for authentication.

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

Add or Modify the lines as shown below

[...]
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24          md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
[...]

Restart PostgreSQL service:

service postgresql-9.4 restart

Access PostgreSQL command prompt

The default database name and database user are “postgres”. To access PostgreSQL related operations:

su - postgres 
bash#psql

Sample Output:

psql (9.4.0)
Type "help" for help.
postgres=#

To exit from PosgreSQL prompt, type \q.

Set “postgres” user password, login to PostgreSQL prompt,

su - postgres 
bash#psql

Set postgres password:

postgres=# \password postgres 
Enter new password: 
Enter it again: 
postgres=# \q

Create New User and Database

For example, let us create a new user called “saurabh” with password “agarwal”, and database called “mypostgres”.

Switch to postgres user:

su - postgres

Create user saurabh.

$ createuser saurabh

Create database:

$ createdb mypostgres

Login to the psql prompt and set password and grant access:

bash# psql
postgres=# alter user senthil with encrypted password 'agarwal';
ALTER ROLE
postgres=# grant all privileges on database mypostgres to saurabh;
GRANT
postgres=#

Hope this helps someone, starting afresh with PostgreSQL installation.

Let me know (in comments below) if something is missed.

Advertisements