How to install PostgreSQL on Ubuntu 17.04

PostgreSQL is a stable, powerful relational database server. PostgreSQL is available for Linux, Windows and other system. On this tutorial, we will show you how to install PostgreSQL on Ubuntu 17.04. PostgreSQL is a great database management system that mostly used by enterprise class business. It’s a well known database system as it’s robust, stable on handling high workload level. PostgreSQL is a suitable database system for Enterprise with more than 30 years of development. 

Install PostgreSQL on Ubuntu 17.04

What you will learn on this tutorial:

  • Install PostgreSQL 
  • Create new user
  • Setup new database Postgresql

Step 1. Install PostgreSQL 

Open Terminal or connect to Ubuntu 17.04 via SSH. First, update Ubuntu to ensure you have up to date system. 

sudo apt update && sudo apt upgrade

Now execute this command to install PostgreSQL

sudo apt install postgresql postgresql-contrib

Output:

ubuntu@ubuntu-server:~$ sudo apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-4.10.0-32 linux-headers-4.10.0-32-generic linux-image-4.10.0-32-generic
  linux-image-extra-4.10.0-32-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libpq5 postgresql-9.6 postgresql-client-9.6 postgresql-client-common postgresql-common postgresql-contrib-9.6
  sysstat
Suggested packages:
  postgresql-doc locales-all postgresql-doc-9.6 libdbd-pg-perl isag
The following NEW packages will be installed:
  libpq5 postgresql postgresql-9.6 postgresql-client-9.6 postgresql-client-common postgresql-common
  postgresql-contrib postgresql-contrib-9.6 sysstat
0 upgraded, 9 newly installed, 0 to remove and 65 not upgraded.
Need to get 5,051 kB of archives.
After this operation, 20.3 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Additionally, we can also install postgresql-client package.

sudo apt install postgresql-client

Step 2. Manage PostgreSQL 

By default, there is only one user (postgres) that can access the PostgreSQL database. On this practice we will show you to create new user for the PostgreSQL Database. For example, we will create new user called “dhani“. 

A. Add new Linux user called dhani

ubuntu@ubuntu-server:~$ sudo adduser dhani
Adding user `dhani' ...
Adding new group `dhani' (1001) ...
Adding new user `dhani' (1001) with group `dhani' ...
Creating home directory `/home/dhani' ...
Copying files from `/etc/skel' ...
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
Changing the user information for dhani
Enter the new value, or press ENTER for the default
        Full Name []: Dhani
        Room Number []: 
        Work Phone []: 
        Home Phone []: 
        Other []: 
Is the information correct? [Y/n] y

B. Add new PostgreSQL user

First, login using postgres user on Ubuntu

su - postgres

Next Execute this command on Terminal

psql

Output:

postgres@ubuntu-server:~$ psql
psql (9.6.4)
Type "help" for help.

Add user called dhani

CREATE USER dhani WITH PASSWORD '12345678';

Output:

postgres=# CREATE USER dhani WITH PASSWORD '12345678';
CREATE ROLE

At this point, new user dhani has been created. Now we can log in to PostgreSQL using user dhani. 

C. Setup new database on PostgreSQL

Since we don’t have default database for user dhani, you will get the following error when trying to use psql command under user dhani.

dhani@ubuntu-server:~$ psql
psql: FATAL:  database "dhani" does not exist

So, we will create a new database. We must use user postgres to create new database.

#login using postgres user
su - postgres

#login to PostgreSQL
psql

#Create new database
CREATE DATABASE test_database;

Output sample:

dhani@ubuntu-server:~$ su - postgres
Password: 
postgres@ubuntu-server:~$ psql
psql (9.6.4)
Type "help" for help.

postgres=# CREATE DATABASE test_database;
CREATE DATABASE

Now grant dhani to new database

GRANT ALL PRIVILEGES ON DATABASE test_database to dhani;

Thanks for reading this how to install PostgreSQL on Ubuntu 17.04. Please share this article if you think this useful. Cheers

Admin

Leave a Reply

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