created on Nov. 15, 2012, 4:49 p.m. by Hevok & updated on Nov. 23, 2012, 10:02 a.m. by Hevok
========== PostgreSQL ==========
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
_):
.. _postgreSQL Rising
: http://wekeroad.com/2012/07/19/postgresql-rising
Postgres.app 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
listen_addresses=192.168.1.1
#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: <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;
To remove a database execute this [http://www.postgresql.org/docs/current/static/sql-dropdatabase.html]::
postgres=# DROP DATABASE [IF EXISTS] <database_name>
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; ============== ================
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:
Ctrl
+ z
quits the shell and Ctrl
+ d
should exit the prompt.
django-postgresql-ubuntu
_
Official ubuntu instructions
_
.. django-postgresql-ubuntu
: http://soledadpenades.com/articles/django/django-postgresql-ubuntu/
.. Official ubuntu instructions
: https://help.ubuntu.com/community/PostgreSQL
Comment on This Data Unit