PostgreSQL

PostgreSQL e' considerato il piu' completo e robusto RDBMS disponibile come free software. Le sue prestazioni ed affidabilita' sono paragonabili a quelle dei piu' diffusi RDBMS commerciali. I suoi principali punti di forza sono:

Ma torneremo su questo alla fine... come introduzione basta cosi'!
Questo documento e' stato preparato inizialmente con la versione 8.1 di PostgreSQL ma e', mutatis mutandis, valido anche per le altre versioni (NdE verificato ed aggiornato fino alla 9.2). Un documento piu' completo, ma anche un poco piu' difficile, e' Qualcosa in piu' su PostgreSQL (9.0).

Installazione

L'installazione con yum o apt-get e' semplicissima! Ad esempio:

yum search postgresql
yum install postgresql-server.x86_64
Fatto!

L'installazione con gli RPM e' semplice.
E' sufficiente scaricare il software corretto dal sito PostgreSQL (eg. RPM). In una configurazione tipica sono necessari solo gli RPM:
RPMDescrizione
postgresql-libs-8.1.0-env.rpmLibrerie
postgresql-8.1.0-env.rpmFiles di base
postgresql-server-8.1.0-env.rpmServer
Scaricato il software, per effettuare l'installazione dare i comandi rpm -il rpm_file Per controllare i pacchetti installati: rpm -qa | grep -i post. Infine, per rimuove i pacchetti il comando e' rpm -e rpm_file. Per attivare la base dati il comando e': /etc/rc.d/init.d/postgresql start che ovviamente puo' essere attivato anche all'avvio del sistema. Se qualcosa non funziona? Continuate a leggere!

Anche l'installazione del tarball di PostgreSQL e' semplice e veloce. Effettuato il download dell'ultima versione (dal sito PostgreSQL), decompresso e scaricato il tar, i passi da seguire sono i seguenti:

./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

Si tratta in pratica del lancio di qualche script, della creazione di una directory, di un utente, di una compilazione (eseguita in automatico). Quindi vengono fatti partire i processi di background di PostgreSQL, inizializzato un DB e lanciato l'interprete SQL.
Se si utilizzano i path e gli utenti standard i comandi riportati funzionano praticamente su tutte le versioni di Linux e, con poco sforzo, su ogni Unix.

In mezz'ora (10 minuti su un sistema veloce) tutto e' fatto e con psql db_name si accede all'interprete SQL!

Configurazione

Una volta installato PostgreSQL puo' essere utilizzato immediatamente. Pero'... per ragioni di sicurezza l'accesso e' consentito solo localmente. Inoltre e' possibile effettuare un tuning fine della base dati a secondo del suo utilizzo.
Quindi vediamo i file di configurazione! Il file postgresql.conf (di default su /var/lib/pgsql/data) e' il principale file di configurazione e nel seguito sono riportati alcuni parametri interessanti:

listen_addresses = '*'		# listen from all the network
max_connections = 100		# max number of user connection
shared_buffers = 10000          # min 16, at least max_connections*2, 8KB each
checkpoint_segments = 3         # logging
lc_messages = 'en_US.UTF-8'     # locale for system error message strings

Il file pg_hba.conf contiene le abilitazione specifiche dei client agli utenti/database presenti:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust
# Meo's PC
host    all         all         10.0.0.84/32          trust
# Everyone from everywhere with password
## host    all         all         0.0.0.0/0             password
E' quindi ovvio che senza una corretta configurazione del parametro listen_addresses e del file pg_hba.conf da rete (la porta di default e' la 5432) non si accede! Bene ora che sono configurati gli accessi da remoto si puo' accedere anche dall'esterno (NdE anziche' il metodo password e' piu' opportuno utilizzare md5: in tal modo la password non viene trasmessa in chiaro dal client). Per effettuare la rilettura dei file di configurazione, senza riavviare la base dati il comando e': pg_ctl -D /var/lib/pgsql/9.0/data reload

Utilizzo

Per accedere con l'interprete dei comandi a PostgreSQL il comando e' psql [db_name]. E' a questo punto possibile utilizzare i normali comandi SQL. Ogni comando va terminato con un ; e e per uscire dall'interprete utilizzare il ^D. Per ottenere l'help sull'SQL: \h, per ottenere l'help sul psql: \?, per ottenere, ad esempio, l'elenco delle tabelle: \dt, la descrizione di una tabella: \d table_name, ...
L'SQL di PostgreSQL e' molto completo ed aderente agli standard (SQL:2008). E' quindi molto semplice da utilizzare per chiunque conosca l'SQL fornito da un altro database relazionale.

Naturalmente oltre all'interprete di comandi si utilizzano anche client grafici... continuate a leggere!

Database, Schema ed utenti

Su un database cluster PostgreSQL vengono creati uno o piu' database; il database postgres e' sempre presente. Ogni database ha un suo catalogo (data dictionary). All'interno di ogni database vengono creati uno o piu' schema; lo schema public viene creato per default. All'interno degli schema vengono create le table. Per identificare una tabella si utilizza la sintassi schema.table.
Su un database cluster PostgreSQL vengono autorizzati piu' utenti e ruoli (dalla versione 8.1 sono stati unificati). Per impostare la password di un utente il comando SQL e': alter user postgres with password 'postgres'; . Un utente puo' lavorare sui diversi database se e' autorizzato, ma deve scegliere su quale database operare al momento della connessione.
Un utente ha sempre il diritto di effettuare modifiche sugli oggetti da lui creati. Per dare i diritti desiderati ad altri utenti viene utilizzata il comando SQL GRANT. E' anche possibile concedere ad altri utenti il diritto di fornire autorizzazioni con WITH GRANT OPTION.
Gli oggetti creati vengono posti, per default, nello schema public. Ma e' possibile modificare il default con l'impostazione SET search_path TO myschema,public;

La scelta su come isolare utenti ed applicazioni (su database differenti, su schema differenti, tutti assieme appassionatamente nello schema public) e' una scelta del DBA che dipende da molti fattori... PostgreSQL permette tutte le possibilita': dal completo isolamento alla condivisione totale.

Amministrazione

pgAdmin e' un completo e semplice strumento Client/Server per l'amministrazione del database. Definito il server cui accedere e la porta da utilizzare la connessione e' immediata.

Altro strumento e' phpPgAdmin che consente la gestione del DB con un'interfaccia web (naturalmente richiede il PHP).

Come tutti i tool grafici sono piu' semplici da utilizzare che da spiegare quindi... provate!

Start/Stop

L'attivazione/disattivazione dei processi e dell'intero ambiente PostgreSQL non puo' essere effettuata da pgAdmin e va eseguita dal sistema server. Oltre che lanciando direttamente il comando (/usr/local/pgsql/bin/postmaster) l'RDBMS viene piu' correttamente attivato con lo script pg_ctl. Le principali opzioni sono:

  pg_ctl start   [-w] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
  pg_ctl stop    [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
  pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o "OPTIONS"]
  pg_ctl reload  [-D DATADIR] [-s]
  pg_ctl status  [-D DATADIR]
  pg_ctl kill    SIGNALNAME PID

Dove SHUTDOWN-MODE puo' essere:

Quindi lo stop -m immediate corrisponde ad shutdown abort su un Oracle RDBMS e non ad uno shutdown immediate! Anche nei DB si trovano i false friends...

L'installazione da RPM configura gli script di avvio al boot e di gestione. Quindi si puo' controllare lo stato con il comando standard RH: service postgresql status.

Normale amministrazione...

Su ogni RDBMS vi sono una serie di attivita' che ricadono nella normale amministrazione. Come in casa si fa pulizia e si butta la spazzatura tutti i giorni, ... (voi lo fate? io no, ma era cosi' per dire), cosi' su un DB si fanno salvataggi, si controllano i log e si effettuano le riorganizzazioni, ...

Naturalmente la modalita' piu' semplice per un DBA di effettuare i backup e' quella di non farli! O meglio di lasciare il compito ai backup di sistema ovviamente avendo l'accortezza di effettuare lo shutdown del database. (NdE non fate cosi' sui vostri DB).
Con pg_dump si effettua un salvataggio logico dei dati dell'intero database o di alcune tabelle, in formato testo, proprietario o compresso. E' lo strumento maggiormente utilizzato per i backup.
Dalla versione 8.0 e' possibile effettuare backup fisici on-line e Point-In-Time Recovery... questo per basi dati su sistemi con livelli di servizio elevati (eg. 7x24).

Le "normali" attivita' di amministrazione sui dati sono:

PostgreSQL e' molto sensibile alla ricostruzione di indici e, su query con tabelle di grandi dimensioni, all'analize.

Il log del processo postamster fornisce spesso utili indicazioni e va controllato con frequenza. Ad esempio:

LOG:  checkpoints are occurring too frequently (29 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
... indica un'alta frequenza di attivita' di checkpoint (per default il checkpoint avviene quando sono scritti 3 segmenti di redo o sono passati 300 secondi dall'ultima sincronizzazione). Non si tratta di un errore ma di un'indicazione utile per il tuning della base dati!
Un'ultima, banale, ma spesso dimenticata avvertenza: lo spazio! Quindi ripulire i log, monitorare l'utilizzo ed evitare i File System Full.

Programmazione

Le modalita' di programmazione utilizzabili con PostgreSQL sono molto ampie e complete.

Sono stati sviluppate interfacce specifiche per utilizzare PostgreSQL come:PL/pgSQL, PL/Tcl, PL/Perl, PL/Phyton, ...
Naturalmente si puo' utilizzare il linguaggio Java accedendo al DB con un driver JDBC.
Molto efficiente e' la libreria libpq nella programmazione in C o in C++.
E' possibile utilizzare la modalita' Embedded SQL: e' fornito il precompilatore ecpg che converte i file *.pgc.

Interessante, per chi e' abituato ad utilizzare funzioni e package Oracle (eg. nvl, dbms_output.putline, ), e' anche l'implementazione di queste in PostgreSQL (orafce).

Architettura

Dal punto di vista dell'architettura di PostgreSQL gli elementi di maggior interesse sono la struttura dei processi e l'organizzazione dei file del Clustered Database.

La struttura dei processi e' visibile con i normali comandi di sistema operativo (ps -efa):

/usr/local/admgis/pgsql-8.3.4/bin/postgres -D /pgdata/data
postgres: logger process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: stats collector process

postgres: geoeng XXXTST 10.0.0.69(37835) idle
postgres: postgres test 10.0.0.84(1474) idle
postgres: postgres test [local] idle in transaction
postgres: postgres test [local] COMMIT  

Nell'esempio riportato vi sono alcuni processi di sistema ed un processo per ogni connessione al DB. Il primo processo postgres e' il processo principale che si occupa della gestione delle connessioni ed e' quindi il "padre" di tutti i processi. Su ogni processo utente e' riportata l'origine e l'attivita' in corso... molto comodo per controllare le attivita' presenti su PostgreSQL anche dal sistema operativo.

Il Clustered Database e', in pratica, il file system che contiene i dati. All'interno vengono mantenuti i file di configurazione, i log delle transazioni e le strutture interne della base dati che contengono tabelle ed indici.

All'interno della base dati viene mantenuto un ricco Catalog che consente di controllare con query SQL gli oggetti presenti nella base dati (eg. pg_database, pg_classes, pg_tables, ...) e l'andamento delle attivita' (eg. pg_locks, pg_settings, pg_statistic, pg_stats,...).
Quando viene sottomesso uno statement SQL l'ottimizzatore determina il query tree da utilizzare con un algoritmo genetico basato sulle statistiche. PostgreSQL utilizza un ottimizzatore cost-based.
La gestione delle transazioni avviene con la tecnica del REDO logging (Write-Ahead Logging).

Configurazioni particolari

PostgreSQL puo' essere utilizzato in configurazioni complesse. Nel seguito vengono dati alcuni cenni sull'utilizzo della replicazione e del cluster. Una nota: nella terminologia PostgreSQL il termine database cluster si riferisce all'insieme di basi dati gestite da una singola istanza ed inizializzati con il comando initdb. Nel seguito parleremo di failover cluster ovvero ambienti e sistemi che forniscono soluzioni di HA (High Availability).

Slony1 e' un modulo aggiuntivo che consente la replicazione master-slave tra nodi. Si tratta di uno strumento di replicazione asincrona Master/Multi-Slave con granularita' a livello di tabella. Quindi e' possibile configurare tabelle che vengono replicate dal DB Master verso uno o piu' DB Slave su cui i dati sono accessibili in lettura. L'implementazione e' basata su trigger e trasmissione asicrona.
La replicazione asincrona presenta diversi vantaggi.
Il carico sulla rete non e' elevato, la rete puo' anche avere un'alta latenza, i server e le connessioni possono essere disattivati e la replicazione riprende automaticamente quando la connessione e' nuovamente possibile. E' quindi possibile utilizzare la replicazione sia per distribuire i dati
Slony1 consente il cambio di ruolo (uno Slave puo' diventare un Master) in modo consistente sui dati. Slony1 consente anche il failover ma, in questo caso, e' possibile che alcune transazioni risultino perse poiche' la replicazione e' asincrona.

PGCluster e' l'ambiente di replicazione su PostgreSQL che supporta il multi master e la replicazione sincrona. In questo caso tuttavia il peso computazionale e' maggiore ed il rilascio delle release e' piu' lento.

PostgreSQL puo' essere definito come servizio in un failover cluster. Tale modalita' non e' nativa del DB ma puo' essere implementata in modo relativamente semplice sui piu' diffusi failover cluster Linux e su cluster commerciali. Naturalmente la configurazione in cluster richiede l'utilizzo di storage condiviso (o replicato) tra i nodi.
E' tuttavia anche possibile una configurazione con minori requisiti HW utilizzando la replicazione dei dati ed un cluster per la sola definizione dell'IP virtuale per l'accesso al nodo corrente. Anche se si tratta di una soluzione non completamente sicura puo' essere utilizzata come alternativa a piu' complesse soluzioni di disaster recovery.

PostgreSQL

La versione piu' recente disponibile come versione di produzione e' la 9.2 [NdE a settembre 2012, ora, gennaio 2015 e' la 9.4].

Le release di PostgreSQL si sono sempre distinte per la qualita' e per la completezza di funzionalita' (eg object-oriented). Da sempre Postgres e' considerato il database Open Source piu' completo e piu' robusto. Ma vediamo un po' di storia...
Le radici di PostgreSQL risalgono al lontano 1977 quando, nella famosa universita' di Berkeley, venne sviluppato l'RDBMS Ingres. Nel 1986, sponsorizzato dal DARPA, parte un nuovo progetto dal nome di The Berkeley POSTGRES Project. Il nome stesso del progetto indica un'evoluzione rispetto ad Ingres: Postgres infatti e' un database Obejct-Relational. Nel 1994 viene realizzato l'interprete SQL che verra' poi rilasciato come Open Source col nome definitivo di PostgreSQL.
Le prestazioni ed affidabilita' di PostgreSQL sono da subito paragonabili a quelle dei piu' diffusi RDBMS commerciali. Rispetto ad altri database Open Source (eg. MySQL) risulta molto piu' completo ma anche piu' complesso.

La versione la 8.1 ha introdotto il 2 Phase Commit, la definizione dei parametri come IN/OUT/INOUT nelle funzioni SQL, la gestione dei roles, il partizionamento delle tabelle oltre a miglioramenti sull'ottimizzatore e sulle performance in generale. Nella versione 8.2 sono stati introdotti: multirow DML, index DDL during DML, SQL:2003 statistical functions, faster locking, FILLFACTOR, monitor/logging.
La 8.3, molto stabile e diffusa, ha introdotto: Full text search, XML, updatable cursors, asynchronous commit, dedicated writes. La 8.4 e' stata ottimizzata per migliorare le performances.

La versione 9.0 aveva introdotto alcune importanti funzionalita' per l'utilizzo Enterprise come la Streaming Replication e l'Hot Stand-by. In precedenza funzionalita' simili erano disponibili solo installando moduli esterni. La 9.1 (Settembre 2011) ha aggiunto estensioni sulla replicazione (Synchronous replication) e sull'SQL PL/pgSQL (CREATE TABLE IF NOT EXISTS, INSTEAD OF triggers on views, FOREACH).
La versione 9.2 (Settembre 2012) prosegue nell'arricchimento delle funzionalita' sulla replicazione (Cascading replication, backup sui server di standby con pg_basebackup), sulle ottimizzazioni (index-only scan) e sulla gestibilita' (ulteriori informazioni sulla pg_stat_activity).

Punti di forza

E' il momento di ritornare sui vantaggi di PostgreSQL... il potente object-relational database managment system:

C'e' anche da ricordare che, a partire dalla versione 8.0, PostgreSQL e' anche disponibile in modo nativo su Server Windows... ma non so se questo sia davvero un pregio o l'unico difetto ;-)


Testo: PostgreSQL
Data: 15 Maggio 2005
Versione: 1.0.9 - 1 Dicembre 2013
Autore: mail@meo.bogliolo.name