Tuesday, February 25, 2014

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: 

4 comments:

Kyle Dolan said...

yum.pgrpms.org is down, ive been trying to install yum install phpPgAdmin and it gets a couldnt resolve host error.

Nicholas A S said...

Kyle, it is unconfirmed but looks like they have move links to yum.postgreql.org only.

I have updated the post. Thank you.

The Geeks said...

Thanks for review, it was excellent and very informative.
thank you :)

The Geeks said...

Thanks for review, it was excellent and very informative.
thank you :)

Blog Archive