How to Install PostgreSQL 9.6 on Ubuntu 16.04 LTS

How to Install PostgreSQL 9.6 on Ubuntu 16.04 LTS

PostgreSQL install ubuntu

POSTGRESQL is an open-source, full-featured relational database.PostgreSQL is the most advanced open source relational database, MySQL did not have triggers, PostgreSQL did.
In this tutorial we will see How to Install PostgreSQL 9.6 on Ubuntu 16.04 LTS. The same procedure can be used for installing PostgreSQL 9.6 on Ubuntu 14.04 LTS.
AWS Architect Online Training by Edureka

Step 1: Add PostgreSQL Apt Repository

$ sudo sh -c 'echo "deb `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
$ wget -q -O - | sudo apt-key add -


Step 2: Install PostgreSQL

$ sudo apt-get update
$ sudo apt-get upgrade

$ sudo apt-get install postgresql postgresql-contrib libpq-dev pgadmin3


Step 3: Connecting to PostgreSQL

Ather the installation of PostgreSQL a user account called postgres created that is associated with the default Postgres role. In order log into to use Postgres account, we nee to give following command:

$ sudo su - postgres
$ psql

To list the databases  type following command

postgres-# \l

The Command above will give you following output

                                  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)

To list the user type following command

postgres-# \du

The Command above will give you following output

                             List of roles
 Role name |                   Attributes                   | Member of 
 postgres  | Superuser, Create role, Create DB, Replication | {}

How to change PostgreSQL user password? type following command to change PostgreSQL:

postgres=# ALTER USER postgres WITH PASSWORD 'test123';

To Create new user in PostgreSQL type following command:

postgres=# CREATE USER user_1 WITH PASSWORD 'test123';

To upgrade a user to be a superuser in PostgreSQL type following command:

postgres=# ALTER USER user_1 WITH SUPERUSER;

We can exit PostgreSQL prompt by typing following command:

postgres-# \q

To check login info use following command

postgres-# \conninfo



  • psql is the standard PostgreSQL application to work with databases
  • It uses a superset of standard SQL
  • It can be interactively like DBaccess, or in scripts like sqlcmd

Common psql Command Line Options

  • -A, –no-align: set output to non-aligned, no padding
  • -c sql, –command sql: execute the sql command and then exit
  • -d name, –dbname name: name of database, same as name as the first non-option argument
  • -f name, –file name: use name as the source of commands
  • -o name, –output name: put the output in name
  • -q, –quiet: suppress welcome messages
  • -t, –tuples-only: suppress print column names, result row counters, etc
  • -?, –help: get command line help

Common psql Meta-commands

  • \a: toggle output format (aligned/unaligned)
  • \c name: connect to a database name
  • \copy table: copy a table to/from a file
  • \d : list all tables (display)
  • \d table: show information about a table
  • \e: edit the query buffer
  • \g: execute the query buffer (go)
  • \h command: display help on command
  • \i name: read name into query buffer (input)

Common psql Meta-commands

  • \o name: send output to name
  • \p: display the query buffer
  • \q: quit the program
  • \r: resets (clears) the query buffer
  • \t: toggle the output headers on/off
  • \w name: write the query buffer to name
  • \! command: execute the Linux command
  • \?: help on meta-commands

Interactive psql

  • Typing in a query loads the query buffer
  • The command will not run unless terminated with a semicolon (;)
  • Meta-commands all start with blackslash (\)
  • Exit with \q

Video Instructions

Partner Sites

1 Comment

  1. I followed the installation instructions to the letter (cut/paste is my friend). When I sudoed to postgres and ran psql here is what I got:

    $ psql
    psql (10.0)
    Type “help” for help.

    The installation instructions are for the latest PostgreSql, not necessarily 9.6. I really don’t want to use the x.0 release of any software. I’ll have to find another site to determine how to install PostgreSql 9.6.

Leave a Reply

Your email address will not be published.