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
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
postgres@ubuntu-server:~$ psql psql (9.6.4) Type "help" for help.
Add user called dhani
CREATE USER dhani WITH PASSWORD '12345678';
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;
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