L'ultima release di questo DBMS (Database Management System), rinomato per la potenza e la completezza di funzionalità, ci fornisce l'occasione per dedicare una serie di articoli a quello che è considerato un vero gioiello tra i software Open Source.
Freephp.html.it si è PHP e PostgreSQL, da un lato della storia del progetto, dall'altro dell'utilizzo con PHP (in alternativa al più celebre MySQL) pertanto in questo articolo ci focalizzeremo principalmente sul database e sulle funzionalità vecchie e nuove.
PostgreSQL 8 è disponibile, per la prima volta, anche in una versione nativa per Windows ma tradizionalmente è sempre stato legato al mondo Unix, e apprezzato in modo particolare dai seguaci del pinguino, quindi è Linux che utilizeremonei nostri esempi.
Le caratteristiche
PostgreSQL introdusse per primo funzionalità che dai database commerciali furono adottate solo in secondo tempo, e oggi le sue caratteristiche lo ascrivono a tutti gli effetti tra i DBMS di tipo "enterprise".
Database object-relational: ogni tabella definisce una classe. PostgreSQL implementa l'ereditarietà tra tabelle, inoltre funzioni e operatori sono polimorfici.
- Segue gli standard SQL92 e SQL99: se ci sono deviazioni dagli standard riguardano caratteristiche aggiuntive proprie di PostgreSQL
- Supporto per la localizzazione
- Supporto per le transazioni ACID basato sul Multi-Version Cuncurrency Control (MVCC): sistema che garantisce prestazioni migliori rispetto all'utilizzo dei lock tipico di altri DBMS.
- Supporto per Stored procedures e Triggers: registrabili in diversi linguaggi (PL/pgSQL, PL/Perl, PL/TCL, PL/Python...)
- Integrità referenziale
- Vincoli di colonna e di tabella
- Tipi di dati complessi (tipi geometrici, array, array multidimensionali...)
- Subselects
- Views
- Ricerca fulltext
- Estrema estendibilità: tipi di dati, funzioni e operatori definiti dall'utente
- Supporto per numerose API client: è possibile interfacciarsi a PostgreSQL attraverso vari linguaggi che vanno da C a PHP
PostgreSQL 8, ancora più avanzato
Per chi utilizza da tempo il DBMS in ambiente Linux non ci sono novità sconvolgenti, gli sviluppatori hanno lavorato soprattutto al perfezionamento di caratteristiche già collaudate. Elenchiamo brevemente le novità di rilievo:
- Savepoints e transazioni annidate
Aumenta il controllo sulle transazioni con l'introduzione della possibilità di rollback parziale, mentre nelle versioni precedenti in caso di errore è necessario annullare l'intera transazione. - Point-In-Time recovery
Rende questo DBMS ancora più affidabile, migliorando le capacità del server per quanto riguarda il backup e la capacità di ricostruire la situazione precisa precedente al momento del danneggiamento dei dati. - Tablespaces
L'introduzione di questa caratteristica consente di stabilire il tipo di filesystem da utilizzare per salvare database, schemi, tabelle o indici. - Prestazioni
Un miglioramento generale delle performance e in modo particolare dell'impatto di operazioni che devono essere eseguite periodicamente (come VACUUM) - Supporto nativo per Windows
Da oggi PostgreSQL è un database multi-piattaforma, mentre in precedenza era necessario ricorrere all'emulatore Cygwin o, per il supporto nativo, a una delle versioni a pagamento commercializzate da terze parti.
MySQL Vs PostgreSQL
Nel parlare di database open-source non possiamo esimerci da un confronto con il più celebre della categoria, ovvero MySQL: si tratta di un argomento suscettibile di scatenare "flame" tra i più accesi, quindi cercheremo di descrivere obiettivamente le caratteristiche che differenziano l'uno dall'altro.
In primo luogo dobbiamo sottolineare che PostgreSQL è un progetto portato avanti da una comunità indipendente, alla quale però contribuiscono anche aziende che intendono servirsi di questo DBMS per le proprie finalità (è di questi giorni la notizia che anche il colosso Fujistsu si è impegnato nello sviluppo di alcune nuove funzionalità che verranno rilasciate a sorgente aperto). Inoltre è tutelato dalla flessibile licenza BSD (Berkeley Software Distribution), permissiva a tal punto che non mancano software-house che commercializzano versioni proprietarie (e a sorgente chiuso) del database.
MySQL ha una storia molto più recente ed è il prodotto di un'azienda, MySQL AB, che rilascia questo software con una doppia licenza, GPL (Gnu Public License) e commerciale: la seconda si rende necessaria quando, nel distribuire un'applicazione che necessita di questo RDBMS, non si vogliano fornire anche i sorgenti.
PostgreSQL è un server di database decisamente completo, noto sia per la ricchezza di funzionalità che per l'estrema affidabilità in situazioni critiche....oltre che, in origine, per una certa lentezza; al contrario, MySQL trova impiego prevalentemente nell'ambito WEB ed è conosciuto per le eccezionali performance, ottenute in buona misura rinunciando ad implementare moltissime caratteristiche tipiche dei database tradizionali.
In realtà questa contrapposizione si attenua costantemente con il passare degli anni, tanto da poter dire che i due DBMS stanno percorrendo strade convergenti: infatti se da un lato MySQL sta gradualmente dotandosi di caratteristiche avanzate come il supporto delle transazioni (ottenuto attraverso le tabelle innoDB), dall'altro gli sviluppatori di PostgreSQL sin dalla versione 7.1 hanno prestato particolare attenzione all'incremento delle performance, sforzo ancora più evidente nelle versioni 7.4.x e nella 8.0 appena uscita.
Infine MySQL è tradizionalmente un DBMS multi-piattaforma mentre, prima dell'ultima release, Postgres era disponibile soltanto per sistemi Unix-like.
Installazione e configurazione iniziale
Descriveremo l'installazione di PostgreSQL 8.0.3 partendo dalla compilazione dei sorgenti ( scelta che ci consentirà un controllo completo sulla configurazione) e il primo passo naturalmente consiste nello scaricare l'archivio dal sito ufficiale (www.postgresql.org) . Gli archivi tar disponibili sono sempre 5: quello denominato postgresql-8.0.3.tar.gz è il più completo e riunisce il contenuto di tutti gli altri, cioè comprende sia il codice sorgente base che la documentazione e le caratteristiche opzionali.
Terminato il download ed estratti i sorgenti in una directory a scelta (nel nostro caso sarà /usr/local/src) vale sempre la pena di leggere le istruzioni per l'installazione riportate nel file "INSTALL": la prima raccomandazione è quella di assicurarsi di utilizzare GNU make e non altri programmi, solitamente si tratta dell'impostazione di default in Linux ma possiamo sempre accertarcene eseguendo make -v.
Altro prerequisito importante è la presenza della GNU readline library che fornirà al client testuale psql (il principale strumento di amministrazione in PostgreSQL) le funzionalità di autocompletamento e history dei comandi.
L'installazione passo passo
$tar -zxvf postgresql-8.0.3.tar.gz
$ cd /usr/local/src/postgresql-8.0.3
$ ./configure ?enable-locale ?enable-nls ?with-perl ?with-python
Come di consueto "./configure --help" ci fornisce un elenco completo di tutte le opzioni di configurazione, le tre appena riportate servono rispettivamente ad abilitare il supporto per la localizzazione, la possibilità di visualizzare i messaggi del server nella lingua preferita e i moduli PL/Perl e PL/Python (necessari se desideriamo avvalerci della possibilità di registrare stored procedures anche utilizzando questi due linguaggi). Se tutto è andato a buon fine possiamo proseguire con la compilazione e l'installazione
$ gmake
$ su
# gmake install
Se non abbiamo specificato un ?prefix in fase di configurazione il percorso d'installazione di default è /usr/local/pgsql
Ora dobbiamo assicurarci che il percorso in cui si trovano gli eseguibili di PostgreSQL sia compreso nel PATH di sistema, la cosa migliore è modificare lo script di shell /etc/profile (o equivalente).
PATH="/usr/local/pgsql/bin:$PATH"
export PATH
Il passo successivo è creare l'utente con il quale verrà eseguito il server, di solito l'utente "postgres", e la directory in cui immagazzineremo i dati. Quest'ultima, una volta creata, deve essere assegnata all'utente postgres che diverrà il superuser a livello del database
root# adduser postgres
root# mkdir /usr/local/pgsql/data
root# chown postgres /usr/local/pgsql/data
PostgreSQL organizza i dati secondo una determinata gerarchia (figura1), al livello più alto si trova il cluster (cioè una collezione di database), la directory appena creata deve essere inizializzata come cluster, e lo faremo con il comando
initdb --pgdata=/usr/local/pgsql/data ?pwprompt
oppure creando un'apposita variabile d'ambiente
export PGDATA="/usr/local/pgsql/data"
initdb ?pwprompt
L'inizializzazione ha anche l'effetto di fornire all'utente postgres i permessi di accesso esclusivi alla directory cluster, l'opzione --pwpromtp serve a richiedere la generazione di una password di accesso per il superuser postgres.
Prima di avviare il server ci sono altri importanti passi da compiere nella configurazione.
Dopo aver trattato le caratteristiche, le differenze da MySQL e l'installazione di PostgreSQL, andiamo avanti esaminando la configurazione e la gestione degli utenti e dei diversi database.
Autenticazione dei client
Nella situazione attuale, una volta che il DBMS verrà avviato, qualunque utente di sistema potrà accedere ai database attraverso il client psql (descritto più avanti) impersonando qualsiasi utente, persino il superuser postgres.
Questo sistema di autenticazione viene detto trust ed è il meno restrittivo dei 5 conosciuti da PostgreSQL, è appropriato per le connessioni locali su workstation utilizzate da un unico utente.
L'autenticazione dei client è controllata dal file pg_hba.conf (hba sta per "host-based authentication") che viene creato di default in /usr/local/pgsql/data/ al momento dell'inizializzazione del cluster.
Si tratta di un normale file di testo nel quale ad ogni riga corrisponde un record che specifica nell'ordine:
- il tipo di accesso
- l'ip (o un range di ip)
- il nome del database
- l'utente
la modalità autenticazione da utilizzare per i client che corrispondono ai parametri appena definiti (vedi box1).
Per modificare le impostazioni di default e far sì che venga richiesta una password a chiunque cerchi di accedere DBMS con un utente accreditato, è sufficiente inserire "password" (anzichè "trust") nell'ultimo parametro, le password vengono immagazzinate nella tabella pg_shadow del database di sistema template1.
Ecco un estratto del file pg_hba.conf modificato per richiedere una password ad ogni tentativo di connessione.
#TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all all password # IPv4-style local connections: host all all 127.0.0.1 255.255.255.255 password # IPv6-style local connections: host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff password
Questa configurazione consente l'accesso ad ogni database da parte di qualsiasi utente, purchè avvenga previa autenticazione e da locale (sia che si tratti di connessioni attraverso i socket Unix che TCP/IP).
È possibile inserire ulteriori regole a cascata tenendo presente che, poiché il parsing del file avviene dall'alto verso il basso, l'ordine dei record è importante: le direttive iniziali devono essere più precise e restrittive sui parametri di connessione e più indulgenti sui metodi di autenticazione, mentre nelle direttive successive il rapporto si inverte progressivamente.
Configurazione generale
I parametri di configurazione non relativi all'autenticazione si trovano nel file /usr/local/pgsql/data/postgresql.conf grazie al quale è possibile impostare tutte le opzioni di runtime del DBMS, che sono davvero molte; per motivi di spazio citiamo soltanto l'opzione tcpip_socket, che deve essere impostata a true per consentire le connessioni TCP/IP da parte dei client, in caso contrario saranno ammessi soltanto i tentativi di connessione socket Unix locale.
Avvio del postmaster
Il postmaster è il server multiutente di PostgreSQL: affinchè un'applicazione client possa accedere a un database deve connettersi ad un postmaster avviato, a sua volta il postmaster avvia un processo separato per ogni connessione e gestisce le comunicazioni tra questi processi.
Sulla stessa macchina possono convivere più postmaster, quindi più istanze di PostgreSQL, purchè ad ognuno siano stati assegnati un differente cluster di database (ad es. initdb --pgdata=/usr/local/pgsql/data2) e, naturalmente, una diversa porta (default 5432).
Il metodo consigliato per avviare ed arrestare PostgreSQL è servirsi dello script di shell pg_ctl (che si trova in /usr/local/pgsql/bin), teniamo presente che il server può essere avviato soltanto dal superuser postgres (ed è bene che sia così).
[postgres@badbeast postgres]$ pg_ctl start -D /usr/local/pgsql/data
postmaster successfully started
[]$ LOG: il sistema database è stato arrestato alle 2004-09-06 12:32:08 CEST
LOG: checkpoint record: 0/9BD5E4
LOG: il redo record è alle 0/9BD5E4; l'undo record è alle 0/0; arresto TRUE
LOG: next transaction ID: 541; next OID: 17142
LOG: sistema database pronto
Specificare il percorso del cluster attraverso l'opzione -D non è necessario se in precedenza abbiamo settato correttamente la variabile d'ambiente PGDATA. pg_ctl --help elenca tutte le opzioni di pg_ctl.
PSQL
È il client a riga di comando che viene distribuito insieme al DBMS e consente la completa amministrazione dei database: psql ?help fornisce la lista di tutte le opzioni disponibili.
Se un utente Unix lancia psql senza specificare alcun argomento, il client cercherà di collegarsi a un database avente il nome dell'utente stesso.
[fabio@badbeast fabio]$ psql
psql: FATAL: il database "fabio" non esiste
Anche nel caso seguente il tentativo fallisce: infatti anche se il database template1 esiste (è un database di sistema) "fabio" non è un utente PostgreSQL.
[fabio@badbeast fabio]$ psql template1
psql: FATAL: user "fabio" does not exist
Allo stato attuale, non avendo ancora creato utenti né database, l'unico modo in cui è possibile utilizzare psql è impersonare il superuser postgres e accedere al database di sistema template1.
[fabio@badbeast fabio]$ psql template1 -U postgres
Password:
Benvenuto in psql 8.0, il terminale interattivo per PostgreSQL.
Digita: copyright per i termini di distribuzione
h per gli aiuti sui comandi SQL
? per gli aiuti sui comandi interni
g oppure punto e virgola per eseguire una query
q per uscire
template1=>
Aggiungere un nuovo utente
Un utente PostgreSQL può essere creato in due modi, attraverso psql o usando lo script di shell createuser, naturalmente in entrambi i casi il primo utente dovrà essere creato dal superuser.
Con psql
template1=# CREATE USER testuser1 WITH PASSWORD 'testpass' CREATEDB ;
L'utente testuser1 ha la facoltà sia di creare nuovi database che altri utenti, ma visto che fornire anche quest'ultima possibilità potrebbe essere un po' azzardato siamo sempre in grado di revocare il permesso in questione.
template1=# ALTER USER testuser1 NOCREATEUSER ;
O addirittura possiamo eliminare del tutto l'utente con
template1=# DROP USER testuser1 ;
Per reperire informazioni sugli utenti PostgreSQL è necessario interrogare la tabella di sistema pg_shadow (accessbile solo al superuser) appartenente a template1, oppure la tabella pg_user (che rappresenta una versione ridotta della precedente ed è accessibile a tutti).
Creazione dei database
Una volta creato un utente possiamo utilizzarlo per effettuare il login attraverso psql
[fabio@localhost fabio]$ psql template1 -U testuser1
Password:
creare un nuovo database
template1=> CREATE DATABASE testdb ;
verificare l'esistenza del nuovo database
testdb=> l
Lista dei database
Nome | Proprietario | Codifica
-----------+--------------+-----------
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
testdb | testuser1 | SQL_ASCII
(3 righe)
e collegarci ad esso
template1=> c testdb ;
Sei collegato al database "testdb".
testdb=>
Quando un utente crea un database ne diventa automaticamente il DBA (database administrator) e sarà in grado di assegnare i diritti sugli oggetti di quel database ad altri utenti. Ad esempio per assegnare tutti i privilegi sul database testdb all'utente testuser2
testdb=> GRANT ALL ON testdb TO testuser2 ;
Anziché assegnare i privilegi ad ogni singolo utente è possibile creare un gruppo, assegnare ad esso alcuni utenti e fornire i privilegi al gruppo nel suo insieme.
testdb=> GRANT ALL ON testdb TO GROUP testgroup ;
Conclusioni
Abbiamo installato il server di database e preso confidenza con esso attraverso l'interfaccia da riga di comando (psql), non mancano comunque GUI (Graphic User Interface) più intuitive come l'ottimo pgAdmin III o phpPgAdmin (l'equivalente di PhpMyAdmin per MySQL). Possiamo reperire l'elenco completo di una serie di utili tool di amministrazione sul sito ufficiale.
Nei prossimi articoli proseguiremo la nostra panoramica affrontando le caratteristiche object-relational e la scrittura di stored procedures e triggers in PL/pgSQL.
