PostgreSQL installation in Ubuntu 12.04


PostgreSQL is a powerful and reliable object-relational database system. It’s a great alternative for MySQL. It is as easy to set up, performs better and offers far more features.

1. Make sure you already have install python-software-properties

$sudo apt-get install python-software-properties

2.Add PPA repository to my Ubuntu.

$sudo add-apt-repository ppa:pitti/postgresql

3. After adding PPA, update your system apt:

$sudo apt-get update

4.Finally install postgresql-9.1:

$sudo apt-get install postgresql

if you having any error, make sure you already install libpq-dev.The libpq-dev package is for compiling wrappers/clients against libpq.

$sudo apt-get install postgresql-9.1 libpq-dev

5.Now check it out installation is successful or…..

$ locate postgresql

6. If done!!!

Check the install version.

$psql -V

7.Now let’s take look to postgres console

$su postgres

7(a) Setup Root User ‘posrgres’

$sudo passwd postgres

give the postgres user a (unix) password,Now we can switch to the user postgres using command

$ su postgres

8. That’s it for postgers installation. If you want to access postgres from remote, Mean localhost. You need to perform some addition setting to postgres conf file.

First we edit the file postgresql.conf:

$ cd /etc/postgresql/9.1/main

List all file in main folder

$ ls -la

total 80
drwxr-xr-x 2 postgres postgres 4096 2012-05-17 18:05 .
drwxr-xr-x 3 postgres postgres 4096 2012-04-07 12:44 ..
-rw-r–r– 1 postgres postgres 316 2012-04-07 12:44 environment
-rw-r–r– 1 postgres postgres 143 2012-04-07 12:44 pg_ctl.conf
-rw-r—– 1 postgres postgres 4653 2012-05-17 18:05 pg_hba.conf
-rw-r—– 1 postgres postgres 4649 2012-04-09 19:29 pg_hba.conf.bak.original
-rw-r—– 1 postgres postgres 1636 2012-04-07 12:44 pg_ident.conf
-rw-r–r– 1 postgres postgres 19235 2012-04-07 12:44 postgresql.conf
-rw-r–r– 1 postgres postgres 19235 2012-04-09 19:29 postgresql.conf.bak.original
-rw-r–r– 1 postgres postgres 378 2012-04-07 12:44 start.conf

I have already back of config file, you need to make back up for further need

$ cp pg_hba.conf pg_hba.conf.bak.original
$ cp postgresql.conf postgresql.conf.bak.original

9. Make changes to pg_hba.conf (authetification methods).

host    all         all       127.0.0.1/32       trust          # md5 -> trust

10.You make change in config file, So once restart you postgres

$sudo /etc/init.d/postgresql restart
*Restarting PostgreSQL 9.0 database server
That’s it. Enjoy………

Postgres commands


1. Access postgresSQL.

->su postgres

2. Create user.

->CREATE USER username

3. Delete user.

-> DROP USER username

4. Create user can create DATABASE/

->CREATE USER manuel WITH PASSWORD ‘jw8s0F4’ CREATEDB;

5. List all user.

->\du

6.This lists databases:

->SELECT datname FROM pg_database;

->\list

7.This lists tables in the current database

->SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

->\dt

8.Select particular database with specific user.

-> psql databaseName userName;

->psql databaseName

->\c databaseName

9. List info about current database connection.

->\conninfo

10.List database list.

->\dg

11. List all user.

->\du

12.Quits the psql program

->\q

13.To exit from psql command line utilities.
->\q type and press enter
14. Describe table like mysql.
>\d+ tablename