Install PostgreSQL 9.4 on Centos 6.x with PgAdmin3

Introduction

PostgreSQL is an Open Source, High Perfomance, object-relational database system (ORDBMS). It is feature rich, with many more functionalities than other systems such as MySQL.

There is a great in-depth article available here, which explains the many benefits of using PostgreSQL.

PostgreSQL-9.4 has just been released, and in this tutorial we will install it on a CentOS 6.5 installation with remote access for use with pgAdmin3.

Prerequisites

• CentOS 6.x

• Linux Shell knowledege

• Access as sudo user

Step 1 - Install PostgreSQL

Install Postgres from the official Postgres repository. For CentOS 6.x 32bit:

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

For CentOS 6.x 64bit:

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

Update your CentOS installation:

yum update

Install postgresql with the following command:

yum install postgresql94-server postgresql94-contrib

Initialise the postgresql database:

service postgresql-9.4 initdb

Start the postgresql service and set it to start automatically on every boot.

service postgresql-9.4 start
chkconfig postgresql-9.4 on

Step 2 - Set the “postgres” user password

To set the postgres password you will need to access the command prompt:

su - postgres

psql

Sample output:

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

To exit the shell type \q to quit. Optional : Postgres Adminpack

The Postgres adminpack provides a number of support functions which pgAdmin and other administration and management tools can use to provide additional functionality, such as remote management of server log files. Install the Postgres Adminpack:

postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION

Step 3 - Create a New User and Database

In this example we will create a new user 'webcorecloud' with the password 'webcore'

su - postgres

$ createuser webcorecloud

$ createdb pg_webcorecloud

$ psql

psql (9.4.0)
Type "help" for help.

postgres=# alter user webcorecloud with encrypted password 'webcore';
ALTER ROLE

postgres=# grant all privileges on database pg_webcorecloud to webcorecloud;
GRANT
postgres=#

Step 4 - MD5 Authenticatiion

To use an encrypted password for authentication we will use MD5 authentication.

Edit /var/lib/pgsql/9.4/data/pg_hba.conf with your favorite editor:

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

Modify the file to change it to md5:

[...]
# 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.0.1/24          md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
[...]

To apply the changes restart the postgresql service:

service postgresql-9.4 restart

Step 5 - Configure TCP/IP for Remote Use

For security reasons TCP/IP connections are disabled by default. We will need to edit the postgresql.conf file to allow users to connect remotely::

nano /var/lib/pgsql/9.4/data/postgresql.conf

[...]
#listen_addresses = 'localhost'
[...]
#port = 5432
[...]

listen_addresses = '*'
port = 5432

To apply the changes restart the postgresql service:

/etc/init.d/postgresql-9.4 restart

Step 6 - PgAdmin3

pgAdmin is a free software project released under the PostgreSQL/Artistic licence. You can download it from here.

Thats the end of the tutorial! Now that remote access is configured you can easily change, create detete databases and users using pgAdmin.