Translate

May 1, 2012

Howto- Install PostgreSQL in Ubuntu 11.10/12.04

  • Install PostgreSQL in Ubuntu, some instructions are given below, Install postgresql using the command on terminal:
  • sudo apt-get install postgresql  libpq-dev
    
  • By default postgres is listening on port 5432. You need to confirm that after installation check that postgres is listening on port 5432
  • To check postgres is really listening on port 5432 use following command on terminal:
  • netstat -atn | grep -v tcp6 | grep 5432                          
    tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN  
    
  • If you see above output, then postgres is installed successfully on your system. You need other configurations to interact with postgres. First you need to create new db user to access the postgres, Default db user postgres is created when you install the postgres.
  • Now change the postgres user password (as root):
  • Make first database user (a superuser is required for practical migrations) in postgres!:
    sudo su postgres                                                   
    createuser pankaj                                                  
    Shall the new role be a superuser? (y/n) y
    
  • Now create a postgres database Create a first postgres development database
  • ~> pssql template1 
    psql (9.1.3)
    Type "help" for help.
    
    template1-# \l
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    (3 rows)
    
    template1-#
    
    template1=# CREATE DATABASE dummy_development;
    CREATE DATABASE
    template1=#
    template1=# \l
                                         List of databases
           Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    ------------------+----------+----------+-------------+-------------+-----------------------
     postgres         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     dummy_development| pankaj   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                      |          |          |             |             | postgres=CTc/postgres
     template1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                      |          |          |             |             | postgres=CTc/postgres
    (4 rows)
    
    template1=#\q
    
    ------ TO Quit use \q
    
Ruby on Rails developers:
  • Install the postgres gem
  • dummy git:master> gem install pg                      
    Building native extensions.  This could take a while...
    Successfully installed pg-0.13.2
    
  • To install GUI client pgsql
  • sudo apt-get install pgadmin3