Installing PostgreSQL on Centos 6

PostgreSQL being an OSS licensed database is very different compared to MySQL. PostgreSQL (or Postges) maintains a repository for Linux systems like Centos to install the database. This repository is known as pgdg and is maintained at yum.postgresql.org or yum.pgrpms.org

Installation of the Postgres comes with psql, this is a CLI to manage Postgres.

Installation of PostgreSQL 9.3 on Centos 6.4 is as follows;

Step 1:
Open a CLI as root user and retrieve the pgdg repo RPM, then install it. This will allow the Centos to find for software from the pgdg.

wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

wget http://yum.pgrpms.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

Step 2:
Install the client and server.

yum groupinstall "PostgreSQL Database Server 9.3 PGDG"

yum install postgresql93 postgresql93-server

(-)

Step 3:
Initialise database and tables, and start. This creates the default user postgres.

service postgresql-9.3 initdb

service postgresql-9.3 start

You can now login with the default user to the Postgres terminal as follows;

[root]# su postgres

bash$ cd
bash$ psql -d postgres

If you get the error "psql: could not connect to server: No such file or directory...." Ensure the postgresql server service is running.

Step 4:

As the postgres user, create an administrative user, then log out from the psql (use \q), then the shell (Control-d).

CREATE ROLE dbmsuser LOGIN PASSWORD 'password' SUPERUSER;

As reference, here is summary of the syntax:
CREATE ROLE name [ [WITH] option [ . . . ] ]

 where option includes:
SUPERUSER | NOSUPERUSER
LOGIN | NOLOGIN

Step 5:

Edit Postgres configuration file;

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

Uncomment the line and save.

#listen_addresses = ‘localhost’ and change it to listen_addresses = ‘*’

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

Add a line at bottom of the file to allow non-local connections, e.g.

host    all             dbmsuser        10.0.0.1/32            md5

Post installation

chkconfig postgresql-9.3 on

Ref: 

How to Install PostgreSQL 9.2 on CentOS 6.3/RedHat EL6/Fedora