How to Setup PostgreSQL on Ubuntu/Dabian [Quick Tip]

Today , I am going to show you how to Install and setup PostgreSQL on Ubuntu/Dabian , and In future Post I will show you how you can use PostgreSQL with Ruby On Rails App . First Introduction and then Installation and Configuration . most of the configuration shown in this tutorial works on any PostgreSQL instance . I demonstrated all this in Ubuntu 14.10 and MacOSX 10.10 Yosemite .

postgresql-logo

Introduction ( From wikipedia )

PostgreSQL, often simply “Postgres”, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data, securely and supporting best practices, and retrieve it later, as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. Recent versions also provide replication of the database itself for availability and scalability.

Installation

Ubuntu/Dabian

sudo apt-get install postgresql postgresql-client libpq5 libpq-dev

MacOSX

brew install postgresql

or download http://www.postgresql.org/download/

Configuration

Add another User

$ sudo su postgres
postgres@ubuntu $ psql
postgres=#

postgres# CREATE ROLE john WITH PASSWORD 'my_password';

create role postgresql

Lets allow our new user john to login and grant him some permissions

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 | {}

Screenshot from 2014-12-10 23:26:52

now lets test to create a db with user rights of john

\q # to quit then

open another terminal and type

$ psql -U john -h 127.0.0.1 template1

# Password for user john:

It will ask you john's password , enter johns password to continue , it will drop you in with template1 database .

See full list of available options http://www.postgresql.org/docs/current/static/sql-createrole.html

Basic Tutorial

Let’s switch to postgreSQL user and launch psql prompt .

  • how to create database
# CREATE DATABASE mydatabase;
  • listing databases
# \list
  • listing users
# \du
  • how to get help
# \h
  • dropping database
# DROP database_name;

Read postgresql manual for all related info . you postgresql is ready to rock .

Useful Links
* http://www.postgresql.org/docs/9.4/static/

Thanks for reading
happy hacking

Tagged With : -
Add Comment