ASIX-M10-UF2. PostgreSQL. Instal.lació, configuració i ús del PostgreSQL
Contingut
Introducció
Desenvolupament
Instal.lació del PostgreSQL
En aquest enllaç tens informació sobre la instal.lació del PostgreSQL a partir del fitxer run.
Nosaltres farem una instal.lació a partir del paquet Ubuntu.
$ sudo apt-get install postgresql
Per saber la versió que tenim instal.lada:
$ psql --version psql (PostgreSQL) 9.1.13
psql és el client de consola que utilitzarem habitualment per connectar-nos a PostgreSQL. En aquest moment ja tens el servidor de PostgreSQL funcionant pel port 5432:
$ ps aux | grep postgres postgres 1187 0.0 0.1 51732 8036 ? S 15:17 0:00 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf
Els principals directoris del PostgreSQL són:
- /usr/lib/postgresql/9.1/
- /usr/share/postgresql/9.1
- /var/lib/postgresql/9.1
- /etc/postgresql/9.1/main/
- /var/log/postgresql
Configuració del PostgreSQL
Des del compte normal d'usuari (joan per ex), no podem accedir a postgres:
$ psql psql: FATAL: role "joan" does not exist
Un cop instal.lat el Postgres hem d'accedir al compte postgres del SO. Com que l'usuari postgres per defecte no té password (no està habilitat, igual que root), només podem fer su postgres si som root (sudo bash) o com a superusuaris (sudo).
joan$ sudo bash root$ su postgres -> des de root ja podem canviar a l'usuari postgres postgres$ psql postgres => \q postgres$
També hauríem pogut fer sudo su postgres, i ens demana el password de joan. No cal passar per root
Fixar-se que per connectar-nos com a postgres primer hem hagut de ser root.
Una possibilitat per no fer totes aquestes martingales és ficar un password a l'usuari postgres:
$ sudo passwd postgres
i aleshores ja podem fer su postgres sense haver de passar per el root o el super-admin (sudo)
$ su postgres Contrasenya: postgres@PROFE06:/home/joan$
Hem pogut connectar-nos a Postgres amb l'opció més senzilla: psql sense cap opció. Per defecte vol connectar-se a la base de dades postgres amb el usuari de Postgres postgres. Com que existeixen, la comanda psql sense cap opció ja funciona. Ara bé, la forma més general és
$ psql -h localhost -p 5432 -U postgres -d postgres postgres=>
on especifico la base de dades a la que vull connectar-me i l'usuari (de Postgres) amb el que vull connectar-me.
Per assignar un password a l'usuari postgres (en el procés d'instal.lació es crea l'usuari postgres sense password)
postgres=> ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres';
Començar a treballar: base de dades project
Descàrrega del script de la base de dades project: Fitxer:Project postgres.sql.zip
Un cop en el prompt, hi ha dos tipus d'ajuda:
- \h for help with SQL commands
- \? for help with psql commands
per exemple per llistar totes les bases de dades:
postgres=# \l
i per llistar totes les taules:
postgres=# \dt
Podem crear una base de dades (compte amb el charset que utilitzem)
postgres=# CREATE DATABASE project WITH OWNER postgres ENCODING 'UTF8'; CREATE DATABASE postgres=# \q
És important escollir correctament la codificació, per tal de no tenir problemes amb accents i caràcters especials. Hi ha altres opcions com s'explica per exemple a:
- http://stackoverflow.com/questions/9961795/utf8-postgreFitxer:Project postgres.sql.zipsql-create-database-like-mysql-including-character-set-encoding-a
Ara ja podem entrar a la base de dades, on encara no hi ha cap taula:
$ psql -h localhost -p 5432 -U postgres -d project project=# \dt No relations found.
De moment entrem a la base de dades amb l'usuari postgres. En una propera pràctica veurem la seguretat i els usuaris, i podrem connectar-nos amb un usuari ad-hoc amb el nivell de seguretat desitjat.
Volem executar un script per tenir taules i dades en la base de dades project:
project=# \? ... \i FILE execute commands from file ...
Per tant,
project=# \i /home/joan/M10_UF2_1415/PostgreSQL/project_postgres.sql
project=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------------+-------+----------
public | address | table | postgres
public | login | table | postgres
public | milestone | table | postgres
public | person | table | postgres
public | person_with_lock | table | postgres
public | project | table | postgres
public | project_status_type | table | postgres
public | related_project_assn | table | postgres
public | team_member_project_assn | table | postgres
project=# select * from login;
id | person_id | username | password | is_enabled
----+-----------+-----------+-----------+------------
1 | 1 | jdoe | p@$$.w0rd | 0
2 | 3 | brobinson | p@$$.w0rd | 1
3 | 4 | mho | p@$$.w0rd | 1
4 | 7 | kwolfe | p@$$.w0rd | 0
És convenient entretenir-se en el script per entendre bé la relació que hi ha entre les taules. Fixa't bé en la sintaxi del script, quins tipus de dades es poden fer servir, com es creen les claus primàries i les claus forànies. Per saber els data types que podem fer servir a PostgreSQL:
per saber la descripció d'una taula, és a dir, els camps de què consta fem \d taula:
project=# \d address
Table "public.address"
Column | Type | Modifiers
-----------+------------------------+------------------------------------------------------
id | bigint | not null default nextval('address_id_seq'::regclass)
person_id | integer |
street | character varying(100) | not null
city | character varying(100) |
Indexes:
"address_pkey" PRIMARY KEY, btree (id)
"idx_address_1" btree (person_id)
Foreign-key constraints:
"address_person_id_fkey" FOREIGN KEY (person_id) REFERENCES person(id)
Per carregar el script SQL des de la línia de comandes de Linux, ho fem amb l'opció f:
$ psql -h localhost -p 5432 -U postgres -d project -f /home/joan/M10_UF2_1920/project_postgres.sql
També podem executar una comanda SQL directament:
$ psql -h localhost -p 5432 -U postgres -d project -c "select * from project"
Tasques a realitzar
Amb tota aquesta informació, ja pots realitzar unes quantes consultes.
1. Llista tots els projectes i les persones que participen en ells.
select name, concat(first_name , ' ' , last_name) as nom from project p, team_member_project_assn pp, person where p.id=pp.project_id and pp.person_id=person.id;
2. Quin és el projecte amb més participants?
select project_id, count(*) as num_participants from team_member_project_assn group by project_id order by num_participants desc limit 1;
select project_id, name, count(*) as num_participants from team_member_project_assn pa, project p where p.id=pa.project_id group by project_id, name order by num_participants desc limit 1;
project_id | name | num_participants
------------+---------------------+------------------
4 | ACME Payment System | 7
3. Llista els projectes que encara estan oberts.
4. Llista els projectes que porten gastat més diners que els pressupostats.
5. Hi ha projectes que tenen subprojectes. Digues quins són aquests projectes i subprojectes.
La solució és:
select p2.id as pid, p2.name as projecte, p1.id as subpid, p1.name as subprojecte from project p1, project p2, related_project_assn rpa where p1.id=rpa.child_project_id and p2.id=rpa.project_id; pid | projecte | subpid | subprojecte -----+-----------------------+--------+-------------------------------------- 1 | ACME Website Redesign | 3 | Blueman Industrial Site Architecture 1 | ACME Website Redesign | 4 | ACME Payment System 4 | ACME Payment System | 1 | ACME Website Redesign
Fixeu-vos bé que no surt el projecte 2 perquè no té consultes (per tal de què sortís, s'hagués pogut fer un outer join)...
Per arribar a aquesta solució s'han fet servir les següents consultes:
# select name, id, description from project;
name | id | description
--------------------------------------+----+--------------------------------------------------------------------------
ACME Website Redesign | 1 | The redesign of the main website for ACME Incorporated
State College HR System | 2 | Implementation of a back-office Human Resources system for State College
Blueman Industrial Site Architecture | 3 | Main website architecture for the Blueman Industrial Group
ACME Payment System | 4 | Accounts Payable payment system for ACME Incorporated
# select * from related_project_assn;
project_id | child_project_id
------------+------------------
1 | 3
1 | 4
4 | 1
# select p1.name, p1.id, p2.project_id from project p1, related_project_assn p2 where
p1.id=p2.child_project_id;
# select p1.id, p1.name, p2.id, p2.name from project p1, project p2, related_project_assn rpa where
p1.id=rpa.child_project_id;
# select p1.id, p1.name, p2.id, p2.name, rpa.project_id from project p1, project p2, related_project_assn rpa where
p1.id=rpa.child_project_id;
select p1.id, p1.name, p2.id, p2.name, rpa.project_id from project p1, project p2, related_project_assn rpa where
p1.id=rpa.child_project_id and p2.id=rpa.project_id;
6. Per saber les dates d'inici i fi dels projectes i subprojectes afegim els camps start_date i end_date a l'anterior consulta, tot i que les dades no són coherents (doncs les dates dels subprojectes haurien d'estar dins dels límits del projecte pare):
# select p2.id as pid, p2.name as projecte, p2.start_date, p2.end_date, p1.id as subpid, p1.name as subprojecte, p1.start_date, p1.end_date from project p1, project p2, related_project_assn rpa where p1.id=rpa.child_project_id and p2.id=rpa.project_id;
És interessant l'opció de bolcar els resultats a un fitxer. Com ja féiem amb el MySQL, és interessant que puguis executar consultes tant des de la shell de postgres com de la shell de Linux.
Comandes SQL des de la shell de Linux
Et seran útils les següents opcions:
$ man psql
...
-c command, --command=command
Specifies that psql is to execute one command string, command, and
then exit. This is useful in shell scripts. Start-up files (psqlrc
and ~/.psqlrc) are ignored with this option.
...
-e, --echo-queries
Copy all SQL commands sent to the server to standard output as
well. This is equivalent to setting the variable ECHO to queries.
...
-o filename, --output=filename
Put all query output into file filename. This is equivalent to the
command \o.
...
NOTA: si no vols introduir el password en aquesta sessió pots fer:
$ export PGPASSWORD=[password] $ echo $PGPASSWORD
ara ja no et demanarà el password. D'aquí a pocs dies aprofundirem més en la seguretat.
Estudia les diferents opcions:
$ psql -h localhost -p 5432 -U postgres -d project -c 'select * from login' $ psql -h localhost -p 5432 -U postgres -d project -e -c 'select * from login' $ psql -h localhost -p 5432 -U postgres -d project -e -c 'select * from login' -o sortida.txt $ psql -h localhost -p 5432 -U postgres -d project -e -c 'select * from login' > sortida.txt
Comandes SQL des de la shell de Postgres
Des de la shell de postgres també pots redirigir la sortida a un fitxer. Per ex,
project=# \o sortida.txt project=# select * from login; project=# select * from person;
i ara no tens cap sortida per pantalla, sinó que totes les consultes que vagis realitzant es redirigeixen al fitxer.
Si vols tancar el buffer, torna a escriure \o.
Si només vols redirigir una sentència pots utilitzar \g, que és l'àlies del ; que simbolitza l'execució de la sentència:
project=# select * from login \g sortida.txt
Formatar la sortida
Cas d'ús: volem portar la sortida d'una query a un full de càlcul. Per fer-ho, utilitzarem les opcions de formatació (\?). Concretament:
# \a Output format is unaligned. # \f ';' Field separator is ";".
Ara la sortida és;
... Blueman Industrial Site Architecture;Samantha Jones Blueman Industrial Site Architecture;Jennifer Smith ACME Payment System;John Doe ACME Payment System;Kendall Public ...
i ho podem copiar i enganxar en un full de càlcul. A l'enganxar li hem de dir que el caràcter separador és un punt i coma, i ens ho enganxarà bé en dues columnes.
També ho podem fer amb un tabulador:
# \f '\t'
però per tal de què funcioni, més que fer un copiar de la consola, hem de redirigir la sortida a un fitxer. La sessió seria:
# \f '\t' # \o sortida.txt # select name, concat(first_name , ' ' , last_name) as nom from project p, team_member_project_assn pp, person where p.id=pp.project_id and pp.person_id=person.id; # \o
i ara en el fitxer sortida.txt tenim les dades correctament tabulades, i les podem enganxar en un full de càlcul.
Una altra possibilitat interessant és formatar la sortida en format de taula HTML, amb l'opció \H. D'aquesta manera podem crear ràpidament una taula HTML amb les dades de la query;
# \H
Output format is html.
# select name, concat(first_name , ' ' , last_name) as nom from project p, team_member_project_assn pp, person
where p.id=pp.project_id and pp.person_id=person.id;
<table border="1">
<tr>
<th align="center">name</th>
<th align="center">nom</th>
</tr>
<tr valign="top">
<td align="left">ACME Website Redesign</td>
<td align="left">Kendall Public</td>
</tr>
...
creat per Joan Quintana Compte, novembre 2014
