Created on Nov. 15, 2012, 4:49 p.m. by Hevok & updated on Nov. 23, 2012, 10 a.m. by Hevok
==========
¶
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).
¶
¶
.. postgreSQL Rising: http://wekeroad.com/2012/07/19/postgresql-rising
¶]
¶
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: <user_name> # 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 <database_name> # db
¶
CREATE DATABASE
¶
¶
Grand access to the user for the database::
¶
¶
$ sudo -u postgres psql
¶
postgres=# alter user <user_name> with encrypted password 'password'; # Didn't do this step. I hate passwords.
¶
ALTER ROLE
¶
postgres=# grant all privileges on database <database_name> to <user_name>;
¶
¶
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 <postgresql_server_name> <database_name> <user_name>
¶
¶
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] <database_name>
¶
¶
¶
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.
¶
¶
Resources
¶
=========
¶
`django-postgresql-ubuntu`_
¶
¶
`Official ubuntu instructions`_
¶
¶
.. django-postgres-ubuntu: http://soledadpenades.com/articles/django/django-postgresql-ubuntu/
¶]
.. Official ubuntu instructions: https://help.ubuntu.com/community/PostgreSQL
¶]
Comment on This Data Unit