ASIX-M10-UF2. PostgreSQL. Instal.lació, configuració i ús del PostgreSQL

De Wikijoan
Revisió de 20:16, 15 gen 2019; Joan (Discussió | contribucions)
(dif) ←Versió més antiga | Versió actual (dif) | Versió més nova→ (dif)
Dreceres ràpides: navegació, cerca

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:

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:

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:

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)

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

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines