Installing and Configuring PostgreSQL 9.4 on Linux Mint/Ubuntu

• At the moment, my preferred Linux distro is Linux Mint 17 (“Quiana”) with the Cinnamon desktop. This is a long-term support release of the Linux Mint distro, very stable, and an excellent place to start. If you do not have a dedicated Linux machine, it is simple enough to spin up a VM using Virtual Box. 
• As of this writing, the most recent version of PostgreSQL is version 9.4, which brought with it some very cool features such as full JSONB support. However, the 9.4 release is not available directly using the Advanced Packaging Tool (APT) or the Linux Mint/Ubuntu Software Manager.
• Fortunately, the PostgreSQL Global Development Group (PGDB) maintain an APT repository of PostgreSQL packages for Debian and Ubuntu-derived Linux distros.
• Before we can install Postgres, we need to add the package source for the distro we are using. In my case, I am using Linux Mint 17, which is derived from, and compatible with, the Ubuntu 14.04 (“Trusty Tahar”) release. We’ll see why this matters in a moment.

Step 1.Add the Postgres Package for Your Linux Release
We need to create a sources file reflecting the proper Postgres source for our particular distro. In my case, as noted above, we need the source compatible with the “Trusty” release of Ubuntu. So we can do this from the terminal to add the file (make sure you use sudo in all of the following steps):

a)Add the PGDB APT Source file From the Terminal:

$ sudo touch /etc/apt/sources.list.d/pgdg.list

b)Open the pgdg.list File in “ using vi edit” (use sudo):

$ sudo “ using vi edit” /etc/apt/sources.list.d/pgdg.list

C)Add the Postgres Package Repository and Specify Your Distro Release:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

Save, and close “ using vi edit”.

d)Add the Package Source in one multi-line Terminal Command:

$ sudo sh -c \'echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > \ /etc/apt/sources.list.d/pgdg.list'

Step 2.Add the Postgres Package Repository Key
then
a)Add the Postgres Package Repository Key:

$ sudo apt-get install wget ca-certificates
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Step 3.Update
Then, we need to update our package sources:

$ sudo apt-get update

Step 4.Upgrade
After Update, we need to upgrade packages to the latest versions:

$ sudo apt-get upgrade

Note, this can be a long process. Also, you may be prompted at several points to make some choices about configuration items. Specifically, you may informed that this that or the other configuration file has been changed, and asked if you want to keep your original version, or replace with the package maintainer’s version. Select “Y” to accept the package maintainer’s version in these cases.after upgradation  is Over then Install Postgres Using pgadmin3

Step 5. Install Postgres:

$ sudo apt-get install postgresql-9.4 pgadmin3

After the  installation is completes, We need to configure our new database.

Step 6.Postgres  Configuration
We’ve now installed both PostgreSQL and the database management utility Pg Admin 3. Next, we should understand a few things about how PostgreSQL works, out of the box.

a)Postgres User
When PostgreSQL was installed, a system user account named postgres was created. with a matching user account in Postgres. By default, the postgres user account is not configured with a password, so it is not possible to log into the server using the postgres user account without first creating a password for it. This postgresaccount has an all-access pass on your postgres database server, permission-wise. The postgres user account is analogous to the sa account in SQL Server. For security reasons, it is recommended that a password not be created for the postgres account.

b)Postgres Database
PostgresSql is installed with a default database name wait for It postgres. From the PostgreSQL documentation:
Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the "template1" and "postgres" databases ,The postgres database is a default database meant for use by users, utilities and third party applications.For the most part, we use the postgres database for admin purposes, and create new databases on the PostgreSQL server to suit our needs.

c) psql Command Line Utility
PostgreSQL includes psql, a command line utility for managing your databases and server. While a GUI-based utility such as pgadmin3 is often easier to use in the day-to-day, the command line utilty psql is also handy. Psql offers complete control of your Postgres system from the terminal, including the ability to execute SQL queries.
Also, we need to use psql to perform our initial configuration, and to create an initial database super user.

Step 7.Create a Super User Account
Since we will not be creating a password for the postgres user account, we need a super-user account in order to work with our database in the day-to-day.
To do this, we will gain access to the postgres account through your system root user, and then use that postgres account to create a new super-user account on your Postgres installation which can be regulated more effectively. As an example, from my own machine (comments denoted by ##):

a)Access the Postgres User Through Root:
switch user to root:

$ su -
Password:

switch user to postgres:

su - postgres

As we can see, we now have a prompt for the postgres user. We can now log in to the default postgres database and, using psql, create a super user account for ourselves:

b)To get into the psql command line utility, we type the following:

$ psql

c)Create a New Super User from the psql Prompt:

postgres=# CREATE USER user1 WITH SUPERUSER CREATEDB CREATEROLE PASSWORD 'userAccountPassword';

Notice in the above we can enter multiple lines of SQL. The SQL is not executed until we enter a semi-colon followed by enter. Which means, the semi-colon matters!

Now, we can exit psql, exit the postgres user, and exit root like so:
Return to your normal user account:

postgres=# \q
$ exit

logout
nijam# exit
logout

With that, we should now be able to log in using psql and make sure everything is wired up correctly.

Step 8.Log In Using Psql and Create a Test Database
Now, just to make sure everything is working correctly, let’s log in with psql using our new super user account and create a quick test database:

a)Log-In Using the New Super-User Account:

$ psql postgres

psql (9.4.1)
Type "help" for help.
postgres=#

Note in the above, we specified the postgres default database when we logged in, since there aren’ tyet any other databases to connect to. We’ll use the default postgres as our admin platform, create a new database, and then connect to the new database to test things out.
So, let’s create a database to play with (once again, make sure to end the SQL statement with a semi-colon!):

b)Create a Test Database Using Psql:

postgres=# CREATE DATABASE nijam WITH OWNER U1;

Now that we have our own database to mess with, use the \connect command to switch psql to that:

c)Switch the Active Psql Connection to the new Test Database:

postgres=# \connect nijam;
You are now connected to database "nijam" as user "U1".
nijam=#

Now, let’s whip up a quick and dirty table, insert some data, and query:

d)Create Table, Insert Data, and Select Query Using Psql:

nijam=# CREATE TABLE Items (id SERIAL PRIMARY KEY, name TEXT);
nijam=# INSERT INTO Items (name) VALUES ('Butter Jams');
INSERT 0 1
nijam=# SELECT * FROM Items;
 id |     name     
----+---------------
  1 | Butter Jams

(1 row)