Change - Postgres

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 &para]
¶
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/ &para]
.. Official ubuntu instructions: https://help.ubuntu.com/community/PostgreSQL &para]


Comment: Added Quickstrart, pgAdmin III and Resources.

Comment on This Data Unit