Setup PostgreSQL On Ubuntu/Debian

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 sudo user. For example, I have “postgres“ user created with sudo previledges here.

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 forjohnuser, 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 postgreSQL user and launch psql prompt as shown in the configuration given above.

• 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/