Setup PostgreSQL in Ubuntu

PostgreSQL is a powerful, open source object-relational database system.

Install PostgreSQL

  • Create a file /etc/apt/sources.list.d/pgdg.list, and add following line for the repository

    # for ubuntu 16.04 users
    deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
    
    # for ubuntu 15.10 users
    deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main
    
    # for ubuntu 14.04 users
    deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
    
    # for ubuntu 12.04 users
    deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
    
  • Import the repository signing key, and update the package lists

    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    sudo apt-get update
    
  • Install postgreSQL and, check version

    sudo apt-get install postgresql-9.5
    psql --version
    

2. From native store

Enter following commands:

sudo apt-get update
sudo apt-get install postgresql-9.4
psql --version

First time setup

  • Connect to the default database with user postgres:

    sudo -u postgres psql template1
    
  • Set the password for user postgres, then exit psql (Ctrl+D):

    ALTER USER postgres with encrypted password 'xxxxxxxx';
    
  • Edit the pg_hba.conf file. And, change ‘peer’ to ‘md5’ on the line concerning postgres:

    sudo vim /etc/postgresql/9.5/main/pg_hba.conf
    

    Before changes:

    local   all     postgres    peer
    

    After changes:

    local   all     postgres    md5
    
  • Restart the database:

    sudo service postgresql restart
    
  • You can check the password with psql -U postgres

  • Create a user (with superuser access):

    createuser -U postgres -d -e -E -l -P -r -s <new_username>
    
  • Again edit pg_hba.conf file, and change ‘peer’ to ‘md5’ on the line concerning ‘all’ other users:

    Before changes:

    local   all     all     peer
    

    After changes:

    local   all     all     md5
    
  • Restart, and check that you can login with new user

    psql -U <new_username> template1
    

To access with you as a user

  • Create user with your username(can be found by whoami):

    createuser -U postgres -d -e -E -l -P -r -s <my_name>
    
  • Restart postgresql server and check that you can login without -U postgres:

    psql template1
    
  • You can create database easily now:

    createdb <db_name>
    

Resources