Postgresql Environment Setup

Postgresql:

    PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

Installation and configuration on Ubuntu  14.04

    Step 1: $ sudo apt-get update

    Step 2: $ sudo apt-get install postgresql

To connect the default Database:

    Step 3: sudo -u postgres psql template1  

To create New user :

    template1>create user <user_name> with encrypted password '<pwd>';

    Ex: create User Varun with encrypted password 'varun'

To grant create database permission to new user:

    template1>ALTER USER <user_name> CREATEDB

    Ex : alter user Varun createdb

Add new user in pg_hba.conf file with MD5 authentication

    Edit the file  

    $sudo gedit /<pgsql dir>/main/pg_hba.conf

   Local All postgres md5

   Local all postgres,Varun md5

   Save the file and restart postgresql service

To Enable other computers to connect to Postgresql:

    Edit the  

    $sudo gedit /etc/<pgsqldir>/postgresql.conf 

in change to

listen_address=”*”    

To restart postgresql service:

   $sudo service postgresql restart

To check Postgresql service status

   $sudo service postgresql status

To connect postgresql with created New user:

   $psql -U Varun -d template1

     password: 

    template1>

Now you can successfully logged in db with new user.

To get list of databases available: 

    template1> \l 

To describe about your table :

    template1> \du

To create password in another way :

     template1> \password

To quit from Postgresql service : 

     template1> \q

To connect from other computers: 

      $sudo psql - U Varun -h 192.168.0.10 -d test