How to Install PostgreSQL on Ubuntu Server 16.04 and Manage

What does PostgreSQL mean?

PostgreSQL is an open-source, object-relational database management system (ORDBMS) that is not owned or controlled by one company or individual. Because PostgreSQL software is open-source, it is managed mostly through a coordinated online effort by an active global community of developers, enthusiasts and other volunteers.

First released in the mid-1990s, postgresSQL is written in C. Its primary competitors include Oracle DB, SQL Server and MySQL.

This term is also known as Postgres.

Installation:

First, let us update the system:

~$ sudo apt-get update && sudo apt-get upgrade

Install PostgreSQL from the Ubuntu package repository:

~$ sudo apt-get install postgresql postgresql-contrib

Change Postgres Linux user’s password
~$ sudo passwd postgres
 To enable remote access you will need to edit two files:
/etc/postgresql/9.5/main/pg_hba.conf
/etc/postgresql/9.5/main/postgresql.conf (9.5 is the version number so it can be various)
~$ sudo nano /etc/postgresql/9.5/main/pg_hba.conf
 Find the below
# IPv4 local connections:
host all all 127.0.0.1/32 md5
and change it as below
# IPv4 local connections:
host all all 0.0.0.0/0 md5
and for second file:
~$ sudo nano /etc/postgresql/9.5/main/postgresql.conf
# listen_addresses=’localhost’ to
listen_addresses=’*’

Switching Over to the postgres Account

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we can log into that account.

Switch over to the postgres account on your server by typing:

~$ sudo -i -u postgres
be below command:
~$ psql

You will be logged in and able to interact with the database management system right away.

~$ postgres=#
 Exit out of the PostgreSQL prompt by typing: \q
~$ postgres=# \q
To change the password for a user type below
~$ postgres=# ALTER USER [user_name] with PASSWORD [password];
 Creating a user
~$ sudo -u postgres createuser [user_name]
Create a database
~$ sudo -u postgres createdb [db_name]
Granting a user on a database
~$ postgres=# grant all privileges on database [db_name] to [user_name];