====== Utilizzo di PostgreSQL ======

**[[http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL|MySQL vs PostgreSQL]]**

L'utente //postgres// (del database, non del sistema ospite) è l'amministratore del database stesso e per default ha tutti i privilegi. Alcune operazioni possono essere fatte con apposite utility da riga di comando, altre invece si devono fare dal monitor interattivo ''**psql**'' (applicazione client). In entrambi i casi il server PostgreSQL deve essere in funzione.

===== Per connettersi a un database, es. template1 =====

Durante l'installazione di PostgreSQL viene creato il database //template1//, per connettersi al server è necessario collegarsi ad un database, se non se ne desidera uno specifico si usa appunto //template1//:

<code>psql template1</code>
utilizza UNIX-Socket, si presenta come l'utente Unix corrente senza password. Quindi se si vogliono tutti i privilegi lo si deve fare non da utente root ma da utente postgres (eseguire prima ''**su - postgres**'').

<code>psql -h localhost -U username -W template1</code>
si connette al database template1 su localhost (127.0.0.1) tramite TCP/IP. Si presenta come username e chiede una password prima della connessione.

Il controllo di accesso (chi, da dove, come, con quale password, ecc.) viene stabilito dal file ''/etc/postgresql/pg_hba.conf''.

Per uscira dall'interprete si usa ''**\q**''. Dentro l'interprete si può chiedere aiuto sui comandi dell'interprete stesso (slash commands) con ''**\?**'' oppure si chiede aiuto sui comandi SQL con ''**\h**''.

===== Riassunto comandi utili =====

<code>
Vedere i database esistenti:    \l
Connettersi ad un database:     \connect dbname
Vedere le tabelle di un db:     \dt
Vedere utenti presenti:         SELECT * FROM pg_user;
Crea utente con password:       CREATE USER "username" PASSWORD 'secret';
Assegnare un DB ad un utente:   UPDATE pg_database SET datdba=104 WHERE datname='dbname';
Creare un DB con tutto:         CREATE DATABASE dbname OWNER dbuser ENCODING 'UTF8';
Con PG 8.4 ci sono più scelte:  CREATE DATABASE dbname OWNER dbuser LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8'
                                TEMPLATE template0 ENCODING 'UTF8';
Assegna privilegi su tabella:   GRANT ALL ON tablename TO "username";
Cambiare password utente DB:    ALTER USER "username" PASSWORD 'secret';
Rimuovere utente:               DROP USER username;
Uscire dalla sessione:          \q
</code>

===== Creazione di un database e un utente, privilegi =====

Creazione di un database: i file vengono creati nella directory ''/home/postgresql/8.1/main/'' in un formato comprensibile solo a PostgreSQL. I nomi dei database esistenti sono contenuti nella tabella di sistema ''**pg_database**''. Per sapere quali database esistono dal prompt di psql si utilizza il comando breve ''**\l**'' oppure un classico ''**SELECT * FROM pg_database;**''.

<code>
Da riga di comando:  $ createdb prova
Da interprete psql:  template1=# CREATE DATABASE prova;
</code>

Usare un database piuttosto che un altro:

<code>
Da riga di comando:  $ psql prova
Da interprete psql:  template1=# \connect prova
</code>

Eliminazione di un database (e quindi dei file relativi):

<code>
Da riga di comando:  $ destroydb prova
Da interprete psql:  template1=# DROP DATABASE prova;
</code>

Creazione di un utente database, normalmente l'UID in ambiente postgres si fa in modo che sia lo stesso di quello Unix. Gli utenti creati vengono memorizzati nelle tabelle di sistema pg_user e pg_shadow. In questo caso l'utility da riga di comando è migliore perché interattiva.

<code>
Da riga di comando:  $ createuser nome
Da interprete psql:  template1=# CREATE USER "nome";
                     template1=# CREATE USER "nome" PASSWORD 'pippo';
                     template1=# ALTER USER "rubrica" PASSWORD 'pluto';
</code>

Per vedere gli utenti esistenti si può usare l'istruzione SQL: ''**SELECT * FROM pg_user;**''. Per eliminare un utente Postgres si usa:

<code>
Da interprete psql:  template1=# DROP USER "nome";
</code>

Per garantire o rimuovere tutti i privilegi su una tabella ad un particolare utente:

<code>
Da interprete psql:  template1=>GRANT ALL ON tabella TO "nome";
                     template1=>REVOKE ALL ON tabella FROM "nome";
</code>

Per dare i permessi su tutto un database ad un certo utente, si cambia il suo ''**datdba**'' nella tabella ''**pg_database**'' (vedi sopra). Per vedere i permessi attualmente impostati si usa ''**\z**''.

===== I template1 e template0 =====

Quando viene creato un database nuovo viene utilizzato automaticamente come modello il **template1**. Questo significa che ogni modifica effettuata al template (aggiunta di funzioni, linguaggi, ecc.) la troveremo anche nei nuovi database.

Esiste anche il **template0**, questo dovrebbe rimanere una copia vergine di un template vuoto per tutta la vita del database: non si dovrebbe **apportare mai alcuna modifica a template0**.

===== Backup e restore di un database =====

Tre esempi di salvataggio da riga di comando:
  - Salvare tutto un database: struttura dati e dati.
  - Salvare solo lo schema (struttura dati) del database.
  - Salvataggio quando non è richiesta autenticazione e si vuole il dump in formato //custom// (compresso) invece che //plain-text SQL//.
L'opzione ''**-c**'' comporta che nello script SQL generato saranno incluse le istruzione per cancellare gli oggetti database prima di crearli.

<code>
pg_dump -h [hostname] -U [username] -W -c [database] > database.dmp
pg_dump -h [hostname] -U [username] -W -c --schema-only [database] > database-schema.sql
pg_dump -Fc [database] > database.dmp
</code>

Per recuperare il contenuto (il database deve già esistere):

<code>
psql -h [hostname] -U [username] -W [database] -e < database.dmp
</code>

**NOTA:** In caso di upgrade del database con cambio di **major** o **minor** version (le prime due parti del numero di versione) è necessario **fare il dump e il successivo restore** dei dati. Se invece cambia solo il numero di **revision** (il terzo numero), i dati rimangono compatibili e non è necessario fare dump/restore.

==== Dump degli utenti (ruoli) ====

Dump global objects that are common to all databases (pg_dump does not save these objects). This currently includes information about database users and groups, and access permissions that apply to databases as a whole:

<code>
pg_dumpall --clean --globals-only > globals.dump
</code>

This is an example of dump from an 8.4 cluster into a 9.1 cluster:

<code>
pg_dumpall --cluster 8.4/main --clean --globals-only > globals-8.4.dump
psql --cluster 9.1/main < globals-8.4.dump
</code>
===== Backup e restore selettivo =====

A volte non è desiderabile il restore completo di un database, ma solo di alcuni elementi. Ad esempio durante l'upgrade di un database PostGIS, le funzioni PostGIS e la tabella //spatial_ref_sys// si preferisce averle dal nuovo template piuttosto che recuperarle dal vecchio dump. Come fare a preparare velocemente la lista delle cose da recuperare?

Anzitutto si effettua il dump nel formato //custom archive// di PostgreSQL (che tra l'altro è compresso):

<code>
pg_dump -Fc mydb > mydb.dump
</code>

Poi si genera la lista degli oggetti contenuti nel dump:

<code>
pg_restore -l mydb.dump > lista.txt
</code>

La lista ottenuta è facilmente filtrabile con ''grep'' o comunque la si può editare a mano. Alla fine si effettua il restore utilizzando la lista opportunamente scremata come elenco delle cose da recuperare:

<code>
pg_restore --clean -L lista.txt -h 127.0.0.1 -U username -W -d mydb mydb.dump
</code>

Il database ''**mydb**'' deve già esistere prima di lanciare il restore, gli oggetti restorati vengono eventualmente cancellari prima di essere ricreati (''--clean'').

====== Debug di PostgreSQL ======

Per vedere le query SQL eseguite sul server (ad esempio da programmi tipo MapServer e QGIS) si può attivare il debug in ''/etc/postgresql/8.3/main/postgresql.conf'' con queste opzioni:

<file>
log_statement = all
#log_min_messages = debug5
#client_min_messages = debug5
</file>

Altra cosa molto interessante è analizzare il query planner su come vengono eseguite le query, soprattutto per scoprire nested loop, ricerche sequenziali e altre cose che rallentano:

<code sql>
EXPLAIN ANALYZE SELECT
    toponimo,
    wpt
    FROM wpt_comuni_view
        WHERE (residenti > 20000);
</code>

Quando invece viene utilizzato un CURSOR per recuperare i record si dovrebbe poter fare un EXPLAIN ma senza ANALYZE:

<code sql>
BEGIN;
EXPLAIN DECLARE mycursor BINARY CURSOR FOR
    SELECT
        toponimo,
        wpt
        FROM wpt_comuni_view
            WHERE (
                wpt &&
                setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326)
            );
FETCH ALL IN mycursor;
END;
</code>


====== Modificare la data_directory ======

Il pacchetto PostgreSQL di Debian Etch usa dome //data_directory// la ''**/var/lib/postgresql/8.1/main/**''. Pare che non ci siano controindicazioni a spostare tutto in ''**/home/postgresql/**'' creando un link simbolico:

<code>
/var/lib/postgresql -> /home/postgresql/
</code>

Attenzione a mantenere ownership e permission della ''/home/postgresql/''. Non sembra necessario modificare il file di configurazione ''**/etc/postgresql/8.1/main/postgresql.conf**''.






====== Lavorare con gli schemi ======

Come vedere gli schemi esistenti e come impostare una **search_path** tale che trovi le tabelle nei vari schemi.

<code sql>
\connect dbname
\dn
SHOW search_path;
SET search_path TO datirt10k,datirt2k,generici,public;
SHOW search_path;
</code>

È possibile impostare una **''search_path''** predefinita per un utente:

<code sql>
ALTER USER test SET search_path TO "$user",public,foo;
</code>

L'impostazione dell'utente è visibile nella vista ''pg_user'', nel campo ''useconfig''.

Per dare i privilegi di lettura su uno schema e pieni privilegi su un altro:

<code sql>
GRANT USAGE ON SCHEMA datirt10k TO user;
GRANT SELECT ON datirt10k.ac TO user;

CREATE SCHEMA corso AUTHORIZATION user;
GRANT ALL PRIVILEGES ON SCHEMA corso TO user;
</code>

La parola chiave ''AUTHORIZATION'' fa sì che tutte le ''GRANT'' eseguite nello schema siano eseguite a nome dell'utente specificato.

====== Lavorare con le sequenze ======

Come impostare il prossimo valore di una sequenza (dopo questo comando //nextval()// ritornerà 1):

<code sql>
SELECT setval('table_column_seq', 1, false);
</code>

Anche questo comando imposta il prossimo valore usato da una sequenza:

<code sql>
ALTER SEQUENCE table_column_seq RESTART WITH 632;
</code>

Come impostare il campo di una tabella in modo che acquisisca il nextval di una sequenza:

<code sql>
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT nextval('tablename_columnname_seq');
</code>

Per consultare il prossimo valore di una sequenza senza alterarlo:

<code sql>
SELECT * FROM tablename_columnname_seq;
</code>

Per **convertire un campo INT in SERIAL**, prima bisogna scoprire il valore massimo utilizzato nel campo, quindi si crea una sequenza che parte da quel valore + 1:

<code sql>
SELECT MAX(columnname) FROM tablename;
CREATE SEQUENCE tablename_columnname_seq START WITH <max+1> OWNED BY tablename.columnname;
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT nextval('tablename_columnname_seq');
</code>

====== Aggiunta di un linguaggio ======

Per poter creare funzioni in un database bisogna che sia disponibile un linguaggio. Ad esempio prima di creare le funzioni di **PostGIS** bisogna attivare il linguaggio **plpgsql**. Oltre al comando SQL **''CREATE LANGUAGE''** esiste anche l'utility da riga di comando **''createlang''**:

<code>
createlang plpgsql dbname
</code>

Per sapere quali linguaggi sono stati creati in un database:

<code>
createlang --list dbname
</code>

====== Elenco delle funzioni C ======

FIXME
Con il comando **''\df''** non si vedono tutte le funzioni, ad esempio non si vedono quelle 
in **C** e quelle in **plpgsql** (verificare!). Questo un sistema per vederle:

<code sql>
SELECT proname 
    FROM pg_proc proc JOIN pg_language lang
        ON proc.prolang = lang.oid
    WHERE lang.lanname = 'plpgsql';
</code>