Setup postgresql on ubuntu

11 Oct 2013

Installation

sudo apt-get install postgresql-9.2 postgresql-contrib-9.2 
postgresql-server-9.2 postgresql-server-dev-9.2

Unlike MySql, In postgres we can have users coupled with system users. So whenever you try psql command the default user will be your current loggedin system user. While installation it creates a user with username postgres in the system to access db.

login into default user

sudo -u postgres psql

After login as default user you need to create a new user with username same as your system username.

create a role

CREATE USER username WITH PASSWORD 'password' CREATEDB;

If you forgot to give CREATEDB permission while creating user you can alter it using the sql below,

ALTER USER revath CREATEDB;

After creating new role/user, logout from the postgres user by typing \q in the psql prompt.

Now login into the psql by selecting the default database.

psql -d postgres

Now you login with the default user which have the same username as your system user.

After login you can create a new DB.

CREATE DATABASE db_name

Using postgres is a bit different from using Mysql,

`SHOW DATABASES` : `\l`  
`SHOW TABLES`    : `\d`  
`DESC table`     : `\d table`  
`exit`           : `\q`  
`help`           : `\h` or `\?`  
If you find my work helpful, You can buy me a coffee.