In this article, we will guide during installation and setup of PostgreSQL on Ubuntu/Debian systems. Most of the configuration given in this tutorial works on any PostgreSQL instance. I demonstrate the process of installation and configuration in Ubuntu 14.10 and MacOSX 10.10 Yosemite.
Introduction
PostgreSQL is the most advanced general purpose, object-relational database management system. It was developed based on POSTGRES 4.2 at Berkeley Computer Science Department, University of California.
PostgreSQL was designed to run on UNIX-like platforms. However, PostgreSQL was also designed to be portable. Therefore, it can run on different platforms such as Mac OS X, Solaris, and Windows, etc. It is a free and open source software. Its source code is available under PostgreSQL license and a liberal open source license. Moreover, you are free to use, modify and distribute PostgreSQL in any form you want.
PostgreSQL requires very minimum maintainence efforts due to its stability. Hence, if you develop applications based on PostgreSQL, the total cost of ownership is low as compared to other database management systems.
Installation
Let’s see how to install PostgreSQL on Ubuntu/Debian system.
For PostgreSQL installation on your Ubuntu/Debian systems run the below command on a terminal window:
sudo apt-get install postgresql postgresql-client libpq5 libpq-dev
You can install PostgreSQL on MacOSX using following command:
brew install postgresql
Configuration
Now ssh your Ubuntu/Debian server with
Following are the commands you have to follow to connect psql prompt.
$ sudo su postgres
postgres@ubuntu $ psql
postgres=#
For example, we are adding user John as a superuser. You have to run the command given below to add this new database user.
postgres# CREATE ROLE john WITH PASSWORD 'my_password';

Now we have to assign some permission to newly added user john.
To do this run command as follows:
postgres# ALTER ROLE john CREATEDB LOGIN SUPERUSER;
postgres# du
\du
will list all users
postgres# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
alok | Superuser, Create DB | {}
john | Superuser, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
rails | Superuser, Create DB | {}

Next, let’s try to create a db with user rights of john
\q # to quit then
open another terminal and type the following command:
$ psql -U john -h 127.0.0.1 template1
# Password for user john:
It will ask you password forjohn
user, enter johns password to continue, it will drop you in with template1 database .
See the full list of available options at http://www.postgresql.org/docs/current/static/sql-createrole.html
Basic Commands
Let’s switch to psql
- To create the database use the following command:
# CREATE DATABASE mydatabase;
- To list all the databases use the following command:
# \list
- You can list the all users using the following command
# \du
- To get help while working on PostgreSQL database prompt, use the
following command:
# \h
- You can drop or delete the database using the following command:
# DROP database_name;
Please read the PostgreSQL manual for all related information. However, your PostgreSQL installation is now ready to use.
Useful Links
* http://www.postgresql.org/docs/9.4/static/