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

CentOS 6.x 64bit:

rpm -Uvh

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               md5
host    all             all             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 

Sample Output:

psql (9.4.0)
Type "help" for help.

To exit from PosgreSQL prompt, type \q.

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

su - postgres 

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';
postgres=# grant all privileges on database mypostgres to saurabh;

Hope this helps someone, starting afresh with PostgreSQL installation.

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