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 [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):
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:# 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 userwith 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;
To remove a database execute this [http://www.postgresql.org/docs/current/static/sql-dropdatabase.html]:
postgres=# DROP DATABASE [IF EXISTS;
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.
Comment on This Data Unit