Postgres

PostgreSQL

Introduction

PostgreSQL (Postgres) is an object-relational management system, available on many platforms and free open source [http://linuxpoison.blogspot.de/2012/01/how-to-install-configure-postgresql.html; http://linuxpoison.blogspot.de/2012/01/how-to-install-configure-postgresql.html].

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).

Postgres.app is the server - Navicat is the client.

Installation

Install the server:

$ sudo apt-get install postgresql

Configuration

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
listen_addresses=192.168.1.1

#password_encryption = on
password_necryption = on

Setup

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
CREATE ROLE

Generate a database with createdb:

$ sudo -u postgres createdb  # db
CREATE DATABASE

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.
ALTER ROLE
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 [http://www.postgresql.org/docs/current/static/sql-dropdatabase.html]:

postgres=# DROP DATABASE [IF EXISTS; 

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 [http://blog.iiilx.com/programming/how-to-install-postgres-on-ubuntu-for-django/].

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 [http://superuser.com/questions/179238/postgres-ident-authentication-failed; http://www.depesz.com/2007/10/04/ident/]:

postgres=# show hba_file;

MySQL-Postgres command translation [http://www.coderholic.com/postgresql-for-mysql-users/]:

Postgres MySQL
\l SHOW databases;
\c database USE database;
\dt SHOW TABLES;
\d DESCRIBE TABLES;
\d table SHOW COLUMNS;
\d+ table DESCRIBE TABLE;

Log in

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

psql -U user_name -d database_name

Quickstart

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

Admin

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 = 127.0.0.1
  • Maintenance DB = $USER
  • User name = $USER
  • Password = $PASSWORD # The previous set one.

Quit

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

Postgresql_elephant.png/
Edit tutorial

Comment on This Data Unit