PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages are available as well.

It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

 

If wont to use PostgreSQL 9.6 need to add repository (Debian current have PostgreSQL 9.4 in repository).

Add PostgreSQL Repository

Add the PostgreSQL repository in the /etc/apt/sources.d/postgresql.list

echo 'deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main' >> /etc/apt/sources.list.d/postgresql.list

Install release key of PostgreSQL repository:

wget --no-check-certificate -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | apt-key add -

And run apt-get update to download the list of packages.

apt-get update

 

Install PostgreSQL and setup new user & database

Install PostgreSQL with:

apt-get install postgresql-9.6

The PostgreSQL package will install all required dependencies.

Choose "Y" to start the installation, wait fo finnish and PostgreSQL is ready for using.

 

Create a PostgreSQL database, user and set permissions

The next step is to create a PostgreSQL database, add user and set permissions.

Login to PostgreSQL on the commandline by running this command:

su - postgres
psql

Enter the following commands to create a database: 

CREATE DATABASE mybase;
CREATE USER myuser WITH PASSWORD 'StorngPasswordHere';
GRANT ALL PRIVILEGES ON DATABASE mybase to myuser;

We have created a PostgreSQL database above, enter the details of that database now:

  • Username:            myuser
  • Password:            StorngPasswordHere
  • Database name: mybase

Change username,password and database to Your needs.

 

Test new PostgreSQL database

Now run simple test:

psql -h localhost -U myuser -W -d mybase

If You success connect - bingo !!!!  Enjoy in PostgreSQL