How To Install and Use PostgreSQL on Ubuntu 14.04

Jaspalsinh Chauhan -

Ok so you know the blah blah of how PostgreSQL is awesome relation database (RDBMS) and great for reliable transactions, good for so many things, lots of enterprise features and gives concurrency without read locks and stuffs.

If you are not aware of this RDBMS, then I would recommend you search for advantages of using PostgreSQL and what is served in its plate.

PostgreSQL is pronounced mostly as "post gray sequel" or "post ge are ee as que el" (I am sounding very dramatic here).

Back to the post now,

Fire up your Ubuntu 14.04 to start the installation. It would almost follow the same for most of the Ubuntu flavors out there.

First step - you know the drill :)

sudo apt-get update

Next step:

sudo apt-get install postgresql postgresql-contrib

postgresql installs the core PostgreSQL engine and RDBMS around which everything else is build. The core files for this database server is installed in this package.

postgresql-contrib are the additional packages that are provided by the team to work with database server. The linked URL show the list of modules provided as part of contrib package in the latest version of PostgreSQL at the time of this writing.

Check if your newly install version of PostgreSQL is running already

sudo netstat -tap | grep postgres

If it is already running (and it should be once installed successfully) then you should see line output similar to this:

tcp        0      0 localhost:postgresql    *:*                     LISTEN      5814/postgres

Indicates that your PostgreSQL instance is running - listening on TCP protocol and port number 5814, bind with host - "localhost".

You check the version of currently install PostgreSQL instance using following command:

psql --version

or

psql -V

or from which installation destination is used

which psql

It outputs psql (PostgreSQL) 9.3.5 on version query and /usr/bin/psql for which command.

PSQL command is basically a PostgreSQL interactive terminal command-line utility and can be used to connect and run queries against any PostgreSQL instance.

As with other RDBMS, PostgreSQL comes with a default admin user / root user called - "postgres"

Here is something interesting if you do not know, when you install PostgreSQL default authentication mode is set to 'ident'. By this it means a PostgreSQL login user is mapped to an actual user on UNIX / LINUX operation system. So obviously we would expect a new user created on our machien named "postgres". Following command confirms that:

awk -F':' '{ print $1}' /etc/passwd

This outputs list of users on your system which enlists "postgres" user.

This means the defaut or admin user of PostgreSQL called postgres when installed, does not come with any default password.

You can login to your PostgreSQL instance as follows:

sudo -u postgres psql

or

sudo -i -u postgres
psql

This will bring up the interactive terminal to access PostgreSQL instance.
The prompt text should be hopefully showing - postgres=#

Set password for the default / admin postgres user:

alter user postgres password 'password_of_your_choice';

Exit out of the PostgreSQL - psql interactive terminal - prompt using following command:

\q

Happy Querying !!