Il database postgresql: installazione ed utilizzo

Anche il database postgresql merita un mini-howto, a causa delle sue potenzialitÃ che lo avvicinano molto ad Oracle, database commerciale e largamente utilizzato nelle realtÃ aziendali.
Postgres Ã¨ un database relazionale in grado di interpretare le istruzioni SQL come il “collega” MySQL molto piÃ¹ diffuso per databases sul Web (solitamente gestito da php).

Nella maggior parte dei casi, la configurazione di un database postgresql su piattaforma Linux richiede un utente “dedicato”.
Teoricamente si possono avere dei sistemi in cui il DBA (DataBase Administrator) non Ã¨ root, l’amministratore del sistema operativo.
E’ una scelta implementativa molto diffusa poichÃ¨ spesso succede che il DBA e l’admin del sistema non sono la stessa persona.
L’utente di default per postgresql Ã¨ l’utente postgres.
Si consiglia vivamente una password letteralmente impossibile per il DBA in quanto avrebbe le potenzialitÃ di “distruggere” ogni base di dati presente nel sistema.
Quindi passwd postgres e il gioco Ã¨ fatto.
Per quanto riguarda la locazione sul filesystem (dipendente dal tipo di compilazione, ovviamente), la directory da tenere a mente Ã¨ la home di postgres, ~/postgres, normalmente /var/lib/postgres.
La dir ~/postgres/data contiene dei file molto importanti. Ecco come si presenta la dir ../data.

sh-2.05b$ ls
PG_VERSION global pg_ident.conf postmaster.opts
base pg_clog pg_xlog postmaster.pid
pg_hba.conf postgresql.conf

Tenere a mente pg_hba.conf e la dir base.

In questa dir c’Ã¨ la sottodirectory template1 o semplicemente 1 che contiene una base di dati di partenza dalla quale poi verrano “generate” tutte le altre.
Il setting del database prevede l’esportazione di alcune variabili d’ambiente tra cui PGDATA e PGLIB.
La directory PGDATA informa tutti i programmi che utilizzano il database che la directory con da cui partire Ã¨ quella contenente i file del template1 (o come anzidetto semplicemente 1).
I programmi “utilizzatori” da ricordare sono postmaster e createdb, solo per citarne alcuni, i quali accettano il parametro -D path per indicare volta per volta la dir di cui stiamo parlando.
E’ preferibile settare la dir tramite export PGDATA=’/dir/dove/sono/i/template’ una volta per tutte.La variabile PGLIB di solito corrisponde alla dir /usr/lib/pgsql utilizzata da initdb per rintracciare global1.bki.source e local1_template1.bki.source.
Questi sono gli script per generare i file della dir iniziale del sistema di basi di dati e della template1 di cui sopra.
Dopo i dovuti settaggi possiamo avviare initdb con i seguenti parametri initdb –pglib=/usr/lib/pgsql –pgdata=/var/lib/pgsql.

Una volta che un database Ã¨ stato inizializzato, un nuovo initdb deve avvenire in una directory differente dal precedente oppure ricordarsi di svuotare la $PGDATA (se si vuole sovrascrivere i file di configurazione).
Ci sono altre opzioni di initdb: consultare le pagine man.

Avvio del demone

L’avvio effettivo del database postgres avviene per mezzo di un demone, come in tutti i sistemi di tipo client-server (in particolare server, visto che si mettono in ascolto di un client, in background).
Il demone in questione Ã¨ postmaster avviato in modo indipendente da inetd.
Lo script che permette l’avvio al boot di solito (parlo per sistemi debian in particolare) si trova in /etc/rc.d/init.d.
Nel mio caso, debian sid, Ã¨ posizionato in /etc/rc2.d/ (runlevel 2 di default sulle mie macchine .
Il demone si mette in ascolto su un socket UNIX (locale) oppure TCP, di solito sulla porta 5432.
Da ricordare che il demone, come ho giÃ detto Ã¨ postmaster il quale resta semplicemente in ascolto; il servente Ã¨ postgres.

Qual’Ã¨ la differenza?
Bene, postmaster aspetta una richiesta di connessione tramite postgres che Ã¨ il programma terminale. Dopo la ricezione della richiesta di connessione, connette effettivamente il cliente con una nuova copia del servente postgres (immaginate una fork di processo che copia il processo effettivamente “forkato”).
Anche postmaster ha delle opzioni di avvio che accetta come parametri alla chiamata.
Vi invito a consultare le solite pagine man per questi.
Un esempio interessante:

tefsom@calypsoII#su postgres -c 'postmaster -S -D/var/lib/pgsql'
L’utente root si trasforma temporaneamente in utente potgres per avviare postmaster, dissociando il programma dalla shell e specificando la directory dalla quale leggere i file della base dei dati.

Lo script di avvio del database al boot puÃ² risultare abbastanza complesso (dipende dalle modalitÃ di avvio di postmaster).

Allego il mio script qui in modo da poter leggere e capire il codice che permette l’avvio di postmaster al boot:

export PATH=$PATH:/sbin:/usr/sbin:/bin:/usr/bin

startup () {
touch ${POSTGRES_LOG:=/var/log/postgresql/postgres.log}
chown postgres.postgres $POSTGRES_LOG
# Clear away postmaster.pid if it doesn’t match a running process
if [ -f $PGDATA/postmaster.pid ]
then
running_process=$(ps -p $(cat $PGDATA/postmaster.pid | head -1) | grep -v PID | awk ‘{print $NF}’)
if [ "$running_process" != postmaster ]
then
rm $PGDATA/postmaster.pid
fi
fi
/sbin/start-stop-daemon –pidfile $PGDATA/postmaster.pid –chuid postgres –startas /usr/lib/postgresql/bin/postgresql-startup –start
# set file-max kernel parameter
. /etc/postgresql/postmaster.conf
fmaxfile=/proc/sys/kernel/file-max
if [ ! -r $fmaxfile ]
then
fmaxfile=/proc/sys/kernel/fs/file-max
if [ ! -r $fmaxfile ]
then
fmaxfile=/proc/sys/fs/file-max
if [ ! -r $fmaxfile ]
then
fmaxfile=
fi
fi
fi
if [ -n "$fmaxfile" ]
then
fmax=`cat $fmaxfile`
if [ $fmax -lt ${KERNEL_FILE_MAX:=1032} ]
then
echo ${KERNEL_FILE_MAX} > $fmaxfile
fi
fi
}
if [ ! $EUID -eq 0 ]
then
echo $0 needs to be run by root.
exit 1
fi

POSTMASTER=/usr/lib/postgresql/bin/postmaster
[ -x $POSTMASTER ] || exit

PG_CTL=/usr/lib/postgresql/bin/pg_ctl
if [ -r /etc/postgresql/postmaster.conf ]
then
. /etc/postgresql/postmaster.conf
else
echo /etc/postgresql/postmaster.conf is missing; cannot start postgresql
exit 1
fi
PGDATA=${POSTGRES_DATA:-/var/lib/postgres/data}
export PGDATA
## Use of pg_ctl to stop the postmaster:
## we use pg_ctl stop -m fast, which will abort and roll back any current
## transactions. Don’t use -immediate, because it’s equivalent to
## kill -9. Don’t use -smart because it waits for people to disconnect
## and we want to force them off when we are shutting down or else the
## system itself will do a kill -9 to get rid of us.

case “$1″ in
start)
startup
;;
stop)
echo “Stopping PostgreSQL database: postmaster”
$PG_CTL stop -w -m fast
echo “.”
;;
restart)
echo “Restarting PostgreSQL database: postmaster”
$PG_CTL stop -w -m fast
startup
echo “.”
;;
force-reload | reload)
$PG_CTL reload -D ${PGDATA}
;;
status)
$PG_CTL status
;;
*)
echo “Usage: /etc/init.d/postgresql {start|stop|restart|reload|force-reload|status}”
exit 1
;;
esac

exit 0

Database checking

Una modalitÃ che personalmente uso per sapere cosa accade in ogni momento al mio database (domestico) Ã¨ quella di dedicare una shell al controllo diagnostico.

CiÃ² che vi serve Ã¨ un avvio manuale del postmaster da una console libera che ospiterÃ anche i messaggi che il programma stamperÃ ad ogni “evento”.

Si potrebbe anche raccogliere questi messaggi in un file e controllare il file periodicamente, conservando tutti i log del nostro database e analizzandoli comodamente a casa.

Un esempio potrebbe essere il seguente:
nohup postmaster -D/var/lib/pgsql -d 1 > /miadir/database/log/miolog 2> [Invio]
Questo comando altro non fa che avviare postmaster in modalitÃ diagnostica con verbose level 1 (opzione -d) spedendo l’output al file miolog e stampando sullo standard output (per i comuni mortali, sul monitor), esattamente sulla console ottava non utilizzata, tutte le informazioni che ritroveremo nel file.

Accesso e permessi

Per finire con i settaggi, ci resta “l’ultimo” file importante da configurare.
Si tratta di pg_hba.conf nella home di postgres (l’utente DBA).
Da questo file decidiamo chi avrÃ accesso al database e se avrÃ accesso, in che modalitÃ .
Insomma Ã¨ un file di notevole importanza nel momento in cui decidiamo di rendere pubblico (anche parzialmente) il nostro database.
La configurazione Ã¨ davvero molto semplice. Lo allego qui in basso:

local all postgres ident sameuser local all all ident sameuser host all all 127.0.0.1 255.255.255.255 ident sameuser host all tefsom sweetdream 255.255.255.0 trust sameuser

local — indica che il “cliente” che accede al nostro database Ã¨ un utente locale (regolarmente riconosciuto dal sistema operativo).

all — se conoscete un po di inglese (o devo dire americano?) sta a significare tutti, cioÃ¨ tutti gli utenti locali

ident — con un pÃ² di logica ci porta alla parola ident…ification.Vuol dire che richiediamo che quell’utente si identifichi con una user e password (nella maggior parte dei casi il lavoro lo fa il sistema operativo).

sameuser — indica che i nomi usati per accedere al database (quelli locali o remoti) sono effettivamente gli stessi (same) che sono stati indicati per la gestione del DBMS (parolone che sta per DataBase Management System).

Gli accessi locali non hanno un IP, non avrebbe senso.

La seconda tipologia di accesso Ã¨ quella che inizia con la parola:
host — indica che il “cliente” che il database si aspetta Ã¨ remoto.
Essendo remoto necessita di un IP e di una netmask rispettivamente nella quarta e quinta colonna.
trust — indica che il database *deve* fidarsi di quell’utente.
Nel mio caso, il database della mia macchina (calypsoII) si fida (si deve fidare) degli accessi provenienti da sweetdream, il mio portatile.

E’ un classico esempio in cui si dovrebbe essere davvero sicuri di quello che si sta facendo perchÃ¨ la parola trust “va presa con le pinze”. Mai fidarsi a meno che la situazione non lo permetti davvero.

Nel mio caso Ã¨ stato possibile perchÃ¨ nessuno immagina il livello di paranoia da quando si dÃ alimentazione al portatile a quando si inizia a digitare il login (:-).

Gestire la base di dati

La prima operazione che un DBA deve fare Ã¨ ovviamente quella di creare il database con il comando createdb.
Il comando duale destroydb non necessita di spiegazione.
Creare una base di dati altro non fa che utilizzare la struttura di partenza template1.

Il direttorio Ã¨ (per default) ~/postgres/base/new_db dove new_db Ã¨ la nuova base creata.
Un utente non autorizzato a creare una base di dati avrÃ problemi di scrittura per template1. Questo porterÃ al fallimento del comando createdb.
Per accedere ad una base precedentemente creata si usa il comando psql nome_db

psql Ã¨ un sottoprogramma di postgresql che si occupa principalmente della base di dati passata come primo argomento.

Le principali opzioni di psql sono:
h[invio] l'help in linea ? l'help in linea h comando l'help in linea per comando, sintassi per il suo utilizzo i miofile si carica il contenuto del file "miofile" il quale di solito contiene istruzioni SQL d elenca le tabelle presenti nella base corrente d tabella elenca i campi della tabella indicata nel parametro q permette di uscire da psql

Chiarisco con un esempio:

la mia base di dati Ã¨ archiviocd costruita personalmente per archiviare i miei numerosi mp3 e file ogg di oltre 120 cd (ogni cd contiene circa 200 file, tanto per avere un’idea quantitativa).

postgres@calypso$psql archiviocd Welcome to psql 7.4.1, the PostgreSQL interactive terminal.

Type: copyright for distribution terms
h for help with SQL commands
? for help on internal slash commands
g or terminate with semicolon to execute query
q to quit

archiviocd=# d

…
…
…
… da completare

frag's

Yet Another Personal Page