How to Install / Configure PostgreSQL

PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of community volunteers employed and supervised by companies such as Red Hat.

It implements the majority of the SQL:2008 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, and indexes, and has a large number of extensions written by third parties.

PostgreSQL Installation:
Open the terminal and type following command to install postgresql
sudo apt-get install postgresql

PostgreSQL Configuration:
After successful installation of postgresql, you can configure postgresql using configuration file /etc/postgresql/9.1/main/postgresql.conf (9.1 is the version of postgresql installed under Ubuntu)

By default, connections via TCP/IP is disabled due to which users will not be able to access PostgreSQL server from another computers. To enable TCP/IP Connection edit the file /etc/postgresql/9.1/main/postgresql.conf and make the following changes.

Change #listen_addresses = localhost to ....
listen_addresses =192.168.1.1
and #password_encryption = on to ...
password_encryption = on

Setup PostgreSQL Users and Password:
NOTE: All the commands below are executed as the postgres privileged user.

Create the user
Go to terminal and type command createuser and answer few quetion to create postgreSQL user
sudo -u postgres createuser
 Enter name of role to add: linuxhowto
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

Create the PostgreSQL Database:
Use the command createdb command to create the database :
sudo -u postgres createdb linuxhowtodb

CREATE DATABASE
Grand access to the user for the database:
And last, using the psql command, set a password for the user and grant accesses :
sudo -u postgres psql
 postgres=# alter user linuxhowto with encrypted password 'password';
ALTER ROLE
 postgres=# grant all privileges on database linuxhowtodb to linuxhowtoin;
GRANT

Now, on the client Linux (Ubuntu) machine, type following command to install PostgreSQL client:
sudo apt-get install postgresql-client

After successful installation of the postgresql client on the client machine, you then connect to the server with the following command
psql -h <postgresql_server_name> <database_name> <username>

After you inserted the password you access PostgreSQL with line commands.

Use the following command (from terminal) to control the PostgreSQL server
Start the service : /etc/init.d/postgresql start
Stop the service : /etc/init.d/postgresql stop
Know the status : /etc/init.d/postgresql status
Restart the service : /etc/init.d/postgresql restart