> > > How to: Install PostgreSQL 9.3 on Ubuntu – Complete Guide

How to: Install PostgreSQL 9.3 on Ubuntu – Complete Guide

PostgreSQL is one among the leading open source RDBMS available for Windows, Linux and many other platforms. Installing PostgreSQL in Windows OS is much easier since PostgreSQL provides pre-packaged setup files for Windows. When it comes to Linux, people, especially newbies, find it difficult to install PostgreSQL because there is no all-in-oninstall postgresql 9.3e one-click installer available. Linux works differently and there are some manual work involved like, ‘changing repositories’, ‘installing dependencies’, etc

PostgreSQL 9.4 is also available, click here to know how to install it on Ubuntu

Let’s see how to install PostgreSQL 9.3 Server and PGAdmin3 on a clean Ubuntu OS in detail!

  1. Install dependency
    Open Terminal and execute the following commands:

    sudo apt-get update
    sudo apt-get -y install python-software-properties
  2. Add PostgreSQL 9.3 repository
    Before installing PostgreSQL 9.3, we have to add the PostgreSQL repository where the packages are located.
    Setup key:

    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

    Setup repository:
    Run the following command and note down the output. It is the codename of your Ubuntu release.

    lsb_release -c

    Replace the codename in the below command with the one you got from above command and run:

    sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ codename-pgdg main" > /etc/apt/sources.list.d/postgresql.list'
  3. Install PostgreSQL 9.3 using aptitude
    sudo apt-get update
    sudo apt-get install postgresql-9.3 pgadmin3
  4. Accessing Database from Terminal
    Installation of PostgreSQL will automatically create a Unix/Ubuntu user, named postgres. We will have to login with this user in order to perform various database activities such as accessing database, taking and restoring backups, starting/stopping server etc.
    We may need to reset the password for postgres user before we are able to login to that account.

    The UNIX user, postgres is locked by default. Changing the password for this user will unlock the account. It is not preferred and even considered as a serious security threat.

    To start with PostgreSQL, you can connect to the default database using the following command:

    sudo -u postgres psql

    For other activities like taking data base dump, you have to login as postgres UNIX user as shown below(assuming you have root user privileges):

    sudo su postgres
  5. Accessing Database from pgAdmin
    Just like the Unix user, PostgreSQL will also create a database user named postgres, which we should use for login to database for the first time.
    To be able to access database from pgAgent3, we should follow either one of the following two options:

    1. Change the password for the postgres DB user
    2. Change the database access rights in the pg_hba.conf file

    We will proceed with the second method, since setting password for postgres DB user is not preferred.

    1. After loging into postgres DB in terminal, run the following command to get the path to your pg_hba.conf file
      show hba_file ;

      In my case it returned: /etc/postgresql/9.3/main/pg_hba.conf

    2. Open the file using any text editors
      sudo gedit /etc/postgresql/9.3/main/pg_hba.conf

      And replace the two lines mentioned below:

      local   all             all                                    md5

      to

      local   all             all                                    trust

      And

      host    all             all             127.0.0.1/32            md5

      to

      host    all             all             127.0.0.1/32            trust

      If you are not able to find any lines similar to above two lines, adding the substitute lines at the top of the file will also do the job.

    3. Restart PostgreSQL server once.

    If you want more information about the pg_hba.conf file and its contents, you can read it here or here.

  6. That’s it! You’ve installed PostgreSQL 9.3 on Ubuntu and now you can use pgAdmin3 to login to your database with any password.

    how to install postgresql 9.3 on ubuntu
    pgAdmin3 Add new Server Window

What is next ?

How to: Install PostGIS 2 for PostgreSQL 9.3 on Ubuntu [Guide]

How to take Automatic SQL Database Backup using pgAgent | PostgreSQL

The following two tabs change content below.

Vipin Raj

Vipin Raj is a software developer specialized in PostgreSQL Database and Data Modeling, the man behind technobytz and an IoT and Security enthusiast. Having 3+ years of experience in the IT industry, he is currently pursuing his masters in computer science and information security. He spend his free time writing blog posts with the intension of sharing his knowledge to the tech community.

11 thoughts on “How to: Install PostgreSQL 9.3 on Ubuntu – Complete Guide

  • January 1, 2014 at 2:00 am
    Permalink

    Top article. I look forward to reading much more. Cheers

    Reply
  • February 6, 2014 at 7:27 am
    Permalink

    Please do not encourage users to set a password on the postgres unix user account or the postgres database user account- neither is necessary. An admin can su to the postgres unix user from root and then connect to PostgreSQL as the postgres user without any need for a password over a secure unix-socket using peer authentication.

    Indeed, any unix user can be set up to authenticate using peer authentication over a unix-socket, as can services which run as other unix users (eg: a web server). Web-server or other users should NOT be set as superusers either- that is akin to ‘root’ access and is very dangerous. A user with superuser access in PostgreSQL can write to any file that the unix postgres user can write to, through PostgreSQL, without much difficulty. A sufficiently motivated attacker could probably get a unix shell with just PostgreSQL superuser access.

    Reply
    • February 12, 2014 at 12:24 am
      Permalink

      Thank you very much for figuring out this serious security problem. I have modified the article accordingly .

      Reply
  • May 14, 2014 at 2:39 am
    Permalink

    How can I reset the server? This information is not supplied anywhere.

    Reply
    • June 5, 2014 at 9:48 am
      Permalink

      You can use these commands to stop or start server accordingily, after login as postgres unix user:
      /etc/init.d/postgresql stop
      /etc/init.d/postgresql start

      Reply
  • Pingback: Pasang MitraDesa2.0 di Server Lokal

  • Pingback: Cara Memasang Mitra Desa versi 2.0 | PRIANTHON.id

  • Pingback: Install & Configure PgBouncer | Pooling in PostgreSQL #1

  • Pingback: How to Install PostgreSQL 9.4 in Ubuntu / Linux Mint

  • August 2, 2017 at 8:15 pm
    Permalink

    I cant login saying “20:14:40: Error: Error connecting to the server: FATAL: password authentication failed for user “postgres”
    FATAL: password authentication failed for user “postgres”

    Reply
  • August 25, 2017 at 8:09 pm
    Permalink

    excellent article. Thank you

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *