Created on Nov. 15, 2012, 4:49 p.m. by Hevok & updated by Hevok on May 2, 2013, 5:05 p.m.



PostgreSQL (Postgres) is an object-relational management system, available on many platforms and free open source [;].

MySQL is as flexible as a delphine, but handles data very childish, while Postres is as constraint as an elephant. Postgres is more strict with handling invalid queries.

Postgres has build in many features such as (postgreSQL Rising):

  • Keyword "infinity" meaning bigger than any value entered here. It works for number as well as data and can be set positive or negative
  • Sensible date keywords like "today", "tomorrow", "yesterday" and many more.
  • Amazing data types like arrays, IP addresses which understand IPV6. Even spatial types like line, squares and cicles are supported.
  • Table inheritance, allows a table literally to inert from another.
  • Natural language full text searching (out of the box). is the server - Navicat is the client.


Install the server:

$ sudo apt-get install postgresql


Postgres configuration file is located here: /etc/postgresql/9.1/main/postgresql.conf (Version 9.1 under Ubuntu)

TCP/IP connections are by default disabled and therefore users will not be able to access the server from another computer. If you need to enable TCP/IP connection change in the configuration file:

#listen_addresses = localhost

#password_encryption = on
password_necryption = on


Create an user and grant s/he no roles with createuser:

$ sudo -u postgres createuser
Enter name or role to add:  # root
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

Generate a database with createdb:

$ sudo -u postgres createdb  # db

Grand access to the user for the database:

$ sudo -u postgres psql
postgres=# alter user  with encrypted password 'password';  # Didn't do this step. I hate passwords.
postgres=# grant all privileges on database  to ;

To quite/exit the sql shell use 'q':

psotgres=# \q # quit/exit

On the client machine:

sudo apt-get install postgresql-client

Connection to the server is established like this way:

psql -h   

The server can be controlled with these commands:

$ sudo /etc/init.d/postgresql start
$ sudo /etc/init.d/postgresql stop
$ sudo /etc/init.d/postgresql status
$ sudo /etc/init.d/postgresql restart

Seeing all available datasets can be requested either from bash or sql shell:

$ psql -l
postgres=# select datname from pg_database;

Database Removal

To remove a database execute this []:


Django Postgres

psycopg2 is the module that enables django to connect to postgres. It can be installed via apt-get:

sudo apt-get install python-psycopg2

or simple with pip (better):

pip install psyocopg2

Postgres is the recommended database-backend for Django, however its setup is not trivial [].

If there is trouble with the postgres identity authentication failed the hba_file needs to be edited which can be found by entering this command in the sql shell [;]:

postgres=# show hba_file;

MySQL-Postgres command translation []:

Postgres MySQL
\l SHOW databases;
\c database USE database;
\d table SHOW COLUMNS;

Log in

During log in into Postgres you can specify the user and the database:

psql -U user_name -d database_name


Enable access for a username (replace $USER):

$ sudo -u postgres createuser --superuser $USER
$ sudo -u postgres psql

Change the password for this user:

\password $USER

Create a database, which take the same name as the user:

$ createdb $USER

Now it should be possible to connect to the prompt just with: psql


pgAdmin III is front-end, which is useful for beginners:

$ sudo apt-get install pgadmin3

pgAdmin III can be launched either via Applications->Programming->pgAdmin III or firing the pgadmin3 simply into the terminal.

To tell pgAdmin to use to create a connection to the local server press the plug button and fill the following fields in:

  • Name = localhost
  • HOST =
  • Maintenance DB = $USER
  • User name = $USER
  • Password = $PASSWORD # The previous set one.


Ctrl + z quits the shell and Ctrl + d should exit the prompt.


Tags: rest, relational database, databases
Categories: Tutorial
Parent: Coding Relational Databases

Update entry (Admin) | See changes

Comment on This Data Unit