Install PostgreSQL 9.6 on Ubuntu 14.04 or 16.04 LTS

PostgreSQL (aka Postgres), is an open-source SQL relational database management system (RDBMS). These RDBMS are the key component for the majority of all the web sites and web applications, providing an efficient way to persist, organize, and access information.
In the blog, I’ll demonstrate, how to install Postgres 9.6 for Ubuntu 14.04, 16.04 LTS.

Step 1: Add Postgresql repository

$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
$ wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Output:
Postgres package

Step 2: Install Postgresql 9.6

$ sudo apt-get update
$ sudo apt-get install postgresql-9.6 postgresql-contrib
The apt-get update, updates the list of available packages and their versions, above we have added the postgresql repository, in the output you can see the apt-postgresql gets added.
Output:
install postgres

Verification – Using psql shell (Optional)

Postgresql comes with an interactive Postgres prompt. On installation the super user postgres gets created. A good practice is to update the password for postgres user and create a separate user for your service or web app.
$ sudo -u postgres psql postgres
Output:
postgres login

Show all the databases

postgres=# \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)postgres=#

Show users/roles

postgres=# \du
List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres=#

Connection Info

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Create a New Database

postgres=# create database testing_db;

Create a new Role

postgres=# create user nahmed with password  'password';
postgres=# grant all privileges on database testing_db to nahmed;
Change password
postgres=# ALTER USER postgres WITH PASSWORD 'yournewpassword';

Leave a Reply

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